Fastest way to write cells to Excel with Office Interop?

Question

I am writing a function to export data to Excel using the Office Interop in VB .NET. I am currently writing the cells directly using the Excel worksheet's Cells() method:

worksheet.Cells(rowIndex, colIndex) = data(rowIndex)(colIndex)

This is taking a long time for large amounts of data. Is there a faster way to write a lot of data to Excel at once? Would doing something with ranges be faster?

Solution 1

You should avoid reading and writing cell by cell if you can. It is much faster to work with arrays, and read or write entire blocks at once. I wrote a post a while back on reading from worksheets using C#; basically, the same code works the other way around (see below), and will run much faster, especially with larger blocks of data.

  var sheet = (Worksheet)Application.ActiveSheet;
  var range = sheet.get_Range("A1", "B2");
  var data = new string[3,3];
  data[0, 0] = "A1";
  data[0, 1] = "B1";
  data[1, 0] = "A2";
  data[1, 1] = "B2";
  range.Value2 = data;

Solution 2

If you haven't already, make sure to set Application.ScreenUpdating = false before you start to output your data. This will make things go much faster. The set it back to True when you are done outputting your data. Having to redraw the screen on each cell change takes a good bit of time, bypassing this saves that.

As for using ranges, you still will need to target 1 (one) specific cell for a value, so I see no benefit here. I am not aware of doing this any faster than what you are doing in regards to actually outputting the data.

Solution 3

Just to add to Tommy's answer.

• You might also want to set the calculation to manual before you start writing.

Application.Calculation = xlCalculationManual

And set it back to automatic when you're done with your writing. (if there's a chance that the original mode could have been anything other than automatic, you will have to store that value before setting it to manual)

Application.Calculation = xlCalculationAutomatic

• You could also use the CopyFromRecordset method of the Range object.

http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.copyfromrecordset(office.11).aspx

Solution 4

The fastest way to write and read values from excel ranges is Range.get_Value and Range.set_Value.

The way is as below:

Range filledRange = Worksheet.get_Range("A1:Z678",Missing);
object[,] rngval = (object[,]) filledRange.get_Value (XlRangeValueDataType.xlRangeValueDefault);

Range Destination = Worksheet2.get_Range("A1:Z678",Missing);
destination.set_Value(Missing,rngval);

and yes, no iteration required. Performance is just voila!!

Hope it helps !!

Solution 5

Honestly, the fastest way to write it is with comma delimiters. It's easier to write a line of fields using the Join(",").ToString method instead of trying to iterate through cells. Then save the file as ".csv". Using interop, open the file as a csv which will automatically do the cell update for you upon open.

View more solutions