export Gridview to Excel using ClosedXML without warning: the file you are trying to open is in a different format

Question

I am working on a ASP.NET 4.5 Webform and I have a Gridview (that has custom TemplateField and gets data from a sqlDataSource)

I have this event to export the gridview contents to an excel sheet, and it does its jobs well except the created file is giving out an warning when user open it (which I understand because the file that got created is not an actual excel file):

"the file you are trying to open is in a different format than specified by the file extension"

protected void btnExport_Excel_Click(object sender, EventArgs e)
        {
            try
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=GV.xls");
                Response.Charset = "";
                Response.ContentType = "application/ms-excel";
                //Response.ContentType = "application/text";
                Response.ContentEncoding = System.Text.Encoding.Unicode;
                Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

                using (StringWriter sw = new StringWriter())
                {
                    HtmlTextWriter hw = new HtmlTextWriter(sw);

                    //To Export all pages
                    GridView4.AllowPaging = false;
                    GridView4.AllowSorting = false;
                    GridView4.ShowFooter = false;
                    GridView4.DataBind();
                    //this.BindGrid();

                    GridView4.HeaderRow.BackColor = Color.White;
                    foreach (TableCell cell in GridView4.HeaderRow.Cells)
                    {
                        cell.BackColor = GridView4.HeaderStyle.BackColor;
                    }
                    foreach (GridViewRow row in GridView4.Rows)
                    {
                        row.BackColor = Color.White;
                        foreach (TableCell cell in row.Cells)
                        {
                            if (row.RowIndex % 2 == 0)
                            {
                                cell.BackColor = GridView4.AlternatingRowStyle.BackColor;
                            }
                            else
                            {
                                cell.BackColor = GridView4.RowStyle.BackColor;
                            }
                            cell.CssClass = "textmode";
                        }
                    }

                    GridView4.RenderControl(hw);

                    //style to format numbers to string
                    string style = @"<style> .textmode { } </style>";
                    Response.Write(style);
                    Response.Output.Write(sw.ToString());
                    Response.Flush();
                    Response.End();
                }

                //Display message
                InfoPanel.Visible = true;
                InfoPanel.CssClass = "panel panel-success";
                lblMessage.CssClass = "text text-sucess bold";
                lblMessage.Text = "File has been exported!";

            }
            catch (Exception ex)
            {
                //Display message
                InfoPanel.Visible = true;
                lblMessage.Text = "<b>An error has occurred. Please try again later!</b></br>" + ex.Message;
                lblMessage.CssClass = "text text-danger bold";
                InfoPanel.CssClass = "panel panel-danger";
                panelResult.Visible = false;
            }
        }

the result in the Excel .xls file is good (no styles except header columns, no footer, just exact as shown on the Gridview):

---

I am finding another way to avoid this warning, so I see people like to use
ClosedXML, so I replace that event above with this event:

protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach(TableCell cell in GridView4.HeaderRow.Cells)
    {
        dt.Columns.Add(cell.Text);
    }
    foreach (GridViewRow row in GridView4.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=GV.xlsx");

        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

and the result is bad (only good new is that the exported file is a real 2007+ Excel sheet so no warnings):

How do I get the "good" result above using closedXML?

Answer 1

The main problem in you second part of code (with ClosedXML) , that you are trying to use Text property of GridViewRow for TemplateField field columns. As you can see here, you can get field value via Text property only for BoundField field columns and automatically generated field columns.

To get value from TemplateField you should navigate to inner control which contains value and get value from it.

If you have the following column template:

   <asp:TemplateField>
       <ItemTemplate>
           <asp:Label ID="labelName" runat="server" Text ='<%# Eval("ABC")%>' ></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>

Your code should be:

    for (int i=0; i<row.Cells.Count; i++)
    {
        dt.Rows[dt.Rows.Count - 1][i] = (row.Cells[i].FindControl("labelName") as Label).Text;
    }

EDIT

Your code should be as follows:

protected void ExportExcel(object sender, EventArgs e)
{
    DataTable dt = new DataTable("GridView_Data");
    foreach (DataControlField col in GridView4.Columns)
    {
        dt.Columns.Add(col.HeaderText);
    }
    foreach (GridViewRow row in GridView4.Rows)
    {
        dt.Rows.Add();
        for (int i = 0; i < row.Cells.Count; i++)
        {
            dt.Rows[dt.Rows.Count - 1][i] = (FindControl(row.Cells[i].Controls, "lbl") as Label).Text;
        }
    }
    //your code below is not changed
}

protected Control FindControl(ControlCollection collection, string id)
{
    foreach (Control ctrl in collection)
    {
        if (ctrl.ID == id)
            return ctrl;
    }

    return null;
}

Ensure that all Label controls used in TemplateField have the same ID as "lbl":

   <asp:TemplateField HeaderText="ID">
       <ItemTemplate>
           <asp:Label ID="lbl" runat="server" Text ='<%# Eval("ID")%>' ></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText="Name">
       <ItemTemplate>
           <asp:Label ID="lbl" runat="server" Text ='<%# Eval("Name")%>' ></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>
   <asp:TemplateField HeaderText="Amount">
       <ItemTemplate>
           <asp:Label ID="lbl" runat="server" Text ='<%# Eval("Amount")%>' ></asp:Label>
       </ItemTemplate>
   </asp:TemplateField>

Answer 2

I tried it's working ,please find the code hope it will help you:

Index.aspx

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Index.aspx.cs" Inherits="ExportExcel.Index" %>

  <!DOCTYPE html>

  <html xmlns="http://www.w3.org/1999/xhtml">
  <head runat="server">
  <title></title>
  </head>
  <body>
<form id="form1" runat="server">
<div>
    <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:TemplateField HeaderText="Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtName" runat="server" Text='<%#Eval("Name") %>'></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Country">
                <ItemTemplate>
                    <asp:Label ID="lblCountry" Text='<%# Eval("Country") %>' runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="btnExport" Text="Export" runat="server" OnClick="btnExport_Click" />
</div>
</form>

Index.aspx.cs

        using ClosedXML.Excel;
        using System;
        using System.Collections.Generic;
        using System.Data;
        using System.Drawing;
        using System.IO;
        using System.Linq;
        using System.Web;
        using System.Web.UI;
        using System.Web.UI.WebControls;

   namespace ExportExcel
    {
    public partial class Index : System.Web.UI.Page
    {
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!this.IsPostBack)
        {
            GetData();
        }
    }

    private void 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, "abc", "UK");
        dt.Rows.Add(2, "def", "India");
        dt.Rows.Add(3, "ghi", "France");
        dt.Rows.Add(4, "jkl", "Russia");
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable dt = new DataTable("GridView_Data");
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                dt.Columns.Add(cell.Text);
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                TextBox txtNameRow = (TextBox)row.FindControl("txtName");

                Label lblCountryRow = (Label)row.FindControl("lblCountry");

                DataRow drow = dt.NewRow();
                for (int i = 0; i < GridView1.Columns.Count; i++)
                {
                    drow[i] = row.Cells[i].Text;
                }
                drow["Name"] = txtNameRow.Text;
                drow["Country"] = lblCountryRow.Text;
                dt.Rows.Add(drow);
            }
            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=GV.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }

        }
        catch (Exception ex)
        {

            throw;
        }
    }




}
}