基础:输出与性能
最近的工作内容之一是对一个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 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