Pythonライブラリ(Excel操作)：Openpyxl

2021年11月14日 21:55

概要

OpenpyxlはExcel操作をするライブラリです。

過去にPandasの記事でExcelのようなテーブルデータを使用しましたが、私の使い分けは下記の通りです。
ちなみにあくまでPythonと連動させたExcel処理であればopenpyxlでよいですが、Excelファイルだけで完結できるならVBAの方がいいと思います個人的な意見ですが。

下表はライブラリ比較表ですが体系的ではないため参考までに。

１．Excelブックの新規作成：pyxl.Workbook()

Excelファイルの作成は下記で作成可能です。

[In]
import openpyxl as pyxl

book = pyxl.Workbook() #新しいワークブックを作成
sheet = book.active #現在アクティブなシートを取得
sheet["A1"] = 'sheet["A1"]でA1セルに値を書き込む'
book.save('note用ファイル.xlsx')

２．Excelシートの処理

シートの追加、コピー、シート名変更は下記の通りです。

[In]
sheet_add = book.create_sheet('追加シート') #末尾に新しいシートを作成->引数無しだとSheet名は自動作成
sheet_copy = book.copy_worksheet(sheet_add) #追加シートにシートをコピー
sheet_copy.title = 'コピーしたシート'
book.save('note用ファイル.xlsx')

　シートの移動、削除は下記の通りです。

[In]
book.move_sheet(sheet_add, offset=-1) #追加シートを左へ一つ移動
book.remove(book['コピーしたシート'])
book.save('note用ファイル_編集後.xlsx')

３．Excelファイルの読み込み

先ほど作成したすでに作成済みのExcelファイルを読み込んでセルの値を取得します。Sheetは1.アクティブ、2.順番、3.シート名から選択できます。

[In]
filepath = 'note用ファイル.xlsx'
book = pyxl.load_workbook(filepath) # ワークブックを読み込む
sheet = book.worksheets[0] # シートを取得
# sheet = book['Sheet'] # シート名からシートを取得
cell = sheet['A1'] # A1セルを取得
print(cell, type(cell), cell.coordinate) #cell情報 
print(cell.value) # A1セルの値を表示

[Out]
<Cell 'Sheet'.A1> <class 'openpyxl.cell.cell.Cell'> A1
sheet["A1"]でA1セルに値を書き込む

３－１．読み込み専用（データ上書き無し）：read_only=True

読み込みだけなら引数にread_onlyを入れると時間短縮できます。※本記事では書き込みするためread_onlyにしてコードをコピペするとエラーがでます。

pyxl.load_workbook(filepath, read_only=True) # 読み込み専用でワークブックを読み込む

３－２．数式が入っているファイル：data_only=True

　下記のように数式が入っているファイルの場合そのままだと数式の文字列を取得します。もし値が欲しい場合は引数にdata_only=Trueを渡します。

[In]
filepath= 'note_計算式込み.xlsx'
book_cal = pyxl.load_workbook(filepath) # ワークブックを読み込む
sheet = book_cal.active # シートを取得
rows = sheet.iter_rows() #全データ取得
for idx, row in enumerate(rows):
   print(f'{idx+1}行目', [cell.value for cell in row], end= ' ')
   
book_cal = pyxl.load_workbook(filepath, data_only=True) # ワークブックをデータのみで読み込む
sheet = book_cal.active # シートを取得
rows = sheet.iter_rows() #全データ取得
for idx, row in enumerate(rows):
   print(f'{idx+1}行目', [cell.value for cell in row], end= ' ')

[Out]
1行目 ['値１', '値２', 'A+B', 'A×B'] 2行目 [1, 5, '=A2+B2', '=A2*B2'] 3行目 [2, 6, '=A3+B3', '=A3*B3'] 4行目 [3, 7, '=A4+B4', '=A4*B4'] 5行目 [4, 8, '=A5+B5', '=A5*B5'] 
1行目 ['値１', '値２', 'A+B', 'A×B'] 2行目 [1, 5, 6, 5] 3行目 [2, 6, 8, 12] 4行目 [3, 7, 10, 21] 5行目 [4, 8, 12, 32]

３－３．マクロ入りファイル.xlsm：keep_vba=True

　マクロファイル拡張のxlsmをそのまま読み込むとファイルが破損するため、引数のkeep_vba=Trueが必要です。

[In]
filepath_macro = 'VBA練習用.xlsm' # マクロ入りxlsmファイル->自分で適当に作成
book_macro = pyxl.load_workbook(filepath, keep_vba=True) # ワークブックを読み込む
book_macro.save(filepath_macro) # マクロ入りxlsmファイルを上書き保存

[Out]　xlsmは破損せずもともとのマクロも使用可能

４．Excelファイルの書き込み

