Introduction
Exporting data from a .NET application to Excel is a very common requirement. A simple search on the Web results in several examples that show us the method to copy data and put it into the Excel cells. However, there is a payload with this method: each interaction to put a value into an Excel cell requires an InterOp invocation. If the amount of data to transfer is huge, we have a problem with the performance of the method. Is there a better way to accomplish this?
Traditional "COPY CELL-BY-CELL" Method
Searching the Web for a method to transfer data to Excel, the most commonly used method consists of copying the values cell by cell into Excel. The following C# code shows how to transfer data from a DataTable to an Excel sheet, copying each value cell by cell:
for (int col = 0; col < dataTable.Columns.Count; col++)
{
for (int row = 0; row < dataTable.Rows.Count; row++)
{
excelSheet.Cells[row + 1, col + 1] =
dataTable.Rows[row].ItemArray[col];
}
}
Each InterOp invocation has an associated payload in performance, so a large amount of data can degenerate our application.
A "Fast Bulk-Copy" Method
Our method consists of using the Value2 property for the Range class provided by the Microsoft Excel Object Library. We can select a range of cells, and assign a value for all of them, with just one InterOp invocation. To correctly assign a value to a range of cells, we can use a bi-dimensional object array. The following C# code shows how to transfer data from a bi-dimensional object array to a range of cells:
excelSheet.get_Range("A1:H25", Type.Missing).Value2 =
bidimensionalObjectArray;
Measuring the Performance
The source code included with this article shows a small Windows application which uses the two described methods to export the same data to Excel. It shows the time that it takes for each method to finish. This DEMO uses the Northwind database to create an SQL Server local connection. It generates a DataSet with the content of the Customers table. To make the amount of data more significant, we duplicate the DataTable to obtain 24 copies from it. Then we apply the two methods to generate two Excel books, one for each method.
The source code includes a C# and a VB.NET version for the DEMO application. My own testing shows me that this method is about 35 times faster. Test it and arrive at your own conclusions.
History
- November 28, 2007: First publication
