【牌】Pythonで華麗にExcelをイジる@openpyxl
PythonでExcel（xlsx）をイジる機会がありましたので、メモがてら記事にします。
openpyxlというパッケージを利用しますが、ネット上に沢山記事があるのでなんともですが。自分なりに・・ね。
（；^ω^）
Excelライセンスを所持していないので、LibreOffice Calcで開発しました。心配無用でした。
ちなみに、openpyxlのバージョンは、3.0.5です。頻繁に機能改良が行われてるので仕様変更されてる可能性があるので注意。
自分が利用中、何か動作がおかしい？と思った時、最新版にすると治ったりなんて事が、何度もありました。不具合対処の最終手段として頭の片隅にあった方が良いです。
まだまだ、安定してないって事でもあります。
はじめに
無論、Openpyxlは、”xls”はイジれないので、”xlsx”（Office2007以降）のみとなります。
また、xlsx（中身XML）を操作するだけのパッケージなので、直接印刷を掛けたりとかは、また別の次元の話になります。
・・という事は、Excelのライセンスが無くても、利用できるという事になります。
ちなみに、PowerShell（中身 .Net）からでも、VBAのようにExcelファイルを操作できますが、ＰＣ上にライセンスが必要です。
openpyxlの公式はこちら
VisualStudio2019で開発をしましたが、openpyxlは動的コード生成で値を出してくるので、デバッガで追うのが困難でした。公式サイトも、いまいち欲しい情報を探しにくいので、とっつきにくいかも・・しれない。
あと、読み込みに関して、画像やグラフは残念ながら対応してません（現段階では）
チュートリアル
公式サイトのこれを見た方が早い。
ワークブックをロード
とっても簡単です。data_onlyでセルの値を、式にするのか値にするのかを指定します。
_wb=openpyxl.load_workbook(_file, data_only=True)
Excelから読み込むだけならTrueが良いでしょう。Trueで開いて保存すると、全ての式が消えてしまいます。
他には、keep_vba=True という指定もできる。
openpyxl.workbook.workbook module
私もハマった要注意点。
data_onlyだと、式の評価値を取得できますが・・、これは、一度Excelで再計算を保存しなければなりません。
つまり、Openpyxlで生成したExcelファイル（式有り）を、直接読んでも、値が取得できません（式のセルValueはNoneになってる）
Openpyxlの数珠つなぎで処理させたい場合は、式を使わず、値だけ格納しなきゃいけない。Openpyxlで再計算の機能があれば・・と思うが、実現は難しいか。
ワークブックを保存
書くまでも無いくらい簡単ですが。
_wb.save(_filepath)
念の為のcloseと。
_wb.close()
ロック・セキュリティ関連はこんなで。
_wb.security = WorkbookProtection()
_wb.security.lockStructure = True
保存するなら、こんなとか。アクティブなワークシートを指定しておく。
_wb.active = _wb.sheetnames.index(“ワークシート名")
ワークシートの作成
_wb.create_sheet(title=”name”)
エクセルの使用上、同名のシートは作れない。作ってしまった場合は、最後に連番が付くようです。
ワークシートの参照
こんな感じで、ワークシート名で参照できます。
_ws = _wb[”ワークシート名”]
また、プロパティからワークシートをリスト参照できます。
for _ws in _wb.worksheets:
よくワークシートのインデックスを利用するので、そういう時はこれ。
_index = _wb.index(_ws)
openpyxl.worksheet.worksheet module
ワークシートを作る＆コピー
新しく作る場合は。
_ws = _wb.create_sheet(“新しいシート”)
コピーする場合は、これ。但し、名前付き範囲（ローカル）とかはコピーしてくれなかったのを確認。
※行桁挿入コピーとかも同様（式が変更されないとか）
_ws = wb.copy_worksheet(ws)
ワークシートの削除・移動
削除は、以下のようになるようです。
_wb.remove(_ws)
ただ、今のバージョンでは・・という感じ。remove_sheetというものもあるが非推奨のよう。
あと、削除した結果、ワークシートが無くなる可能性もあるので、そこは注意が必要。
移動は、以下にように書くのですが、現在地から右への移動しか出来ません。ソースを見ても、消して、挿入してるだけみたいだった。
_wb.move_sheet(_ws.offset = 0)
ワークシートプロパティ
ワークシートのプロパティは、ここから参照できます。
_ws.sheet_properties
openpyxl.worksheet.properties module
ワークシート名は。
_ws.title
利用されている範囲の取得は。
_ws.min_row _ws.min_column
_ws.max_row _ws.max_column
その他、設定関連。見れば分かるよね。selectionは、分割ワークシートしてる場合、０－１となる。
_ws.sheet_view.selection[0].activeCell = 'A1’
_ws.sheet_view.selection[0].sqref = 'A1’
_ws.sheet_view.zoomScale = 100
_ws.sheet_view.zoomScaleNormal = 100
_ws.sheet_view.tabSelected = False
_ws.sheet_state = “hidden” とか "visible"、"veryHidden"
印刷関連。
_ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
_ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
_ws.page_setup.fitToHeight = 0
_ws.page_setup.fitToWidth = 1
イテレータ(Worksheet)
iter_cols(min_col=None, max_col=None, min_row=None, max_row=None, values_only=False)
iter_rows(min_row=None, max_row=None, min_col=None, max_col=None, values_only=False)
行・列繰り返し処理が可能です。
セルの操作
数値でセル位置を指定するなら。
_ws.cells(row=1,column=1,value=”書いちゃう”)
とか
_ws[“A1”].value
しっかり、セル書式形式に対応した値になるので（日付・数値・文字とか）、不明な場合はtypeを確認する必要があります。
_cols = _ws.column_dimensions[“A”]
_rows = _ws.row_dimensions[1]
下線はこんな。
_ws[“B2"].font = openpyxl.styles.Font(underline = “single")
桁番号を文字に変換する
_col_letter = openpyxl.utils.get_column_letter(5) + “10”
とか
_ws.cell(row=10,column=5).coordinate
式はそのまま書いても良し。こんな
_cell = _ws.cell(row=_index,column=12)
_cell.value = “=sum(%s:%s)" % \
(_ws.cell(row=_index_summary,column=12).coordinate,\
_ws.cell(row=_index – 1,column=12).coordinate)
セル修飾
書式はこんな。そのままですね。
_cell.number_format = “#,##0;[RED]-#,##0"
フォントなどの修飾は、このように作る。
_font = openpyxl.styles.fonts.Font(name=’ＭＳ 明朝’,size=11)
また、既存セルから参照する場合は、このようにコピーして利用する。
from copy import copy
_font = copy.copy(_ws[“B2"].font)
_font.underline = None
_ws[“A1”].font = _font
中央寄せなどはこんな。
_ws.cell(row=_index,column=3).alignment = \
Alignment(horizontal = “center", vertical = “center",wrap_text = False)
ちなみに日本語文書の様に縦書きにする場合はこれ（textRotation = 255）
嘘っぽいが、実際に保存された値を調べた結果です。
_cell.alignment = \
Alignment(horizontal = “center", vertical = “center",textRotation = 255, wrap_text = False)
罫線はこんな。
範囲指定してまとめて処理出来ないようで、ちょっと面倒です。
from openpyxl.styles.borders import Border, Side
_side = Side(style="thin", color="000000″)
_border = Border(top=_side, bottom=_side, left=_side, right=_side)
_cell.border = _border
セルのコピー
from copy import copy
にて、copy()を利用してコピーできます。沢山の要素があるので、結構原始的です。
to_cell.value = cell.value
to_cell.data_type = cell.data_type
罫線やスタイルはこんな。
if cell.has_style:
to_cell.font = copy(cell.font)
to_cell.border = copy(cell.border)
to_cell.fill = copy(cell.fill)
to_cell.number_format = copy(cell.number_format)
to_cell.protection = copy(cell.protection)
to_cell.alignment = copy(cell.alignment)
他にもあるかも。
Workbookが違う場合でも、こういった方法で地道にコピーできますが、結構、厳しい場面に出くわします。だって、セル内容だけじゃないし。
行・桁の操作
よく使う行桁挿入はこんなです。amountは省略可です。Excelでの操作のように、罫線や書式なんかは継承してくれない。
一番メンドイのが、式の評価セルも、自動更新してくれません。
_ws.insert_rows(番号,amount=数)
_ws.insert_columns(番号,amount=数)
なんだか変な挙動のような気がします。
これを利用すると、編集済みのところが消えてしまうんですよね。なので、（今の所は）予めこれで、大きく確保してから編集した方が良いようです。
行高さ設定はこんな。例では、使われてる全行の高さを指定してます。値は、ポイント。
for _i in range(_ws.min_row,_ws.max_row):
_ws.row_dimensions[_i].height = 13.4
鬼門なのが、列幅指定です。Excelでは、文字数で指定するのですが、Openpyxlを経由すると、それを取得できませんし、ガチッとした指定ができません（そもそも、フォントによって幅が変わるだろうし、実際のExcel仕様がどうなってる？）
to_column_dimension = _ws.column_dimensions[openpyxl.utils.get_column_letter(_col_no)]
to_column_dimension.width = 15 こやつが鬼門
rowの行高さは、問題ありません。
セル結合・解除
結合はこの書き方になる。
_ws.merge_cells('A1:B5’)
_ws.merge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
解除はこれ。
_ws.unmerge_cells('A1:B5’)
_ws.unmerge_cells(range_string=None, start_row=None, start_column=None, end_row=None, end_column=None)
既に結合されてる所に、結合すると、もれなく壊れます。
LibreOffice Calcでは何もエラー表示されないけど、Excelで開くと修復が促されます。
ちなみに、マージセルは、Worksheetで管理されていて、以下のようにシート全体を参照できます。
_ws.merged_cells
名前付きセル
ワークシートローカルの名前付きセルの場合は。
_ws_id = _wb.sheetnames.index(_ws.title)
_dn = _wb.defined_names.get(_namedCell, _ws_id)
グローバル名前付きの場合は。
_dn = _wb.defined_names[_namedCell]
そしたら、こんな感じで、_cellsで参照できます。チュートリアル通りですが。
_ad = _dn.destinations
_cells = []
for _sheet_name, _coord in _ad:
_ws = _wb[_sheet_name]
_cells.append(_ws[_coord])
但し、セル結合されている場合は、先頭以外はNone値になるので注意です。
これイジってたらバグを見つけてしまった。名前付きセルを削除する場合は、以下のようになるのですが。
_wb.defined_names.delete(_name,_scope)
ワークシートを削除する場合に、内部でシートのローカル名前も削除してるんですが、これ正常に動作してません（ソース確認）
名前付きを全削除するなら、以下な感じで消せた事を確認。
_ls = []
for _dn in _wb.defined_names.definedName:
_ls.append((_dn.name,_dn.localSheetId))
for _v in _ls:
_wb.defined_names.delete(_v[0],_v[1])
ファイルが破損する場合
最初は良いのですが、複雑なファイルになっていくにつれて、openpyxlの悪い点が見えてきます。
現段階では、（予め）画像やグラフが入ってるファイルを編集後、保存すると確実に破損します。
なので、画像やグラフを扱う際は要注意（使えないとは言ってない）
あと自分が確認したのは、ワークシート削除の際、名前付きセル・範囲が、正常に消えてくれず、破損扱いになるケース。
openpyxl側ソースでは、しっかり名前付きを削除してくれてるのですが、なぜか正常動作しません。原因不明（多分、既にどこかで壊れてるのかも）
また、内部のXML（MIME情報）が、誤って保存される場合もあるようです（海外ソース）
後記
公式を見ればわかる、openpyxlはほとんどの操作が出来ます。
罫線やグラフ、画像差し込みなど、まず多くの場合のExcelファイルを生成する事ができます。ほんとに凄いです。
ただ、実感として、まだ発展途上の域だなというのは感じます。それも、Excelの仕様が膨大すぎる（便利過ぎる）ためが所以かと思います。
セル修飾とかやろうとすると、コードが膨らんでくるので、自分がやりそうな事は、別途関数なり用意してしまっても良いかもしれない。
インデックスだけで操作するなら、いっそR1C1形式も頭に浮かびますが、その辺は納入先と相談じゃないでしょうか（全部その形式になっちゃうので）
バージョンアップによって機能改善される事があり（開発中に体験ｗ）、見逃せないプロジェクトでもあります。
（；^ω^）
ただ、事前に、Excelのスキルを所持している必要があります。そうでないと、必要な情報まで辿り着くのに、非常に時間が掛かるでしょう。
過去に、VBAとかで操作した経験があるとかです。
Excelファイルを生成するのが目的なら、ゼロから作ると、コードの量が大変な事になるので、雛形のExcelファイルから更新を掛けた方が良いと思います。
メンテも楽だしね。
（；^ω^）
あと、LibreOffice Calcと、Excelで微妙な所の動作の違いが結構沢山見えてきた。
標準外のところ、たとえば文書プロパティの設定とかすると、Excel側で起動時に修復メッセージが表示されてしまうし、余計な事はするなという事なのやもしれない。
あと、セル修飾系も結構、動作が違うし、LibreOfficeは罫線がよく壊れる。。。
(；´Д｀)
セル修飾系で困ってるなら、Openpyxlでやるよりも、テンプレートファイル側で、スタイル指定した方が楽な場合があります（全体セルで同じ修飾したい場合とか）
LibreOffice Calcだけで開発してると、Excelで開いた時に破損してる場合が、「結構ある」ので注意です。この場合でも、LibreOffice Calcでは普通に開けるから怖い。