使用ClosedXML没有警告出口的GridView到Excel：您试图打开该文件是在不同的格式 [英] export Gridview to Excel using ClosedXML without warning: the file you are trying to open is in a different format

问题描述

我工作的一个ASP.NET 4.5的表单，我有一个GridView（具有自定义模板列，并从一个SqlDataSource获取数据）

我有这个事件的gridview的内容导出到一个Excel工作表，它做它的工作做好，除了创建的文件是给了一个警告，当用户打开它（我的理解，因为得到了创建该文件不是实际Excel文件）：

  

你试图打开该文件是在不同的格式
  由文件扩展名中指定

 保护无效btnExport_Excel_Click（对象发件人，EventArgs的发送）
        {
            尝试
            {
                Response.Clear（）;
                将Response.Buffer =真;
                Response.AddHeader（内容处置，附件;文件名= GV.xls）;
                Response.Charset的=;
                Response.ContentType =应用程序/ MS-Excel的;
                //Response.ContentType =应用程序/文本;
                Response.ContentEncoding = System.Text.Encoding.Uni code;
                Response.BinaryWrite（System.Text.Encoding.Uni code.Get preamble（））;                使用（StringWriter的SW =新的StringWriter（））
                {
                    HtmlTextWriter的HW =新的HtmlTextWriter（SW）;                    //导出所有页面
                    GridView4.AllowPaging = FALSE;
                    GridView4.AllowSorting = FALSE;
                    GridView4.ShowFooter = FALSE;
                    GridView4.DataBind（）;
                    //this.BindGrid（）;                    GridView4.HeaderRow.BackColor = Color.White;
                    的foreach（在GridView4.HeaderRow.Cells TableCell的细胞）
                    {
                        cell.BackColor = GridView4.HeaderStyle.BackColor;
                    }
                    的foreach（在GridView4.Rows GridViewRow行）
                    {
                        row.BackColor = Color.White;
                        的foreach（在row.Cells TableCell的细胞）
                        {
                            如果（row.RowIndex％2 == 0）
                            {
                                cell.BackColor = GridView4.AlternatingRowStyle.BackColor;
                            }
                            其他
                            {
                                cell.BackColor = GridView4.RowStyle.BackColor;
                            }
                            cell.CssClass =文本模式;
                        }
                    }                    GridView4.RenderControl（HW）;                    //风格格式的数字串
                    串风格= @＆LT;风格＆GT; .textmode {}＆LT; /风格与GT;
                    的Response.Write（样式）;
                    Response.Output.Write（sw.ToString（））;
                    Response.Flush（）;
                    到Response.End（）;
                }                //显示消息
                InfoPanel.Visible = TRUE;
                InfoPanel.CssClass =面板面板成功;
                lblMessage.CssClass =TEXT文本sucess大胆;
                lblMessage.Text =文件已远销！;            }
            赶上（异常前）
            {
                //显示消息
                InfoPanel.Visible = TRUE;
                lblMessage.Text =＆LT; B＆GT;发生错误，请稍后再试＆LT;！/ B＆GT;＆LT; / BR＆gt;中+ ex.Message;
                lblMessage.CssClass =TEXT文本的危险大胆;
                InfoPanel.CssClass =面板面板危险;
                panelResult.Visible = FALSE;
            }
        }
 

结果在Excel的.xls文件还是不错的（无样式除了标题列，无尾，只是具体如图所示GridView控件）：

---

我发现另一种方式来避免此警告，所以我看到人们喜欢用结果
ClosedXML ，所以我代替上述与此事件该事件：

 保护无效ExportExcel（对象发件人，EventArgs的发送）
{
    DataTable的DT =新的DataTable（GridView_Data）;
    的foreach（在GridView4.HeaderRow.Cells TableCell的细胞）
    {
        dt.Columns.Add（cell.Text）;
    }
    的foreach（在GridView4.Rows GridViewRow行）
    {
        dt.Rows.Add（）;
        的for（int i = 0; I＆LT; row.Cells.Count;我++）
        {
            dt.Rows [dt.Rows.Count  -  1] [I] = row.Cells [I]。文本;
        }
   }
    使用（XLWorkbook WB =新XLWorkbook（））
    {
        wb.Worksheets.Add（DT）;        Response.Clear（）;
        将Response.Buffer =真;
        Response.Charset的=;
        Response.ContentType =应用程序/ vnd.openxmlformats-officedocument.s preadsheetml.sheet
        Response.AddHeader（内容处置，附件;文件名= GV.xlsx）;        使用（MemoryStream的MyMemoryStream =新的MemoryStream（））
        {
            wb.SaveAs（MyMemoryStream）;
            MyMemoryStream.WriteTo（Response.OutputStream）;
            Response.Flush（）;
            到Response.End（）;
        }
    }
}
 

