仕事でopenpyxlを使う機会があったので、備忘として良く使う機能をまとめました。

バージョン

3.0.9

Bookの操作

# workbookを作成
wb = openpyxl.Workbook()
wb.save('test.xlsx')

# workbookを読み込む
wb = openpyxl.load_workbook('test.xlsx')

Sheetの操作

# 一覧の表示
print(wb.sheetnames)
>>>
['Sheet']

# sheetを追加
wb.create_sheet()
print(wb.sheetnames)
>>>
['Sheet', 'Sheet1']

# 既に存在シートを加えようとすると変な名前になる
wb.create_sheet(title='Sheet1')
print(wb.sheetnames)
>>>
['Sheet', 'Sheet1', 'Sheet11']

# 存在確認してから追加した方が良い
ws_name = "Sheet1"
if ws_name in wb.sheetnames:
    print('既に存在')
else:
    wb.create_sheet(title=ws_name)
print(wb.sheetnames)
>>>
既に存在
['Sheet', 'Sheet1', 'Sheet11']

# シートを削除する
ws = wb['Sheet11']
wb.remove(ws)
print(wb.sheetnames)
>>>
['Sheet', 'Sheet1']

# シートをコピーする
from_ws = wb['Sheet1']
wb.copy_worksheet(from_worksheet=from_ws)
# コピーすると元のシート名 Copyという名前になる
print(wb.sheetnames)
>>>
['Sheet', 'Sheet1', 'Sheet1 Copy']

# もう一回コピーすると 元のシート名 Copy1となる
wb.copy_worksheet(from_worksheet=from_ws)
print(wb.sheetnames)
['Sheet', 'Sheet1', 'Sheet1 Copy', 'Sheet1 Copy1']

# シートの名前を変更
ws = wb['Sheet1 Copy1']
ws.title = 'hogehoge'
print(wb.sheetnames)
>>>
['Sheet', 'Sheet1', 'Sheet1 Copy', 'hogehoge']

# シートを非表示にする
ws = wb['hogehoge']
ws.sheet_state = 'hidden'
for sheet in wb.worksheets:
    print(f'{sheet.title}:{sheet.sheet_state}')
>>>
Sheet:visible
Sheet1:visible
Sheet1 Copy:visible
hogehoge:hidden

# シートの色を変える
color_map = {'blue': '0d6efd', 'indigo': '6610f2',
             'purple': '6f42c1', 'pink': 'd63384'}

wb['Sheet'].sheet_properties.tabColor = color_map['blue']
wb['Sheet1'].sheet_properties.tabColor = color_map['indigo']

とりあえずこんなところでしょうか。

セルの操作

値を書きこむ

２通りあります。ここはVBAと一緒ですね。

ws = wb['Sheet']

# 値を書きこむ
ws['a1'] = 'spam'
ws.cell(row=1, column=2).value = 'ham'
ws.cell(1, 3).value = 'egg'

最終行の取得

列に関わらず、一番下にある行番が取得されます。

ws.cell(2, 1).value = 'hoge'
ws.cell(3, 3).value = 'fuga'
print(ws.max_row)
>>>
3

VBAだとcells(rows.count,2).end(xlup).rowと書くと２列目の最終行を取得できます。

openpyxlだと調べた限りそういう機能はないので、地道に特定します。

例えばこういうセルで1列目の最終行を取得するにはこうします。

# 指定した列の最終行を取得
col = 1
end_row = 1
# 逆順に回して値があるところが最終
for i in reversed(range(1, ws.max_row + 1)):
    if ws.cell(row=i, column=col).value:
        end_row = i
        break
print(end_row)
>>>
2

最終列の取得

print(ws.max_column)
>>>
3

同様に指定した行の最終列を取得。

row = 2
end_col = 1
for i in reversed(range(1, ws.max_column + 1)):
    if ws.cell(row=row, column=i).value:
        end_col = i
        break
print(end_col)
>>>
1

スタイル

公式を参考

https://openpyxl.readthedocs.io/en/stable/styles.html

文字フォント

from openpyxl.styles import Font

purple = '6f42c1'
ft = Font(bold=True, italic=True, size=20, color=purple)
cell = ws.cell(row=1, column=1)
cell.font = ft

セルの色を変える

from openpyxl.styles import PatternFill

c3 = ws['c3']
indigo = "6610f2"
fill = PatternFill(fill_type='solid',
                   fgColor=indigo,
                   bgColor=indigo)
c3.fill = fill
c3.value = 'indigo'

セルに罫線を引く

# セルに罫線を引く
from openpyxl.styles import Border, Side

c3 = ws['c3']
style = 'thin'
border = Border(left=Side(style=style),
                top=Side(style=style),
                right=Side(style=style),
                bottom=Side(style=style))
c3.border = border
c3.value = 'border'

罫線のスタイルは色々あるので書き出してみました。

border_styles = ('dashDot', 'dashDotDot', 'dashed', 'dotted',
                 'double', 'hair', 'medium', 'mediumDashDot',
                 'mediumDashDotDot', 'mediumDashed', 'slantDashDot', 'thick',
                 'thin')
r = 5
c = 3
for i, style in enumerate(border_styles, start=1):
    cell = ws.cell(row=r, column=c)
    border = Border(left=Side(style=style),
                    top=Side(style=style),
                    right=Side(style=style),
                    bottom=Side(style=style))

    cell.border = border
    cell.value = style

    # 4つ書き出したら行を変える
    if i % 4:
        c += 2
    else:
        r += 2
        c = 3

セル結合、解除

ws.merge_cells(start_row=1, end_row=3, start_column=1, end_column=3)

解除するにはこうします。

ws.unmerge_cells(start_row=1, end_row=3, start_column=1, end_column=3)