ファイルへの書き込みはシート(row=行番号, column=列番号)でセルを取得して、読み込み同様にvalueで値を追加します。なおExcelへ反映させるにはファイルを上書き保存する必要があります。

[In]
sheet.cell(row=2, column=1).value = 'sheet.cell(row=2, column=1).valueでA2セルに値を書き込む'
sheet.cell(row=3, column=1).value = 'sheet.cell(row=3, column=1).valueでA3セルに値を書き込む'

book.save(filepath) #ファイルを上書き保存

最終行にデータを追加する場合はsheet.append(リスト)となります。

[In]
import string

filepath2 = 'note_appendで書き込み.xlsx'
book2 = pyxl.Workbook()
sheet2 = book2.active
sheet2.append(['A', 'B', 'C']) #シートの最終行に追加※新規ファイルのため1行目
sheet2.append(['1', '2', '3']) #シートの最終行に追加
sheet2.append([i for i in string.ascii_uppercase]) #シートの最終行に追加

book2.save(filepath2)

５．ファイル情報取得

　Excelのシート名称やセル情報を取得します。

[In]
print(book.sheetnames) # Excelファイル内の全シート名を表示
print(sheet.title) # シート名を表示
print('sheet.max_row：', sheet.max_row, 'sheet.max_column：', sheet.max_column) # シート内の最大行と列を表示
print(cell.coordinate, cell.row, cell.column, cell.column_letter) # セル情報を表示
print('number format', cell.number_format, 'datatype=',cell.data_type) # セル情報を表示

[Out]
['Sheet']
Sheet
sheet.max_row： 3 sheet.max_column： 1
A1 1 1 A
number format General datatype= s

６．データの一括抽出

　まず初めにデータの一括抽出の説明用に下記ファイルを作成しました。

[In]
book = pyxl.Workbook() #新しいワークブックを作成
sheet = book.active #現在アクティブなシートを取得
for row in range(1, 11):
   for col in range(1, 11):
       sheet.cell(row=row, column=col).value = sheet.cell(row=row, column=col).coordinate 
book.save('note_pyxlcoordinate.xlsx')

データ一括抽出は(for文を除いて)①セル範囲選択、②iter_rows()があります。

６－１．データ抽出１：sheet[範囲]

　データ範囲を選択して取得すると各行のデータをTupleで取得できます。for文などで1行取得してその中のセルデータを抽出します。

[In]
filepath_cor = 'note_pyxlcoordinate.xlsx'
book_cor = pyxl.load_workbook(filepath_cor) # ワークブックを読み込む
sheet = book_cor.active
rows = sheet['B2': 'D4'] #型式はTuple
print(rows)

for row in rows:
   print([cell.value for cell in row])

[Out]
((<Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>), (<Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>), (<Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>))

['B2', 'C2', 'D2']
['B3', 'C3', 'D3']
['B4', 'C4', 'D4']

６－２．データ抽出２：sheet.iter_rows()

　データ範囲を選択して取得するとイテラブルなオブジェクトfor文で値が取得できる変数を返します。for文などで1行取得してその中のセルデータを抽出します。
出力がリストなのでsheet.append()で別シートに追記もできます。

[In]
filepath_cor = 'note_pyxlcoordinate.xlsx'
book_cor = pyxl.load_workbook(filepath_cor) # ワークブックを読み込む
sheet = book_cor.active
rows = sheet.iter_rows(min_row=2, min_col=2, max_col=4, max_row=4) #B2からD4までのセルを取得
print(rows)

for row in rows:
   print([cell.value for cell in row])

[Out]
<generator object Worksheet._cells_by_row at 0x000002ADD955BEB0>

['B2', 'C2', 'D2']
['B3', 'C3', 'D3']
['B4', 'C4', 'D4']

なおiter_rows()は引数無しでも使用でき、その場合は全データ抽出します。

[In]
rows = sheet.iter_rows() #全データ取得
for row in rows:
   print([cell.value for cell in row])    
[Out]   
1行目 ['A1', 'B1', 'C1', 'D1', 'E1', 'F1', 'G1', 'H1', 'I1', 'J1'] 2行目 ['A2', 'B2', 'C2', 'D2', 'E2', 'F2', 'G2', 'H2', 'I2', 'J2'] 3行目 ['A3', 'B3', 'C3', 'D3', 'E3', 'F3', 'G3', 'H3', 'I3', 'J3'] 4行目 ['A4', 'B4', 'C4', 'D4', 'E4', 'F4', 'G4', 'H4', 'I4', 'J4'] 5行目 ['A5', 'B5', 'C5', 'D5', 'E5', 'F5', 'G5', 'H5', 'I5', 'J5'] 6行目 ['A6', 'B6', 'C6', 'D6', 'E6', 'F6', 'G6', 'H6', 'I6', 'J6'] 7行目 ['A7', 'B7', 'C7', 'D7', 'E7', 'F7', 'G7', 'H7', 'I7', 'J7'] 8行目 ['A8', 'B8', 'C8', 'D8', 'E8', 'F8', 'G8', 'H8', 'I8', 'J8'] 9行目 ['A9', 'B9', 'C9', 'D9', 'E9', 'F9', 'G9', 'H9', 'I9', 'J9'] 10行目 ['A10', 'B10', 'C10', 'D10', 'E10', 'F10', 'G10', 'H10', 'I10', 'J10']

