Excelシートの作成・更新（A）とそのシートに追加するデータ(CSVなど)の取得・処理（B）をどうするか? 個人用の雑記
A. シートの作成・更新
はじめに
以下の処理パターンがある。
- ワークブックの新規オブジェクトにシート/データを追加後、名前を付けて保存＝新規作成
- 既存のワークブックを読み込みデータを追加後、新しい名前を付ける＝テンプレート利用
- 既存のワークブックを読み込みデータを追加後、同じ名前を付ける＝既存シートの更新利用
一般的にopenpyxlライブラリを使っておけばOK（他のライブラリにはもっと処理が速いものがある。ただしその分読み込みにしか対応していない=そのワークブックのオブジェクトへの書き込み(更新)ができないなどの制約がある）。
1. シートの作成
1-1. ワークブックのオブジェクト(wb)の作成
- ①「オブジェクトにデータを追加後、名前を付けて保存＝新規作成」
ライブラリのインポート
from openpyxl import Workbook
wb = Workbook()
- ②「既存のbookを読み込みデータを追加後、新しい名前を付ける＝テンプレート利用」
- ③「既存のbookを読み込みデータを追加後、同じ名前を付ける＝既存シートの更新利用」
from openpyxl import Workbook
wb = px.load_workbook(self.template_filepath)
1-2. ワークシートのオブジェクト(ws)の作成
ws = wb.worksheets[sheetnum] / wb.worksheets[self.sheetname]
2. ワークシートに追加するデータの用意と追加(後述)
- 2-1. データの取得
- 2-2. データの加工
- 2-3. ワークシートのオブジェクト(wb/ws/cell)へデータを順次代入する
（後でそのオブジェクトにファイル名を指定して実際の保存を行う）。
4. ワークブックの保存
そのワークブックのワークシートのオブジェクトに追加したデータを実際のワークブックに保存する。
- 既存のファイルパスを指定するとパターン③、
- 新しいファイルパスを指定すると①または②になる
wb.save(self.output_filepath)
B. ワークシートに追加するデータの用意と追加
はじめに
実装パターンとしては以下が挙げられる
- データをデフォルトの方法で取得した後、データ形式を整えてから順次ワークシートのオブジェクトに追加する
- データを特定ライブラリで取得した後、同ライブラリで一括エクスポートする
- データを特定ライブラリで取得した後、データ形式を整えてから順次ワークシートのオブジェクトに追加する
1. データを取得する
この方法として以下のパターンがある
- ①通常のwith openを使う
- ②大規模なデータ処理に特化したライブラリを使う
そのデータに余分なデータがあったり、集計が必要な場合はこれで前処理をする。このライブラリでは、データフレームと呼ぶ形式でデータをCSVなどから取得してくる。データフレームとして取得されたデータは集計・計算処理がしやすい形になっている。ワークシートを更新したり、既存のファイルをテンプレートとして利用するのでなく、取得したデータを加工してエクスポートしたいだけならばこれだけでもOK。
なおメモリが問題になることも多いので以下を参考にする
https://www.sejuku.net/blog/74447
その他関連
https://qiita.com/gangun/items/f97c40f5540f8011c252
①通常のopenを使うケース
順次取得する
wizh open(self.source_filepath, 'r', encoding='utf-8') as file:
while True:
line = file.readline()
row = next(line) #これは次の項目の話と被る
ws.append(row) #これは次の項目の話
- 1行目(カラム)をスキップする方法
with open(self.source_filepath, 'r', encoding='utf-8') as file:
next(file)
②データ処理に特化したライブラリを使うケース
この方法で最初に得られるデータのタイプをデータフレームと呼ぶ。普通はpandasを使う。daskでデータを読み込むと早い。multiprossessの並列処理は最高らしい。だが使いクセがなくて躓きにくいのもこの順。集計方法などの詳細は多岐にわたるため基本的には省略する。なお、データのタイプに注意しないで処理するとエラーになる。
(1) ライブラリのインポート
import pandas as pd / import dask as dd
(2) データを取得する
下記コードでデータソースからdataframeというタイプのデータが得られる
df = pd.read_csv(self.source_filepath ,encoding=self.source_file_encoding)
df = dd.read_excel(self.source_filepath ,encoding=self.source_file_encoding)
- Note: 1行目(カラム)をスキップする方法
We can use the header and skiprows option to tell Pandas not to use the column labels already in our spreadsheet. Note that both header=None and skiprows=1 must be set in this case, as the first row of actual data will be skipped if the header option is missing.
https://wellsr.com/python/python-pandas-read_excel-to-import-excel-file-into-dataframe/
read_csv(self.source_filepath ,encoding=self.source_file_encoding, header=None, skiprows=1)
- 順次取得する場合(メモリ利用の削減)
- pandas の chunksizeオプション
- chunksize=*** を指定 → 指定した数値分chunkというタイプのデータの塊が得られる
- pandas の chunksizeオプション
せっかくなのでPandasのchunksizeもパターンを変えて実験しました。結論から言うと、1000万行に対してchunksize=30000辺りが一番速く処理が終わった。
https://qiita.com/gangun/items/17155a8b59079e37b075
read_csv(self.source_filepath ,encoding=self.source_file_encoding, chunksize=30000)
- 並列分散処理をしながら取得する場合(メモリ利用の効率化)
- dask
- multiprossess (installにクセがある)
参考：
https://qiita.com/hoto17296/items/586dc01aee69cd4915cc
https://qiita.com/simonritchie/items/e174f243bc03fb25462e
https://qiita.com/simonritchie/items/1ce3914eb5444d2157ac
- データをワークブックオブジェクトから取得するケース
参考：
https://soudegesu.com/post/python/cell-excel-with-openpyxl/
・行/列単位の読み取り
for col in ws.iter_cols(min_row=2):
...
for row in ws. iter_rows(min_row=2):
...
・セル単位の取得
(略)
3. データをワークブックオブジェクトに追加する
「A. シートの作成・更新」と交差する部分になる。なぜならば、取得・加工したデータをOpenPyXlで作成したシート/セルのオブジェクトに追加する必要があるので。
ここでいうデータとは実際のセルの値の他、データ型や書式(レイアウト)も含む。ここでは詳しくはメモしないこととする。なおこれらに関し検索すると、行→セルのように処理していくか、列を指定したとしても最終的にはセル単位での処理を行うような説明が多い。ただしレイアウトに関してはOpenPyXlで既存ワークブック(テンプレート)を読み込んだ時、そのシートで条件付き書式が設定されていれば、元の書式を丸ごと保つことが可能な場合がある。これにより例えば小計の行にのみ色を付けるといったことが可能になる。
- データをchunk型で取得しているが、行単位でデータを追加するケース
for chunk in chunks:
rows = chunk.values
for row_data in rows_data:
row_data = row_data.tolist()
ws.append(row_data)
- データをデータフレーム型で取得しており、行単位でデータを追加するケース
for i in dataframe_to_rows(df, index=False, header=True):
ws.append(row)
- セル単位で追加するケース
例1
https://gist.github.com/bisco/a65e71c8ba45337f91174e6ae3c139f9
例2
セル内改行
https://www.relief.jp/docs/openpyxl-line-feed.html
ws['A1'].value = 'AAA\nBBB'
ws['A1'].alignment = openpyxl.styles.Alignment(wrapText=True)
例3
rows_data = df.values.tolist()
for i, row_data in enumerate(rows_data):
for j, cell_data in enumerate(row_data):
if type(cell_data) is int:
ws.cell(row=i+1, column=j+1).number_format = '#'#currentFormat
ws.append(row_data)
- 行列形式
[[...],[...]]でデータを取得しており、それを行ごとに追加する場合
for row in matrix:
ws.append(row)
- 特定列で追加するケース
ws['B2'] = '=vlookup(A2,C:C,1,false)'
特定列のセルの処理
for row, cellObj in enumerate(list(ws.columns)[col_num]: #col_num:列番号
n= '=vlookup(A%d,C:C,1,false)' %(row+1)
cellObj.value = n
- データフレームをそのままファイル出力する場合(新規作成または上書き更新)
df.reset_index().to_csv(self.output_filepath, encoding=self.output_file_encoding, index=False)
df.reset_index().to_excel(self.output_filepath, encoding=self.output_file_encoding, index=False)
# reset_index()はオプション
参考になった記事などまだあるので暇なときに順次追加予定