Error with EPPlus Loading DataTable to ExcelWorksheet

395 Views Asked by At

I have the following code, where I use a SqlDataAdapter to fill a DataTable. I'm using EPPlus to create an Excel Package and worksheet, but when I call LoadFromDataTable, the worksheet is not being populated with data from the data table. I'm not sure wherein lies the problem, but I'm stumped.

Protected Sub btnExportToExcel_Click(sender As Object, e As EventArgs)

    Dim dt As DataTable = New DataTable()
    Dim da As SqlDataAdapter = New SqlDataAdapter("usp_StoredProcedure", sQLConnString)
    da.SelectCommand.CommandType = CommandType.StoredProcedure
    da.SelectCommand.Parameters.AddWithValue("param1", "text")
    da.SelectCommand.Parameters.AddWithValue("param2", "text")
    da.SelectCommand.Parameters.AddWithValue("param3", "text")
    da.SelectCommand.Parameters.AddWithValue("param4", "text")
    da.SelectCommand.Parameters.AddWithValue("param5", "text")
    da.SelectCommand.Parameters.AddWithValue("param6", "text")
    da.SelectCommand.Parameters.AddWithValue("param7", "text")
    da.SelectCommand.Parameters.AddWithValue("param8", "text")

    Try
        da.Fill(dt)
    Catch ex As Exception
        ' Do Stuff
        Return
    End Try

    If dt.Rows.Count > 0 Then
        Using pck As ExcelPackage = New ExcelPackage()
            Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("SheetName")
            ws.Cells("A1").LoadFromDataTable(dt, True)
            Response.ClearContent()
            Response.BufferOutput = True
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("Content-Disposition", "attachment; filename=" + String.Format("FileName-{0:yyyy-MM-dd-HH-mm-ss}.xlsx", DateTime.UtcNow))
            Response.BinaryWrite(pck.GetAsByteArray())
            Response.Flush()
            Response.End()
        End Using
    End If
End Sub
0

There are 0 best solutions below