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
Very nice article Dennis.
I love the practicle application topics.
Comment by Mike Alexander — February 13, 2008 @ 12:13 am
Thanks Mike - Highly appreciated
Kind regards,
Dennis
Comment by Dennis Wallentin — February 13, 2008 @ 1:48 am
Hi Dennis, ]
I was going to make a post just like this but never got around to it! I also use this methods, the only only way I saw that was possibly as quick was to use the windows clipboard? Have you tried this approach?
Thanks
Ross
Comment by Ross — February 13, 2008 @ 12:23 pm
Hi Ross,
Actually, using Windows clipboard has not crossed my mind. If I understand it correctly the difference would be when transferring the data from the clipboard to Excel.
Perhaps it would be a faster approach for large amount of data?
Kind regards,
Dennis
Comment by Dennis Wallentin — February 13, 2008 @ 12:35 pm
Yes, when I was looking into it someone suggested the windows clipboard might be quick - one to keep in the back of the mind!
Comment by Ross — February 14, 2008 @ 1:33 pm
Ross - Thanks for pointing out the clipboard option. I will play around with it.
Kind regards,
Dennis
Comment by Dennis Wallentin — February 14, 2008 @ 2:03 pm
Really nice article Dennis,
We get this kind of question on the XVBT .NET Automation forum all the time. I now know where to send them!
Ross: I would generally recommend staying away from the clipboard, unless you can RESTORE it to the previous state. Otherwise, the user will lose whatever was previously stored on the clipboard, which could be a minor annoyance or a very serious issue depending on what the user was doing at the time…
Just my 2c!
Comment by Mike Rosenblum — February 17, 2008 @ 4:54 pm
Mike,
Thanks - Yes,the XVBT.NET’s forum gives ideas for some posts here including the ‘Accessing a running instance of Excel’
Good point with the clipboard.
Kind regards,
Dennis
Comment by Dennis Wallentin — February 17, 2008 @ 5:28 pm
That’s a cool article, can you write this in C# please.
Comment by santosh — March 24, 2008 @ 12:50 pm
Sorry, I don’t have the time for it.
Kind regards,
Dennis
Comment by Dennis Wallentin — March 24, 2008 @ 8:54 pm