Export to Excel GridView Data using OpenXml in ASP.Net

Introduction:
 In this article I will explain how to Export to Excel GridView Data using OpenXml in ASP.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

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.

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, "Asma Qureshi", "Pakistan");

        dt.Rows.Add(2, "Safder Khan", "Pakistan");

        dt.Rows.Add(3, "Murat Kozuklo", "Turket");

        dt.Rows.Add(4, "Robert Brown", "Russia");

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

}

Namespaces.

using System.IO;

using System.Data;

using 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.
 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.

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

        {

            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();

        }

    }

}

You can download the Project