openpyxlって何だぜ(^~^)?

すぺー(^~^) ぼこー(^▽^) 公開下書き

気の早い人向け

📖 openpyxl-practice - Git Hub に置いたもの

今回の話題

ramen-tabero-futsu2.png
「 VBA分かんね。 Pythonスクリプトで 開いているExcelファイルを自動操作できないのかだぜ?」

kifuwarabe-futsu.png
「 Microsoft がおまけで付けたVBAスクリプト、真剣に語られても サポートやりづらいだろうしな」

📖 openpyxl
📖 xlwings

ohkina-hiyoko-futsu2.png
「 👆 ツイッターでの情報によると、openpyxl とか xlwings とか あるみたいよ?」

ramen-tabero-futsu2.png
「 1つなら すぐ選べるのに、なんで 2つ あるんだぜ!」

📖 # openpyxl と xlwings の比較
📖 【Python】openpyxlとxlwingsとの比較
📖 Differences between xlwings vs openpyxl Reading Excel Workbooks

ramen-tabero-futsu2.png
「 👆 浅く 軽く 早く 調べよう」

ramen-tabero-futsu2.png
「 openpyxl は .xlsx ファイルを編集するPythonスクリプトで、
xlwings は動いている Excel に プロセス間通信して動かすPythonスクリプトなんだって」

ramen-tabero-futsu2.png
「 openpyxl のメリットは Excel がインストールされていなくても動くことと、 xlwings より8倍速いこと、
デメリットは 数式を計算し損ねて ヌル にしてしまうデメリットがあり、 事務用途には使えないとのことだぜ」

ramen-tabero-futsu2.png
「 xlwings は Excel とほぼ互換なことで、
デメリットは Windowsのバージョン アップデートで動かなくなったことがあったり、
Excel にプロセス間通信してるので ループ文の書き方によっては openpyxl より8倍遅くなったりするから
AtCoderばりに ループの数を減らす必要があることだぜ」

kifuwarabe-futsu.png
「 openpyxl は、 計算結果のロストとか 致命的なのでは……。
しかし xlwings は、 Excel はそもそも遅いのに、 Excel に通信して動かしたって やっぱり遅いのでは……」

ohkina-hiyoko-futsu2.png
「 計算結果のロストが どれぐらいのものか 分かんないのよね。
最初に 諦めがつく方をマスターしてみてはどう?」

ramen-tabero-futsu2.png
「 わらう」

# まず pip をアップグレード
python.exe -m pip install --upgrade pip

# 最新の pip を使って、openpyxl をインストール
pip install openpyxl

ramen-tabero-futsu2.png
「 👆 インストールはすんなりいったぜ」

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')

ramen-tabero-futsu2.png
「 👆 Workbook 作るのも すんなり行ったぜ」

kifuwarabe-futsu.png
「 しかし お父んの抱えている Excel VBAマクロだけを置き換えるには どうすればいいんだぜ?」

ohkina-hiyoko-futsu2.png
「 スタイルを読み込めるか 試してみてよ。 セルの色と 罫線」

20210904openpyxl1.png

ramen-tabero-futsu2.png
「 👆 じゃあ こんなシートを作っておくぜ」

20210904openpyxl2a1.png

ramen-tabero-futsu2.png
「 👆 そして タイルの上を選択して TileMap という名前を付けておくぜ」

20210904openpyxl3.png

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})')

ramen-tabero-futsu2.png
「 👆 名前付きセルは読めてるぜ」

ohkina-hiyoko-futsu2.png
「 セルのフォント色、背景色、罫線は どうやって読み取んの?」

📖 Working with styles

ramen-tabero-futsu2.png
「 👆 スタイル関連の説明は このページだな」

20210904openpyxl4.png

ramen-tabero-futsu2.png
「 👆 線の太さは thin とか thick とか medium で入ってるんだな」

kifuwarabe-futsu.png
「 ゲットができたなら、次は セットだな」

ramen-tabero-futsu2.png
「 しかし インデックス カラー なんか返されても 嬉しくないな」

20210904openpyxl5.png

