Page 1 of 1

Excel performance

Posted: Tue Nov 10, 2009 8:44 pm
by atom
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

Re: Excel performance

Posted: Tue Nov 10, 2009 10:02 pm
by Ciege
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...

Re: Excel performance

Posted: Wed Nov 11, 2009 12:27 pm
by atom
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.