Exporting multiple datasets to single Excel worksheet with EPPlus

Question

I'm not able to find examples on how to export multiple data sets/tables to a single Excel worksheet utilizing the EPPlus library. When I run the codes, it returns one set that happens to be the last set. What am I doing wrong with the following codes? Thanks!

Dim dSets as DataSets
Dim dGrid as DataGrid
Dim dTable as DataTable
Dim sheet as String
sheet = "DumpSets"
Dim attachment as String
attachment = "attachment; filename=" + sheet + ".xlsx"
Dim xlPack as ExcelPackage = New ExcelPackage()
Dim ws as ExcelWorksheet = xlPack.Workbook.Worksheets.Add(sheet)
Response.Clear()
Response.AddHeader("content-disposition", attachment)
Response.Charset = ""

For each dTable in dSets.Tables
    dGrid = New DataGrid
    Me.EnableViewState = False

    dGrid.DataSource = dTable
    ws.Cells(1, 1).LoadFromDataTable(dGrid.DataSource, True)

    dGrid.DataBind()
Next

Response.BinaryWrite(xlPack.GetAsByteArray())
Response.End()

Answer 1

As @Blackwood already mentioned, you always add the data in the same place.

So it should be something like this:

Dim cnt As Integer = 1

For each dTable in dSets.Tables
    dGrid = New DataGrid
    Me.EnableViewState = False

    dGrid.DataSource = dTable
    ws.Cells(1, cnt).LoadFromDataTable(dGrid.DataSource, True)

    dGrid.DataBind()

    cnt = (cnt + 1)
Next

You could also add every dataset to it's own sheet.

Dim cnt As Integer = 1
For Each dt As DataTable In dSets.Tables
    Dim ws As ExcelWorksheet = xlPack.Workbook.Worksheets.Add(("Sheet " + cnt))
    ws.Cells(1, 1).LoadFromDataTable(dGrid.DataSource, True)
    cnt = (cnt + 1)
Next