kifuwarabe-futsu.png
「 👆 TileMap2 に罫線を付けてくれだぜ」

ramen-tabero-futsu2.png
「 やればできるのかもしれないが 大変そう」

ohkina-hiyoko-futsu2.png
「 ひとまず TileMap2 の範囲にある文字を赤色にしてよ」

ramen-tabero-futsu2.png
「 あっ、 Excel でシート開いていると 保存に失敗するぜ。 Excel 閉じてから Pythonスクリプト実行しないといけないな」

kifuwarabe-futsu.png
「 いまいちだな」

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

ramen-tabero-futsu2.png
「 👆 あれっ? Python に イミュータブル という概念があんの?」

20210904openpyxl6.png

# インポート文 気になるなら こんなのがある
# 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'

ramen-tabero-futsu2.png
「 👆 これで 文字の色は 赤にでけた。
数も増やしちゃったけど……」

ohkina-hiyoko-futsu2.png
「 次は 背景色を付けてちょうだい」

            # 背景色を青色にします
            cell.fill.fgColor.rgb = '000000FF'

ramen-tabero-futsu2.png
「 👆 fgColor と bgColor があって、なぜか fgColor の方で 背景色が設定できた」

20210904openpyxl7.png

ohkina-hiyoko-futsu2.png
「 次は 罫線(けいせん)を付けてちょうだい」

kifuwarabe-futsu.png
「 あれは 境界線(きょうかいせん;Border)らしいぜ 英語的に」

20210904openpyxl8.png

from openpyxl.styles.borders import Border, Side

# 中略

            # セルの上辺に線を引きます
            side = Side(style='thin', color='000000')
            cell.border = Border(top=side, bottom=None, left=None, right=None)

📖 openpyxlで罫線を引く

ramen-tabero-futsu2.png
「 👆 境界線も 使いこなせそう」

ohkina-hiyoko-futsu2.png
「 セルの座標は 取得したり 指定したり できんの?」

            # 前後略
            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)

📖 Pythonでセルの情報を読み取る(openpyxl)

ramen-tabero-futsu2.png
「 👆 セルのアドレスを取得するのは できそう」

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})')

ramen-tabero-futsu2.png
「 👆 こんな感じでセルを取れるから、セットもできるんじゃないか」

ohkina-hiyoko-futsu2.png
「 じゃあ Test1 シートの C3セルのスタイルを丸ごと Test2 シートの C3セルに コピーすることってできるの?」

ramen-tabero-futsu2.png
「 やろうとしていることは分かった」

20210904openpyxl9.png

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)

📖 セルスタイルopenpyxlをコピー

ramen-tabero-futsu2.png
「 👆 いけるぜ」

kifuwarabe-futsu.png
「 お父んが メンテナンスしなければならない 罫線の補修のようなものは これまでの分だけでも だいぶ自動化できそうだな」

ramen-tabero-futsu2.png
「 Excel の罫線を補修するので Python 使ってください、とか 個人事業主同士なら さくっと話になるが
日本企業の組織の一員になったら まず Pythonって何? っていう管理職の人から説得だな。諦めろだぜ」

ohkina-hiyoko-futsu2.png
「 幸運なことに 上司のいないボランティア作業だから プログラムで作業が捗るわね」

ramen-tabero-futsu2.png
「 上司がいないって 楽だよな」

.xlsx? .xlsm?

ramen-tabero-futsu2.png
「 あれっ? 今まで .xlsx で練習してきたが、作業したいファイルは .xlsm だぜ?」

📖 xlsmファイルへの保存

kifuwarabe-futsu.png
「 👆 load_workbook メソッドには keep_vba=True というオプションがあるようだぜ」

ramen-tabero-futsu2.png
「 おっ、いけたぜ」

copy() はシャロ―コピー?

from copy import copy

# 中略

            cell2.fill = copy(cell1.fill)
            cell2.fill.fgColor.rgb = 'FFFFFFFF'

📖 copyとdeepcopyとnumpy.copyの挙動について

ramen-tabero-futsu2.png
「 👆 あれっ? セル1も セル2も 背景色が白になった……。
ディープコピーしてないんじゃないか? シャロ―コピーでは?」

