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?

Solution 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>

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




}
}

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

}

Solution 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.

View more solutions

Comments

[Ronaldinho Learn Coding]

How do I get all the headers, in my code I have something like <asp:TemplateField HeaderText="Amount" SortExpression="Amount" HeaderStyle-Width="8%">

[Ronaldinho Learn Coding]

Also can you help with the loop, so I have like 7 ItemTemplate, most are labels, how do I get the data? It's would be really helpful if you can show a complete function or the codes/loop that will get the data.

[Ronaldinho Learn Coding]

I still can't get this working, can you show an example, assume that there are 5 TemplateField (with HeaderText), each has 1 ItemTemplate which contain 1 label. How your functions look like?

[Pavel Timoshenko]

I've changed answer. I hope it will help you to solve the problem.

[Ronaldinho Learn Coding]

For the header column, got an err: 'object' does not contain a definition for 'HeaderText' and no extension method 'HeaderText' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)

[Ronaldinho Learn Coding]

I was looking at these articles, can you take a look at them: codeproject.com/Questions/1027572/… and this: aspsnippets.com/Articles/… to find out? Your code was not working?

[Pavel Timoshenko]

I did a small fix. Everything works correctly for me.

[mason]

This method serves an XLSX file up with the MIME type of an XLS file and using the .XLS file extension. It would be better to use the proper extension and MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.‌​sheet.

[MethodMan]

@mason I am using ClosedXML and I am well aware.. if you have a better working version by all means please post an it as an answer

[mason]

I do not understand your comment. You're serving an XLSX file with the MIME type used by an XLS file. You need to use the correct MIME type. It's as simple as that.

[MethodMan]

@mason I have given the example using the type that I was testing with as it also works using the xlsx type I will update the answer it's as simple as that..!

[mason]

I see you made a 1 character edit, but it still has the wrong MIME type.

[mason]

Just because you can does not mean you should. The MIME type should accurately describe the content. It's then up to the user's settings to determine what to open the file with.

[code4life]

@mason: I'm referring to the original office HTML specs. Which used to exist on MSDN, but apparently not there anymore. Which, however, is still supported. Hence if you decide to save a datatable to HTML and then open it in Excel, it will quite easily do that for you.