-1

I have a website in Asp.net and my code behind is as follows

 Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
    Response.Clear()
    Response.AddHeader("content-disposition", "attachment;filename=FileName.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.xls"
    Dim stringWrite As New System.IO.StringWriter()
    Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
    GridView5.RenderControl(htmlWrite)
    Response.Write(stringWrite.ToString())
    Response.[End]()
End Sub

The file is downloads by clicking on the button but when I open it using Microsoft Excel it gives me the message that the file format and extension does not match is there any way to bypass this. I have tried using alternative formats like csv too.

Snb93
  • 41
  • 2
  • 11
  • Start using a specialized library for creating Excel files, like [EPPlus](http://stackoverflow.com/documentation/epplus/drafts/98280) for example. All you are doing now is creating a HTML page with an .xls extension. – VDWWD Jul 31 '17 at 17:49
  • @VDWWD thanks foe the update I will go through the link – Snb93 Jul 31 '17 at 18:10

1 Answers1

0

Using Imports ClosedXML.Excel and adding the following code behind solves the issue

aspx.vb

Protected Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
    Dim dt As New DataTable("GridView_Data")
    For Each cell As TableCell In GridView5.HeaderRow.Cells
        dt.Columns.Add(cell.Text)
    Next
    For Each row As GridViewRow In GridView5.Rows
        dt.Rows.Add()
        For i As Integer = 0 To row.Cells.Count - 1
            dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
        Next
    Next
    Dim wb As New XLWorkbook
    wb.Worksheets.Add(dt)
    Response.Clear()
    Response.Buffer = True
    Response.Charset = ""
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    Response.AddHeader("content-disposition", "attachment;filename=GridViewPOLQA.xlsx")
    Using MyMemoryStream As New MemoryStream()
        wb.SaveAs(MyMemoryStream)
        MyMemoryStream.WriteTo(Response.OutputStream)
        Response.Flush()
        Response.[End]()
    End Using
End Sub
Snb93
  • 41
  • 2
  • 11