In this article I will explain solution to the warning / error “The file you are trying to open is in a different format than specified by the file extension”, that one gets when he tries to open the Excel file that was generated when exporting GridView to Excel.
Cause
This happens because in the traditional Export to Excel method, the GridView is first converted to an HTML string and then that HTML string is exported to Excel. Thus originally it is not an Excel file hence the Excel Application throws the warning / error “The file you are trying to open is in a different format than specified by the file extension”.
Such an exported Excel file can only be used for display purpose only and you cannot use it for Excel operations that you can generally perform in a valid Excel file.
Solution
The solution to this problem is using ClosedXML library which is wrapper over the DocumentFormat.OpenXml library.
Now one question will come into your mind “Why to use an additional library i.e. ClosedXML when you have DocumentFormat.OpenXml library?”
The answer is since ClosedXML simplifies the methods and makes it easier for programmers to perform operations by providing intermediate functions and thus reducing the code and logic.
Download DocumentFormat.OpenXml and ClosedXML Libraries
You can download the libraries using the following download locations.
Note: You will need to install the OpenXml SDK 2.0 in your Windows Operating System.
HTML Markup
The HTML markup consists of an ASP.Net GridView and a Button to export the GridView contents to Excel file.
<asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
Binding the GridView
The GridView is populated using some dummy records using DataTable.
For more information on this technique please refer:
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id"), new DataColumn("Name"), new DataColumn("Country") });
dt.Rows.Add(1, "John Hammond", "United States");
dt.Rows.Add(2, "Mudassar Khan", "India");
dt.Rows.Add(3, "Suzanne Mathews", "France");
dt.Rows.Add(4, "Robert Schidner", "Russia");
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id"), New DataColumn("Name"), New DataColumn("Country")})
dt.Rows.Add(1, "John Hammond", "United States")
dt.Rows.Add(2, "Mudassar Khan", "India")
dt.Rows.Add(3, "Suzanne Mathews", "France")
dt.Rows.Add(4, "Robert Schidner", "Russia")
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub
![[Solution] GridView Export to Excel: The file you are trying to open is in a different format than specified by the file extension]()
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Export GridView contents to Excel using DocumentFormat.OpenXml and ClosedXML Libraries
Below is the code to export the GridView contents to Excel file, firstly a new DataTable is created with some name.
Note: The name of the DataTable is important as with the same name the Excel Sheet will be created.
Then columns are added to the DataTable by fetching the names of each column text in the GridView Header Row. Once the columns are added then a loop is executed over the GridView rows and the contents of each GridView Row are added to the DataTable Row.
Finally ClosedXML WorkBook object is created
and to its WorkSheet the DataTable is added.
Finally the WorkBook object is saved to the MemoryStream and the MemoryStream is written to the Response OutputStream which finally sends the Excel File for download.
C#
protected void ExportExcel(object sender, EventArgs e)
{
DataTable dt = new DataTable("GridView_Data");
foreach(TableCell cell in GridView1.HeaderRow.Cells)
{
dt.Columns.Add(cell.Text);
}
foreach (GridViewRow row in GridView1.Rows)
{
dt.Rows.Add();
for (int i=0; i<row.Cells.Count; i++)
{
dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
}
}
using (XLWorkbook wb = 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=GridView.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
VB.Net
Protected Sub ExportExcel(sender As Object, e As EventArgs)
Dim dt As New DataTable("GridView_Data")
For Each cell As TableCell In GridView1.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
For Each row As GridViewRow In GridView1.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
Using 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=GridView.xlsx")
Using MyMemoryStream As New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.[End]()
End Using
End Using
End Sub
![[Solution] GridView Export to Excel: The file you are trying to open is in a different format than specified by the file extension]()
Demo
Downloads
