Excel輸出與性能

最近的工作內容之一是對一個Windows Forms程序做性能調整,過程曲折有趣,記下來和大家分享一下。

這個程序的功能其實挺單純:先檢索Oracle,然後把結果輸出到一個Excel文件裏;輸出時使用了Excel 2002/2003提供的Excel Object庫。客戶反映說程序太慢,輸出5000條數據就得苦等一個上午。我們也覺得奇怪,就把代碼翻出來看。這份代碼大概十年前就有了,最初是VB5做的,後來經過一次升級,變成了現在的VB.NET版(基于.NET Framwork 1.1)。看了半天代碼,我們好像找到問題所在了:在向Excel輸出的時候,代碼的做法比較笨——它針對每個單元格逐一賦值,而每次賦值都應該會導致一次磁盤寫入操作,程序很可能因此變慢。假定檢索結果包含5000條記錄,每一條記錄裏有50個字段,這樣就需要生成一個5000行×50列的Excel文件。采用單元格逐一賦值的做法,就意味著要執行25萬次磁盤寫入操作。示例代碼如下:

'Reference for Microsoft Excel is required.

'Imports Microsoft.Office.Interop

Public Function WriteIntoExcelCellbycell(ByVal ExcelFile As String, ByVal ExcelRowCount As Integer, ByVal ExcelColumnCount As Integer) As TimeSpan

Dim dtStart As DateTime

dtStart = Now

Dim objExcelApp As Excel.Application = Nothing

Dim objWorkBook As Excel.Workbook = Nothing

Dim objWorkSheet As Excel.Worksheet = Nothing

Try

objExcelApp = New Excel.Application

objExcelApp.Visible = False

objWorkBook = objExcelApp.Workbooks.Open(ExcelFile)

objWorkBook.Activate()

objWorkSheet = DirectCast(objWorkBook.Worksheets.Add(), Excel.Worksheet)

objWorkSheet.Activate()

For intRow As Integer = 1 To ExcelRowCount

For intColumn As Integer = 1 To ExcelColumnCount

objWorkSheet.Cells.Item(intRow, intColumn) = intRow & "-" & intColumn & "ABCDEFG"

Next

Next

objWorkBook.Save()

Return DateTime.Now.Subtract(dtStart)

Catch ex As Exception

Throw ex

Finally

If objWorkBook Is Nothing Then

Else

objWorkBook.Close()

End If

If objExcelApp Is Nothing Then

Else

objExcelApp.Workbooks.Close()

objExcelApp.Quit()

End If

End Try

End Function

于是,我們嘗試了另一種做法——先把所有檢索結果轉換成一個二維數組,然後一次性寫入Excel。 代碼示意如下:

'Reference for Microsoft Excel is required.

'Imports Microsoft.Office.Interop

Public Function WriteIntoExcelByRange(ByVal ExcelFile As String, ByVal ExcelRowCount As Integer, ByVal ExcelColumnCount As Integer) As TimeSpan

Dim dtStart As DateTime

dtStart = Now

Dim objExcelApp As Excel.Application = Nothing

Dim objWorkBook As Excel.Workbook = Nothing

Dim objWorkSheet As Excel.Worksheet = Nothing

Try

objExcelApp = New Excel.Application

objExcelApp.Visible = False

objWorkBook = objExcelApp.Workbooks.Open(ExcelFile)

objWorkBook.Activate()

objWorkSheet = DirectCast(objWorkBook.Worksheets.Add(), Excel.Worksheet)

objWorkSheet.Activate()

Dim dataBuffer As String(,)

dataBuffer = Array.CreateInstance(Type.GetType("System.String"), ExcelRowCount, ExcelColumnCount)

For intRow As Integer = 0 To ExcelRowCount

For intColumn As Integer = 0 To ExcelColumnCount

dataBuffer(intRow, intColumn) = intRow & "-" & intColumn & "ABCDEFG"

Next

Next

Dim objRange As Excel.Range

objRange = objWorkSheet.Range(objWorkSheet.Cells(1, 1), objWorkSheet.Cells(ExcelRowCount, ExcelColumnCount))

objRange.Value = dataBuffer

objWorkBook.Save()

Return DateTime.Now.Subtract(dtStart)

Catch ex As Exception

Throw ex

Finally

If objWorkBook Is Nothing Then

Else

objWorkBook.Close()

End If

If objExcelApp Is Nothing Then

Else

objExcelApp.Workbooks.Close()

objExcelApp.Quit()

End If

End Try

End Function

我們找了現場最老的一台PC(CPU:Celeron 2GHZ,內存:512MB)做測試,發現使用新方法輸出16000條數據只需要不到5分鍾時間。我們都感到高興,以爲這件事這樣就算搞定了。但是,當我們把檢索結果件數增加到65000條時(這是客戶要求的最大數據輸出量,但我們猜測他們自己或許從來不曾一次輸出過這麽多數據),發現程序又變得像老牛一樣了——整整花費了8個小時才能完成輸出。

我們做了一下計算:

■檢索結果:65000條

■每條記錄平均長度:600字節

