OpenPyXLでセルの書式設定を変更

OpenPyXLはPythonからExcelを操作するパッケージですが、今回はOpenPyXLでセルの書式設定を変更する方法を紹介します。

例えばExcelで「01」という値をセルに入力すると、デフォルトのセルの書式設定では数値として認識され「1」となってしまいます。

これをそのまま「01」と入力したければ、セルの書式設定を文字列にしておく必要があります。

このほか数値の小数点以下の桁数を指定したり、パーセンテージの書式に変更したい場合もあるでしょう。

このようにデフォルトの書式設定以外を使いたいければ明示的にセルに書式設定が必要です。これはPythonのOpenPyXLを使った場合も同じです。

セルの書式を文字列に設定する

OpenPyXLからセルに書式設定するのは簡単です。次のようにOpenPyXLのCellオブジェクトのnumber_formatプロパティに表示形式を表す文字列を代入するだけです。

<Cellオブジェクト>.number_format = <表示形式文字列>

セルの書式を文字列に設定する例でこれをみてみましょう。次のコードを実行すると、A1セルの書式が文字列に設定されます。

import openpyxl

wb =openpyxl.Workbook()
ws = wb.active
cell = ws["A1"]
cell.number_format = openpyxl.styles.numbers.FORMAT_TEXT
wb.save('セルの書式を文字列に設定.xlsx')

コードを実行した後にA1セルの書式設定を確認すると、文字列に設定されていることがわかります。

この例でセルに書式設定を変更しているコードは次の行です。

cell.number_format = openpyxl.styles.numbers.FORMAT_TEXT

このようにCellオブジェクトのnumber_formatプロパティに表示形式を表す文字列を代入するだけです。

ここではopenpyxl.styles.numbersモジュールにあらかじめ定義された定数FORMAT_TEXTを代入しています。この定数の値は単なる「’@’」という文字列です。

代入できる値はあらかじめ定義された定数に限られません。number_formatプロパティにはExcelの表示形式を表す文字列を自由に設定できます。

表示形式を表す文字列とは、Excelの「表示形式」の「ユーザー定義」などに設定する文字列のことです。以下の図を参照してください。

例えば次のようにnumber_formatプロパティに設定すると、数値を小数点1桁まで表示します。

import openpyxl

wb =openpyxl.Workbook()
ws = wb.active
cell = ws["A1"]
cell.number_format = "0.0"
cell.value = 123.456
wb.save('セルの書式を数値に設定.xlsx')

上記のコードを実行するとA1セルは「123.5」と表示されます。

Excelの書式記号の意味

Cellオブジェクトのnumber_formatプロパティに設定するときの、Excelの書式記号が使えることは前述しましたが、その書式記号のいくつかをここでて簡単に説明します。

すべての記号についてはExcelのヘルプを参照してください。

数値の書式記号

書式記号	説明
#	1桁の数字を示す。#の桁数が表示されるが余分な0は表示しない
0	1桁の数字を示す。0の桁数は常に数字が表示される
,	数値の桁を区切る

日付の書式記号

書式記号	説明
yy	西暦の下2桁を表示します
yyyy	西暦を4桁で表示します
m	月を表示します
mm	1桁の月には0をつけて2桁で表示します
d	日にちを表示します
dd	1桁の日にちには0をつけて2桁で表示します

曜日の書式記号

書式記号	説明
aaa	漢字で曜日の頭文字（日〜土）を表示します
aaaa	漢字で曜日（日曜日〜土曜日）を表示します

時刻の書式記号

書式記号	説明
h	時刻（0〜23）を表示
hh	1桁の時刻には0をつけて時刻（00〜23）を表示
m	分（0〜59）を表示
mm	1桁の分は0をつけて分（00〜59）を表示
s	秒（0〜59）を表示
ss	1桁の秒は0をつけて秒（00〜59）を表示

時間の書式記号

書式記号	説明
[h]:mm	24時間を超える時間の合計を表示します
[mm]:ss	60分を超える分の合計を表示
[ss]	60秒を超える秒の合計を表示

openpyxl.styles.numbersモジュールで定義されている他の書式

openpyxl.styles.numbersモジュールでは、いくつかの書式が定数としてあらかじめ定義されています。先程の例では文字列の定数を使いましたが、そのほかにいくつかの書式をここで試してみます。

コードは後に記載しますが、実際に実行したExcelの結果をまずご覧ください。

この結果のそれぞれの列の意味は次の通りです。

• A列はopenpyxl.styles.numbersモジュールでの定義です。辞書の要素が代入されているものがありますが、その辞書もnumbersモジュールで定義されています。
  
  numbersモジュールのソースコードはSource code for openpyxl.styles.numbersで参照できますので、ソースコードと見比べるとわかりやすいでしょう。
• B列は定数の値です。例えばFORMAT_TEXTの値は’@’です。
• C列は実際にExcelのセルにB列の書式を設定して、セルに値を入力した結果です。

日付・時刻の値を入力する

セルの書式設定とは直接関係ありませんが、書式設定した後にセルに日付や時刻を設定する方法をちょっと説明します。

