| ライブラリ | openpyxl ※pandasのto_excel内でも使われている。 |
(import
pandas as pd) pd.read_excel() pd.to_excel() |
xlwings (import xlwings as xw) | xlrd（黒字）, xlwt（オレンジ） ※pandas内でも使われている。 |
xlsxwriter ※pandasのto_excel内でも使われている。 |
|
| 参考サイト | https://gammasoft.jp/support/how-to-use-openpyxl-for-excel-file/ | read_excel | https://ysko909.github.io/posts/edit-excel-with-python-and-xlwings/ | https://note.nkmk.me/python-xlrd-xlwt-usage/ | https://www.python-izm.com/third_party/excel/xlsxwriter/xlsxwriter_write/ | |
| 公式 | to_excel | アドイン化 | 公式 | |||
| at_iat_loc_iloc | 公式の翻訳版 | |||||
| サポート拡張子 | .xlsx ※.xls非サポート |
.xlsx, .xls ※read_cvs, to_csvなら.csvも扱える。 |
.xlsx | .xlsx, .xls (xlwtは.xlsのみ対応) |
.xlsx | |
| データの定義 | - | df = pd.DataFrame([[11, 21, 31], [12, 22, 32], [31, 32, 33]], index=['one', 'two', 'three'], columns=['a', 'b', 'c']) |
- | - | - | |
| ブック操作 | 新規作成 | wb = openpyxl.Workbook() | df.to_excel(''Sample.xlsx",
sheet_name="Sheet1") ※データdfの定義が先。 ※ファイルがなければ新規作成 |
wb = xw.Book() | wb1 = xlwt.Workbook() | wb = xlsxwriter.Workbook('Sample.xlsx') |
| 保存 | wb.save("Sample.xlsx") | df.to_excel(''Sample.xlsx",
sheet_name="Sheet1") ※データdfの定義が先。 ※ファイルが存在する場合は上書き保存 |
wb.save("Sample.xlsx") | wb1.save("Sample.xls") ※このwbはxlwtのみ対応。xlrdしたwbのsaveは非対応。 ※add_sheet後に保存可 |
wb.close() ※既存ファイル名を指定すると上書き保存 |
|
| 既存Excelファイルの読み込み | wb = openpyxl.load_workbook("Sample.xlsx") ※ワークブック型として読み込む。 |
df = pd.read_excel('Sample.xlsx', sheet_name=0) ※sheet_name引数を省略すると一番左のシート内データが読み込まれる。 |
wb = xw.Book("Sample.xlsx") ※実行するとExcelが開く。 |
wb = xlrd.open_workbook('Sample.xlsx') | - | |
| with
pd.ExcelWriter("Sample.xlsx", engine="openpyxl",
mode="a") as writer: ※read_excelはデータを読み込むのに対し、ExcelWriterは実質wbオブジェクト(データを書き込む対象とするexcelファイル)を定義する。 ※engineはxlsxwriterも使用可 ※mode="a"で追記モードが使える。 |
||||||
| シート操作 | シート取得(シート名で) | ws = wb["Sheet1"] ・・・(A) ※データだけではなくシートそのもの(データ込み)を定義するイメージ。 |
df = pd.read_excel('Sample.xlsx', sheet_name="Sheet1") ※シートオブジェクトではなく、DataFrameオブジェクト（シート内のデータ）の取得になる。 ※pandasではwbはパス指定だけで直にシートの中身を見に行く。 |
ws = wb.sheets["Sheet1"] | ws = wb.sheet_by_name('Sheet1') | - |
| シート取得(番号で) | ws = wb.worksheets[0] | df = pd.read_excel('Sample.xlsx', sheet_name=0) | ws = wb.sheets[0] | ws = wb.sheet_by_index(0) | - | |
| 複数シートの中身取得 | - | df_sheet_multi = pd.read_excel('Sample.xlsx', sheet_name=[0,
"sheet2"]) ※indexもしくはシート名で取得。それらをkeyとしてシートの中身がvalueの辞書. |
- | - | - | |
| 複数シートから取得した中身の読み出し | - | df_sheet_multi[0] df_sheet_multi["sheet2"] |
- | - | - | |
| 全シートの中身取得 | - | ws_all = pd.read_excel('Sample.xlsx', sheet_name=None) ※None指定により全シート一括取得になる。 ※sheet_nameを指定しないとindex0のシートが選択される。 |
- | ws = wb.sheets() | - | |
| wb内シート名リストの取得 | ws_list = wb.sheetnames | - | - | - | ||
| シート名の取得 | ws.title | - | - | - | ||
| シート名変更 | (A)を前提として ws.title = "Sheet2" |
- | - | - | ||
| 指定シートを指定wb内にコピー（末尾に） | ws2 = wb.copy_worksheet(wb["Sheet2"]) ※("ws2 ="の部分はなくてもよい) |
with pd.ExcelWriter('Sample.xlsx') as writer: df.to_excel(writer, sheet_name='sheet1') df2.to_excel(writer, sheet_name='sheet2') ※事前にdf, df2を用意 |
- | - | ||
| シート追加 | ws3 = wb.create_sheet(title="Sheet3") | with pd.ExcelWriter('Sample.xlsx') as writer: df.to_excel(writer, sheet_name='sheet1_copy') |
ws = wb.add_sheet("Sheet2") wb.save("Sample.xls") ※xlsのみ対応。 |
ws = wb.add_worksheet('Sheet3') | ||
| シート追加（指定indexに） | ws3 = wb.create_sheet(title="Sheet3", index=2) | ※上記ブック保存参照 | - | - | ||
| 指定シートの削除 | wb.remove(ws3) | - | - | - | ||
| 末尾のシートの削除 | wb.remove(wb.worksheets[-1]) | - | - | - | ||
| 行・列幅指定 | - | - | - | ws.set_row(3, 50)
※3行目を幅50 ws.set_column("A:A", 50) |
||
| セル操作 | １セルオブジェクトの取得(１) | c1 = ws["A1"] | - | - | c1 = ws.cell(1,2) | - |
| １セルオブジェクトの取得(2) | c1 = ws.cell(row=1, column=1) | - | c1 = ws.cell(1,2) | - | ||
| 1行／１列分のセルオブジェクト取得 | row = ws[2] ※0ではなく1から始まるインデックスのリスト。 |
row = ws.row(2) column = ws.col(3) |
- | |||
| １行内の複数セルオブジェクト取得 | rows = ws[1:2] ※0ではなく1から始まるインデックスのリスト。 |
- | ||||
| 複数行分のセルオブジェクト取得 | cell_range = ws['A1':'C2'] | - | - | - | ||
| １行／１列分の値取得 | - | df.loc["行名"] df["列名"] |
ws.row_values(2) ws.col_values(1) |
- | ||
| セルアドレス取得 | c1.coordinate | - | - | - | ||
| セルの行番号取得 | c1.row | - | - | - | ||
| セルの列番号取得 | c1.column | - | - | - | ||
| 列アルファベット取得 | c1.column_letter | - | - | - | ||
| index/column値の取得 | - | df.index.values df.columns.values |
- | - | ||
| １セルの値の読み取り | val1 = c1.value | df.at["行名", "列名"] <-１セルのみ df.iat[行数, 列数] <-１セルのみ df.loc["行名", "列名"] <-複数行列可能 df.iloc[行数, 列数] <-複数行列可能 |
ws.range("A1").value | セルオブジェクトから： c1.value シートオブジェクトから直接： ws.cell_value(1.2) |
- | |
| １セルの値の書き込み | c1.value = 12000 | df.at["行名", "列名"] = 値 df.loc["行名", "列名"] = 値 |
ws.range("A1").value = 1 | ws.write(行, 列, "値") ※xlsのみ対応 |
ws.write(行, 列, "値") ws.write("A1", "値") ※xlsxも対応 |
|
| 複数セルの値の読み取り | for文対応 | df.loc["開始行名":"終了行名", "列名"] df.loc[:"終了行名", ["列１", "列２"]] |
ws.range("A1").expand("table").value ※選択セルの上下左右に隣接するセルたちからなるテーブル範囲を自動で取得 |
- ※リスト内包表記と組み合わせれば可能。 |
||
| 複数セルの値の書き込み | for文対応 | ws.range("A1").value = [[1,2],[3,4]] | ||||
| 数式の書き込み | c1.value = "=SUM(C1:C5)" | - | ws.write(行, 列, "xlwt.Formula(sum(B2:B3))") | ws.write('B5', '=sum(B2,B3,B4)') | ||
| 書式設定 | セルの書式設定(1) | ws["B2"].number_format = "0.00" ws["A2"].number_format = "yyyy年mm月dd日" |
- | - | - | |
| セルの書式設定(2) | from openpyxl.styles import Font ws["C2"].font = Font(bold=True, italic=True) |
- | - | format = wb.add_format() format.set_bold() format.set_font_color('blue') format.set_font_name('Arial') ws.write('A1', '値', format) |
||
| 罫線追加 | side = Side(style='thin', color='000000') border = Border(top=side, bottom=side, left=side, right=side) sheet["A1"].border=border |
- | - | format = wb.add_format() format.set_bottom() format.set_top(1) #細実線 format.set_left(2) #中太実線 format.set_right(3) #長破線 ws.write('C3', '値', format) |
||