How to export data from placeholder control to Excel using asp.net web page?

Question

User-1640542475 posted

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

   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

</div>

Answer 1

User665608656 posted

Hi maideen,

Since you only provided part of the code, what I need to confirm to you is where your TruckMovementReport method is executed?

If this method is executed in the page_load method, did you place the method in this statement:

If Not IsPostBack Then

If so,I do reproduce the issue you have encountered.

To sove this issue, you should call TruckMovementReport method when pageload, not just when the page is first loaded, because when you click the button to download, the page refreshes and the data is not exported to the excel.

Here is a complete example:

<form id="form1" runat="server">
        <div>
            <asp:PlaceHolder ID="PlaceHolder1" runat="server"></asp:PlaceHolder>
            <br />
            <asp:Button ID="Button1" runat="server" Text="Export"   OnClick="Button1_Click"/>
        </div>
    </form>

Class SurroundingClass
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        TruckMovementReport()
    End Sub

    Private Sub TruckMovementReport()
        Dim dt As DataTable = GetTruckMovementDATA()
        Dim html As StringBuilder = 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()
        })
    End Sub

    Private Function GetTruckMovementDATA() As DataTable
        Dim dt As DataTable = New DataTable()
        Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand("SELECT * from Employee", conn)
        Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ds As DataSet = New DataSet()
        da.Fill(ds)
        dt = ds.Tables(0)
        conn.Close()
        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

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)
        ExportTableToExcel()
    End Sub
End Class

Best Regards,

YongQing.