c# - 在没有警告的情况下使用 ClosedXML 将 Gridview 导出到 Excel : the file you are trying to open is in a different format

Question

我正在使用 ASP.NET 4.5 Webform，我有一个 Gridview(它有自定义的 TemplateField 并从 sqlDataSource 获取数据)

我有这个事件将 gridview 内容导出到 excel 工作表，它很好地完成了它的工作，除了创建的文件在用户打开它时发出警告(我理解这是因为创建的文件不是实际的excel文件):

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

Excel .xls 文件中的结果很好(除了标题列之外没有样式，没有页脚，与 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();
        }
    }
}

结果很糟糕(唯一的好消息是导出的文件是真正的 2007+ Excel 工作表，因此没有警告):

如何使用 closedXML 获得上述“良好”结果？

Answer 1

第二部分代码(使用 ClosedXML)中的主要问题是，您正在尝试将 GridViewRow 的 Text 属性用于 TemplateField字段列。如你所见here ，您只能通过 BoundField 字段列和自动生成的字段列的 Text 属性获取字段值。

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

确保 TemplateField 中使用的所有 Label 控件具有与 “lbl” 相同的 ID:

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