from copy import deepcopy

kifuwarabe-futsu.png
「 👆 じゃあ ディープコピーを使えだぜ」

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

ramen-tabero-futsu2.png
「 👆 循環参照を踏んでしまったようだぜ」

ohkina-hiyoko-futsu2.png
「 必要なところだけ ディープコピーしなさい」

            fill1 = cell1.fill
            cell2.fill = PatternFill(fgColor=copy(fill1.fgColor), fill_type=fill1.fill_type)
            cell2.fill.fgColor.rgb = 'FFFFFFFF'

ramen-tabero-futsu2.png
「 👆 これぐらい 細かくすると セル1と セル2 は別の背景色になるけど……」

                cell2.fill = PatternFill(fgColor='FFFFFFFF', fill_type='solid')

ramen-tabero-futsu2.png
「 👆 なんか solid も指定しないと 別のセルまで塗られる。 solid にしとこ」

rgbRed って何色?

rgbRed
rgbDarkSalmon
rgbBurlyWood
...

ramen-tabero-futsu2.png
「 👆 Excel VBA の定数の rgbRed とか、どんな RGB値なんだぜ?」

kifuwarabe-futsu.png
「 調べても 見つからないな」

ohkina-hiyoko-futsu2.png
「 Windowsのカラーなんじゃないの?」

ramen-tabero-futsu2.png
「 フーム」

📖 Colorプロパティの設定値一覧(カラー定数、XlRgbColor列挙)

ramen-tabero-futsu2.png
「 👆 こんなページを見つけたが、使うとこだけ実装しよ」

VLookup

kifuwarabe-futsu.png
「 お父んの VBAスクリプトで VLookup関数が使われているが、 openpyxlで使えるのかだぜ?」

ramen-tabero-futsu2.png
「 openpyxl は XMLファイルを編集しているだけで、Excelの関数とか無いのでは……?」

ohkina-hiyoko-futsu2.png
「 検索しましょう!」

ramen-tabero-futsu2.png
「 汎用的でない関数を自力実装してる感じだな」

20210904openpyxl10.png

ramen-tabero-futsu2.png
「 👆 3 を入れたら Catherine が出てくるような関数が欲しいんだぜ」

kifuwarabe-futsu.png
「 A列を舐めて 行を求めたら B列を見ろだぜ。この際 ソートのプログラムは書かなくていいだろ」

📖 【Python】Excelのセルの記号を変換する(1,1→A1)

# 'A'
openpyxl.utils.get_column_letter(1)

ramen-tabero-futsu2.png
「 👆 数字を 列のアルファベットに変換するから、あとは何とかしろということかだぜ」

# 3
openpyxl.utils.column_index_from_string('C')

ramen-tabero-futsu2.png
「 👆 列のアルファベットを数字に変換するのは こう」

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}')

ramen-tabero-futsu2.png
「 👆 とりあえず 今はこれだけあればいいや」

kifuwarabe-futsu.png
「 シートではなく、名前が定義された範囲の中から 見つけたいんだが」

20210904openpyxl11.png

ramen-tabero-futsu2.png
「 👆 名前付きの範囲は 飛び地にも対応しているのか テーブルの形をしてないんだよな」

ramen-tabero-futsu2.png
「 これは すぐには作れないな。該当箇所は 検索を自力実装して ハードコーディングしよう」

ohkina-hiyoko-futsu2.png
「 つら」

Alignment や WrapText とか

        With Range("VenueArea")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlTop
            .WrapText = True
        End With

kifuwarabe-futsu.png
「 センタリングとか 上寄せとか 行末の折り返しは どうやるんだぜ?」

📖 Source code for openpyxl.styles.alignment

ramen-tabero-futsu2.png
「 👆 ソースコードにはあるみたいだぜ」

📖 openpyxlでセルの値を中央揃えにする

ohkina-hiyoko-futsu2.png
「 👆 使い方の記事があるわよ」

行の高さとか

ramen-tabero-futsu2.png
「 行の高さって どうやってセットするんだぜ?」

