在 c# 中导出到 Excel

本文关键字:Excel | 更新日期: 2023-09-27 18:36:32

我有一个带有网格视图的页面。我需要以 excel 格式下载该网格视图数据。我使用以下代码下载为 excel 格式。

public static void Export(string fileName, GridView gv)
{
    string style = @"<style> .text { mso-number-format:'@;text-align:right; } </style> ";
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
    HttpContext.Current.Response.ContentType = "application/ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {
            HttpContext.Current.Response.Write(style);
            //  Create a table to contain the grid      
            System.Web.UI.WebControls.Table table = new System.Web.UI.WebControls.Table();
            //  include the gridline settings  
            table.GridLines = gv.GridLines;
            //  add the header row to the table 
            if (gv.HeaderRow != null)
            {
                PrepareControlForExport(gv.HeaderRow);
                table.Rows.Add(gv.HeaderRow);
            }
            //  add each of the data rows to the table   
            foreach (GridViewRow row in gv.Rows)
            {
                // add numeric style for each cell   
                foreach (TableCell cell in row.Cells)
                {
                    cell.Attributes.Add("class", "text");
                }
                PrepareControlForExport(row);
                table.Rows.Add(row);
            }
            //  add the footer row to the table  
            if (gv.FooterRow != null)
            {
                PrepareControlForExport(gv.FooterRow);
                table.Rows.Add(gv.FooterRow);
            }
            //  render the table into the htmlwriter  
            table.RenderControl(htw);
            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
}
/// <summary>
/// Replace any of the contained controls with literals        
/// </summary>       
/// <param name="control"></param> 
private static void PrepareControlForExport(Control control)
{
    for (int i = 0; i < control.Controls.Count; i++)
    {
        Control current = control.Controls[i];
        if (current is LinkButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
        }
        else if (current is ImageButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
        }
        else if (current is HyperLink)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
        }
        else if (current is DropDownList)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
        }
        else if (current is CheckBox)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
        }
        if (current.HasControls())
        {
            PrepareControlForExport(current);
        }
    }
}
下载

文件后,也会下载母版页的 css 文件。你能告诉我,如何解决这个问题。

在 c# 中导出到 Excel

从 C# 创建真正的 Excel 文件的最简单方法是使用 ClosedXML 库:ClosedXML。ClosedXML 也可作为 NuGet 包提供,以便于入门。

试试我的工作方法:

void ExportToExcel(GridView grdData, string filename)
{
    grdData.BorderStyle = BorderStyle.Solid;
    grdData.BorderWidth = 1;
    grdData.BackColor = Color.WhiteSmoke;
    grdData.GridLines = GridLines.Both;
    grdData.Font.Name = "Verdana";
    grdData.Font.Size = FontUnit.XXSmall;
    grdData.HeaderStyle.BackColor = Color.DimGray;
    grdData.HeaderStyle.ForeColor = Color.White;
    grdData.RowStyle.HorizontalAlign = HorizontalAlign.Left;
    grdData.RowStyle.VerticalAlign = VerticalAlign.Top;
    HttpResponse response = HttpContext.Current.Response;
    response.Clear();
    response.Charset = "";
    response.ContentType = "application/vnd.ms-excel";
    response.AddHeader("Content-Disposition", "attachment;filename='"" + filename+ "'"");
    using (var sw = new StringWriter())
    {
        using (var htw = new HtmlTextWriter(sw))
        {
            grdData.RenderControl(htw);
            response.Write(sw.ToString());
            response.End();
        }
    }
} 
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "excelname.xls"));
        Response.ContentType = "application/ms-excel";
        //which row you dont want in gridview
        GridView5.Columns[0].Visible = false;
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView5.AllowPaging = false;
        GridView5.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();