提高MS-Excel的书写性能

您可以读取整个范围并将其保存到二维阵列中,而不是逐个单元读取。然后可以像访问excel中的单元格一样访问二维数组。

我不太懂英语VB.NET 对于excel对象,但如果您了解C#,请快速阅读此链接并尝试实现它。

http://dotnetperls.com/excel-interop

伟大的!!!

我使用了二维阵列的方法,实现了巨大的性能提升!!。

Dim cell As Excel.Range = Nothing
cell = sheet.Cells(rowIndex, colIndex)
cell.Value = "Some value"

这里每 sheet.Cells cell.Value 是一个互操作调用,它为每个调用调用Excel.exe,这需要更多的时间。

在2D方法中,我将要复制到Excel单元格中的数据填充到2D数组中,然后将2D数组赋给选定单元格重新排列的值。如下图所示,

Dim darray(recordCount - 1, noOfCol - 1) As String
//Fill the data in darray
//startPosRange = Get the range of cell from where to start writing data
startPosRange = startPosRange.Resize(recordCount, noOfCol)
startPosRange.Value = darray

经过这些修改,我收集了性能数据,这两种方法太好了! 后一种方法是前一种方法的25倍 .

逐个单元的方法,

Dim usedRange As Excel.Range = sheet.UsedRange
For Each row As Excel.Range In usedRange.Rows()
For Each cellData As Excel.Range In row.Cells
    //Gather cellData.Value in some container.
Next

二维阵列方法,

Dim usedRange As Excel.Range = sheet.UsedRange
//Here the array index starts from 1. why???
Dim darray(,) As Object = CType(usedRange.Value, Object(,))

Dim rows As Integer = darray.GetUpperBound(0)
Dim cols As Integer = darray.GetUpperBound(1)
For i As Integer = 1 To rows    
    For j As Integer = 1 To cols
        Dim str As String
        If darray(i, j) Is Nothing Then
            str = ""
        Else
            str = darray(i, j).ToString
        End If
        //Use value of str
    Next
Next

请参考, http://support.microsoft.com/kb/306023 , http://dotnetperls.com/excel-interop (感谢Chicksentmeheigh的链接)