Excelの帳票の処理をPython+pandas,openpyxl,xlwingsを使って実験した時のまとめ。VBAとの速度比較も。

*2020/1/27 実行速度に関する示唆に富んだコメントを@k_makiさんにいただきました(@k_makiさんありがとうございました)

ちょっと前に、Excelで作られた大量の帳票に、ちょっとした変更を加えて保存しなおす、という仕事をVBAとPythonを使ってやりました。その時に得られた知見のメモ。

*エクセルで作られた帳票とは
罫線とかセルの結合とかフォントの大小とかを駆使して印刷した時の見栄えの良さを重視した、見積書とか請求書とか明細書とか、あんな感じのやつです。

まずはVBAのコードを。実際にやったことはもっと複雑なのですが、ここでは簡略化しています。

Sub ボタン_Click()
    Dim inpfile As Variant
    Dim inputFileName
    Dim inputPath, outputPath, inputFullPath, outputFullPath As String
    Dim Tm
     
    Tm = Timer  '処理時間計測用
     
     '入力ファイル格納パス(フルパスでないとダメ)
    inputPath = "C:\帳票変換\inputFiles"
    '出力ファイル格納パス(フルパスでないとダメ)
    outputPath = "C:\帳票変換\outputFiles"

    Dim fsobj
    'FileSystemObjectを設定する
    Set fsobj = CreateObject("Scripting.FileSystemObject")
  
    
    '入力パスの*.xlsファイルをすべて処理する
    For Each inpfile In fsobj.GetFolder(inputPath).Files
        
        '*.xlsファイルが処理対象
        If fsobj.GetExtensionName(inpfile) = "xls" Then
                    
            inputFileName = fsobj.GetFileName(inpfile)
            
            inputFullPath = inputPath + "\" + inputFileName
            outputFullPath = outputPath + "\" + inputFileName
            
            Debug.Print inputFullPath + "を処理します"
    
    
            '指定されたbookを開く
            With Workbooks.Open(inputFullPath)
                        
                'A10を更新する
                Cells(10, 1).Value = "済"
                    
            End With
            
            '処理後のbookをoutputに保存する
            Debug.Print outputFullPath + "に保存します"
            Workbooks(inputFileName).SaveAs (outputFullPath)
    
            '処理済みのbookを閉じる
            Workbooks(inputFileName).Close savechanges:=True
        Else
            'xlsでないとき
            Debug.Print "Not xls File:[" + file + "]"
        End If
    Next    '次のファイルへ
        
    '処理時間を表示
    Debug.Print "処理時間: " + Str(Timer - Tm) + "(sec)"
End Sub

処理時間:  3.753906(sec)

4枚の帳票を処理するのに4秒弱かかりました。1枚に1秒とすると1000枚の帳票を処理するのに16分程度ですかね。
*使ったマシンはWindows10 Pro 64bit Core-i5 4300M 2.6GHz 16GBのマシンです。

次にPythonのコードを。PythonでExcelファイルを扱えるライブラリとしては、pandas, openpyxl, xlwingsなどがあります。これらを比較してみました。

Python + pandasを試してみる

pandasでは.xls(97-2003ブック)という古いフォーマットしか扱えませんが、今回の入力データがそうなので問題なし、のはずです。

import pandas as pd
import os
import glob as gb
import time

start = time.perf_counter()     #処理時間を計測する

#帳票の入力フォルダ
inputFiles = gb.glob("../inputFiles/*.xls")

#入力フォルダの帳票を全て処理するループ
for inputfile in inputFiles:
	#帳票を入力します
	print ("入力ファイル=",inputfile)
	df_i = pd.read_excel(inputfile, sheet_name="帳票")

	#特定のセル(ここではA10)に特定の値(ここでは"済")を入れます。
	df_i.loc[9,0] = "済"

	#別のフォルダに保存します
	#出力先フォルダ
	outputPath = "../outputFiles"
	outputFullpath = outputPath + "/" + os.path.basename(inputfile)

	#保存
	df_i.to_excel(outputFullpath)
