styles.numbers【表示形式】モジュール

from openpyxl import Workbook
import datetime

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active

# 数値
ws.column_dimensions['B'].width = 16
VALUE_NUM = 1234567
cell = ws['B2']
cell.value = VALUE_NUM
cell.number_format = '0'

cell = ws['B3']
cell.value = VALUE_NUM
cell.number_format = '#,##0'

cell = ws['B4']
cell.value = VALUE_NUM
cell.number_format = '0000000000'

cell = ws['B5']
cell.value = VALUE_NUM
cell.number_format = '*#0'

format = '#,##0_);[Red](#,##0);-_)'
cell = ws['B6']
cell.value = VALUE_NUM
cell.number_format = format
cell = ws['B7']
cell.value = -VALUE_NUM
cell.number_format = format
cell = ws['B8']
cell.value = 0
cell.number_format = format

# 日付
ws.column_dimensions['C'].width = 20
VALUE_DATE = datetime.datetime(2022, 5, 6)
cell = ws['C2']
cell.value = VALUE_DATE
cell.number_format = 'yyyy/m/d'

cell = ws['C3']
cell.value = VALUE_DATE
cell.number_format = 'yyyy"年"m"月"d"日"'

cell = ws['C4']
cell.value = VALUE_DATE
cell.number_format = 'ggg e"年"m"月"d"日"'

cell = ws['C5']
cell.value = VALUE_DATE
cell.number_format = 'yyyy"年（"ggg[DBNum3][$-411]e"年）"'

# 正数・負数・ゼロ・テキスト
ws.column_dimensions['D'].width = 16
format ='#,##0;[Red]#,##0;"-";* @'
cell = ws['D2']
cell.value = VALUE_NUM
cell.number_format = format

cell = ws['D3']
cell.value = -VALUE_NUM
cell.number_format = format

cell = ws['D4']
cell.value = 0
cell.number_format = format

cell = ws['D5']
cell.value = 'テキスト'
cell.number_format = format

# 条件
ws.column_dimensions['E'].width = 16
format ='[>100]"(>100) "0;[Red][>10]"(>10) "0;"(etc) "0'
cell = ws['E2']
cell.value = 150

cell = ws['E3']
cell.value = 50

cell = ws['E4']
cell.value = 5

for row in ws['E2:E4']:
    for cell in row:
        cell.number_format = format

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.styles import numbers

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
VALUE_NUM = 1234567
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
row = 2
col = 2

# 定数
format = numbers.FORMAT_NUMBER
ws.cell(row, col, format)
cell = ws.cell(row, col + 1, VALUE_NUM)
cell.number_format = format
row += 1

format = numbers.FORMAT_NUMBER_00
ws.cell(row, col, format)
cell = ws.cell(row, col + 1, VALUE_NUM)
cell.number_format = format
row += 1

format = numbers.builtin_format_code(3)
ws.cell(row, col, format)
cell = ws.cell(row, col + 1, VALUE_NUM)
cell.number_format = format
row += 1

format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1
ws.cell(row, col, format)
cell = ws.cell(row, col + 1, VALUE_NUM)
cell.number_format = format

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.styles.numbers import (
    BUILTIN_FORMATS,
    builtin_format_code,
    builtin_format_id,
    is_builtin,
    is_date_format,
    is_datetime,
    is_timedelta_format,
)
import datetime

# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
ws.column_dimensions['A'].width = 2
ws.column_dimensions['B'].width = 4
ws.column_dimensions['C'].width = 60
ws.column_dimensions['D'].width = 20

# 組み込み表示形式
row = 2
for key in BUILTIN_FORMATS.keys():
    format = builtin_format_code(key)
    idx = builtin_format_id(format)
    ws.cell(row, 2, idx)
    ws.cell(row, 3, format)
    cell = ws.cell(row, 4)
    value = 0
    if is_timedelta_format(format):
        value = datetime.timedelta(hours=45, minutes=6, seconds=7)
    elif is_date_format(format):
        type = is_datetime(format)
        if type == 'datetime':
            value = datetime.datetime(2001, 2, 3, 4, 5, 6)
        elif type == 'date':
            value = datetime.date(2001, 2, 3)
        elif type == 'time':
            value = datetime.time(4, 5, 6)
    else:
        if 9 <= idx <= 10:
            value = 0.01234
        elif 12 <= idx <= 13:
            value = 12.34
        elif idx == 49:
            value = 'テキスト'
        else:
            value = -1234567
    cell.number_format = format
    cell.value = value
    if 5 <= idx <= 8 or 41 <= idx <= 44:
        ws.cell(row, 5, 'Local')
    row += 1

# 各種定数
lst = [
    numbers.FORMAT_CURRENCY_EUR_SIMPLE,
    numbers.FORMAT_CURRENCY_USD,
    numbers.FORMAT_CURRENCY_USD_SIMPLE,
    numbers.FORMAT_DATE_DATETIME,
    numbers.FORMAT_DATE_DDMMYY,
    numbers.FORMAT_DATE_DMMINUS,
    numbers.FORMAT_DATE_DMYMINUS,
    numbers.FORMAT_DATE_DMYSLASH,
    numbers.FORMAT_DATE_MYMINUS,
    #numbers.FORMAT_DATE_TIME7,
    #numbers.FORMAT_DATE_TIME8,
    numbers.FORMAT_DATE_TIMEDELTA,
    numbers.FORMAT_DATE_YYMMDD,
    #numbers.FORMAT_DATE_YYMMDDSLASH,
    numbers.FORMAT_DATE_YYYYMMDD2,
    numbers.FORMAT_NUMBER_COMMA_SEPARATED2,
]
row += 2
for format in lst:
    ws.cell(row, 3, format)
    cell = ws.cell(row, 4)
    value = 0
    if is_timedelta_format(format):
        value = datetime.timedelta(hours=45, minutes=6, seconds=7)
    elif is_date_format(format):
        type = is_datetime(format)
        if type == 'datetime':
            value = datetime.datetime(2001, 2, 3, 4, 5, 6)
        elif type == 'date':
            value = datetime.date(2001, 2, 3)
        elif type == 'time':
            value = datetime.time(4, 5, 6)
    else:
        value = -1234.56
    cell.number_format = format
    cell.value = value
    row += 1

# その他関数
print(is_builtin(numbers.FORMAT_DATE_TIME3))
# 出力：True
print(is_builtin(numbers.FORMAT_DATE_DATETIME))
# 出力：False

# ワークブック保存
wb.save(WORKBOOK)