• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 583
  • Last Modified:

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.Excel (Spelling?)
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.
0
Evan Cutler
Asked:
Evan Cutler
  • 3
  • 2
1 Solution
 
appariCommented:
In my applications I do it like this, works faster.
Code is in VB.net, i suppose you can change it to C# easily.
Dim excelData(dataTable.Rows.Count, dataTable.Columns.Count) As object
Dim currentDataTableCol  as integer
Dim currentDataTableRow  as integer 

            For Each drData As DataRow In dataTable.Rows
                currentDataTableCol = 0
                For Each dcItem As Object In drData.ItemArray
	if dcItem isnot nothing then
                    excelData(currentDataTableRow, currentDataTableCol) = dcItem
	else
	  excelData(currentDataTableRow, currentDataTableCol) = ""
	end if
                  currentDataTableCol += 1
                Next
                currentDataTableRow += 1
            Next

            Dim range As Excel.Range
            range = excelSheet.Range(excelSheet.Cells(startRow, 1), excelSheet.Cells(startRow + dataTable.Rows.Count - 1, dataTable.Columns.Count))
            range.Value2 = excelData

Open in new window

0
 
appariCommented:
excelSheet is the current active sheet object. startRow is the row number in excel sheet, where we want to insert the data.
0
 
Evan CutlerAuthor Commented:
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?
0
 
appariCommented:
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.
0
 
Evan CutlerAuthor Commented:
This got me started.
It's much faster now.
Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now