VSTO & .NET & Excel

February 12, 2008

Export data from DataGridView to Excel

Filed under: .NET & Excel, SQL Server — Dennis M Wallentin @ 8:50 pm

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:

dgview1.png 

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:

dgviewcode3.png

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

Advertisements

37 Comments »

  1. Very nice article Dennis.
    I love the practicle application topics.

    Comment by Mike Alexander — February 13, 2008 @ 12:13 am

  2. Thanks Mike – Highly appreciated 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — February 13, 2008 @ 1:48 am

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. That’s a cool article, can you write this in C# please.

    Comment by santosh — March 24, 2008 @ 12:50 pm

  10. Sorry, I don’t have the time for it.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — March 24, 2008 @ 8:54 pm

  11. use this site to convert code from VB.NET to C# or vice versa:

    labs.developerfusion.co.uk/convert/csharp-to-vb.aspx

    Comment by bkwdesign — June 3, 2008 @ 2:30 pm

  12. Thanks . I did some tests and it seems to work rather OK in both ends.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — June 3, 2008 @ 4:39 pm

  13. i am wondering how come the export button wont work for me.. 😦

    Comment by siokhoon — July 29, 2008 @ 5:06 am

  14. In what way does it not work? Do You get an error message or?

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — July 29, 2008 @ 11:29 pm

  15. Thanks Dennis you made me a great service

    Comment by sedMhd — August 11, 2008 @ 1:28 pm

  16. You’re welcome.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 11, 2008 @ 6:55 pm

  17. Thanks Dennis, realy nice.
    I wonder if there is a limitation in this function? It works great when i use a small aumont off rows (30 000-40 000) but when i select like 80 000 it stops on row 57692 at “)).value = DataArr”
    I tried to select less columns then i get more rows but it still stops.
    Please help me. I love this function and have to get it to work, do you have any ideas on why it stops?
    Sorry for my bad SwingElich. 🙂
    Best regards
    Kenneth

    Comment by Goa Gubbar — August 13, 2008 @ 2:42 pm

  18. Kenneth,

    Which version of Excel do You actually use?

    As long as we can understand each other there is no need to apologize 🙂

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — August 13, 2008 @ 4:44 pm

  19. 🙂 Thanks Dennis

    Im using Office 2007 Pro Swedish version and i try to keep up with all the Microsoft updates. 🙂
    Then i use VB.Net 2005 Studio with a Janus GridEX as source.
    By the way i use 20 columns insted of 5.

    Thanks for all help
    Kenneth

    Comment by Kenneth — August 13, 2008 @ 7:17 pm

  20. Hi Dennis,
    I Dont now if it is relevant but i changed the Connection to.
    Dim cn As New SqlClient.SqlConnection(My.Settings.ProdDBConnectionString)
    Dim cmd As New SqlClient.SqlCommand(SQLExpression, cn)

    Kind regards
    Kenneth

    Comment by Goa Gubbar — August 14, 2008 @ 1:36 pm

  21. Thank you for the code, it worked great. I had to make a couple minor modifications. Since my datagridview column count varies, I couldn’t hardcode fieldname, counts, etc.

    Comment by Travis — December 17, 2008 @ 1:26 am

  22. Travis,

    You’re welcome.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — December 17, 2008 @ 1:57 am

  23. If you are looking for speed, write the data to a text file and make the extension “.csv”. When you open the csv file in excel save to whatever format you are looking for. With this method I can export seven to eight thousand records within 2 to 3 seconds.

    Comment by imareb — February 5, 2009 @ 10:08 pm

  24. Hi Dennis

    Your function works ok on small amount of data in the grid but fails when there are 20,000 rows.

    Comment by Elijah — February 19, 2009 @ 4:01 pm

  25. Hi Elijah,

    Thanks for the information, highly appreciated. I will test it myself.

    Kind regards,
    Dennis

    Comment by Dennis Wallentin — February 19, 2009 @ 8:04 pm

  26. Do you have code for the datagrid? Because i use datagrid instead of datagridview. I need to save datagrid to excel. Can you please help me?

    Comment by Nire Memu — May 4, 2009 @ 1:51 pm

    • Hi,

      In general You should be able to use the same approach. What kind of problem do You face?

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — May 4, 2009 @ 2:52 pm

  27. Hi Dennis,

    thanks a lot for ur code. i had really hard problems to export my datagridview to Excel, because it was too slow (it took about 120 secs, now its done in 2!!!)

    i just have one question: what exactly does the

    GC.Collect()

    do? (I’m new in vb.net)

    THX a lot again,
    Janette

    Comment by Janette Gifhorn — May 26, 2009 @ 1:50 pm

  28. Thankx Dennis, i appreciate your efforts, i am getting an error as “xlwbatemplate is not a member of excel”. Kindly help

    Comment by Night Mare — July 5, 2009 @ 4:13 pm

    • Hi,

      You’re welcome. What version of Excel do You use?

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 5, 2009 @ 5:35 pm

      • I am using MS office 2007.

        Comment by Night Mare — July 7, 2009 @ 11:54 am

  29. Hello,
    nice idea, but it requires Office installed or?
    I have written a version which does not require Office installed.
    http://www.ploetzeneder.eu/softwareentwicklung/cvbnet/38-datagridview-in-excel-exportieren.html
    What do you think about :)?
    Best regards from austria,

    Andreas

    Comment by Andreas — July 23, 2009 @ 11:25 am

    • Andreas,

      That’s looking very nice. Thanks for telling me and I’m consider to make another post about this subject but without the need to have Excel available.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 23, 2009 @ 12:07 pm

  30. 🙂
    I can not see my topics any more?

    Comment by Andreas — July 23, 2009 @ 12:40 pm

    • Andreas,

      I forgot to approve the entry but now it should be there.

      Kind regards,
      Dennis

      Comment by Dennis Wallentin — July 23, 2009 @ 12:44 pm

  31. Many thanks Dennis for the code! I’m a 1-year VB coder, and looking forward to sticking with it and continual learning. I was wondering whether you could provide the code in your example for having the file open in a default CSV format, instead of an Excel .xls format? Just curious if that’s an easy code change. Also, once I deploy my Win App, will end users need to have the Office 2003 PIAs on their computers in order for the export feature to work? (I had to specially install the Office 2003 PIAs on my development computer for the Imports Excel = Microsoft.Office.Interop.Excel to work.) By the way, thanks for your patience and thoughtfullness with all of the newbie questions!

    Comment by Sam M — November 10, 2009 @ 7:17 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: