Improve performance of MS Excel writing

Question

Hi,

I am facing performance issues while reading/writing data from/to MS-Excel cells. I am using MS Excel 11.0 object library for automation with VB.NET.

Currently it takes too much time to read and write from/to Excel files. (10 mins to read 1000 rows :( ). It seems the cell-by-cell reading and writing approach is not that effiecient. Is there any way to read/write data using bulk operation?

Answer 1

Rather than reading cell by cell you could read a whole range and save it into a 2D arrray. You can then access the 2D array as you would access a cell in excel.

I'm not well versed in VB.NET for excel objects but if you understand C# then give this link a quick read and try to implement it.

http://dotnetperls.com/excel-interop Read the "Getting workbook data" section

Answer 2

Great!!!

I used the 2D array approach and achieved the enormous performance boost!!.

Previously I used the cell-by-cell aprroach as shown below,

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

I used to iterate over a range of cells and used to copy the value in each cell. Here every sheet.Cells and cell.Value is an interop call and for every call it gives call to the Excel.exe, which costs more time.

In 2D approach I have filled the data, which is to be copied in Excel cells, in 2D array and then assigned the 2D array to the value of the selected reange of cells. It is as shown below,

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

After these modifications, I gathered the performance data for both the approaches and results are surprisingly great!!. The later approach is 25 times as fast as previous one.

Similarly, I have used the 2D array approach for reading data from cells and seen the similar performance boost. Code samples are as shown below.

Cell-by-cell approach,

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

2D array approach,

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

Please refer, http://support.microsoft.com/kb/306023 , http://dotnetperls.com/excel-interop (thanks ChickSentMeHighE for the link)

Enjoy the performance!!!