【罫線や塗りつぶしの扱い】
Excelシート内に罫線など体裁を整える処理が入っている場合セルデータが空でもopenpyxlはデータ有とみなしてその行も取得します。不要の場合はデータ範囲を引数で選択するかif文で空データvalue=Noneを処理します。

７．関数式の入力

Excelファイルで計算をチェックする場合上司からExcelでチェックしたいといわれた場合はExcelファイルに数式を残しておきたいです。３章で使用したexcelファイルを使用して関数式を追加します。数式はExcelの関数を文字列で記載します。

[In]
filepath= 'note_計算式込み.xlsx'
book = pyxl.load_workbook(filepath) # ワークブックを読み込む
sheet = book.active # シートを取得
sheet.cell(row=1, column=5).value = '合計値(A-D)' #項目名称を記載
sheet.cell(row=2, column=5).value = '=SUM(A2:D2)' #項目名称を記載
book.save('note_計算式込み_関数追加.xlsx')

上記を繰り返し処理する場合の一つの手法は下記の通りです。

[In]
filepath= 'note_計算式込み.xlsx'
book = pyxl.load_workbook(filepath) # ワークブックを読み込む
sheet = book.active # シートを取得
sheet.cell(row=1, column=5).value = '合計値(A-D)' #項目名称を記載
rows = sheet.iter_rows(min_row=2) #2行目移行を取得

for row in rows:
   cell_A, cell_D = row[0].coordinate, row[3].coordinate #行の先頭から3列目までを取得
   sheet.cell(row=row[0].row, column=5).value = f'=SUM({cell_A}:{cell_D})' #項目名称を記載
   print(cell_A, cell_D, f'=SUM({cell_A}:{cell_D})')
   
book.save('note_計算式込み_関数追加.xlsx')

８．スタイルの修正

openpyxlではセルの表示形式・書式、色、塗りつぶし、罫線、配置などを修正できます。

[In]
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import Font
from openpyxl.styles import PatternFill

book = pyxl.load_workbook('note_計算式込み_関数追加.xlsx') # ワークブックを読み込む
sheet = book.active # シートを取得

sheet['A2'].number_format = '0.00' #A2セルを数値形式に設定

columns = sheet.iter_cols(min_col=5) #E列移行を取得※今回はE列のみ
for column in columns:
   for cell in column:
       cell.font = Font(name='ＭＳ ゴシック', size=12, bold=True, color='FF0000') #フォント設定
       cell.alignment = Alignment(horizontal='center', vertical='center') #セルの位置設定
       cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) #セルの輪郭設定
       cell.fill = PatternFill(fill_type='solid', start_color='FFCCFFCC', end_color='FFCCFFCC') #セルの色設定


book.save('note_計算式込み_関数追加.xlsx')

より細かいスタイル設定は公式ドキュメントをご参照ください。

９．グラフの作成(Linechart)

MatplotlibやSeabornでも可視化グラフ化可能ですがExcelファイルで見て操作したい人もいる上のおじさんとかのでOpenpyxlでグラフを作成します。
【使用データ・参照元】
使用ファイルはbitflyerの仮想通貨データをcsv->xlsxにしました。

９－１．コード紹介

　BTCとETCのトレンドを同じグラフ上にプロットしました。

[In]
import openpyxl as pyxl
from openpyxl.chart.axis import DateAxis
from openpyxl.chart import LineChart, Reference

filepath = 'Prices_2021_10_20211104.xlsx'
book = pyxl.load_workbook(filepath) # ワークブックを読み込む
sheet = book.active # シートを取得


#データ情報を参照
dates = Reference(sheet, min_col=1, min_row=2, max_col=1, max_row=sheet.max_row) #A列のデータを参照
prices_BTC = Reference(sheet, min_col=2, min_row=1, max_col=2, max_row=sheet.max_row) #カラム情報も使用するため1行目から取得
prices_ETC = Reference(sheet, min_col=3, min_row=1, max_col=3, max_row=sheet.max_row) #型=><class 'openpyxl.chart.reference.Reference'>


#折れ線グラフ(Line Chart)の作成
linechart = LineChart() #LineChartクラスのインスタンスを作成


#チャートのサイズ設定
linechart.title = 'Bitcoinの価格トレンド' #チャートタイトル
linechart.style = 14 #グラフのフォントサイズ
linechart.width, linechart.height = 15, 10 #グラフのサイズ設定

