📖 openpyxl-practice - Git Hub に置いたもの
「 VBA分かんね。 Pythonスクリプトで 開いているExcelファイルを自動操作できないのかだぜ?」
「 Microsoft がおまけで付けたVBAスクリプト、真剣に語られても サポートやりづらいだろうしな」
「 👆 ツイッターでの情報によると、openpyxl とか xlwings とか あるみたいよ?」
📖 # openpyxl と xlwings の比較
📖 【Python】openpyxlとxlwingsとの比較
📖 Differences between xlwings vs openpyxl Reading Excel Workbooks
「 openpyxl は .xlsx ファイルを編集するPythonスクリプトで、
xlwings は動いている Excel に プロセス間通信して動かすPythonスクリプトなんだって」
「 openpyxl のメリットは Excel がインストールされていなくても動くことと、 xlwings より8倍速いこと、
デメリットは 数式を計算し損ねて ヌル にしてしまうデメリットがあり、 事務用途には使えないとのことだぜ」
「 xlwings は Excel とほぼ互換なことで、
デメリットは Windowsのバージョン アップデートで動かなくなったことがあったり、
Excel にプロセス間通信してるので ループ文の書き方によっては openpyxl より8倍遅くなったりするから
AtCoderばりに ループの数を減らす必要があることだぜ」
「 openpyxl は、 計算結果のロストとか 致命的なのでは……。
しかし xlwings は、 Excel はそもそも遅いのに、 Excel に通信して動かしたって やっぱり遅いのでは……」
「 計算結果のロストが どれぐらいのものか 分かんないのよね。
最初に 諦めがつく方をマスターしてみてはどう?」
# まず pip をアップグレード
python.exe -m pip install --upgrade pip
# 最新の pip を使って、openpyxl をインストール
pip install openpyxl
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# insert at the end (default)
ws1 = wb.create_sheet("Mysheet")
# insert at first position
ws2 = wb.create_sheet("Mysheet", 0)
# insert at the penultimate position
ws3 = wb.create_sheet("Mysheet", -1)
ws.title = "New Title"
ws.sheet_properties.tabColor = "1072BA"
ws3 = wb["New Title"]
print(wb.sheetnames)
for sheet in wb:
print(sheet.title)
source = wb.active
target = wb.copy_worksheet(source)
# Saving to a file
wb.save('./output/tutorial-1-create-a-workbook.xlsx')
「 しかし お父んの抱えている Excel VBAマクロだけを置き換えるには どうすればいいんだぜ?」
「 スタイルを読み込めるか 試してみてよ。 セルの色と 罫線」
「 👆 そして タイルの上を選択して TileMap
という名前を付けておくぜ」
import openpyxl as xl
# Book
wb = xl.load_workbook('test-data/test-data.xlsx')
print(f'Sheet names| {wb.sheetnames}')
# 名前付き範囲
tileMap = wb.defined_names['TileMap']
print(f'tileMap.attr_text| {tileMap.attr_text}')
# 飛び地を集めています。リストの中に、ネストしたタプルが入っています
bookView = [wb[s][r] for s, r in tileMap.destinations]
print(f'bookView| {bookView}')
# 各セルに1つずつ訪れます
for sheetView in bookView:
print(f'sheetView| {sheetView}')
for rowView in sheetView:
print(f'rowView| {rowView}')
for cell in rowView:
print(f'cell(row={cell.row} column={cell.column} coordinate={cell.coordinate} value={cell.value})')
「 セルのフォント色、背景色、罫線は どうやって読み取んの?」
「 👆 線の太さは thin とか thick とか medium で入ってるんだな」
「 しかし インデックス カラー なんか返されても 嬉しくないな」
「 ひとまず TileMap2 の範囲にある文字を赤色にしてよ」
「 あっ、 Excel でシート開いていると 保存に失敗するぜ。 Excel 閉じてから Pythonスクリプト実行しないといけないな」
cell.font.color='FF0000'
Traceback (most recent call last):
File "update_named_cells_style.py", line 61, in <module>
cell.font.color = Font(color='FF0000')
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\styles\proxy.py", line 29, in __setattr__
raise AttributeError("Style objects are immutable and cannot be changed."
AttributeError: Style objects are immutable and cannot be changed.Reassign the style with a copy
「 👆 あれっ? Python に イミュータブル という概念があんの?」
# インポート文 気になるなら こんなのがある
# import openpyxl as xl
# from openpyxl.styles.colors import Color
# from openpyxl.styles.fonts import Font
# from openpyxl.styles import PatternFill
# 中略
# 値に 1 を足します
cell.value += 1
# 文字を赤色にします
cell.font.color.rgb='00FF0000'
「 👆 これで 文字の色は 赤にでけた。
数も増やしちゃったけど……」
# 背景色を青色にします
cell.fill.fgColor.rgb = '000000FF'
「 👆 fgColor と bgColor があって、なぜか fgColor の方で 背景色が設定できた」
「 あれは 境界線(きょうかいせん;Border)らしいぜ 英語的に」
from openpyxl.styles.borders import Border, Side
# 中略
# セルの上辺に線を引きます
side = Side(style='thin', color='000000')
cell.border = Border(top=side, bottom=None, left=None, right=None)
# 前後略
print(f'cell(row={cell.row} column={cell.column} coordinate={cell.coordinate} value={cell.value})')
cell(row=3 column=2 coordinate=B3 value=1)
cell(row=3 column=3 coordinate=C3 value=2)
cell(row=3 column=4 coordinate=D3 value=3)
cell(row=3 column=5 coordinate=E3 value=4)
cell(row=4 column=2 coordinate=B4 value=5)
cell(row=4 column=3 coordinate=C4 value=6)
cell(row=4 column=4 coordinate=D4 value=7)
cell(row=4 column=5 coordinate=E4 value=8)
cell(row=5 column=2 coordinate=B5 value=9)
cell(row=5 column=3 coordinate=C5 value=10)
cell(row=5 column=4 coordinate=D5 value=11)
cell(row=5 column=5 coordinate=E5 value=12)
cell(row=6 column=2 coordinate=B6 value=13)
cell(row=6 column=3 coordinate=C6 value=14)
cell(row=6 column=4 coordinate=D6 value=15)
cell(row=6 column=5 coordinate=E6 value=16)
import openpyxl as xl
# Book
wb = xl.load_workbook('test-data/test-data.xlsx')
# Sheet
ws = wb['Test1']
# Cell
c3 = ws['C3']
# Cell value
print(f'cell(value={c3.value})')
「 👆 こんな感じでセルを取れるから、セットもできるんじゃないか」
「 じゃあ Test1 シートの C3セルのスタイルを丸ごと Test2 シートの C3セルに コピーすることってできるの?」
from copy import copy
import openpyxl as xl
filePath = './test-data/test-data.xlsx'
# Book
wb = xl.load_workbook(filePath)
# Sheet
ws1 = wb['Test1']
ws2 = wb['Test2']
# Cell
ws1c3 = ws1['C3']
ws2c3 = ws2['C3']
# Copy style
ws2c3.font = copy(ws1c3.font)
ws2c3.border = copy(ws1c3.border)
ws2c3.fill = copy(ws1c3.fill)
ws2c3.number_format = copy(ws1c3.number_format)
ws2c3.protection = copy(ws1c3.protection)
ws2c3.alignment = copy(ws1c3.alignment)
# Saving to a file
wb.save(filePath)
「 お父んが メンテナンスしなければならない 罫線の補修のようなものは これまでの分だけでも だいぶ自動化できそうだな」
「 Excel の罫線を補修するので Python 使ってください、とか 個人事業主同士なら さくっと話になるが
日本企業の組織の一員になったら まず Pythonって何? っていう管理職の人から説得だな。諦めろだぜ」
「 幸運なことに 上司のいないボランティア作業だから プログラムで作業が捗るわね」
「 あれっ? 今まで .xlsx
で練習してきたが、作業したいファイルは .xlsm
だぜ?」
「 👆 load_workbook
メソッドには keep_vba=True
というオプションがあるようだぜ」
from copy import copy
# 中略
cell2.fill = copy(cell1.fill)
cell2.fill.fgColor.rgb = 'FFFFFFFF'
📖 copyとdeepcopyとnumpy.copyの挙動について
「 👆 あれっ? セル1も セル2も 背景色が白になった……。
ディープコピーしてないんじゃないか? シャロ―コピーでは?」
from copy import deepcopy
python.exe refresh_style_of_input_circle_sheet.py
1 .Traceback (most recent call last):
File "refresh_style_of_input_circle_sheet.py", line 29, in <module>
cell2.font = deepcopy(cell1.font)
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\copy.py", line 180, in deepcopy
y = _reconstruct(x, memo, *rv)
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\copy.py", line 281, in _reconstruct
if hasattr(y, '__setstate__'):
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\styles\proxy.py", line 24, in __getattr__
return getattr(self.__target, attr)
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\styles\proxy.py", line 24, in __getattr__
return getattr(self.__target, attr)
File "C:\Users\むずでょ\AppData\Local\Programs\Python\Python37\lib\site-packages\openpyxl\styles\proxy.py", line 24, in __getattr__
return getattr(self.__target, attr)
[Previous line repeated 495 more times]
RecursionError: maximum recursion depth exceeded while calling a Python object
fill1 = cell1.fill
cell2.fill = PatternFill(fgColor=copy(fill1.fgColor), fill_type=fill1.fill_type)
cell2.fill.fgColor.rgb = 'FFFFFFFF'
「 👆 これぐらい 細かくすると セル1と セル2 は別の背景色になるけど……」
cell2.fill = PatternFill(fgColor='FFFFFFFF', fill_type='solid')
「 👆 なんか solid も指定しないと 別のセルまで塗られる。 solid にしとこ」
rgbRed
rgbDarkSalmon
rgbBurlyWood
...
「 👆 Excel VBA の定数の rgbRed とか、どんな RGB値なんだぜ?」
📖 Colorプロパティの設定値一覧(カラー定数、XlRgbColor列挙)
「 お父んの VBAスクリプトで VLookup関数が使われているが、 openpyxlで使えるのかだぜ?」
「 openpyxl は XMLファイルを編集しているだけで、Excelの関数とか無いのでは……?」
「 👆 3 を入れたら Catherine が出てくるような関数が欲しいんだぜ」
「 A列を舐めて 行を求めたら B列を見ろだぜ。この際 ソートのプログラムは書かなくていいだろ」
📖 【Python】Excelのセルの記号を変換する(1,1→A1)
# 'A'
openpyxl.utils.get_column_letter(1)
「 👆 数字を 列のアルファベットに変換するから、あとは何とかしろということかだぜ」
# 3
openpyxl.utils.column_index_from_string('C')
import openpyxl as xl
# Book
wb = xl.load_workbook('test-data/test-data.xlsx')
# Sheet
ws = wb['Test3']
def vlookup(key, keyColumnAlphabet, resultColumnAlphabet):
"""keyColumnAlphabet列から key を探し、見つけた行の resultColumnAlphabet列のセルを返します
Parameters
----------
key : str
探す値
keyColumnAlphabet : str
探す列
resultColumnAlphabet : str
欲しい値がある列
"""
for rowNumber in range(2,10):
id = ws[f'{keyColumnAlphabet}{rowNumber}'].value
print(f'id={id}')
if id == key:
return ws[f'{resultColumnAlphabet}{rowNumber}']
elif id is None or id == '':
return None
return None
result = vlookup(3, 'A', 'B')
print(f'found={result.value}')
「 シートではなく、名前が定義された範囲の中から 見つけたいんだが」
「 👆 名前付きの範囲は 飛び地にも対応しているのか テーブルの形をしてないんだよな」
「 これは すぐには作れないな。該当箇所は 検索を自力実装して ハードコーディングしよう」
With Range("VenueArea")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
End With
「 センタリングとか 上寄せとか 行末の折り返しは どうやるんだぜ?」
📖 Source code for openpyxl.styles.alignment
📖 Check workbook for sheet and add if missing
try:
ws = wb['Sheet-name']
except KeyError:
# 無かった
pass
「 👆 読んでみて、例外返したら 無い というのも考え方だな」
「 シートの拡大の倍率を 70% にセットするには どうしたらいいんだぜ?」
📖 How to set the zoom level for a workbook in OpenPyXL?
for ws in workbook.worksheets:
ws.sheet_view.zoomScale = 85
workbook.save(excelFile)
「 小数点第2位まで表示するよう指定するには どうしたらいいんだぜ?」
「 👆 セルに number_format というプロパティがあるらしいぜ」
📖 Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について
ws.sheet_properties.tabColor = 'FFFF00'
「 👆 ワークシートに sheet_properties.tabColor というプロパティがあるらしいぜ」
「 カーソルを A1 セルに戻しておくには どうやるんだぜ?」
📖 Python openpyxl How to SET the active or selected cell
「 👆 カーソルを動かすのではなく、セルを選択するという観点でググったら出てきたぜ。これを試せだぜ」
sheet.cell(row=1, column=1)
「 👆 こう書いても カーソルが A1 セルに戻ってくれるわけじゃ ないみたいだぜ」
sheet.sheet_view.selection[0].activeCell = 'A1'
sheet.sheet_view.selection[0].sqref = 'A1'
「 Workbookを開く、無ければ新しく作るには どうやるんだぜ?」
import openpyxl as xl
# 中略
wb = None
fileName = 'example1.xlsx'
try:
wb = xl.load_workbook(fileName)
except:
wb = xl.Workbook()
wb.save(fileName)
Crieitは個人で開発中です。
興味がある方は是非記事の投稿をお願いします! どんな軽い内容でも嬉しいです。
なぜCrieitを作ろうと思ったか
また、「こんな記事が読みたいけど見つからない!」という方は是非記事投稿リクエストボードへ!
こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください!