Python使ってExcel出力
こんにちは。松山担当のpopoです。
前回、PythonのPandasライブラリを使って簡単な集計を試してみたのですが、あれから結構Pandas使うようになりました。
popoの業務では、方々に散らばっているDBや様々な部門お手製のExcel帳票などからデータを収集して分析することが多いのですが、Pythonを使えばデータをまとめて加工できるので便利です。
ただ、PandasのExcel出力ではあっさりしすぎているので、Excelで開いて背景色付けたり罫線付けたりしながら報告文書にまとめてました。そんな時です。「PythonでExcelの加工って出来ないんだっけ?」と思い立ったのは。
早速ネットを漁ると「OpenPyXL 」ってのが良さそうです。と言う事で今日は「OpenPyXL 」を使ってPythonからExcelのワークブックの編集をしてみようと思います。
まずは目標!
今回の目標は「綺麗なExcelを自動で作る。」です。
Panadasのto_excel()で出力されるExcelではなく、背景色や罫線で飾られた綺麗なExcelを作りたいと思います。
PandasのExcel出力はこんなんです。タイトルだけ強調されたなんともあっさりした帳票になります。
今回は、背景色付けたり、文字色変えたり、グルーピングしたりして綺麗なExcel帳票を作ることを目的とします。
前準備
まずは、pipでopenpyxlをインストールします。
PS C:\> pip openpyxl
Excelに出力するデータは既にPandasで加工された後という体で、DataFrameを作ります。
import pandas as pd
data = pd.DataFrame({'販売店':['伊勢店', '伊勢店', '伊勢店', '伊勢店 小計', '出雲店', '出雲店', '出雲店', '出雲店 小計', '靖国店', '靖国店', '靖国店', '靖国店 小計', '総計'],
'商品' :['おふだ', 'おみくじ', 'お守り', '', 'おふだ', 'おみくじ', 'お守り', '', 'おふだ', 'おみくじ', 'お守り', '', ''],
'1月1日':[6321, 176, 1140, 7637, 6018, 273, 101, 6392, 2847, 2203, 3939, 8989, 23018],
'1月2日':[323, 27, 507, 857, 297, 4, 101, 402, 2598, 1874, 2736, 7208, 8467],
'1月3日':[192, 78, 341, 611, 48, 81, 84, 213, 2341, 1049, 3092, 6482, 7306]})
では早速、openpyxlを触っていきましょう。
DataFrameをExcelに書き込む
openpyxlでは、Workbookと言う関数でワークブックを生成し、アクティブにしたワークシートに対して操作を行う様です。
[https://openpyxl.readthedocs.io/en/stable/tutorial.html]
今回、PandasのDataFrameをワークシートに書き込むのに openpyxlのユーティリティであるdataframe_to_rows()を使ってみます。
dataframe_to_rows()では、DataFrameの行をリスト型として表現したジェネレータに変換してくれます。今回はenumerate関数を使ってオフセットを持たせ、セルひとつづつを書き込んでみます。
[http://openpyxl.readthedocs.io/en/stable/pandas.html#working-with-pandas-dataframes]
また、ワークシートへの書き込みは、cell関数を使います。cell関数には行列と書込む情報を指定するようです。
[https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.cell]
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
# Excelワークブックの生成
wb = Workbook()
ws = wb.active
ws.title = '2020年'
rows = dataframe_to_rows(data, index=False, header=True) # openpyxlのユーティリティを使用
# ワークシートへデータを書き込む
row_start_idx = 3
col_start_idx = 2
for row_no, row in enumerate(rows, row_start_idx):
for col_no, value in enumerate(row, col_start_idx):
ws.cell(row=row_no, column=col_no, value=value) # 1セルづつ書込む
ws.cell(row=1, column=1, value='■Excel出力のサンプル') # タイトル
Excelのスタイル
今回のExcelでは、背景色、罫線、フォントに関して飾りつけを行っていきたいと思います。これらの修飾は cellの各々の属性にオブジェクトをセットする形で実現されています。
[https://openpyxl.readthedocs.io/en/stable/styles.html]
・背景色
背景色は、openpyxl.styles.fills.PatternFillオブジェクトで表現します。PatternFillオブジェクトはopenpyxl.styles.PatternFill関数で生成できます。タイトル行、小計行、総計行用のPatternFillオブジェクトを作ってみましょう。
from openpyxl.styles import PatternFill
FILL_TITLE = PatternFill(patternType='solid', fgColor='B0E0E6')
FILL_SUBTOTAL = PatternFill(patternType='solid', fgColor='FFE4B5')
FILL_TOTAL = PatternFill(patternType='solid', fgColor='8B4513')
・罫線
罫線は、openpyxl.styles.borders.Sideオブジェクトで表現します。Sideオブジェクトはopenpyxl.styles.Side関数で生成します。
Cellオブジェクトに設定するのは、openpyxl.styles.borders.Borderオブジェクトです。Borderオブジェクトは上下左右の罫線の情報を持ったオブジェクトで、openpyxl.styles.Border関数で生成できます。
from openpyxl.styles import Border, Side
SIDE_NORMAL = Side(border_style="thin", color='000000')
BORDER_NORMAL = Border(top=SIDE_NORMAL, left=SIDE_NORMAL, right=SIDE_NORMAL, bottom=SIDE_NORMAL)
・フォント
フォントは、openpyxl.styles.fonts.Fontオブジェクトで表現します。Fontオブジェクトはopenpyxl.styles.Font関数で生成します。
タイトル行と総計行用のFontオブジェクトを作ってみましょう
from openpyxl.styles import Font
FONT_BOLD = Font(bold=True)
FONT_BOLD_WHITE = Font(bold=True, color='FFFFFF')
その他の設定
スタイル以外にもウィンドウ枠の固定とワークシートの表示倍率の設定を行っておきます。
ws.freeze_panes = 'A3' # ウィンドウ枠の固定
ws.sheet_view.zoomScale = 90 # ワークシートの表示倍率
列幅の設定
列幅はワークシートのcolumn_dimensions[]に設定します。
ws.column_dimensions['A'].width = 3 # A列の列幅
表へスタイルを設定する
では、表にスタイルを設定していきたいと思います。途中で列幅と数字の表示形式も設定していきましょう。
まずは、タイトル行を取得して1セルづつスタイルを設定していきます。二重ループは、list関数を使ってターゲットになっているタイトルセルの列を1セルづつ処理していきます。
行に対してスタイルを設定する為、タイトルが’販売店’の列を処理する時に対象の行番号(小計行と総計行の行番号)をリストに保持しておきます。また、小計行でも総計行でもない行はrow_dimensions[]にアウトラインのレベルが1のRowDimensionオブジェクトを設定してグループ化が実現できるようにしています。
リストに保持された行番号の場合に、背景色とフォントを設定しています。罫線はすべてのセルに設定しています。また、type(cell.value) is int でセルの値が数字かを確認し、数字の場合は書式の設定も行っています。
最後に列ごとにセルに書かれている文字数の最大を保持しておき、column_dimensions[].widthで設定しています。
column_dimensionsに指定する列を表す文字列は、get_column_letter関数を使って列番号から変換して利用しています。
subtatal_list = []
tatal_list = []
header = ws[row_start_idx]
for header_cell in header:
if header_cell.column < col_start_idx:
continue
# タイトルのスタイル設定
header_cell.fill = FILL_TITLE
header_cell.border = BORDER_NORMAL
header_cell.font = FONT_BOLD
max_len = 3
row_count = 0
for cell in list(ws.columns)[header_cell.column - 1]: # 列ごとに処理を行う
row_count = row_count + 1
if row_count <= row_start_idx: continue if header_cell.value == '販売店':
if str(cell.value).find('小計') >= 0:
subtatal_list.append(row_count)
elif str(cell.value).find('総計') >= 0:
tatal_list.append(row_count)
else:
# アウトラインのレベル設定
ws.row_dimensions[row_count] = RowDimension(ws, index=1,
outline_level=1, hidden=False)
# 小計行のスタイル設定
if row_count in subtatal_list:
cell.fill = FILL_SUBTOTAL
# 総計行のスタイル設定
if row_count in tatal_list:
cell.fill = FILL_TOTAL
cell.font = FONT_BOLD_WHITE
# 罫線は全てのセルに設定
cell.border = BORDER_NORMAL
# セルの値が数値の場合は書式を設定
if type(cell.value) is int:
cell.number_format = '#,##0'
# 列ごとの最大文字数を保持
value_len = len(str(cell.value))
max_len = value_len if value_len > max_len else max_len
# 列ごとの最大文字数から列幅を設定
max_len = (max_len + 1) * 2
ws.column_dimensions[get_column_letter(header_cell.column)].width = max_len # 列幅
ワークブックの保存
saveでExcelファイルを保存します。
wb.save('sample.xlsx')
最後に
今回はopenpyxlを使って綺麗なExcelを作ってみました。オブジェクトの関係は Excel VBAと同じような感じで理解しやすかったです。
ただ、Pythonでやる必要があるのか?と問われると回答に窮するところではありますが、これからもopenpyxl、ぼちぼち使っていこうかと思います。