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