■一次性寫入Excel的數據量:約37MB

一次性向Excel文件寫入37MB數據,或許有些太爲難Excel Object庫了。那麽,應該如何改善呢?到目前爲止我們還沒有找到解決方法,但已經有了一些初步的設想——

第一,可以考慮換一種思路。客戶的目的是使用Excel查看查詢結果,並能把結果另存爲Excel文件。現在性能卡在Excel文件輸出上,那麽,我們能不能繞道而行,避開把數據直接輸出到Excel文件上的做法?譬如先把結果輸出到CSV文件上,然後再寫個Macro(宏)將數據從CSV裏讀取出來放入Excel顯示。相對于Excel文件,CSV文件的寫操作速度應該快許多,而利用Macro從CSV文件提取數據應該也不會太慢。

第二,可以考慮放棄Excel Object庫,換一個性能好一點的Excel庫。有一個名爲ExcelCreator.NET的庫可以用。據說這個庫效率高過Excel Object很多倍。下面的性能測試數據來自那個公司的網站:http://www.adv.co.jp/products/product_ExcelCreator5_feature2.htm

■測試用例1:256列×300行Excel輸出

ExcelObject:6′6″

ExcelCreator 5.0 for .NET:1.4″

■測試用例2:30列×2000行Excel輸出

ExcelObject:4′45″

ExcelCreator 5.0 for .NET:1.2″

ASP程序中輸出Excel文件實例一則
在asp中利用excel的一個方法是將excel文件作爲一個數據庫進行鏈接,然後的操作和對access數據庫操作類似。但是這個方法不是總能有用的,應爲excel不是關系型的數據庫。對于一個固定格式,這個格式裏有複雜的單元格合...查看完整版>>ASP程序中輸出Excel文件實例一則
 
Excel表格頁碼設置挺簡單 打印輸出更直觀
Excel表格頁碼設置挺簡單 打印輸出更直觀
  Excel中頁碼設置不象在Word中那樣方便,所以多數朋友在打印輸出表格時都不打印頁碼,而是依靠序號列去識別,這給我們閱讀報表帶來了很多不便。其實在Excel中插入頁碼也非常簡單,下面我們就一起來試試:  一、...查看完整版>>Excel表格頁碼設置挺簡單 打印輸出更直觀
 
如何將網頁數據輸出爲Excel或Word文件
將一個JSP頁面中的<%@ page contentType="text/html; charset=GBK" language="java" %>替換爲<%@ page contentType="application/vnd.ms-excel;charset=GBK" language="java"%> //Excel或<%@ page co...查看完整版>>如何將網頁數據輸出爲Excel或Word文件
 
在ASP.NET中將數據直接輸出成Excel格式
     本文實現了將數據庫中的數據直接輸出到Excel文件格式並在浏覽器裏輸出。下面就是實現的例子:  查看例子  ExcelExport.aspx<%@ Page Language="vb" AutoEventWireup="false" Code...查看完整版>>在ASP.NET中將數據直接輸出成Excel格式
 
ASP.NET中將數據輸出到Excel
     近來,在開發ISO文件管理系統的時候,曾經遇到過要將ASPX直接輸出到EXCEL的需求,現將經驗所得與大家分享。 其實,利用ASP.NET輸出指定內容的WORD、EXCEL、TXT、HTM等類型的文檔很容易的。主要分爲三步來完...查看完整版>>ASP.NET中將數據輸出到Excel
 
使用緩沖提高輸入/輸出的性能
  Java IO 的性能可以使用標准的緩沖類提高。  如果要求從磁盤上讀取數據,它會試圖讀取內存中已有的數據;如果代碼希望向磁盤寫一些東西,它可能在完成寫操作前將輸出在內存中存放一段時間以等待更多的數據。 ...查看完整版>>使用緩沖提高輸入/輸出的性能
 
用jsp動態輸出excel文檔和中文亂碼問題的解決
  最近在網上看到一個用java來操縱excel的open source,在weblogic上試用了一下,覺得很不錯,特此向大家推薦一下。  首先去http://www.andykhan.com/jexcelapi/index.html下載最新的JExcelApi,把jxl.jar置于你的cl...查看完整版>>用jsp動態輸出excel文檔和中文亂碼問題的解決
 
輸出EXCEL文件的通用函數,很實用
阿余常要把各種各樣的查詢結果輸出到EXCEL中,所以做了下面這段小程序,用于把一個SQL的SELECT查詢出的結果輸出爲EXCEL格式文件,這個程序你只要設好用于取得一個記錄集的SQL的SELECT查詢語句和一個文件名,程序就能...查看完整版>>輸出EXCEL文件的通用函數,很實用
 
輸出EXCEL文件的通用函數,很實用
輸出EXCEL文件的通用函數,很實用 輸出EXCEL文件的通用函數,很實用 以下文章來自于http://www.dalianit.com/edu/|78|107|86|121|79|120|62|62|.html,在此向原作者表示感謝!阿余常要把各種各樣的查詢結果輸...查看完整版>>輸出EXCEL文件的通用函數,很實用
 
 
回到王朝網路首頁