print("処理時間:", time.perf_counter() - start,"(sec)")

0.3905718000023626 (sec)

4枚の帳票を処理するのに390ミリ秒。速いっすね。しかし。。。。

実行してみると、xlwtは将来的にはサポートされない云々のワーニングが出ます。調べてみるとpandasではxlsの読み込み(xlrd)、書き込み(xlwt)は将来的にサポートする予定がないのだそうです。
でも出力ディレクトリにはxlsが出力されています。
...が、出力されたxlsファイルを開いてみると、セルの属性(罫線とか色とか結合状態とか）が全部なくなっています。残っているのは数字とか文字とかのデータだけです。

pandasというのはnumpyなどと組み合わせて、科学技術計算とか、そういう用途で使うものであるらしい。見た目重視の帳票に使うものではない、ということですね。なるほど。

Python + openpyxlを試してみる

openpyxlで扱えるフォーマットは、読み込みも書き込みも.xlsxだけなので、帳票をあらかじめExcelでxls→xlsx変換しておきます。

import openpyxl as pxl
import os
import glob as gb
import time

start = time.perf_counter()     #処理時間を計測する

#帳票の入力フォルダ
inputFiles = gb.glob("../inputFiles/*.xlsx")

#入力フォルダの帳票を全て処理するループ
for inputfile in inputFiles:
	#帳票を入力します
    print ("入力ファイル=",inputfile)
    #入力した帳票
    wb_i = pxl.load_workbook(inputfile)
    ws_i = wb_i["帳票"]

	#特定のセル(ここではA10)に特定の値(ここでは"済")を入れます。
    ws_i.cell(10,1).value = "済"

	#ファイル名を変更して保存します
    #出力先フォルダ
    outputPath = "../outputFiles"
    outputFullpath = outputPath + "/" + os.path.basename(inputfile)
    print ("出力ファイル=",outputFullpath)
            
    #保存
    wb_i.save(outputFullpath)
print("処理時間:", time.perf_counter() - start,"(sec)")

処理時間: 2.3348909000633284 (sec)

4枚の帳票を処理するのに2.33秒。VBAの2倍近く速いです。

出力ファイルを見てみると、ちゃんと帳票の形を保ったままA10セルが更新されていました。
ただxlsファイルが処理できない点がちょっとアレですかね。いまだに古いOSでxlsファイルを使っているところはけっこうあるらしいので。。。

Python + xlwingsを試してみる

import xlwings as xw
import os
import glob as gb
import time

start = time.perf_counter()     #処理時間を計測する

#帳票の入力フォルダ
inputFiles = gb.glob("../inputFiles/*.xlsx")

#入力フォルダの帳票を全て処理するループ
for inputfile in inputFiles:
	#帳票を入力します
    print ("入力ファイル=",inputfile)
    #入力した帳票
    wb_i = xw.Book(inputfile)
    ws_i = wb_i.sheets["帳票"]

	#特定のセル(ここではA10)に特定の値(ここでは"済")を入れます。
    ws_i.cells(10,1).value = "済"

	#ファイル名を変更して保存します
    #出力先フォルダ
    outputPath = "../outputFiles"
    outputFullpath = outputPath + "/" + os.path.basename(inputfile)
    print ("出力ファイル=",outputFullpath)
            
    #保存
    wb_i.save(outputFullpath)
    #閉じる
    wb_i.close()
print("処理時間:", time.perf_counter() - start,"(sec)")

処理時間: 9.258645400055684 (sec)

4枚の帳票を処理するのに10秒近くかかっちゃってます。あれっどうしたの？と思うくらい遅いです。

xlsもxlsxも処理できるのは素晴らしいです。出力ファイルをチェックしても、ちゃんと帳票の形を保ったまま変更できています。
しかし遅い。。。😮‍💨これは処理する帳票が多い場合、致命的な欠点になってしまいます。
でも、調査時点のxlwingsのバージョンは0.28.7です。1.0になる頃には、この問題も解決しているかもしれませんね。