#X・Y軸設定
linechart.y_axis.title = '価格' #Y軸タイトル
linechart.x_axis.title = '日付' #X軸タイトル
linechart.x_axis.number_format = 'yyyy/m/d h:mm;@' # X軸の値を日付形式で表示
linechart.x_axis.majorTimeUnit = "days" # X軸の値を日付形式で表示
linechart.x_axis.majorTickMark = "in" # X軸のメモリの向き
linechart.y_axis.majorTickMark = "in" # Y軸のメモリの向き

#データの追加
linechart.add_data(prices_BTC, titles_from_data = True)
linechart.add_data(prices_ETC, titles_from_data = True)
linechart.set_categories(dates) #add_data後に設定すること

#シートにグラフを追加
sheet.add_chart(linechart, 'A35')

#LineのStyle修正
s1 = linechart.series[0] #Lineの1つ目のデータを取得※BTC
s1.marker.symbol = "triangle" #マーカー追加
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.solidFill = "FF0000" #色設定->赤色
s1.graphicalProperties.line.width = 20000 #太さ設定->サイズはEMUsで指定


s2 = linechart.series[1] #Lineの1つ目のデータを取得※BTC
s2.graphicalProperties.line.solidFill = "0000ff" #色設定->青色
s2.graphicalProperties.line.dashStyle =  'dash' #線種設定->破線
s2.graphicalProperties.line.width = 20000 #太さ設定->サイズはEMUsで指定


book.save('グラフ追加ファイル.xlsx')

１０．参照用コード／パッケージ（自分用）

　Excelの体裁まで調整するとOpenpyxlは比較的コード量が多くなるため、モジュール/パッケージ化して使いやすいような形にすることを推奨します。
　パッケージのイメージ及び使用方法は下記の通りです。

[フォルダ構成]
.
├── note用ノートブック.ipynb
└── utils_pyxl
    ├── __init__.py
    └── pyxl_format.py


[使用時のイメージ]
from utils_pyxl
from utils_pyxl.pyxl_format import set_IntFormat

１０－１．体裁調整

　コード内に下記のような機能を入れた関数を記載しました。

【機能一覧】
●列幅を文字長さに合わせて自動調整
●数値（整数、小数）の表示形式を設定
●罫線・色・塗りつぶし・フォントなどの設定

[pyxl_format.py]
import openpyxl as pyxl
from openpyxl.styles.alignment import Alignment
from openpyxl.styles.borders import Border, Side
from openpyxl.styles import Font
from openpyxl.styles import PatternFill

#列幅を自動調整
def adjust_colwidth(sheet):
    for col in sheet.columns: 
        max_length = 0
        column = col[0].column_letter #列記号を取得(A,B,C...)

        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].width = adjusted_width #列幅を設定

#設定|配置：中央、フォント:12,MSPゴシック,青色、罫線:細線
def set_BoaderFormat1(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center') #セルの中央寄せ
    cell.font = Font(size=12, name='ＭＳ Ｐゴシック', color='0070C0') #フォントの設定 color='0070C0'で青色
    cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) #罫線の設定

#設定|配置：中央、フォント:12,MSPゴシック,青色、罫線:細線(左右),破線(上下)、塗りつぶし:黄色
def set_BoaderFormat2(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center') #セルの中央寄せ
    cell.font = Font(size=12, name='ＭＳ Ｐゴシック', color='0070C0') #フォントの設定 color='0070C0'で青色
    cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='dotted'), bottom=Side(style='dotted')) #罫線の設定
    cell.fill = PatternFill(patternType='solid', fgColor='FFFFE0') #塗りつぶしの設定 color='FFFFE0'で黄色

#set_BoaderFormat2()の塗りつぶし色違い(水色)
def set_BoaderFormat3(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center') #セルの中央寄せ
    cell.font = Font(size=12, name='ＭＳ Ｐゴシック', color='0070C0') #フォントの設定 color='0070C0'で青色
    cell.border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='dotted'), bottom=Side(style='dotted')) #罫線の設定
    cell.fill = PatternFill(patternType='solid', fgColor='E0FFFF') #塗りつぶしの設定 color='E0FFFF'で水色


#数値設定：0桁コンマ区切り
def set_IntFormat(cell):
    cell.number_format = '#,##0' #数値のフォーマット

#数値設定：1桁コンマ区切り
def set_FloatFormat(cell, digit):
    cell.number_format = f'#,##0.{"0"*digit}' #数値のフォーマット

参考資料

あとがき

機械学習には関係ないけど業務効率化の仕事が入ってきたので先に落とし込みをしておきたかった。
何とか1日で作成できてよかったけどGithub Copilot様のおかげだった。

この記事が気に入ったらサポートをしてみませんか？