和效果不好（唯一的好新鲜的是，导出的文件是一个真正的2007+ Excel工作表所以没有警告）：

如何获得好的结果上面使用closedXML？

解决方案

在您code（带ClosedXML）的第二部分，主要的问题，您尝试使用文本为模板列字段列 GridViewRow 的财产。正如你所看到的这里，你可以通过文本属性字段值仅适用于的BoundField 字段列和自动生成的字段列。

要从模板列获得价值，你应该浏览到包含价值的内部控制，并从中获得价值。

如果您有以下柱的模板：

 ＆LT; ASP：的TemplateField＆GT;
       ＆LT;＆ItemTemplate中GT;
           ＆LT; ASP：标签ID =标签=服务器文本='＆LT;％＃的eval（ABC）％GT;' ＆GT;＆LT; / ASP：标签＆gt;
       ＆LT; / ItemTemplate中＆GT;
   ＆LT; / ASP：的TemplateField＆GT;
 

您code应该是：

 的for（int i = 0; I＆LT; row.Cells.Count;我++）
    {
        dt.Rows [dt.Rows.Count  -  1] [I] =（row.Cells [I] .FindControl（标签）作为标签）。文本;
    }
 

修改

您code应该如下：

 保护无效ExportExcel（对象发件人，EventArgs的发送）
{
    DataTable的DT =新的DataTable（GridView_Data）;
    的foreach（在GridView4.Columns的DataControlField COL）
    {
        dt.Columns.Add（col.HeaderText）;
    }
    的foreach（在GridView4.Rows GridViewRow行）
    {
        dt.Rows.Add（）;
        的for（int i = 0; I＆LT; row.Cells.Count;我++）
        {
            dt.Rows [dt.Rows.Count  -  1] [I] =（的FindControl（row.Cells [I] .ControlsLBL）作为标签）。文本;
        }
    }
    //你的code下面没有改变
}保护控制的FindControl（的ControlCollection集合，字符串ID）
{
    的foreach（在集散控制CTRL）
    {
        如果（ctrl.ID == ID）
            返回CTRL;
    }    返回null;
}
 

确保在模板列使用的所有标签控件具有相同的 ID 为LBL：

 ＆LT; ASP：的TemplateField的HeaderText =ID＆GT;
       ＆LT;＆ItemTemplate中GT;
           ＆LT; ASP：标签ID =LBL=服务器文本='＆LT;％＃的eval（ID）％GT;' ＆GT;＆LT; / ASP：标签＆gt;
       ＆LT; / ItemTemplate中＆GT;
   ＆LT; / ASP：的TemplateField＆GT;
   ＆LT; ASP：的TemplateField的HeaderText =名称＆GT;
       ＆LT;＆ItemTemplate中GT;
           ＆LT; ASP：标签ID =LBL=服务器文本='＆LT;％＃的eval（姓名）％＆GT;' ＆GT;＆LT; / ASP：标签＆gt;
       ＆LT; / ItemTemplate中＆GT;
   ＆LT; / ASP：的TemplateField＆GT;
   ＆LT; ASP：的TemplateField的HeaderText =金额＆GT;
       ＆LT;＆ItemTemplate中GT;
           ＆LT; ASP：标签ID =LBL=服务器文本='＆LT;％＃的eval（额）％＆GT;' ＆GT;＆LT; / ASP：标签＆gt;
       ＆LT; / ItemTemplate中＆GT;
   ＆LT; / ASP：的TemplateField＆GT;
 

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?

解决方案

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>

这篇关于使用ClosedXML没有警告出口的GridView到Excel：您试图打开该文件是在不同的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！