きっかけ

業務でエクセルにあるこんなデータを読み込んで、別のシートに書き出すといった作業をしたので備忘録程度に記しておきます。

openpyxlに慣れる導入にもなるかと思うので、良ければ試してみてください！

実際は行も列も数倍大きいです
エクセルってcsvやjsonファイルと違って、読み込みが複雑そうな印象でしたが、実際に人手で開いて作業するイメージと同じ感覚でコードを書くことができました。なお、公式ドキュメント読めればいいって人はこちら。

インストール

インストールは簡単でpipで入れられます。

pip install openpyxl

エクセルファイルのロード

パスを指定して読み込むだけです。workbookとは作業するエクセルファイルを指しています。ファイルの中にはシート毎にデータが書き込まれていますよね。そのシートの集まりがbookです。なお、data_onlyとはデータのみ読み込みます。Falseにするとセルに記載された数式(関数)が読み込まれます。その他のパラメーターについては公式ドキュメントを参照してください。

wb = xp.load_workbook('test.xlsx', data_only=True)

シートの選択

作業したいシートを選択します。人手で作業する時もシートを選ぶかと思いますが、同じ感覚です。シートの選択は先ほど読み込んだworkbookに対してシート名を指定するだけでいけます。

ws = wb[Sheet1]

表形式データを読み込む

列名は省いてデータ部分のみ読み込みます(下記画像のA2:C3部分)。今回はlist in listの形で読み込むようにしていますが、割と自由度高めでいけるかなと思っています。

rows = []
for row in ws[f'A2:C{ws.max_row}']:
    values = []
    for cell in row:
        values.append(cell.value)
    rows.append(values)

print(rows)
>[[1, 'Shota', 'Japan'], [2, 'James', 'America']]

2行目でセル範囲を指定して、それ以降はイテレーションさせているだけです。
セル範囲指定部分の列部分をハードコーディングしていますが、ws.max_columnなどを使用していけば取得できると思います(実装が複雑で読みづらくなるかと思い、省略しました)。

シートへ書き込む

読み込んだ後、煮るなり焼くなり好きにして、それをまたエクセルに書き出したい時があるかと思います。

新しいシートの作成

まずは書き出したいシートを用意します。読み込んだシートに上書きしたい場合は、読み込む時に使用したオブジェクト(上記コードではws)で問題ありません。

sheet = wb.create_sheet(index=1, title='Sheet2')
indexはシートの順番(0始まり)、titleはシート名です。

新しいエクセルファイルを作成して、書き出したい時は次のように書きます。

wb = xp.Workbook()
sheet = wb.active

列名の書き出し

列名を書きます(エクセル画像のA1:C1部分)。なお、enumerateに第２引数を渡すと、その数値から始まります。

labels = ['No.', 'Name', 'Country']
for i, label in enumerate(labels, 1):
    sheet.cell(
        row=1,
        column=i,
        value=label
    )

列名部分に色を塗る

必要に応じて、列名とぱっと見分かるよう色を塗りましょう。塗る際はPatternFillモジュールを使います。

fill = PatternFill(patternType='solid', fgColor='ccff00')
for row in sheet:
    for cell in row:
        sheet[cell.coordinate].fill = fill

fgColorで指定している色コードを変えれば、他の色に変えられます。

データの書き出し

ラベル部分の書き出し済んだら、今度はデータを書き出していきましょう。書き方は大体、ラベル書き出す時と同じです。


for y, row in enumerate(rows, 1):
    for x, cell in enumerate(row, 1):
        sheet.cell(
            row=y+1,
            column=x,
            value=cell
        )

罫線を引く

大体、エクセルの表形式のデータって罫線引かれている気がします(偏見でしょうか？)。ひとまず罫線を引いておきましょう。罫線を引く時は、Border, Sideモジュールを使います。


from openpyxl.styles.borders import Border, Side

side = Side(style='thin', color='000000')
border = Border(top=side, bottom=side, left=side, right=side)
for row in sheet:
    for cell in row:
        sheet[cell.coordinate].border = border

列幅の調整

文字列の多いデータをセルに書き込むこともあるかと思います。列幅を自動調整してくれた方が、あとでエクセルファイル確認する時などに楽だと思うので記載しておきます。

for col in sheet.columns:
    max_length = 0
    column = col[0].column
    column_name = col[0].coordinate[0]
    for cell in col:
        if len(str(cell.value))>max_length:
            max_length = len(str(cell.value))
    adjusted_width = (max_length+2)*1.2
    sheet.column_dimensions[column_name].width = adjusted_width

保存

最後はワークブックを保存すれば完了です。