openpyxlでワークシートを連結する
数百個のエクセルファイルのデータをすべて連結して一枚のシートにせよとのタスク。
手動でやってたらおそらく数週間くらいかかる。しかもちらちらと見たところ、非表示になっているrowとかcolumnがあったり。たぶんexcelVBAでもできるんでしょうけど、pythonでやってみる。
sheetの最大row数でループさせる
最初に書いたスクリプトはこんな感じ。sheetオブジェクトのmax_rowに最大row数が入っているので、その数字でforを回す。でもって、読み込んだデータをCSVで標準出力にだす。
データは必ず最初のシートに入っているようなので、sheetnames[0]だけ参照する。
import os,sys import glob import openpyxl files = glob.glob("/path/to/files/*.xlsx") for file in file: #存在するファイルでループ data1="" data2="" book = openpyxl.load_workbook(file) sheetnames = book.sheetnames; sheet = book[sheetnames[0]] #データは最初のシートに入って rn =1 #row番号。1発進。 while( rn <= sheet.max_row): data1 = sheet.cell(column=1,row=rn).value data2 = sheet.cell(column=2,row=rn).value print(data1,":",data2) } rn = rn+1
ところが、シートによっては数行しかデータが入っていないのにもかかわらず、sheet.max_rowが数万だったりすることがあり、とっても時間がかかる。それにprintでcellの値を標準出力にだすと、セル値に改行がはいってたりするとprintでも当然改行してしまう。とにかく遅い。
これじゃ使いものにならない。
rowとcolumnでループさせる。
とにかくmax_rowの数字がおかしいので別の方法を考える。
もとデータのループはオーソドックスにrowとcolumnでforを回すことにする。
空白行の判定はrow中のすべてのセル値がNoneだったらということにして、その条件にマッチしたらループをskipさせる。
それとCSVで出すのはやめて、直接別のエクセルファイルに出力することにする。改行とか、特殊文字とかほんとめんどくさい。
ってことで次に書いたバージョンがこんなの。
import os,sys import glob import openpypx files = glob.glob("/path/to/files/*.xlsx") outwb = openpyxl.Workbook() #出力先のワークシート outsheet = outwb.active rn = 1 #出力先シートのrow番号 for file in files: #存在するファイルでループさせる book = openpyxl.load_workbook(file) sheetnames = book.sheetnames sheet = book[sheetnames[0]] for row in sheet: # sheetはrowオブジェクトのタプル if all( c.value is None for c in row): # rowはcellのタプル。すべてのcellがNoneだったら next # 次のrowへ else: outsheet.cell(column=cell.col_idx,row=rn).value = cell.value # なにか入ってたらoutsheetに代入する。 rn = rn + 1 outwb.save('./output/result.xlsx') #結果を出力
このバージョンは割と早かった。全部で5万row程度でしたが、2〜3分くらいで終了。
row番号やcol_idxは1発進だけど、sheetは0発進だったり。Microsoftの仕様なんでしょうけど、ちょっと妙だ。