export Gridview to Excel using ClosedXML without warning: the file you are trying to open is in a different format
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
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>