ふにゃるんv2

もとは、http://d.hatena.ne.jp/Wacky/

win32comでの、Excelのワークシート操作の高速化テクニック

python

win32comを使って、Excelのワークシートを操作する時、普通に制御すると結構時間が掛かると思います。
それを、少しでも速くしましょう。ってネタです。


話は飛びますが、Excelを操作するってケースは結構多いようで、ちょっとググるだけで、ポンポン出て来ます。

まぁ、仕事で大抵必須ですからねぇ。

普通のワークシート操作の場合

まずは、普通〜に、ワークシートを操作するプログラムを作る場合、どうなるか？を見てみましょう。
大抵の場合、こんな感じに書くハズです。

#!/bin/env python
# -*- encoding: cp932 -*-
"""
    ExcelのCOMオブジェクトの使い方テスト
"""
import sys
import win32com
import pdb
from win32com.client import *
import pprint
import datetime
import msvcrt

def main():
    xapp = win32com.client.Dispatch("Excel.Application")
    xapp.Visible = True
    
    book = xapp.Workbooks.Open(r"H:\user\test3\sp2Changes.xls")
    sheet = book.Worksheets.Item(r"SP-2 Changes")
    
    wk = sheet.UsedRange
    x_cnt = wk.Columns.Count
    y_cnt = wk.Rows.Count
    print "x_cnt=%d y_cnt=%d" % (x_cnt, y_cnt)
    
    st_tm = datetime.datetime.today()
    cnt = 0
    for y in range(1, y_cnt):
        for x in range(1, x_cnt):
            print "check x=%d y=%d  \r" % (x + 1, y + 1),
            if wk.Cells(y, x).Value != None:
                cnt += 1
    print "総有効セル数=%d" % (cnt)
    en_tm = datetime.datetime.today()
    print "検査開始:%s\n検査終了:%s\n実行時間:%s" % (st_tm, en_tm, en_tm - st_tm)

if __name__ == "__main__":
    main()

これを私の手元の環境(P4 2.6GHz。何世代前のマシンやねん orz)で動かすと、以下の結果でした。

$ python test_1.py
x_cnt=35 y_cnt=624
総有効セル数=4873
検査開始:2009-10-11 19:21:43.406000
検査終了:2009-10-11 19:22:54.984000
実行時間:0:01:11.578000

ふむふむ、1分11秒(77秒)ですか。

改善したワークシート操作の場合

まず、答えから言うと、以下のWeb siteで紹介されているテクニックを使います。


上で示されたテクニックを、Python風に書き直すと、以下のようになります。

#!/bin/env python
# -*- encoding: cp932 -*-
"""
    ExcelのCOMオブジェクトの使い方テスト
"""
import sys
import win32com
import pdb
from win32com.client import *
import pprint
import datetime
import msvcrt

def main():
    xapp = win32com.client.Dispatch("Excel.Application")
    xapp.Visible = True
    
    book = xapp.Workbooks.Open(r"H:\user\test3\sp2Changes.xls")
    sheet = book.Worksheets.Item(r"SP-2 Changes")
    
    wk = sheet.UsedRange
    x_cnt = wk.Columns.Count
    y_cnt = wk.Rows.Count
    print "x_cnt=%d y_cnt=%d" % (x_cnt, y_cnt)
    
    st_tm = datetime.datetime.today()
    cnt = 0
    wk2 = wk.Value
    for y in range(0, y_cnt - 1):
        for x in range(0, x_cnt - 1):
            print "check x=%d y=%d  \r" % (x + 1, y + 1),
            if wk2[y][x] != None:
                cnt += 1
    print "総有効セル数=%d" % (cnt)
    en_tm = datetime.datetime.today()
    print "検査開始:%s\n検査終了:%s\n実行時間:%s" % (st_tm, en_tm, en_tm - st_tm)

if __name__ == "__main__":
    main()


これを私の手元の環境で実行すると、以下の結果が得られました。

$ python test_2.py
x_cnt=35 y_cnt=624
総有効セル数=4873
検査開始:2009-10-11 19:24:20.234000
検査終了:2009-10-11 19:24:23.406000
実行時間:0:00:03.172000

おっと、3秒です。
先の実行結果から、96%の改善です。速いですね。

参考：2つのコードの違い

ちょっと判りにくいかと思うので、ユニファイド形式でのdiff結果を載せておきます。

$ diff -u test_1.py test_2.py
--- test_1.py   2009-10-11 20:38:36.703125000 +0900
+++ test_2.py   2009-10-11 20:38:23.390625000 +0900
@@ -25,10 +25,11 @@

        st_tm = datetime.datetime.today()
        cnt = 0
-       for y in range(1, y_cnt):
-               for x in range(1, x_cnt):
+       wk2 = wk.Value
+       for y in range(0, y_cnt - 1):
+               for x in range(0, x_cnt - 1):
                        print "check x=%d y=%d  \r" % (x + 1, y + 1),
-                       if wk.Cells(y, x).Value != None:
+                       if wk2[y][x] != None:
                                cnt += 1
        print "総有効セル数=%d" % (cnt)
        en_tm = datetime.datetime.today()

何が、どう変わったのか？(技術的な話)

上のテクニックは、「セルを配列に入れる」と書いてあります。
これを技術的な言葉に変換すると、「アウトプロセスサーバー呼び出しの回数を減らす」でしょうか。


つまりですね。

  • 最初のコードの場合、「if wk.Cells(y, x).Value != None:」のアウトプロセスサーバー呼び出しを、35列×624行＝21840回呼び出す。
  • 次のコードの場合、「wk2 = wk.Value」で、一旦Pythonプロセスの二次元配列に変換＆コピーし、後はPythonプロセス内で「if wk2[y][x] != None:」の内部配列参照を行っている。つまり、「1回だけ」アウトプロセスサーバー呼び出している。

って事なんです。
21840回 VS 1回 じゃ、比べ物にならないですよね。そういう事なんです。


ちなみに、アウトプロセス呼び出しって何やねん？という場合、こんな感じで捉えれば良いかと。

  • インプロセス呼び出し：DLLの関数を呼び出す
  • アウトプロセス呼び出し：他所のプロセス(EXE)の関数を呼び出す
  • リモートプロセス呼び出し：他所のマシンのプロセス(EXE)の関数を呼び出す


Pythoのライブラリレベルで強引な例えをすると、以下の感じでしょう。

  • インプロセス呼び出し：スクリプト内の関数を呼び出す
  • アウトプロセス呼び出し：Python2.6に備わった、multiprocessingモジュールを使って関数を呼び出す
  • リモートプロセス呼び出し：xmlrpclibモジュールを使って関数を呼び出す


という訳で、このテクニックを使えば、大分速くなりますよ。と。

余談

Webで眺めていると、他の高速化テクニックとして、以下がありました。

  • Application.ScreenUpdating = False : 画面更新を抑える
  • Worksheet.EnableCalculation = False : シートの再計算を抑える


このテクニックは、逐次描画を抑える事で高速化に貢献しようというTipsでして、頻繁に描画更新される状態じゃないと、あまり高速化に貢献してくれませんでした。
それよりかは、アウトプロセスサーバー呼び出しの回数を如何に抑えるか、コードの呼び出しを見直した方が良いかも知れません。


後、自分でプログラムを作っていた時の話ですが。
自分のマシンが遅い事もあって、win32com.client.Dispatch("Excel.Application")でプロセスを起動させるのに結構時間を喰っていました。(後、Openもバカにならない程、時間を喰う)
なので、Excelプロセスが起動済みなら、そこにアタッチする事で、少し時間を稼ぎました。(win32com.client.GetObject(None, "Excel.Application"))


他にどんな高速化があるかなぁ。

お助け〜

自信ありげに説明していますが、自分でも困っている事があります。
上のセル情報を、一括で配列に叩き込む手法なんですが、Range.Interior オブジェクトや、 Range.Comment オブジェクトには効かないんですよね。


もし、こうやれば Range.Value 以外のオブジェクトに対して、配列技が使えるぜ！という方が居られましたら、教えてくださいませ〜。