提昇 Excel 程式效率

這兩天 DR 在處理一支自己所寫的工具在特定狀況下效能嚴重低落的問題，這支工具是用 Python 寫成，其主要用途是分析特定的 XML 檔案，再經由 COM 介面呼叫 Excel 產出試算表格式的報告結果。就在昨天，DR 接到一支不過 2MB 大小的 XML 檔案，用工具跑卻遲遲沒有結果，比它還大上許多的檔案也未曾遇過這問題。於是 DR 置入了一些 print 函式監看程式的處理狀況，並關閉一些模組來交叉測試。從中發現這支 XML 雖小，符合個別分析條件的資料量卻是異常的多（基本上，這是一支不正常的檔案），致使得工具雖然沒有當掉，但仍在一些程序上停滯不前。

DR 接下來所做的事情就是將程式碼清查一遍，尤其 DR 常仗著硬體速度越趨發達、執行的工作又單純，對程式碼的效能便不是很在意，所以很容易寫出低劣的程式碼……在清查過程中找到了一些明明可以先經過篩選從而減少迴圈次數的程式段落，將其修正後再試一次，執行效率確實有變快一點，但還是不夠。

於是 DR 將程式碼一段一段、各個模組拆開來測，發現真正影響效能的元兇僅是單一一行程式碼（不過因為迴圈的緣故，要跑很多次）：

sheet.Range(COLUMN_SOURCE + str(pos)).GetCharacters(start+1, len(highlight[x])).Font.ColorIndex = WARNING_COLOR

這行程式的作用是在試算表裡、將該欄位中特定位置的字串做顏色標示，由於異常的 XML 檔案會致使得單一欄位需要被寫入極大量的資料，接著會導致 Excel 在該欄位裡為特定字串上色時變得非常、非常緩慢，而 Python 程式得等這個動作執行完畢才能繼續下一個步驟，所以真正的效能瓶頸其實是 Excel。

因此 DR 開始上網搜尋看看有沒有人使用 GetCharacters() 遇到和 DR 一樣的效率問題，以及其解法。循線查找下來，最終在 MSDN 找到這份極完整的文件：

http://msdn.microsoft.com/zh-tw/library/office/ff726673%28v=office.14%29.aspx

其實若是 VBA 的重度使用者，對於這類問題應該早就有處理經驗了，然而 DR 沒用過 VBA，對 Excel 也不精，所以完全是後知後覺。藉由文件裡頭的說明，在確認不會影響最終的報告輸出後，DR 將以下程式碼全數加入到工具裡：

excelapp.ScreenUpdating = False
excelapp.EnableEvents = False
excelapp.DisplayStatusBar = False
excelapp.Workbooks.Add()
excelapp.Calculation = -4135
sheet = excelapp.Worksheets(1)
sheet.DisplayPageBreaks = False

其中第四、第六行是原本工具裡就有的程式碼，在此一同列出來是為了標示出新增程式碼的次序。加入以上程式碼後再執行工具，果然效率大增，回復到可以接受的水平。

除此之外，DR 還在不同環境的連番測試中發現 Excel 2007 的程式化執行效率比 Excel 2003 還要差，在更新程式碼後，2007 仍然會花上多於 2003 三倍的時間來完成工作，不知道 2010、2013 有沒有比較好一點。