Excel write throughput

Posted on 2012-08-23
Last Modified: 2012-08-28
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.
Question by:Evan Cutler
    LVL 39

    Accepted Solution

    In my applications I do it like this, works faster.
    Code is in, 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

    LVL 39

    Expert Comment

    excelSheet is the current active sheet object. startRow is the row number in excel sheet, where we want to insert the data.
    LVL 9

    Author Comment

    by:Evan Cutler
    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?
    LVL 39

    Expert Comment

    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.
    LVL 9

    Author Closing Comment

    by:Evan Cutler
    This got me started.
    It's much faster now.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now