Excel performance

Ask general questions here.
atom
Posts: 357
Joined: Sun Dec 07, 2008 11:14 pm
Location: Dublin, Ireland

Excel performance

Post by atom » Tue Nov 10, 2009 8:44 pm

Hiya,

Probably not the right forum for this, but has this community used Excel data much for automation?
If so id like to know, what is the fastest way to create an Excel document with say 13,000 lines and 5 columns?
I mean I have a DataSet, and need to save it to Excel

The method I tried was iterating over the Rows and Columns of the dataset
And writing a XMLSS (2003 Excel XML format) document
It takes forever....

Thanks

User avatar
Ciege
Posts: 1336
Joined: Thu Oct 16, 2008 6:46 pm
Location: Arizona, USA

Re: Excel performance

Post by Ciege » Tue Nov 10, 2009 10:02 pm

Have not tried that many myself...
Have you tried using the Excel Interop COM object?

Also, you could try creating say 5 named ranges (one for each column) then write to those named ranges based from a string array... Also, if using the Excel interop, only open and close the Excel object once. Opening and closing for each write is where the big time waster comes in. Might speed things up a bit...
If this or any response has helped you, please reply to the thread stating that it worked so other people with a similar issue will know how you fixed your issue!

Ciege...

atom
Posts: 357
Joined: Sun Dec 07, 2008 11:14 pm
Location: Dublin, Ireland

Re: Excel performance

Post by atom » Wed Nov 11, 2009 12:27 pm

Thanks.
Did my own modification to use StringBuilder class instead of & operator
We use XML Excel format, so we can generate Excel documents even when Excel is not installed

13,000 rows and 5 columns now takes under 2 minutes to produce.