Search code examples
.netperformanceexcelautomationread-write

Improve performance of MS Excel writing

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?

Solution

  • 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