1、Excel输出及性能Excel输出及性能 Excel 输出及性能!最近的工作内容之一是对一个Windows Forms程序做性能调整，过程曲折有趣，记下来和大家分享一下。 这个程序的功能其实挺单纯：先检索 Oracle，然后把结果输出到一个 Excel文件里；输出时使用了 Excel 2002/2003提供的 Excel Object库。客户反映说程序太慢，输出 5000条数据就得苦等一个上午。我们也觉得奇怪，就把代码翻出来看。这份代码大概十年前就有了，最初是 VB5做的，后来经过一次升级，变成了现在的 VB.NET版（基于.NET Framwork 1.1） 。看了半天代码，我们好像找到问题
2、所在了：在向 Excel输出的时候，代码的做法比较笨它针对每个单元格逐一赋值，而每次赋值都应该会导致一次磁盘写入操作，程序很可能因此变慢。假定检索结果包含 5000条记录，每一条记录里有 50个字段，这样就需要生成一个 5000行50 列的Excel文件。采用单元格逐一赋值的做法，就意味着要执行 25万次磁盘写入操作。示例代码如下：Reference for Microsoft Excel is required.Imports Microsoft.Office.InteropPublic Function WriteIntoExcelCellbycell(ByVal ExcelFile As
3、 String, ByVal ExcelRowCount As Integer, ByVal ExcelColumnCount As Integer) As TimeSpanDim dtStart As DateTimedtStart = NowDim objExcelApp As Excel.Application = NothingDim objWorkBook As Excel.Workbook = NothingDim objWorkSheet As Excel.Worksheet = NothingTryobjExcelApp = New Excel.ApplicationobjEx
4、celApp.Visible = FalseobjWorkBook = objExcelApp.Workbooks.Open(ExcelFile)objWorkBook.Activate()objWorkSheet = DirectCast(objWorkBook.Worksheets.Add(), Excel.Worksheet)objWorkSheet.Activate()For intRow As Integer = 1 To ExcelRowCountFor intColumn As Integer = 1 To ExcelColumnCountobjWorkSheet.Cells.I
5、tem(intRow, intColumn) = intRow & “-“ & intColumn & “ABCDEFG“NextNextobjWorkBook.Save()Return DateTime.Now.Subtract(dtStart)Catch ex As ExceptionThrow exFinallyIf objWorkBook Is Nothing ThenElseobjWorkBook.Close()End IfIf objExcelApp Is Nothing ThenElseobjExcelApp.Workbooks.Close()objExcelApp.Quit()
6、End IfEnd TryEnd Function于是，我们尝试了另一种做法先把所有检索结果转换成一个二维数组，然后一次性写入 Excel。 代码示意如下：Reference for Microsoft Excel is required.Imports Microsoft.Office.InteropPublic Function WriteIntoExcelByRange(ByVal ExcelFile As String, ByVal ExcelRowCount As Integer, ByVal ExcelColumnCount As Integer) As TimeSpanDim d
7、tStart As DateTimedtStart = NowDim objExcelApp As Excel.Application = NothingDim objWorkBook As Excel.Workbook = NothingDim objWorkSheet As Excel.Worksheet = NothingTryobjExcelApp = New Excel.ApplicationobjExcelApp.Visible = FalseobjWorkBook = objExcelApp.Workbooks.Open(ExcelFile)objWorkBook.Activat
8、e()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 ExcelRowCountFor intColumn As Integer = 0 To Exce
9、lColumnCountdataBuffer(intRow, intColumn) = intRow & “-“ & intColumn & “ABCDEFG“NextNextDim objRange As Excel.RangeobjRange = objWorkSheet.Range(objWorkSheet.Cells(1, 1), objWorkSheet.Cells(ExcelRowCount, ExcelColumnCount)objRange.Value = dataBufferobjWorkBook.Save()Return DateTime.Now.Subtract(dtSt
10、art)Catch ex As ExceptionThrow exFinallyIf objWorkBook Is Nothing ThenElseobjWorkBook.Close()End IfIf objExcelApp Is Nothing ThenElseobjExcelApp.Workbooks.Close()objExcelApp.Quit()End IfEnd TryEnd Function我们找了现场最老的一台 PC（CPU：Celeron 2GHZ，内存：512MB）做测试，发现使用新方法输出 16000条数据只需要不到 5分钟时间。我们都感到高兴，以为这件事这样就算搞定了
11、。但是，当我们把检索结果件数增加到 65000条时（这是客户要求的最大数据输出量，但我们猜测他们自己或许从来不曾一次输出过这么多数据） ，发现程序又变得像老牛一样了整整花费了 8个小时才能完成输出。 我们做了一下计算： 检索结果：65000 条 每条记录平均长度：600 字节 一次性写入 Excel的数据量：约 37MB 一次性向 Excel文件写入37MB数据，或许有些太为难 Excel Object库了。那么，应该如何改善呢？到目前为止我们还没有找到解决方法，但已经有了一些初步的设想 第一，可以考虑换一种思路。客户的目的是使用Excel查看查询结果，并能把结果另存为 Excel文件。现在性
12、能卡在 Excel文件输出上，那么，我们能不能绕道而行，避开把数据直接输出到 Excel文件上的做法？譬如先把结果输出到 CSV文件上，然后再写个 Macro（宏）将数据从 CSV里读取出来放入 Excel显示。相对于 Excel文件，CSV 文件的写操作速度应该快许多，而利用Macro从 CSV文件提取数据应该也不会太慢。第二，可以考虑放弃Excel Object库，换一个性能好一点的 Excel库。有一个名为ExcelCreator.NET的库可以用。据说这个库效率高过 Excel Object很多倍。下面的性能测试数据来自那个电脑知识的网站：http:/测试用例 1：256 列300 行 Excel输出 ExcelObject：66 ExcelCreator 5.0 for .NET：1.4 测试用例 2：30 列2000行 Excel输出 ExcelObject：445 ExcelCreator 5.0 for .NET：1.2
