Excel write throughput
Greetings,
I have a C# application that I wrote that takes SQL Server datasets and outputs them to EXCEL.
Right now I have the following:
SQL Server connection via DSN an ODBC.
Excel Connected via Microsoft.Interop.Office.E
Excel Application Object called, workbook initiated.
Then for each row, I go column-by-column and enter data to Excel.
The table has 24 columns. That means 24 inserts per record.
This takes time. Approximately 1000/minute.
On some computers it's even less than that.
I was assuming that was a standard OFFICE COM based throughput.
I was wondering if there was a way to increase it.
Is there a way to insert a full range at once?
turn it from a cell by cell to a row by row?
Or any other option to increase throughput?
Any wisdom would be greatly appreciated.
I have a C# application that I wrote that takes SQL Server datasets and outputs them to EXCEL.
Right now I have the following:
SQL Server connection via DSN an ODBC.
Excel Connected via Microsoft.Interop.Office.E
Excel Application Object called, workbook initiated.
Then for each row, I go column-by-column and enter data to Excel.
The table has 24 columns. That means 24 inserts per record.
This takes time. Approximately 1000/minute.
On some computers it's even less than that.
I was assuming that was a standard OFFICE COM based throughput.
I was wondering if there was a way to increase it.
Is there a way to insert a full range at once?
turn it from a cell by cell to a row by row?
Or any other option to increase throughput?
Any wisdom would be greatly appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
appari
excelSheet is the current active sheet object. startRow is the row number in excel sheet, where we want to insert the data.
ASKER
ok. It appears as if you are loading the whole table into an array then putting it into Excel.
I could consider that, however I am dealing with probably 100,000+ rows of data.
Don't ask. i wouldn't use Excel either. But the program I'm working with only takes Excel.
Should I do it in 100 row batches? would that work? Is it the time it takes to put into Excel that takes the time? or is it the pull from SQL?
I could consider that, however I am dealing with probably 100,000+ rows of data.
Don't ask. i wouldn't use Excel either. But the program I'm working with only takes Excel.
Should I do it in 100 row batches? would that work? Is it the time it takes to put into Excel that takes the time? or is it the pull from SQL?
we created excel sheets with upto 20000 rows without any problem. if your excel version supports 100000+ rows it should work without any problem. just to make sure it works you can divide the process in to a batch of 5000 rows, 100 rows is too small in my opinion.
if extracting the data from SQL is taking time check the SQL see if you can change it to make it faster.
if extracting the data from SQL is taking time check the SQL see if you can change it to make it faster.
ASKER
This got me started.
It's much faster now.
Thanks.
It's much faster now.
Thanks.