Gridview Export to Excel VB.Net Error

Question

User475570419 posted

I am getting the file is corrupt when exporting a gridview to Excel.  Below is my code.  Any help is greatly appreciated.

 Try
            GridView1.AllowPaging = "False"
            GridView1.DataBind()
            Dim tw As New StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frm As HtmlForm = New HtmlForm()
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("content-disposition", "attachment; filename=" & txtDateFrom.Text.ToString & "_" & txtDateTo.Text.ToString & "- DR Report" & ".xls")
            'Response.AddHeader("content-disposition", "attachment;filename=" & "output.xls")
            Response.Charset = ""
            EnableViewState = False
            Controls.Add(frm)
            frm.Controls.Add(GridView1)
            frm.RenderControl(hw)
            Response.Write(tw.ToString())
            Response.End()
            GridView1.AllowPaging = "True"
            GridView1.DataBind()

        Catch ex As Exception
            lblerror.Text = ex.Message
        End Try

Answer 1

User-691209617 posted

Hi,

Try to use CloseXML library for export purpose.

Here is a working solution for you.

http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension.aspx

Hope it helps.

Answer 2

User-271186128 posted

Hi Nighthawk0712,

Gridview Export to Excel VB.Net Error

If you can provide the detail information about the error, it might be easier for us to guide you and help you get what you need.

Also, you could try to use the following code to export GridView to Excel:

Protected Sub ExportToExcel(sender As Object, e As EventArgs)
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Using sw As New StringWriter()
        Dim hw As New HtmlTextWriter(sw)
 
        'To Export all pages
        GridView1.AllowPaging = False
        Me.BindGrid()
 
        GridView1.HeaderRow.BackColor = Color.White
        For Each cell As TableCell In GridView1.HeaderRow.Cells
            cell.BackColor = GridView1.HeaderStyle.BackColor
        Next
        For Each row As GridViewRow In GridView1.Rows
            row.BackColor = Color.White
            For Each cell As TableCell In row.Cells
                If row.RowIndex Mod 2 = 0 Then
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor
                Else
                    cell.BackColor = GridView1.RowStyle.BackColor
                End If
                cell.CssClass = "textmode"
            Next
        Next
 
        GridView1.RenderControl(hw)
        'style to format numbers to string
        Dim style As String = "<style> .textmode { } </style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.[End]()
    End Using
End Sub
 
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
    ' Verifies that the control is rendered 
End Sub

Best regards,
Dillion

Answer 3

User475570419 posted

I used this code to get this work

  Response.ClearContent()
        Response.AddHeader("content-disposition", "attachment; filename=" & txtDateFrom.Text.ToString & "-" & txtDateTo.Text.ToString & "- DR Report" & ".xls")
        Response.ContentType = "application/excel"
        Dim sw As New System.IO.StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        GridView1.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.[End]()

Thank you both for replying.