Export Datatable to excel very slow

Question

Greetings,

I am developing and app in vb.net that exports a datatable to excel for 2 different versions of excel: v.8.0 and v.11.

I am able to reference the 2 separate libraries: "8.0 Object Library" and 'Interop.Excel v.11'.

I am finding that the code for v.8.0 is much qucker than v.11.

For the v.8.0 I can 'speedily' add the data to the spreadsheet via:

  For i = 0 dtblExcel.Rows.Count - 1

      For x = 0 To dtblExcel.Columns.Count - 1

        sn(i, x) = dtblExcel.Rows(i).Item(x).ToString

      Next

  Next

  oSheet.Range("A2", "J11147").Value = sn

Where:

-dtblExcel is the datatable

-sn is an array

-oSheet is a worksheet object

If I try this w/ v.11 I get the following error:

'Does not support a collection.'

So to export the table to excel I use:

    For i = 0 dtblExcel.Rows.Count - 1

      For x = 0 To dtblExcel.Columns.Count - 1

        oSheet.Cells(i + 1, x+ 1).Value  = dtblExcel.Rows(i).Item(x).ToString

      Next

  Next

For some of the data it takes a very long time, 4 mintues for 11,000+ records with 10 columns.

I tried late binding but it did not work either.

Is there a quicker way to accomplish datatable exporting to excel in version 11?

Thanks for your time.

---

Pete

Answer 1

Hi Pete,

Please try the followings steps by Helen Toomik from StackOverflow.

Instead of setting cell values one by one, do it in a batch.

Step 1. Transfer the data from your DataTable into an array with the same dimensions.

Step 2. Define an Excel Range object that spans the appropriate range.

Step 3. Set the Range.Value to the array.

This will be a lot faster because you will have a total two calls across the Interop boundary (one to get the Range object, one to set its value), instead of two per cell (get cell, set value).

I hope this will be helpful.

Best regards,

---

Shanks Zen
MSDN Community Support | Feedback to us

Answer 2

Thanks Zen,

It is what I tried but with v.11 excel I get an error if I use early binding.

However, I used late binding and the datatable-to-array to-excel did work:

Dim oxlA as Object

Dim owB as Object

oxlA = CType(CreateObject("Excel.Application"), Microsoft.Office.Interop.Excel.Application)

owB = oxlA.Workbooks.Add

Dim oSheet As Object = CType(owB.Worksheets(1), Microsoft.Office.Interop.Excel.Workbook)

With this the following did work and was quick:

  For i = 0 dtblExcel.Rows.Count - 1

      For x = 0 To dtblExcel.Columns.Count - 1

        sn(i, x) = dtblExcel.Rows(i).Item(x).ToString

      Next

  Next

  oSheet.Range("A2", "J11147").Value = sn

**************************************************************

But another issue arose where I cannot use formatting with late binding, such as:

Dim oRng as Object

oRng = oSheet.Range("F2", "F11147")

oRng.Select()

oRng.NumberFormat = "$#,##0.00_);($#,##0.00) "

I'll submit this particular problem to a more appropriate group.

Thanks for your time.

---

Pete