openpyxlで数値の表示フォーマットを指定する

Introduction

ここに以下のようなデータファイルtest.xlsxがあります。あるとき、「このデータのcalc_AFのデータ列を見やすくするために、小数点以下3桁までだけを表示するようにしてほしい」と頼まれました。

この作業をopenpyxlで自動化するにはどうしたらよいか、というのが今回のお題です。

実行スクリプトとその実行結果

以下に、上述の願いを叶えるスクリプトを示します。

sheet_numberformat.py

'''
    sheet_numberformat.py
    purpose: read xlsx and set number format automatically
'''

import openpyxl as xl
from openpyxl.utils import column_index_from_string


# set input file name
inputfile = 'test.xlsx'

# read input xlsx
wb1 = xl.load_workbook(filename=inputfile)
ws1 = wb1.worksheets[0]

# write in sheet
for row in ws1:
    for cell in row:
        col = column_index_from_string(cell.column)

        if ws1.cell(row = 1, column = col).value == 'calc_AF':
            cell.number_format = '0.000'

# save xlsx file
wb1.save(inputfile)

実行結果は以下のようになります。

calc_AF列の値の情報はそのままに、表示フォーマットが変更されていることがわかります。

スクリプト詳細

cell.number_format

cell.number_format = '0.000'

で、セルの値の表示を小数点3桁まで表示するようにしています。

if文がない場合の結果

for文内でif文

if ws1.cell(row = 1, column = col).value == 'calc_AF':

を用いて、calc_AFの列のみにnumber_formatを適用しています。このif文を外すとどうなるでしょうか。以下にその結果を示します。

整数で大きな値が入っていたPOS列にまで'0.000'フォーマットが適用され、正しく表示できなくなっていることがわかります。

POS列に違うフォーマットを適用させる

POS列の整数を見やすくするために、3桁毎にカンマ区切りで表示するようにしてみましょう。そのためfor文内に以下のelif文を追加します。

elif ws1.cell(row=1, column=col).value == 'POS':
    cell.number_format = '#,##0'

#,##の部分で3桁毎にカンマ区切り、0の部分で小数点以下を表示しない、という設定を行なっています。結果は以下のようになります。

from openpyxl.styles import numbers

from openpyxl.styles import numbers

numbersモジュールをインポートすることで、openpyxlがデフォルトで備え持つ表示フォーマットを使うことができるようになります。以下にnumbersで指定できる定数をご紹介します。

FORMAT_GENERAL
FORMAT_TEXT
FORMAT_NUMBER
FORMAT_NUMBER_00
FORMAT_NUMBER_COMMA_SEPARATED1
FORMAT_NUMBER_COMMA_SEPARATED2
FORMAT_PERCENTAGE
FORMAT_PERCENTAGE_00
FORMAT_DATE_YYYYMMDD2
FORMAT_DATE_YYMMDD
FORMAT_DATE_DDMMYY
FORMAT_DATE_DMYSLASH
FORMAT_DATE_DMYMINUS
FORMAT_DATE_DMMINUS
FORMAT_DATE_MYMINUS
FORMAT_DATE_XLSX14
FORMAT_DATE_XLSX15
FORMAT_DATE_XLSX16
FORMAT_DATE_XLSX17
FORMAT_DATE_XLSX22
FORMAT_DATE_DATETIME
FORMAT_DATE_TIME1
FORMAT_DATE_TIME2
FORMAT_DATE_TIME3
FORMAT_DATE_TIME4
FORMAT_DATE_TIME5
FORMAT_DATE_TIME6
FORMAT_DATE_TIME7
FORMAT_DATE_TIME8
FORMAT_DATE_TIMEDELTA
FORMAT_DATE_YYMMDDSLASH
FORMAT_CURRENCY_USD_SIMPLE
FORMAT_CURRENCY_USD
FORMAT_CURRENCY_EUR_SIMPLE

使用するときはnumbers.FORMAT_DATA_TIME1のように記述します。