kifuwarabe-futsu.png
「 ドキュメントに それっぽい項目がないな」

📖 【Python】Excelのセルの幅・高さを変更する

ohkina-hiyoko-futsu2.png
「 👆 あるみたいよ?」

シートの有無は?

ramen-tabero-futsu2.png
「 シートが有るか無いかって どうやって判定するんだぜ?」

📖 Check workbook for sheet and add if missing

try:
    ws = wb['Sheet-name']
except KeyError:
    # 無かった
    pass

kifuwarabe-futsu.png
「 👆 読んでみて、例外返したら 無い というのも考え方だな」

ズーム倍率

ramen-tabero-futsu2.png
「 シートの拡大の倍率を 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)

kifuwarabe-futsu.png
「 👆 このサンプルを応用しろだぜ」

書式 浮動小数点数

ramen-tabero-futsu2.png
「 小数点第2位まで表示するよう指定するには どうしたらいいんだぜ?」

📖 openpyxlで数値の表示フォーマットを指定する

kifuwarabe-futsu.png
「 👆 セルに number_format というプロパティがあるらしいぜ」

ramen-tabero-futsu2.png
「 -10.00 みたいな書式にしたいとき どう書くの?」

📖 Excel のセルの表示形式で [ユーザー定義] に使用できる書式記号について

kifuwarabe-futsu.png
「 👆 これを読んで全パターン試せば 途中で知るだろう」

ワークシートのタブに色を付けるには?

ramen-tabero-futsu2.png
「 ワークシートのタブに色を付けるには どうやるんだぜ?」

📖 Coloring a tab in openpyxl

ws.sheet_properties.tabColor = 'FFFF00'

kifuwarabe-futsu.png
「 👆 ワークシートに sheet_properties.tabColor というプロパティがあるらしいぜ」

カーソルをホームに戻すには?

ramen-tabero-futsu2.png
「 カーソルを A1 セルに戻しておくには どうやるんだぜ?」

ohkina-hiyoko-futsu2.png
「 方法が見つかんないわねぇ」

📖 Python openpyxl How to SET the active or selected cell

kifuwarabe-futsu.png
「 👆 カーソルを動かすのではなく、セルを選択するという観点でググったら出てきたぜ。これを試せだぜ」

    sheet.cell(row=1, column=1)

ramen-tabero-futsu2.png
「 👆 こう書いても カーソルが A1 セルに戻ってくれるわけじゃ ないみたいだぜ」

    sheet.sheet_view.selection[0].activeCell = 'A1'
    sheet.sheet_view.selection[0].sqref = 'A1'

ramen-tabero-futsu2.png
「 👆 この2行を書いて でけたぜ。1行だとダメ」

Wookbookを作るには?

ramen-tabero-futsu2.png
「 Workbookを開く、無ければ新しく作るには どうやるんだぜ?」

import openpyxl as xl

# 中略

        wb = None
        fileName = 'example1.xlsx'
        try:
            wb = xl.load_workbook(fileName)
        except:
            wb = xl.Workbook()
            wb.save(fileName)

kifuwarabe-futsu.png
「 👆 これでどうだぜ?」

ramen-tabero-futsu2.png
「 かんぺきにテストしたわけじゃないが 動いてるからいいんじゃないか?」

何度でもクリック!→

むずでょ@きふわらべ第29回世界コンピューター将棋選手権一次予選36位

光速のアカウント凍結されちゃったんで……。ゲームプログラムを独習中なんだぜ☆電王戦IIに出た棋士もコンピューターもみんな好きだぜ☆▲(パソコン将棋)WCSC29一次予選36位、SDT5予選42位▲(パソコン囲碁)AI竜星戦予選16位

Crieitは個人で開発中です。 興味がある方は是非記事の投稿をお願いします! どんな軽い内容でも嬉しいです。
なぜCrieitを作ろうと思ったか

また、「こんな記事が読みたいけど見つからない!」という方は是非記事投稿リクエストボードへ!

こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください!

ボードとは?

むずでょ@きふわらべ第29回世界コンピューター将棋選手権一次予選36位 の最近の記事