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;
        }
    }




}
}

Answer 3

I call the Export to Excel on a button click event like the following

protected void btnPrint_Click(object sender, EventArgs e)
{
    fileName = string.Format(fileName, DateTime.Now.ToString("MMddyyyy_hhmmss"));
    Extensions.ExportToXcel_SomeReport(dt, fileName, this.Page);
}

from there I have a utils class called Extensions where I have the ExportToExcel_SomeReport method defined

public static class Extensions
{
     internal static void ExportToXcel_SomeReport(DataTable dt, string fileName, Page page)
    {
        var recCount = dt.Rows.Count;
        RemoveHtmlSpecialChars(dt);
        fileName = string.Format(fileName, DateTime.Now.ToString("MMddyyyy_hhmmss"));
        var xlsx = new XLWorkbook();
        var ws = xlsx.Worksheets.Add("Some Report Name");
        ws.Style.Font.Bold = true;
        ws.Cell("C5").Value = "YOUR REPORT NAME";
        ws.Cell("C5").Style.Font.FontColor = XLColor.Black;
        ws.Cell("C5").Style.Font.SetFontSize(16.0);
        ws.Cell("E5").Value = DateTime.Now.ToString("MM/dd/yyyy HH:mm");
        ws.Range("C5:E5").Style.Font.SetFontSize(16.0);
        ws.Cell("A7").Value = string.Format("{0} Records", recCount);
        ws.Style.Font.Bold = false;
        ws.Cell(9, 1).InsertTable(dt.AsEnumerable());
        ws.Row(9).InsertRowsBelow(1);
       // ws.Style.Font.FontColor = XLColor.Gray;
        ws.Columns("1-9").AdjustToContents();
        ws.Tables.Table(0).ShowAutoFilter = true;
        ws.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        DynaGenExcelFile(fileName, page, xlsx);
    }


    /// <summary>
    /// Remove all HTML special characters from datatable field if they are present 
    /// </summary>
    /// <param name="dt"></param>
    private static void RemoveHtmlSpecialChars(DataTable dt)
    {
        for (int rows = 0; rows < dt.Rows.Count; rows++)
        {
            for (int column = 0; column < dt.Columns.Count; column++)
            {
                dt.Rows[rows][column] = dt.Rows[rows][column].ToString().Replace("&nbsp;", string.Empty);
            }
        }
    }

    /// <summary>
    /// Call this Method to Generate the Excel Files from different Lap Reports depending on which one has been selected
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="page"></param>
    /// <param name="xlsx"></param>
    private static void DynaGenExcelFile(string fileName, Page page, XLWorkbook xlsx)
    {
        page.Response.ClearContent();
        page.Response.ClearHeaders();
        page.Response.ContentType = "application/vnd.ms-excel";
        page.Response.AppendHeader("Content-Disposition", string.Format("attachment;filename={0}.xlsx", fileName));

        using (MemoryStream memoryStream = new MemoryStream())
        {
            xlsx.SaveAs(memoryStream);
            memoryStream.WriteTo(page.Response.OutputStream);
        }
        page.Response.Flush();
        page.Response.End();
    }   

}

Answer 4

Contrary to popular belief, you can set the extension of your file to be .html, and Excel can open it up.

Just set the extension to HTML:

Response.AddHeader("content-disposition", "attachment;filename=GV.html");

And retain Excel as the content type:

Response.ContentType = "application/ms-excel"; 

EDIT: Oh, right, forgot to mention, this should make that annoying dialog go away.

EDIT 2: Looks like the original question has changed... now it's talking about using ClosedXML... but I'll leave this answer here just in case someone else is using HTML and Excel.