In this article I will explain how to export GridView Data to Excel using OpenXml library in ASP.Net using C# and VB.Net.
I making use of ClosedXml library along with OpenXml as ClosedXml is a wrapper library of OpenXml and makes it easier to use.
 
Download DocumentFormat.OpenXml and ClosedXML Libraries
You can download the libraries using the following download locations.
Download OpenXml SDK 2.0
Download ClosedXML Library
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
View Demo
 
Downloads