ASP.Net Export to Excel: The file you are trying to open is in a different format than specified by the file extension

on

ASP.Net Export to Excel: The file you are trying to open is in a different format than specified by the file extension

Step 1

Download and Install suitable version of ClosedXML and OpenXML versions

OPEN XML v2
OPEN XML V2.5 for .Net 4.0

  1. Download and install the OpenXML
  2. Within my project in Visual Studio, select "Project" then "Add Reference"
  3. Select the "Browse" tab
  4. In the "Look in:" pull down, navigate to: C:\Program Files(x86)\Open XML SDK\V2.0\lib and select the "DocumentFormat.OpenXml.dll
  5. Hit OK
  6. In the "Solution Explorer" (on the right for me), the "References" folder now shows the DocumentFormat.OpenXML library.
  7. Right-click on it and select Properties
  8. In the Properties panel, change "Copy Local" to "True".
Download and select "Project" then "Add Reference" --> refer Closed XML dll.
Step 2.
Import ClosedXML.excel in your class or vb file.
Imports ClosedXML.Excel
Step 3 Write method for export to excel
 Public Shared Sub ExportDataSetToExcel(ByVal ds As DataSet, ByVal filename As String)
            Dim response As HttpResponse = HttpContext.Current.Response
           ' first let's clean up the response.object   
            response.Clear()
            response.Charset = ""

               'Newer version excel export options
            Using wb As New XLWorkbook()
                wb.Worksheets.Add(ds)

                response.Clear()
                response.Buffer = True
                response.Charset = ""
                response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                response.AddHeader("Content-Disposition", "attachment;filename=""" & filename & """")
                Using MyMemoryStream As New MemoryStream()
                    wb.SaveAs(MyMemoryStream)
                    MyMemoryStream.WriteTo(response.OutputStream)
                    response.Flush()
                    response.[End]()
                End Using
            End Using

        End Sub

Other Sources

Comments

Post a Comment