openpyxlでpythonからエクセルファイルを操作する

OpenPyXLとは

pythonでエクセルファイル（xlsx, xlsm）を操作するパッケージです
https://openpyxl.readthedocs.io/en/stable/index.html

値をいじるだけじゃなく書式やテキストボックス、画像オブジェクトなどの要素を変更したり出来ますので、一般ユーザー向けに結果をEXCELファイルで書き出す際に書式を整えたりするのに便利です

公式チュートリアルは以下になります
https://openpyxl.readthedocs.io/en/stable/index.html

1. インストール

pipで入ります

terminal

pip install openpyxl

画像ファイルを扱いたい場合はpillowも要ります

terminal

pip install pillow

2. bookの操作

openpyxl.workbook.workbook.Workbookクラスのインスタンスとして扱われます

2-1. 新規作成

以下を実行すると空のワークブックを作成します

>>> import openpyxl as xl
>>> wb = xl.Workbook()
>>> wb

<openpyxl.workbook.workbook.Workbook at 0x1a29558e670>

2-2. 保存

wbオブジェクトのsaveメソッドでファイルに書き出すことが出来ます

wb.save('./book.xlsx')

2-3. 読み込み

ファイルからの読み込みも出来ます

>>> wb2 = xl.load_workbook('./book.xlsx')
>>> wb2

<openpyxl.workbook.workbook.Workbook at 0x20388537e50>

3. sheetの操作

openpyxl.worksheet.worksheet.Worksheetクラスのインスタンスとして扱われます

3-1. アクティブシートの取得

>>> ws = wb.active
>>> ws

<Worksheet "Sheet">

3-2. シート一覧の取得

ワークシートを左から順に入れたlistを受け取れます

シートオブジェクトとして取得

>>> wb.worksheets

[<Worksheet "Sheet">]

シート名のみ取得

>>> wb.sheetnames

['Sheet']

シートを１つずつ処理する場合は以下のように書くことが出来ます

シートを順番に取り出す

>>> for sheet in wb:
>>>     print(sheet)

<Worksheet "Sheet">

3-3. シート名を変更する

>>> ws.title = 'hoge'
>>> ws

<Worksheet "hoge">

3-4. シートタブの色を変更する

RGB16進数で指定できます

>>> ws.sheet_properties.tabColor = "1072BA"
>>> ws.sheet_properties.tabColor

<openpyxl.styles.colors.Color object>
Parameters:
rgb='001072BA', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'

3-5. シートを複製する

コピー対象のワークシートの右側にコピーしたワークシートを追加できます

>>> ws2 = wb.copy_worksheet(ws)
>>> wb.worksheets

[<Worksheet "hoge">, 
 <Worksheet "hoge Copy">]

3-6. シートを追加する

wb.create_sheet(シート名, 追加位置)でワークシートを追加できます

ワークシートを左端に追加する

>>> ws3 = wb.create_sheet("sheet3", 0)
>>> wb.worksheets

[<Worksheet "sheet3">, 
 <Worksheet "hoge">, 
 <Worksheet "hoge Copy">]

挿入位置を指定してワークシートを追加する

>>> ws4 = wb.create_sheet("sheet4", 1)
>>> wb.worksheets

[<Worksheet "sheet3">,
 <Worksheet "sheet4">,
 <Worksheet "hoge">,
 <Worksheet "hoge Copy">]

追加位置を省略すると右端に追加するという指定になります

シートを右端に追加する

>>> ws5 = wb.create_sheet("sheet5")
>>> wb.worksheets

[<Worksheet "sheet3">,
 <Worksheet "sheet4">,
 <Worksheet "hoge">,
 <Worksheet "hoge Copy">,
 <Worksheet "sheet5">]

右から何番目という指定もできます

右から3番目に挿入する

>>> ws6 = wb.create_sheet("sheet6", -2)
>>> wb.worksheets

[<Worksheet "sheet3">,
 <Worksheet "sheet4">,
 <Worksheet "hoge">,
 <Worksheet "sheet6">,
 <Worksheet "hoge Copy">,
 <Worksheet "sheet5">]
​```


### 3-8. シートを移動する


```python
>>> wb.move_sheet(ws, offset=-1)
>>> wb.worksheets

[<Worksheet "sheet3">,
 <Worksheet "hoge">,
 <Worksheet "sheet4">,
 <Worksheet "sheet6">,
 <Worksheet "hoge Copy">,
 <Worksheet "sheet5">]

