Qlitre's Blog

2022.02.06

Python openpyxlの基本操作

仕事で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']




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

セルの操作


値を書きこむ

2通りあります。ここは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と書くと2列目の最終行を取得できます。
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)