.net 导出Excel,设置Excel页眉及单元格换行方法

最新推荐文章于 2026-07-11 16:56:07 发布

原创最新推荐文章于 2026-07-11 16:56:07 发布 · 6.1k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

net

本文介绍如何使用C#编程语言将GridView控件中的数据导出为Excel文件，包括数据集、导出路径的设置，以及根据不同条件调整表头格式。

/// <summary>
    /// 从GridView导出
    /// </summary>
    /// <param name="ds">导出的数据集</param>
    /// <param name="path">导出的路径</param>
    /// <returns></returns>
    public string Export(DataSet ds, string path)
    {
        GridView GV = new GridView();//实例化一个Gridview
        try
        {
            GV.DataSource = ds;
            GV.AllowPaging = false;//禁止分页
            GV.DataBind();//绑定数据
            // GV.HeaderStyle.Height = 30;//设置表头的行高
            GV.HeaderStyle.Font.Size = 10;
            //GV.HeaderStyle.BackColor = System.Drawing.Color.Gray;//设置表头的背景色
            // GV.HeaderStyle.Font.Bold = true;//设置表头加粗
            GV.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;//设置表头居中
            // GV.BackColor = System.Drawing.Color.FromArgb(255, 255, 153);//设置背景色
            GV.HorizontalAlign = HorizontalAlign.Center;//设置居中
            GV.RowStyle.HorizontalAlign = HorizontalAlign.Center;//设置居中
            GV.RowStyle.Font.Size = 10;
            GV.HeaderRow.Cells[1].Width = 140;//部门
            if (drpType.SelectedValue == "1")
            {
                GV.HeaderRow.Cells[5].Width = 90;
                GV.HeaderRow.Cells[6].Width = 90;
                GV.HeaderRow.Cells[8].Width = 90;
                GV.HeaderRow.Cells[9].Width = 180;
            }
            if (drpType.SelectedValue == "2")
            {
                GV.HeaderRow.Cells[4].Width = 70;
                GV.HeaderRow.Cells[5].Width = 85;
                GV.HeaderRow.Cells[6].Width = 90;
            }
            if (drpType.SelectedValue == "3")
            {
                GV.HeaderRow.Cells[5].Width = 90;
                GV.HeaderRow.Cells[6].Width = 90;
                GV.HeaderRow.Cells[8].Width = 90;
                GV.HeaderRow.Cells[7].Width = 100;
                GV.HeaderRow.Cells[9].Width = 180;
            }
            if (drpType.SelectedValue == "4")
            {
                GV.HeaderRow.Cells[4].Width = 90;
                GV.HeaderRow.Cells[5].Width = 90;
                GV.HeaderRow.Cells[8].Width = 90;
                GV.HeaderRow.Cells[6].Width = 65;
                GV.HeaderRow.Cells[9].Width = 280;
            }
            if (drpType.SelectedValue == "5")
            {
                GV.HeaderRow.Cells[1].Width = 250;
            }
            OutPutExcel(GV, Page, path);
            return "导出成功！";
        }
        catch (Exception exc)
        {
            return exc.Message;//捕捉异常信息
        }
    }

 #region 将控件内容导出到excel
    /// <summary>
    /// 将控件内容导出到excel
    /// </summary>
    /// <param name="ExportObj">需要导出内容的控件</param>
    /// <param name="page">提供Reponse事件的page</param>
    /// <param name="fileName">导出的文件名</param>
    public void OutPutExcel(System.Web.UI.Control ExportObj, System.Web.UI.Page page, string fileName)
    {
        //定义文档类型、字符编码

        page.Response.Clear();
        page.Response.Buffer = true;
        page.Response.Charset = "GB2312";
        //下面这行很重要， attachment 参数表示作为附件下载，您可以改成 online在线打开
        //filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为：.doc 　　 .xls 　　 .txt 　　.htm
        page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
        page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        //Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档

        page.Response.ContentType = "application/ms-excel";

        System.Globalization.CultureInfo cult = new System.Globalization.CultureInfo("zh-CN", true);

        //　定义一个输入流
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter(cult);
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

        //设置excel页眉,格式如下:
        //<style><!--@page{mso-header-data:"&L&10【请假单】   导出时段\:从 2012\/09\/11 16\:22  到  2012\/09\/12 16\:22\;\000A当前时间\:2012\/09\/21 16\:26\:33";}--></style>
        //&L&10:靠左,10号字体
        //\000A:换行
        StringBuilder printHead = new StringBuilder();
        printHead.Append("<style><!--@page{mso-header-data:\"&L&10");
        printHead.Append(Convert.ToString(ViewState["jdType"]).Replace("/", "\\/").Replace(":", "\\:").Replace(";", "\\;")+"\";");
        printHead .Append("}--></style>");
        oHtmlTextWriter.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">");
        oHtmlTextWriter.WriteLine(printHead.ToString());

        ExportObj.RenderControl(oHtmlTextWriter);

        //excel单元格换行,先将要换行的单元格数据加入标识字符比如"<br>",然后输出时将<br>替换成<br style='mso-data-placement:same-cell;'/>
        page.Response.Write(oStringWriter.ToString().Replace("<br>", "<br style='mso-data-placement:same-cell;'/>"));
        page.Response.End();
    }
    #endregion

标签

#excel #.net #dataset #string #exception