3-9. シートを削除する

>>> wb.remove(ws)
>>> wb.worksheets

[<Worksheet "sheet3">,
 <Worksheet "sheet4">,
 <Worksheet "sheet6">,
 <Worksheet "hoge Copy">,
 <Worksheet "sheet5">]

4. セルの操作

4-1. セルの取得

R1C1で指定する

>>> wb = xl.Workbook()
>>> ws = wb.active
>>> c1 = ws['A4']
>>> c1

<Cell 'Sheet'.A4>

行、列の番号で指定する

>>> c2 = ws.cell(row=4, column=1)
>>> c2

<Cell 'Sheet'.A4>

4-2. 値の取得

セルのvalueプロパティに値が入っています
値が入っていない場合はNoneが返ってきます

R1C1で指定する

>>> ws['A4'].value

行、列の番号で指定する

>>> ws.cell(row=4, column=1).value

以下のように書いても同じです

R1C1で指定して取得してから値を参照する

>>> c1 = ws['A4']
>>> c1.value

行、列の番号で指定して取得してから値を参照する

>>> c2 = ws.cell(row=4, column=1).value
>>> c2.value

4-3. 値の変更

R1C1で指定する

>>> ws['A4'].value = 3.14
>>> ws['A4'].value

3.14

行、列の番号で指定する

>>> ws.cell(row=4, column=1).value = 6.28
>>> ws.cell(row=4, column=1).value

6.28

ワークシートのcellメソッドで値を入力することもできます

行、列の番号で指定する

>>> c2 = ws.cell(row=4, column=1, value=6.28)
>>> c2.value

6.28

4-4. 数式の書き込み

文字列で書き込めば数式として扱ってくれるようです

>>> ws['A4'].value = '= 2 ^ 8'
>>> print(ws['A4'].value, type(ws['A4'].value))

'= 2 ^ 8' <class 'str'>

文字列で入ってますが書き出すときにセル関数に変換してくれます

openpyxlは数式が正しいかチェックしてくれないようですが、セル関数が存在するかは以下のように確認することが出来るようです

>>> from openpyxl.utils import FORMULAE
>>> "HEX2DEC" in FORMULAE

True

4-5. 書式の取得

number format

import datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>> ws['A1'].number_format

'yyyy-mm-dd h:mm:ss'

4-6. 書式の変更

文字方向を回転する

ws['B2'].alignment = xl.styles.alignment.Alignment(textRotation=90)

背景色を変更する

ws['B2'].fill = xl.styles.PatternFill(patternType='solid', fgColor='FFAAAA', bgColor='FFAAAA')
wb.save('./book.xlsx')

文字色を変更する

ws['D3'].font = openpyxl.styles.fonts.Font(color='0000FF', bold=True)

4-7. セルの結合

結合

R1C1で指定

ws.merge_cells('A2:D2')

行、列の番号で指定

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

結合の解除

R1C1で指定

ws.unmerge_cells('A2:D2')

行、列の番号で指定

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

4-8. セルに画像を貼り付ける

img = xl.drawing.image.Image('./sample.jpg')
ws.add_image(img, 'C3')

5. 範囲の操作

5-1. 範囲の取得

範囲で取得

範囲で取得するとセルオブジェクトをtupleに入れたものを取得できます

R1C1で指定

>>> cell_range = ws['A1':'C2']
>>> cell_range

((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>),
 (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>))

行、列の番号で指定

>>> cell_range = tuple(ws.iter_rows(min_row=1, max_col=3, max_row=2))

((<Cell 'hoge'.A1>, <Cell 'hoge'.B1>, <Cell 'hoge'.C1>),
 (<Cell 'hoge'.A2>, <Cell 'hoge'.B2>, <Cell 'hoge'.C2>))

列、行の順で取得することもできます

列、行の順番で取得

>>> tuple(ws.iter_cols(min_row=1, max_col=3, max_row=2))

((<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>),
 (<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>),
 (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>))

行を選んで取得

10行目を選択

>>> row10 = ws[10]
>>> row10

(<Cell 'Sheet'.A10>, 
 <Cell 'Sheet'.B10>, 
 <Cell 'Sheet'.C10>)

行全体を選択しているのに3列分しか取得されていないのは、現時点でワークシート内に3列分しかセルの情報がないからです

5-10行目を範囲選択

>>> row_range = ws[5:10]
>>> row_range

