openpyxlの使い方　Excelの差し込みがワンクリックで

pandasとopenpyxlの違い

pandasは、Excelファイルの読み書きもできますが、本来の⽤途はPythonの中で表データを編集することです。Excelへの書き出しはオマケ的な機能なので、列幅やフォントなどの書式は設定できません。そのため、pandasで作成したExcelファイルはデータが列幅からはみ出してしまい、あとで調整が必要になってしまいます。

Excelファイルを扱う専⽤のパッケージに、openpyxlがあります。これを利⽤すると、セルへの書式設定やワークシートの追加といったVBAのような操作ができます。書式設定済みのExcelファイルを開いて、書式を崩さずにデータを追記することも可能です。

今回はCSVなどから変換したような表からデータを抜き出し、書式のみが設定された表に差し込むプログラムを作成します。

Excelのサンプルファイルは下のボタンからダウンロードしていただけます。

サンプルファイル

サンプルコード

pyxl.py

import pandas as pd

import openpyxl

import datetime

df= pd.read_excel("旅行スケジュール.xlsx")

workbook=openpyxl.load_workbook("旅行スケジュール（原本）.xlsx")

sheet=workbook.active

sheet["C1"]=datetime.date.today()

row_index=3

for index,rows in df.iterrows():

    col_index=1

    for data in rows:

        sheet.cell(row=row_index,column=col_index,value=data)

        col_index=col_index+1

    row_index=row_index+1

workbook.save(“旅行スケジュール（完成版）.xlsx")

解説

openpyxlの使い⽅はpandasとはかなり異なっていて、どちらかといえばExcel VBA（Excel内蔵のマクロ⾔語）に似ています。

ワークブック（ファイル）を開いてワークシートを選び、セル番地を指定してセルを編集します。ですのでExcel VBAの経験がある⼈なら馴染みやすいです。

openpyxlはExcelを使わずに直接Excelファイルを操作するので、VBAより実⾏速度が速いです。また、複数のExcelファイルの操作もやりやすいですよ。

書式未設定の元データが⼊った「旅⾏スケジュール.xlsx」は、これまで同様 pandasで取りいます。 read_excel 関数で読み込み、DataFrameオブジェクトの形で変数dfに⼊れておきます。

書式が設定された「旅⾏スケジュール（原本）.xlsx」はopenpyxlのload_workbook関数で読み込みます。そして、取得したWorkbookオブジェクトのactive属性で、ファイル内で選択中のワークシートを取得します。

セルC1に今⽇の⽇付を⼊⼒しています。その前までの処理で変数sheetにWorksheetオブジェクトが⼊っているので、「sheet[“C1”]」で⽬的のセルを参照できます。datetime.date.today()はPythonで今⽇の⽇付を取得する記述なので、それを代⼊するとセルC1に今⽇の⽇付が⼊ります。

for index,rows in df.iterrows():

はデータを転記する2重ループです。今回は追加先がopenpyxlで操作しているExcelファイルとなります。

WorkSheetオブジェクトのcellメソッドでExcelファイルに追加しています

cellメソッドでは引数row、引数columnでセルの位置を指定し、引数valueに設定したい値を指定します。⽇付の⼊⼒に使った「sheet[“C1”]」という書き⽅に⽐べると、⾏と列を数値で指定するので、繰り返し処理に向いています。