OpenPyXLでセルに日付や時刻の値を入力するときは、Pythonの日付や時刻を扱う次のオブジェクトを使います。

• datetime.datetimeオブジェクト（日付と時刻）
• datetime.dateオブジェクト（日付）
• datetime.timeオブジェクト（時刻）

これ以外、例えば「’2022/1/23’」のような文字列でセルに値を設定しても書式設定した形式で正しく表示されないことがあるので注意してください。

Excelは日付や時刻を「シリアル値」で管理していますので、日付や時刻をシリアル値を設定することもできます。

書式サンプルのソースコード

先程のExcelのサンプルは次のコードを実行すれば再現できます。

import datetime
import openpyxl
from openpyxl.styles import numbers

# openpyxl.styles.numbersで定義された定数の辞書
GENERAL = {
  numbers.FORMAT_GENERAL:
  "FORMAT_GENERAL = BUILTIN_FORMATS[0]" 
}
TEXT = { 
  numbers.FORMAT_TEXT:
  "FORMAT_TEXT = BUILTIN_FORMATS[49]"
}
NUMBER = {
  numbers.FORMAT_NUMBER:
  "FORMAT_NUMBER = BUILTIN_FORMATS[1]",
  numbers.FORMAT_NUMBER_00:
  "FORMAT_NUMBER_00 = BUILTIN_FORMATS[2]",
  numbers.FORMAT_NUMBER_COMMA_SEPARATED1:
  "FORMAT_NUMBER_COMMA_SEPARATED1 = BUILTIN_FORMATS[4]",
  numbers.FORMAT_NUMBER_COMMA_SEPARATED2:
  "FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-'"
}
PERCENTAGE = { 
  numbers.FORMAT_PERCENTAGE:
  "FORMAT_PERCENTAGE = BUILTIN_FORMATS[9]",
  numbers.FORMAT_PERCENTAGE_00:
  "FORMAT_PERCENTAGE_00 = BUILTIN_FORMATS[10]"
}
DATE = {
  numbers.FORMAT_DATE_YYYYMMDD2:
  "FORMAT_DATE_YYYYMMDD2 = 'yyyy-mm-dd'",
  numbers.FORMAT_DATE_DATETIME:
  "FORMAT_DATE_DATETIME = 'yyyy-mm-dd h:mm:ss'",
  numbers.FORMAT_DATE_TIME1:
  "FORMAT_DATE_TIME1 = BUILTIN_FORMATS[18]",
  numbers.FORMAT_DATE_TIME2:
  "FORMAT_DATE_TIME2 = BUILTIN_FORMATS[19]",
  numbers.FORMAT_DATE_TIME3:
  "FORMAT_DATE_TIME3 = BUILTIN_FORMATS[20]",
  numbers.FORMAT_DATE_TIME4:
  "FORMAT_DATE_TIME4 = BUILTIN_FORMATS[21]",
  numbers.FORMAT_DATE_TIMEDELTA:
  "FORMAT_DATE_TIMEDELTA = '[hh]:mm:ss'"
}

# それぞれの項目を表示する列
ITEM_COL   = 1
FORMAT_COL = 2
STYLE_COL  = 3

# セルへ入力する値
DATETIME = datetime.datetime(2022, 1, 23, 1, 23, 45)
NUM      = 1234.56789

# ITEM_COL列、FORMAT_COL列、STYLE_COL列にそれぞれ
# 定義、定数の値、書式設定サンプルを設定する
def print_format(ws, row, dict, cell_value):
  
  for k, v in dict.items():
    item_cell = ws.cell(row=row, column=ITEM_COL)
    item_cell.value = v
    format_cell = ws.cell(row=row, column=FORMAT_COL)
    format_cell.number_format = numbers.FORMAT_TEXT
    format_cell.value         = k
    styel_cell = ws.cell(row=row, column=STYLE_COL)
    styel_cell.number_format = k
    styel_cell.value         = cell_value
    row += 1
    
  return row

# ヘッダテキストを設定する
def print_header(worksheet, row, text):
  worksheet.cell(row=row, column=ITEM_COL, value=text)
  return row + 1

wb =openpyxl.Workbook()
ws = wb.active
cur_row = 1

cur_row = print_header(ws, cur_row, '標準')
cur_row = print_format(ws, cur_row, GENERAL, NUM)
    
cur_row = print_header(ws, cur_row, 'テキスト')
cur_row = print_format(ws, cur_row, TEXT, NUM)

cur_row = print_header(ws, cur_row, '数値')
cur_row = print_format(ws, cur_row, NUMBER, NUM)
    
cur_row = print_header(ws, cur_row, 'パーセンテージ')
cur_row = print_format(ws, cur_row, PERCENTAGE, NUM)
    
cur_row = print_header(ws, cur_row, '日付')
cur_row = print_format(ws, cur_row, DATE, DATETIME)
    
wb.save('書式設定テスト.xlsx')

まとめ

OpenPyXLで書式設定するにはCellオブジェクトのnumber_formatプロパティに書式文字列を設定するだけで、意外と簡単でした。

ただし書式設定には直接関係ありませんが、OpenPyXLで日付や時刻はPythonのdatetimeモジュールを使うことは覚えておきましょう。