((<Cell 'hoge'.A5>, <Cell 'hoge'.B5>, <Cell 'hoge'.C5>, <Cell 'hoge'.D5>),
 (<Cell 'hoge'.A6>, <Cell 'hoge'.B6>, <Cell 'hoge'.C6>, <Cell 'hoge'.D6>),
 (<Cell 'hoge'.A7>, <Cell 'hoge'.B7>, <Cell 'hoge'.C7>, <Cell 'hoge'.D7>),
 (<Cell 'hoge'.A8>, <Cell 'hoge'.B8>, <Cell 'hoge'.C8>, <Cell 'hoge'.D8>),
 (<Cell 'hoge'.A9>, <Cell 'hoge'.B9>, <Cell 'hoge'.C9>, <Cell 'hoge'.D9>),
 (<Cell 'hoge'.A10>, <Cell 'hoge'.B10>, <Cell 'hoge'.C10>, <Cell 'hoge'.D10>))

すべての行を選択

>>> ws = wb.active
>>> tuple(ws.rows)

((<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <Cell 'Sheet'.C1>, <Cell 'Sheet'.D1>),
 (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>),
 (<Cell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>),
 (<Cell 'Sheet'.A4>, <Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>),
 (<Cell 'Sheet'.A5>, <Cell 'Sheet'.B5>, <Cell 'Sheet'.C5>, <Cell 'Sheet'.D5>),
 (<Cell 'Sheet'.A6>, <Cell 'Sheet'.B6>, <Cell 'Sheet'.C6>, <Cell 'Sheet'.D6>),
 (<Cell 'Sheet'.A7>, <Cell 'Sheet'.B7>, <Cell 'Sheet'.C7>, <Cell 'Sheet'.D7>),
 (<Cell 'Sheet'.A8>, <Cell 'Sheet'.B8>, <Cell 'Sheet'.C8>, <Cell 'Sheet'.D8>),
 (<Cell 'Sheet'.A9>, <Cell 'Sheet'.B9>, <Cell 'Sheet'.C9>, <Cell 'Sheet'.D9>),
 (<Cell 'Sheet'.A10>,
  <Cell 'Sheet'.B10>,
  <Cell 'Sheet'.C10>,
  <Cell 'Sheet'.D10>))

列を選んで取得

C列を選択

>>> colC = ws['C']
>>> colC

(<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>)

C-D列を選択

>>> col_range = ws['C:D']
>>> col_range

((<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>),
 (<Cell 'Sheet'.D1>, <Cell 'Sheet'.D2>))

すべての列を選択

>>> tuple(ws.columns)

((<Cell '左に足す'.A1>,
  <Cell '左に足す'.A2>,
  <Cell '左に足す'.A3>,
  <Cell '左に足す'.A4>,
  <Cell '左に足す'.A5>,
  <Cell '左に足す'.A6>,
  <Cell '左に足す'.A7>,
  <Cell '左に足す'.A8>,
  <Cell '左に足す'.A9>),
 (<Cell '左に足す'.B1>,
  <Cell '左に足す'.B2>,
  <Cell '左に足す'.B3>,
  <Cell '左に足す'.B4>,
  <Cell '左に足す'.B5>,
  <Cell '左に足す'.B6>,
  <Cell '左に足す'.B7>,
  <Cell '左に足す'.B8>,
  <Cell '左に足す'.B9>),
 (<Cell '左に足す'.C1>,
  <Cell '左に足す'.C2>,
  <Cell '左に足す'.C3>,
  <Cell '左に足す'.C4>,
  <Cell '左に足す'.C5>,
  <Cell '左に足す'.C6>,
  <Cell '左に足す'.C7>,
  <Cell '左に足す'.C8>,
  <Cell '左に足す'.C9>))

5-2. 値の取得

空のセルの場合はNoneが返ってきます

シート全体の値を取得する

>>> wb = xl.Workbook()
>>> ws = wb.active
>>> ws.values

((None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None),
 (None, None, None))

範囲で取得した場合はtupleにセルオブジェクトをまとめてあるだけなので、ws['A1':'C2'].values のような書き方はできません

>>> wb = xl.Workbook()
>>> ws = wb.active
>>> 
>>> cell_range = ws['A1':'C2']
>>> for row in cell_range:
>>>     for cell in row:
>>>         print(cell, cell.value)

<Cell 'Sheet'.A1> None
<Cell 'Sheet'.B1> None
<Cell 'Sheet'.C1> None
<Cell 'Sheet'.A2> None
<Cell 'Sheet'.B2> None
<Cell 'Sheet'.C2> None

