Put datatable in new worksheet

Question

I'm trying to import a datatable into Excel using VB. I found tons of ways to do so, but all of them got an issue for me.

The first method I tried was using ClosedXML. It was great for importing data, but it messed up the pivot table. This is a known issue. Also, if the datatable is too big, it will throw and out of memory exception, another known issue.

Then I switched to Interop. But using Interop I only find one way to import the data, and that's with a double loop, as shown below. The issue for me here is, that it takes over 15 minutes to import a 3k rows, 39 columns table. This method seems slow and inefficient to me anyways, and I can't imagine Microsoft didn't implement a more effecive way to import larger datatables.

Is there someone who can show me a better method?

For i As Integer = 0 To datatable.Rows.Count() - 1
  For j As Integer = 0 To datatable.Columns.Count() - 1
    xlWorkSheet.Cells(i + 2, j + 1) = datatable.Rows(i)(j).ToString()
  Next
Next

Fadi's reply and a quick google search solved it for me.

    'dt is your Datatable and xlWorksheet is Your Worksheet
    Dim cc = dt.Columns.Count
    Dim rc = dt.Rows.Count        

    Dim arr(rc - 1, cc - 1) As Object
    For r = 0 To rc - 1
        Dim dr = dt.Rows(r)
        For c = 0 To cc - 1
            arr(r, c) = dr(c).ToString 'I added ToString() here, because it raised an type error
        Next
    Next
    xlWorkSheet.Range("A2").Resize(rc, cc).Value = arr

You can create 2D Array and fill it from datatable then assign this array to Excel range like here [Assign Values of 2D Array to Range of Cells](https://stackoverflow.com/questions/34733061/excel-vba-assign-values-of-2d-array-to-range-of-cells/34733237#34733237) in my test it take 670 milliseconds for 3k rows and 39 columns — Fadi, Jun 07 '21 at 20:21

Fadi · Accepted Answer · 2021-06-07T20:44:45.647

Filling Excel range cell by cell is slow, You can create 2D Array and fill it from Datatable then assign this array to Excel range.

This code take 670 milliseconds in my test on Datatable with 3000 rows and 39 columns.

    'dt is your Datatable and xlWorksheet is Your Worksheet
    Dim cc = dt.Columns.Count
    Dim rc = dt.Rows.Count        

    Dim arr(rc - 1, cc - 1) As Object
    For r = 0 To rc - 1
        Dim dr = dt.Rows(r)
        For c = 0 To cc - 1
            arr(r, c) = dr(c)
        Next
    Next
    xlWorkSheet.Range("A2").Resize(rc, cc).Value = arr

For Test:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim xl = New Excel.Application
    Dim wb = xl.Workbooks.Add()
    Dim xlWorkSheet As Excel.Worksheet = CType(wb.Worksheets(1), Excel.Worksheet)
    Dim dt = CreateDatatable()

    Dim cc = dt.Columns.Count
    Dim rc = dt.Rows.Count
    Dim tt = Now.Ticks

    Dim arr(rc - 1, cc - 1) As Object
    For r = 0 To rc - 1
        Dim dr = dt.Rows(r)
        For c = 0 To cc - 1
            arr(r, c) = dr(c)
        Next
    Next
    xlWorkSheet.Range("A2").Resize(rc, cc).Value = arr
    Dim ee = (Now.Ticks - tt) / 10000
    MsgBox(ee)
    xl.Visible = True
    xl.UserControl = True

End Sub

Function CreateDatatable() As DataTable

    Dim dt = New DataTable
    For c = 1 To 39
        dt.Columns.Add($"Col{c}")
    Next
    For r = 1 To 3000
        Dim dr As DataRow = dt.NewRow
        For c = 1 To 39
            dr(c - 1) = $"R{r},C{c}"
        Next
        dt.Rows.Add(dr)
    Next
    Return dt

End Function

Put datatable in new worksheet

1 Answers1