Export data from DataGridView to Excel

A common task is to view filtered data from databases in Windows applications where the users then can select records to be imported into Excel. In this context the main issue is usually how to achieve an acceptable performance when writing the selected data to Excel.

When it comes to automation of Excel the communication between Excel and for instance a .NET application is said to be an Out-of-Process communication as Windows allocate separated memory areas to them. In other words, the applications do not share the same memory area which has a negative impact on the overall performance.

When it comes to .NET automation of Excel there are two additional aspects we need to be aware of:

• The COM interop calls themselves create an overhead and
• The more calls to Excel Object Model the slower it gets.

The best approach is therefore to reduce the number of calls to Excel Object Model and before calling Excel prepare the data as much as possible. In VBA we have the data type ‘Variant’ which is good to use when working with cells’s data. In .NET we have a similar data type by the name of ‘Object’. The final key to increase the speed is to use as much as possible 2D arrays (which are 0-based) and when necessary declare them as ‘Object’.

The Case

For the case I use the Northwind database which is connected to a SQL Server 2005 where the data is presented for the users in the following Windows form:

 

The screenshot below shows all the code in use for the case. The ‘Export’ procedure shows the use of 2D arrays where they in the first step are populated with data in managed code. In the next step the data is ‘dumped’ in one go into the targeting COM worksheet:

The above code can be downloaded from here. Of course, in the above case we could have reduced the number of arrays to only use one.

When I have managed to fully port myself to VS 2008 and when SQL Server 2008 is out I will make some posts about LINQ to SQL.

Kind regards,
Dennis