Excel输出与性能

2024-08-23 19:47:59

字体：大中小

来源：转载

供稿：网友

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

上一篇：EXCEL中更改单元格格式后不刷新问题的解决方法

下一篇：将表记录导入到Excel完整代码