PythonからExcelを操作するためのライブラリ「openpyxl」で、Excelファイルの読み込みを行い、操作する方法を解説します。
使用ライブラリ
インストール：必要
外部ライブラリのopenpyxlを使うとPythonでExcelファイル（.xlsx）を読み書きすることができます。
ライブラリのインストールとインポート
コマンドプロンプトを起動してpipでopenpyxlライブラリをインストールします。
pipの使い方はこちらの記事を参照してください。
・関連記事：Pythonライブラリのインストール(pipの使い方)
Successfully installed openpyxl-*.*.* と表示されればインストールは成功です。
インストールが完了したら openpyxl をインポートします。
1 | import openpyxl |
openpyxlの使い方
openpyxl は、Excelファイル（xlsx/xlsm/xltx/xltm）の読み書きを行うためのライブラリです。
操作を行うためには、openpyxlのメソッドでワークブックを開き、どのシート、どのセルに操作を行うのかを指定する必要があります。
エクセルマクロの記述とよく似ている為、マクロを利用した事がある方はイメージが湧くと思います。
サンプルファイル
Excelファイルは以下のsample.xlsxを使用します。ファイルはpythonスクリプトファイルと同じフォルダに保存されているものとします。
ワークブックを操作する
ワークブックの新規作成、既存ブックの読み込み、保存操作が可能です。
ワークブックを新規作成
ブックを新規で作成するには wrokbook() メソッドを使用します。引数は不要です。
1 | wb = openpyxl.workbook() |
ワークブックを読み込む
既存のExcelファイルを読み込むには openpyxl.load_workbook() でExcelファイルのパスを指定します。
1 2 3 | wb = openpyxl.load_workbook('sample.xlsx') print(wb.sheetnames) #['2020', '2021'] |
ワークブックを保存する
ブックを保存するには save() メソッドを使用して、引数はファイルのパスを指定します。
上書き保存する場合は、読み込んだファイルと同名を指定します。
1 2 3 4 5 | #上書き保存 wb.save('sample.xlsx') #別名で保存 wb.save('sample_test.xlsx') |
ワークブックを閉じる
ブックを閉じるには close() メソッドを使用します。
1 | wb.close() |
ワークシート操作
ワークシートの取得
ワークブックオブジェクトから[シート名]でワークシートオブジェクトを取得できます。
インデックス番号を指定して取得することもできます。インデックスは左端が0になります。
1 2 3 4 5 | #シート名を指定して取得 ws = wb['2020'] #先頭のシートを取得（先頭のインデックス番号は0） ws = wb.worksheets[0] |
アクティブシートを選択するには active プロパティで選択できます。
1 | ws = wb.active |
ワークシート名を取得する
読み込んだワークブックのシート名を取得するには sheetnames プロパティを使用します。
結果はリストになります。
・関連記事：リスト（配列）
1 2 | print(wb.sheetnames) # ['2020', '2021'] |
ワークシートを追加する
ワークシートを追加するには create_sheet() メソッドで、引数に追加するシート名と追加位置を指定します。
最後尾にシートを追加する場合は位置の指定は不要です。
1 2 3 | ws_2022 = wb.create_sheet(title='2022') print(wb.sheetnames) # ['2020', '2021', '2022'] |
位置を指定して追加する（第二引数で位置＝インデックスを指定）
1 2 3 | ws_new = wb.create_sheet(title='Sheet_New', index=1) print(wb.sheetnames) # ['2020', 'Sheet_New', '2021', '2022'] |
インデックスは先頭が０となり、最後尾に追加する場合は引数の省略可。
ワークシートをコピーする
ワークシートをコピーするには copy_worksheet() メソッドを使用します。引数でコピー元のワークシートを指定します。
コピーしたシート名は「元のシート名 Copy」となる。コピー先の位置の指定はできません。
1 2 | ws_copy = wb.copy_worksheet(wb['2022']) # ['2920', 'Sheet_New', '2021', '2022', '2022 Copy'] |
ワークシートを削除する
ワークシートを削除するには remove() を使用します。引数にはコピー元のワークシートオブジェクトを指定します。複数のシートを一度に削除することはできません。
1 2 3 | wb.remove(ws_new) print(wb.sheetnames) # ['Sheet1', 'Sheet2', 'Sheet3', 'Sheet2 Copy'] |
シート名を指定して削除したい場合は del を使用します。
1 2 3 | del wb['2022 Copy'] print(wb.sheetnames) # ['2020', '2021', '2022'] |
複数のワークシートを削除する
複数シートを削除するには、ブック内のシートをfor 文で一つずつ取り出して、削除したいシートのリストと照合し、マッチした場合は削除します。
1 2 3 4 5 6 7 | del_sheets = ['Sheet_New', '2022 Copy'] for sheetName in wb.sheetnames: if sheetName in del_sheets: del wb[sheetName] print(wb.sheetnames) #['2020', '2021', '2022'] |
ワークシート名を変更する
ワークシート名を変更するには、ワークシートオブジェクトの title プロパティを指定します。
1 2 3 | ws_2022.title = '2022rename' print(wb.sheetnames) # ['2020', '2021', '2022rename'] |
ワークシートのインデックス番号を取得する
ワークシートのインデックス番号は index() メソッドで、ワークシートを引数に指定して取得します。
1 2 3 4 5 6 7 | num = wb.index(ws_copy) print(num) # 3 num = wb.index(wb['2020']) print(num) # 0 |
セル操作
セル値の取得
ワークシートオブジェクトから［’A1’］のようなセル指定文字列でセルオブジェクトを取得し、属性 value でセル値を取得できます。
1 2 3 | cell = ws['B3'] print(cell.value) #8.3 |
cell() メソッドで行番号(row)、列番号(column)を指定して取得することも可能です。
行番号はExcelの行番号の数字をそのまま使い、列番号はA＝1, B＝2・・・と数値に読み換えて指定します。
row=, column= は省略も可能です。
1 2 3 4 5 6 7 | cell = ws.cell(row=3, column=2) print(cell.value) #8.3 cell = ws.cell(3, 2) print(cell.value) #8.3 |
セル値の書き込み・削除
セルに値を書き込むには、セルオブジェクトの属性 value に値を設定します。
入力した値を実際のファイルに反映させるには、ワークブックを保存する必要があります。
ワークシートオブジェクト.cell(row = 行番号, column = 列番号).value = 値
ワークシートオブジェクト.cell(row = 行番号, column = 列番号, value = 値)
1 2 3 4 5 6 7 8 9 10 11 | ws['B14'].value = 10.0 print(ws['B14'].value) # 10.0 ws.cell(row=14, column=2).value = 11.0 print(ws['B14'].value) # 11.0 ws.cell(row=14, column=2, value=12.0) print(ws['B14'].value) # 12.0 |
値に 'None' を設定することで、セル値を削除する事が出来ます。
1 2 3 4 5 6 7 | ws['B14'].value = 'None' print(ws['B14'].value) # None ws.cell(row=14, column=2, value='None') print(ws['B14'].value) # None |
数式を書き込む
数式を書き込むには、数式の文字列を指定して書き込みます。
1 2 3 4 | c_form = ws['B14'] c_form.value = '=AVERAGE(B2:B13)' print(c_form.value) # =AVERAGE(B2:B13) |
ワークブックを保存して開くと式が入力されて計算されている事が確認できます。
1 | wb.save(sample_test.xlsx) |
保存したファイルをopenpyxlで開き直して数式を入力したセルの値を取得しても、計算結果の数値を取得することはできません。これはopenpyxlが文字列をそのまま取得するようになっているためです。
1 2 3 4 5 | wb = openpyxl.load_workbook('sample_test.xlsx') ws = wb_test['2020'] c = ws_test['B14'] print(c.value) # =AVERAGE(B2:B13) |
計算結果を取得するには、一度手動でワークブックをエクセルアプリで開いて上書き保存する必要があります。その上で、openpyxlでブックを開く際に引数に data_only=True を指定することで取得できるようになります。（openpyxlに再計算させる手段は無いようです）
1 2 3 4 5 6 7 | # エクセルアプリで開いて上書き保存した後に実行 wb_data = openpyxl.load_workbook('sample_test.xlsx', data_only=True) ws_data = wb_data['2020'] c_data = ws_data['B14'] print(c_data.value) # 16.53333333333333 |
セルのアドレスを確認する
セルのアドレス（C1R1形式）は、セルオブジェクトの coordinate プロパティで取得できます。
1 2 3 | c = ws.cell(row=4, column=1) print(c.coordinate) # A4 |
行列番号、列名はセルオブジェクトの row, column, column_letter で取得できます。
セルオブジェクト.row
列番号
セルオブジェクト.column
列名
セルオブジェクト.column_letter
1 2 3 4 5 6 7 8 9 10 11 | # 行番号 print(c.row) # 4 # 列番号 print(c.column) # 1 # 列名 print(c.column_letter) # A |
繰り返し処理
１シートずつ繰り返す
ワークブックオブジェクトをfor文で処理すると１シートずつ取得することができます。
1 2 3 4 | for ws in wb: print(ws.title) #シート名を出力 #2020 #2021 |
１行ずつ繰り返す
sample.xlsxのsheet1 A1：D13のデータを１行ずつ読み込みます。
ワークシートオブジェクトの rows プロパティでシートの行を取得し、for文でループ処理することで１行ずつ読み込めます。
以下の例では読み込んだデータを１行ずつリストに追加しています。リストについては下記の記事を参照してください。
・関連記事：リスト
1 2 3 4 5 6 7 8 9 10 11 | for row in ws.rows: #１行ずつ繰り返し data = [] #データ格納用リストを準備 for cell in row: #行内のセルを繰り返し data.append(cell.value) #リストにセルのデータを追加 print(data) #['年月日', '平均気温(℃)', '最高気温(℃)', '最低気温(℃)'] #[datetime.datetime(2020, 1, 1, 0, 0), 7.1, 18.6, 0.6] #[datetime.datetime(2020, 2, 1, 0, 0), 10, 18.2, -2.1] ・・・・・ #[datetime.datetime(2020, 12, 1, 0, 0), 7.7, 17.2, -0.6] |
読み込む範囲を指定する場合には iter_rows() メソッドを利用します。
次の例ではヘッダーを飛ばして２行目から読み込みます。
1 2 3 4 5 6 7 8 9 10 11 | #2行目からセルの値を取得 for row in ws.iter_rows(min_row=2): #２行目以降を１行ずつ繰り返し data = [] #データ格納用リストを準備 for cell in row: #行内のセルを繰り返し data.append(cell.value) #リストにセルのデータを追加 print(data) #[datetime.datetime(2020, 1, 1, 0, 0), 7.1, 18.6, 0.6] #[datetime.datetime(2020, 2, 1, 0, 0), 8.3, 18.2, -2.1] ・・・・・ #[datetime.datetime(2020, 12, 1, 0, 0), 7.7, 17.2, -0.6] |
以下の例では２行目から１０行目、２列目から３列目までを読み込みます。
1 2 3 4 5 6 7 8 9 10 11 | #２行目から１０行目、２列目から３列目までセルの値を取得 for row in ws.iter_rows(min_row=2, max_row=10, min_col=2, max_col=3): data = [] #データ格納用リストを準備 for cell in row: #行内のセルを繰り返し data.append(cell.value) #リストにセルのデータを追加 print(data) #[7.1, 18.6] #[8.3, 18.2] ・・・・・ #[24.2, 35.1] |
全ワークシートのデータを取得する
シートの繰り返し処理と１行ずつの繰り返し処理を入れ子にします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | for ws in wb: print(ws.title) #ワークシート名を出力 for row in ws.rows: data = [] #データ格納用リストを準備 for cell in row: #行内のセルを繰り返し data.append(cell.value) #リストにセルのデータを追加 print(data) #2020 #[datetime.datetime(2020, 1, 1, 0, 0), 7.1, 18.6, 0.6] #[datetime.datetime(2020, 2, 1, 0, 0), 8.3, 18.2, -2.1] ・・・・・ #[datetime.datetime(2020, 12, 1, 0, 0), 7.7, 17.2, -0.6] #2021 #[datetime.datetime(2021, 1, 1, 0, 0), 5.4, 18.7, -2.4] #[datetime.datetime(2021, 2, 1, 0, 0), 8.5, 21.9, -0.5] ・・・・・ #[datetime.datetime(2021, 12, 1, 0, 0), 8.1, 20.3, -2.2] |
openpyxlを使ってExcelファイルを処理する方法を解説しました。
行の高さ・列幅設定
行の高さを変更する
row_dimensions[]で変更する行番号を指定します。
1 | ws.row_dimensions[1].height = 10 |
列の幅を変更する
column_dimensions[]で変更する列名を指定します。
1 | ws.column_dimensions['A'].width = 25 |
配置設定
文字の配置を設定する：横位置
設定を行うためには、stylesパッケージのalignmentモジュールを読み込みます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | from openpyxl.styles import Alignment c = ws['A1'] # 左揃え c.alignment = Alignment(horizontal='left') # 右揃え c.alignment = Alignment(vertical='right') # 中央揃え c.alignment = Alignment(horizontal='center') # 標準 c.alignment = Alignment(horizontal='general') # 均等割り付け c.alignment = Alignment(horizontal='distributed') # 両端揃え c.alignment = Alignment(horizontal='justify') # 選択範囲内で中央 c.alignment = Alignment(horizontal='centerContinuous') # 繰り返し c.alignment = Alignment(horizontal='fill') |
文字の配置を設定する：縦位置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # 上揃え c.alignment = Alignment(vertical='top') # 均等割り付け c.alignment = Alignment(vertical='distributed') # 両端揃え c.alignment = Alignment(vertical='justify') # 中央揃え c.alignment = Alignment(vertical='center') # 下揃え c.alignment = Alignment(vertical='bottom') |
文字を折り返して全体を表示
1 | c.alignment = Alignment(wrap_text=True) |
文字を縮小して全体を表示する
1 | c.alignment = Alignment(shrink_to_fit=True) |
セルの書式設定
フォント設定
stylesパッケージのFontモジュールを読み込みます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | from openpyxl.styles import Font # フォント種類 ws['A1'].font = Font(name='Meirio UI') # フォントサイズ ws['A1'].font = Font(size=14) # 太字 ws['A1'].font = Font(bold=True) # イタリック ws['A1'].font = Font(italic=True) # フォント色 ws['A1'].font = Font(color='FF0000') # アンダーライン ws['A1'].font = Font(underline='single') |
フォント設定で、複数のプロパティを設定したい場合は、１行で記載しないと最後の設定のみが有効になるため注意が必要です。
上記の場合はフォント色の設定のみ反映され、種類とサイズは初期値にリセットされます。
複数の設定を行いたい場合は以下のように記述します。
1 | ws['A1'].font = Font(name='Meirio UI', size=14, bold=True, color='00B0F0', italic=True, underline='single') |
背景色の設定
stylesパッケージのPatternFillモジュールを読み込みます。
bgColor、fgcolor を使用する場合です。bgColorだけでは背景色が正常に変更できない為、fgcolor もセットで指定する必要があります。
1 2 3 | from openpyxl.styles import PatternFill sheet['A1'].fill = PatternFill(patternType='solid', fgColor='FF0000', bgColor='FF0000') |
start_colorでも設定可能です。
1 | ws['A1'].fill = PatternFill(start_color='FF0000', fill_type = 'solid') |
・関連記事： pandasでExcelファイルの読み込み(read_excel)
・関連記事： pandasでExcelファイルの書き込み(to_excel)
・関連記事：VBAマクロからPythonを実行する（xlwings）