王朝网络
分享
 
 
 

Excel输出与性能

王朝other·作者佚名  2008-10-25
宽屏版  字体: |||超大  

最近的工作内容之一是对一个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″

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
>>返回首页<<
推荐阅读
 
 
频道精选
 
静静地坐在废墟上,四周的荒凉一望无际,忽然觉得,凄凉也很美
© 2005- 王朝网络 版权所有