Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi

I tried to export the place Holder data which is fetched from Mssql database.
Data are display in asp.net page. But if Click the Button to export,
Excel is empty.

Pls advice me

Thank you

Maideen

What I have tried:

Private Sub TruckMovementReport()
    Dim dt As DataTable = GetTruckMovementDATA()
    Dim html As New StringBuilder()
    html.Append("<table border = '1'>")
    html.Append("<tr>")
    For Each column As DataColumn In dt.Columns
        html.Append("<th>")
        html.Append(column.ColumnName)
        html.Append("</th>")
    Next
    html.Append("</tr>")

    For Each row As DataRow In dt.Rows
        html.Append("<tr>")
        For Each column As DataColumn In dt.Columns
            html.Append("<td>")
            html.Append(row(column.ColumnName))
            html.Append("</td>")
        Next
        html.Append("</tr>")
    Next
    html.Append("</table>")
    PlaceHolder1.Controls.Add(New Literal() With {.Text = html.ToString()})
    cmd = Nothing
    conn.Close()
End Sub
Private Function GetTruckMovementDATA() As DataTable
    Dim cmd As New SqlCommand
    cmd = New SqlCommand
    cmd.CommandText = "ZR_usp_BulkExport_XLS"
    cmd.Parameters.AddWithValue("@Action", "EXPORT_ALL")

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Connection = conn
    conn.Open()
    cmd.ExecuteNonQuery()
    Dim da As New SqlDataAdapter(cmd)
    Dim dt As New DataTable
    da.Fill(dt)
    Return dt
End Function

Private Sub ExportTableToExcel()
    Response.ContentType = "application/vnd.ms-excel"
    Response.AddHeader("Content-Disposition", "attachment; filename=test.xls;")
    Response.ContentEncoding = Encoding.UTF8
    Dim stringWrite As StringWriter = New StringWriter()
    Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite)
    Me.PlaceHolder1.RenderControl(htmlWrite)
    Response.Write(stringWrite.ToString())
    Response.[End]()
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ExportTableToExcel()
End Sub
Posted
Updated 25-Jul-19 4:37am

Exporting a DataTable to Excel is trivial with EPPlus (GitHub[^], NuGet[^]):
VB.NET
Private Sub ExportTableToExcel()
    Response.Clear()
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.AddHeader("Content-Disposition", "attachment; filename=test.xlsx")
    
    Dim dt As DataTable = GetTruckMovementDATA()

    Using package As New ExcelPackage()
        Dim ws As ExcelWorksheet = package.Workbook.Worksheets.Add("Report")
        ws.Cells("A1").LoadFromDataTable(dt, True)
        Dim ms As New System.IO.MemoryStream()
        package.SaveAs(ms)
        ms.WriteTo(Response.OutputStream)
    End Using
End Sub
 
Share this answer
 
v2
Comments
Maideen Abdul Kader 25-Jul-19 21:21pm    
Thank you Mr.Richard
Error is in ws.cell["A1"]
pls advice me
Thank you
Maideen Abdul Kader 25-Jul-19 21:34pm    
Thank you I have changed ["A1"] to ("A1") it is working fine
Nowhere in that code do you export anything resembling an Excel sheet to a file.

Writing an HTML table to a file with an extension of ".xls" does NOT make it an Excel workbook.

You actually have to write Excel content to the file. You can do that with the OpenXML SDK, or ClosedXML SDK.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900