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

Excel write throughput

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.
Evan Cutler
Evan Cutler
  • 3
  • 2
1 Solution
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
	  excelData(currentDataTableRow, currentDataTableCol) = ""
	end if
                  currentDataTableCol += 1
                currentDataTableRow += 1

            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

excelSheet is the current active sheet object. startRow is the row number in excel sheet, where we want to insert the data.
Evan CutlerVolunteer Chief Information OfficerAuthor 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?
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.
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
This got me started.
It's much faster now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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