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

Question

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

I have this event to export the contents of the gridview to an excel sheet, and it performs its tasks well, except that the generated file gives a warning when the user opens it (which I understand because the created file is not the actual excel file) :

"the file you are trying to open is in a different format than the one 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, exactly the same as shown on the Gridview):

---

, , ClosedXML, :

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

( - Excel 2007 +, ):

"" , closedXML?

Answer 1

( ClosedXML) , Text GridViewRow TemplateField. , Text BoundField .

TemplateField, , .

:

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

:

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

:

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

, Label, TemplateField, ID "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

, , , :

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

Excel ,

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

utils Extensions, ExportToExcel_SomeReport

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

, .html, Excel .

HTML:

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

Excel :

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

EDIT: , , , .

2: , ... ClosedXML... , - HTML Excel.