Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 17. October 2010 16:13

The current project I am working on requires writing large amount of data to Excel worksheets. In this type of situation, I create an array with all the data I want to write, and set the value of the entire target range at once. I know from experience that this method is much faster than writing cells one by one, but I was curious about how much faster, so I wrote a little test, writing larger and larger chunks of data and measuring the speed of both methods:

private static void WriteArray(int rows, int columns, Worksheet worksheet)
{
   var data = new object[rows, columns];
   for (var row = 1; row <= rows; row++)
   {
      for (var column = 1; column <= columns; column++)
      {
         data[row - 1, column - 1] = "Test";
      }
   }

   var startCell = (Range)worksheet.Cells[1, 1];
   var endCell = (Range)worksheet.Cells[rows, columns];
   var writeRange = worksheet.Range[startCell, endCell];

   writeRange.Value2 = data;
}
private static void WriteCellByCell(int rows, int columns, Worksheet worksheet)
{
   for (var row = 1; row <= rows; row++)
   {
      for (var column = 1; column <= columns; column++)
      {
         var cell = (Range)worksheet.Cells[row, column];
         cell.Value2 = "Test";
      }
   }
}

Clearly, the array approach is the way to go, performing close to 1000 times faster per cell. It also seems to improve as size increases, but that would require a bit more careful testing.

WriteDataToExcel

More...

Comments

Comment RSS