5-3. 範囲内のセルの変更

セル１つずつ書き換える場合

>>> wb = xl.Workbook()
>>> ws = wb.active
>>> 
>>> cell_range = ws['A1':'C2']
>>> for i, row in enumerate(cell_range):
>>>     for j, cell in enumerate(row):
>>>         cell.value = '{}-{}'.format(i, j)        
>>> tuple(ws.values)

<Cell 'Sheet'.A1> 0-0
<Cell 'Sheet'.B1> 0-1
<Cell 'Sheet'.C1> 0-2
<Cell 'Sheet'.A2> 1-0
<Cell 'Sheet'.B2> 1-1
<Cell 'Sheet'.C2> 1-2
(('0-0', '0-1', '0-2'), 
 ('1-0', '1-1', '1-2'))

ws.append()で書いていくとまだ何も書かれていない新しい行に1行ずつ書き足していくことが出来ます

1行ずつ書き足す場合

>>> wb = xl.Workbook()
>>> ws = wb.active
>>> ws.append([1, 2, 3])
>>> ws.append([2, 3, 4])
>>> wb.save('./book.xlsx')
>>> tuple(ws.values)

((1, 2, 3), 
 (2, 3, 4))

6. pandasの利用

6-1. pandas.DataFrameの内容をワークシートに貼り付ける

pandas.DataFrameからワークシートをつくる機能が一応用意されています

df = pd.DataFrame({'a': [1, 2, 3], 'b': [2, 3, 4]})        
df

dataframe_to_rows()にpandas.DataFrameを渡すと1行単位で値を出してくれるイタレータをつくってくれます。これをワークシートオブジェクトにappend()していけばDataFrameの内容をシートに書き写すことが出来ます

>>> from openpyxl.utils.dataframe import dataframe_to_rows
>>> 
>>> wb = xl.Workbook()
>>> ws = wb.active
>>> 
>>> rows = dataframe_to_rows(df, index=True, header=True)
>>> for k, row in enumerate(rows):
>>>     print(k, row, type(row))
>>>     ws.append(row)
>>>     
>>> wb.save('./book.xlsx')

0 [None, 'a', 'b'] <class 'list'>
1 [None] <class 'pandas.core.indexes.frozen.FrozenList'>
2 [0, 1, 2] <class 'list'>
3 [1, 2, 3] <class 'list'>
4 [2, 3, 4] <class 'list'>

上記はチュートリアルのコードそのままですが、これだと余計なのが出てきて1行空白が出来てしまうので、typeを確認して要らないものが出てきたらappend()しないように変更した方が良さげです

6-2. ワークシートの値からpandas.DataFrameをつくる

ws.valuesでワークシート全体の値を取得すると2次元配列で取得できるので比較的スムーズに変換できます

from itertools import islice
data = ws.values
cols = next(data)[1:]
data = list(data)
idx = [r[0] for r in data]
data = (islice(r, 1, None) for r in data)
df = pd.DataFrame(data, index=idx, columns=cols)

7. 行、列の操作

7-1. 行幅の変更

行幅はfloatで渡さないと無視されます

ws.row_dimensions[3].height = 30.

7-2. 列幅の変更

列幅もfloatで渡さないと無視されます

ws.column_dimensions['B'].width = 30.

7-3. 行、列をfoldして非表示にする

以下のようにすることで、A～D列、1～10行が非表示になります

ws.column_dimensions.group('A', 'D', hidden=True)
ws.row_dimensions.group(1, 10, hidden=True)

hidden=Falseにすると表示されるようになります

ws.column_dimensions.group('A', 'D', hidden=False)
ws.row_dimensions.group(1, 10, hidden=False)

8. 巨大なファイルを扱う場合

Read-only mode、Write-only modeを切り替えることでメモリ消費量を抑えて巨大なファイルを扱うことが可能になるようです
https://openpyxl.readthedocs.io/en/stable/optimized.html

9. グラフを操作する

たくさんあり過ぎて読む気がなくなったのでチュートリアルページのリンクだけ貼っておきます
https://openpyxl.readthedocs.io/en/stable/charts/introduction.html

まとめ

正直、EXCELファイルの操作自体はVBAと比べて特別に書きやすいということはないと思いますが、EXCELファイルの操作以外の部分ではpythonの方が圧倒的に書きやすいので、EXCELから操作する必要がないのであれば python + openpyxl で書けることで得られるメリットはすごく大きいと思います。

レッツトライ