TemplateField column values are missing when exporting ASP.Net GridView to Excel using ClosedXML

Hey smile,

Please refer below sample.

HTML

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="AdmissionNo" HeaderText="Reg No" />
        <asp:TemplateField HeaderText="Student Name">
            <ItemTemplate>
                <asp:Label ID="lbl_Name" runat="server" Text='<%# Bind("SName") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Remaining">
            <ItemTemplate>
                <asp:Label ID="lbl_RemBal" runat="server" Text='<%# Bind("RemBal") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField HeaderText="F Phone">
            <ItemTemplate>
                <asp:Label ID="Label5" runat="server" Text='<%# Bind("FPhone") %>'></asp:Label>
            </ItemTemplate>
            <ItemStyle HorizontalAlign="Center" />
        </asp:TemplateField>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID="checkAll" runat="server" onclick="checkAll(this);" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" onclick="Check_Click(this)" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<asp:Button Text="ExportToExcel" runat="server" OnClick="btnExportExcel_Click" />

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid();
    }
}
private void BindGrid()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("AdmissionNo", typeof(int)), new DataColumn("SName", typeof(string)), new DataColumn("RemBal", typeof(int)), new DataColumn("FPhone", typeof(string)) });
    dt.Rows.Add(1, "A", 200, "56456456456");
    dt.Rows.Add(2, "B", 300, "65656511121");
    dt.Rows.Add(3, "C", 400, "98742368542");
    GridView1.DataSource = dt;
    GridView1.DataBind();
}

protected void btnExportExcel_Click(object sender, EventArgs e)
{
    using (XLWorkbook wb = new XLWorkbook())
    {
        for (int i = 0; i < GridView1.PageCount; i++)
        {
            GridView1.PageIndex = i;
            this.BindGrid();
            DataTable dt = new DataTable("Page_" + (i + 1));
            for (int cell = 0; cell < GridView1.HeaderRow.Cells.Count - 1; cell++)
            {
                dt.Columns.Add(GridView1.HeaderRow.Cells[cell].Text);
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                dt.Rows.Add();
                for (int j = 0; j < row.Cells.Count - 1; j++)
                {
                    if (row.Cells[j].Controls.Count > 0)
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = (row.Cells[j].Controls[1] as Label).Text;
                    }
                    else
                    {
                        dt.Rows[dt.Rows.Count - 1][j] = row.Cells[j].Text;
                    }
                }
            }
            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=FeesDefaulterStu.xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    If Not Me.IsPostBack Then
        BindGrid()
    End If
End Sub

Private Sub BindGrid()
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("AdmissionNo", GetType(Integer)), New DataColumn("SName", GetType(String)), New DataColumn("RemBal", GetType(Integer)), New DataColumn("FPhone", GetType(String))})
    dt.Rows.Add(1, "A", 200, "56456456456")
    dt.Rows.Add(2, "B", 300, "65656511121")
    dt.Rows.Add(3, "C", 400, "98742368542")
    GridView1.DataSource = dt
    GridView1.DataBind()
End Sub

Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
    Using wb As XLWorkbook = New XLWorkbook()
        For i As Integer = 0 To GridView1.PageCount - 1
            GridView1.PageIndex = i
            Me.BindGrid()
            Dim dt As DataTable = New DataTable("Page_" & (i + 1))
            For cell As Integer = 0 To GridView1.HeaderRow.Cells.Count - 1 - 1
                dt.Columns.Add(GridView1.HeaderRow.Cells(cell).Text)
            Next
            For Each row As GridViewRow In GridView1.Rows
                dt.Rows.Add()
                For j As Integer = 0 To row.Cells.Count - 1 - 1
                    If row.Cells(j).Controls.Count > 0 Then
                        dt.Rows(dt.Rows.Count - 1)(j) = (TryCast(row.Cells(j).Controls(1), Label)).Text
                    Else
                        dt.Rows(dt.Rows.Count - 1)(j) = row.Cells(j).Text
                    End If
                Next
            Next

            wb.Worksheets.Add(dt)
        Next
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=FeesDefaulterStu.xlsx")
        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.[End]()
        End Using
    End Using
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
End Sub

Screenshot

Gridview Data

Excel Data