Block copy of Datatable to Excel Spreadsheet using C#

[Peter S.]

I am currently copying data from a DataTable to an Excel spreadsheet on a
cell by cell basis. I would like to speed things up by doing this on a row by
row basis or one big block copy. I can't seem to be able to figure out on how
to do this on a row by row basis or by one single command. Can anyone supply
a snippet of how this can be performed? Thanks!

 

[Ralph]

I used the code from this web site, it uses copyfromrecordset in Excel. Works
well

http://www.codeproject.com/cs/database/DataTableToRecordset.asp

 

[crferguson]

I'm not sure exactly what the code would be in C#, but all you need is
to get the excel workbook object and then set an array equal to the
range of cells you're wanting to pull. Assuming you already have the
workbook object (let's say MyWorkbook) since you're already pulling
cell by cell:

Dim arrRange
arrRange = MyWorkbook.Range("A1:C10")

 

[crferguson]

Oh, I totally misunderstood the question. My apologies...

 

[Ralph]

Sorry I should have said you can use copyfrom recordset with the function
something like:

Range("A1").CopyFromRecordset(ConvertToRecordset(tableToCopy))

 

[crferguson]

However, in spite of misreading your question, I don't think the
content of the link posted addresses it either. Perhaps if you can
get the contents of the datatable into an array (or it might work
straight from the datatable if the Excel object recognizes it as an
array), you can simply set the Excel Range equal to your array. Kind
of the reverse of what I posted in the first place. You just have to
specify the range specifically. For example if your datatable has 5
columns and however many rows:


MyWorkbook.Range("A1:E" & ubound(YourZeroBasedArray)+1) =
YourZeroBasedArray

 

[Peter S.]

This is an interesting solution, thanks for the response. Do I have to go
through some hoops to convert the DataTable to a recordset? It seems like it
is not a straighforward process. I am wondering if the conversion (to a
recordset) might only be somewhat more efficient than populating each cell
individually?? It would be optimal if I could use the DataTable without
conversion/modifications....

 

[Ralph]

It did not seem liike a good alternative to me either. But after testing it
on a datatable that was copying over 2,000 records I noticed a significant
improvement. I copied both of the functions from the web page to a new class
module in my project then used the

Range("A1").CopyFromRecordset(ConvertToRecordset(tableToCopy))

in place of looping through each row in the datatable.

 

[Peter S.]

Your right! I tried it and as soon as I pulled the records from the database
the Excel spreadsheet was built instantaneously! This is definitely much
faster! Whereas before it would take at least 15 seconds minimum. For
everyone's information I also needed to include the COM reference: Microsoft
ActiveX Data Objects 2.8 Library in addition to the two functions you posted
in the first reply. Thanks again!!!!

Here is what I used for code (I started at A2 because I write column headers
in the spreadsheet)

Excel.Range tableRange = excelApp.get_Range("A2","A2");

tableRange.CopyFromRecordset(ConvertToRecordset(dataTbl),dataTbl.Rows.Count,dataTbl.Columns.Count);

=====================================================

 

[Peter S.]

One small note, I set a breakpoint on the TranslateType function and noticed
when it receives a type of Decimal it returns adCurrency. So that looked
great however I noticed that all my data shows up with a cell format of
General. Is this something that is out of my control?

 

[Ralph]

set a range variable for the columns to format then use numberformat:

tablerange.NumberFormat= "$#,##0.00"