| 導購 | 订阅 | 在线投稿
分享
 
 
 

Excel輸出與性能

2008-10-25 09:18:30  編輯來源:互聯網  简体版  手機版  移動版  評論  字體: ||

最近的工作內容之一是對一個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″

最近的工作內容之一是對一個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很多倍。下面的性能測試數據來自那個公司的網站:[url=http://www.adv.co.jp/products/product_ExcelCreator5_feature2.htm]http://www.adv.co.jp/products/product_ExcelCreator5_feature2.htm[/url] ■測試用例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″
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
王朝網路微信公眾號
微信掃碼關註本站公眾號 wangchaonetcn
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有