Export GridView to Excel (xlsx format) using OpenXml in ASP.Net

Hi akhter,

I checked below code is working.

HTML

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" OnRowDataBound="gvCustomers_RowDataBound"
    ShowFooter="true">
    <Columns>
        <asp:TemplateField HeaderText="Id">
            <ItemTemplate>
                <asp:Label ID="lblId" runat="server" Text='<%#Bind("Id")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label ID="lblName" runat="server" Text='<%#Bind("Name")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:Label ID="lblContry" runat="server" Text='<%#Bind("Country")%>'></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" runat="server" OnClick="btn_ExportExcel_Click" />

Namespaces

C#

using System.IO;
using System.Data;
using ClosedXML.Excel;

VB.Net

Imports System.IO
Imports System.Data
Imports ClosedXML.Excel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = GetData();
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

private static DataTable GetData()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Country",typeof(string)) });
    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");
    return dt;
}

protected void btn_ExportExcel_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach (TableCell cell in gvCustomers.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }
    foreach (GridViewRow row in gvCustomers.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            if (row.Cells[i].Controls.Count > 0)
            {
                dt.Rows[dt.Rows.Count - 1][i] = (row.Cells[i].Controls[1] as Label).Text;
            }
            else
            {
                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 Page_Load(ByVal sender As Object, ByVal e As EventArgs)Handle Me.Load
        If Not Me.IsPostBack Then
            Dim dt As DataTable = GetData()
            gvCustomers.DataSource = dt
            gvCustomers.DataBind()
        End If
    End Sub

    Private Shared Function GetData() As DataTable
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
        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")
        Return dt
    End Function

    Protected Sub btn_ExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim dt As DataTable = New DataTable("GridView_Data")
        For Each cell As TableCell In gvCustomers.HeaderRow.Cells
            dt.Columns.Add(cell.Text)
        Next

        For Each row As GridViewRow In gvCustomers.Rows
            dt.Rows.Add()
            For i As Integer = 0 To row.Cells.Count - 1
                If row.Cells(i).Controls.Count > 0 Then
                    dt.Rows(dt.Rows.Count - 1)(i) = (TryCast(row.Cells(i).Controls(1), Label)).Text
                Else
                    dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
                End If
            Next
        Next

        Using wb As XLWorkbook = 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 MemoryStream = New MemoryStream()
                wb.SaveAs(MyMemoryStream)
                MyMemoryStream.WriteTo(Response.OutputStream)
                Response.Flush()
                Response.End()
            End Using
        End Using
    End Sub

Screenshot