程序博客网 > 我知你好未再书包网

将 GridView 中的内容导出到 Excel 中多个工作表（Sheet） 的方法

来源：互联网 发布：我知你好未再书包网 编辑：程序博客网 时间：2024/05/22 13:29

Excel 可以保存成 xml 格式，并且支持Sheet功能，因此，我们就可以利用这个功能将 Gridview 导出到多个 Sheet  中去。而且可以很好地控制导出的格式。下面就是完整的代码（注意：本站的代码都是可以直接复制、保存成aspx文件运行的。）：

例子

ASPX 代码
<%@ Page Language="C#" EnableViewState="true" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
    if (!Page.IsPostBack)
    {
      System.Data.DataTable dt = new System.Data.DataTable();
      System.Data.DataRow dr;
      dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));
      dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));
      dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));
      System.Random rd = new System.Random();
      for (int i = 0; i < 88; i++)
      {
        dr = dt.NewRow();
        dr[0] = "班级" + i.ToString();
        dr[1] = "【孟子E章】" + i.ToString();
        dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);
        dt.Rows.Add(dr);
      }
      GridView1.DataSource = dt;
      GridView1.DataBind();
    }
  }

  protected void Button1_Click(object sender, EventArgs e)
  {
    //假如每10条数据放在一个 Sheet 里面，先计算需要多少个 Sheet
    int ItenCountPerSheet = 10;
    int SheetCount = Convert.ToInt32(Math.Ceiling((double)GridView1.Rows.Count / ItenCountPerSheet));

    String ExportFileName = "孟宪会Excel表格测试";
    if (Request.Browser.Browser.IndexOf("MSIE") > -1)
    {
      ExportFileName = Server.UrlEncode(ExportFileName);
    }
    Response.ClearContent();
    Response.BufferOutput = true;
    Response.Charset = "utf-8";
    Response.ContentType = "text/xml";
    Response.ContentEncoding = System.Text.Encoding.UTF8;
    Response.AppendHeader("Content-Disposition", "attachment;filename=" + ExportFileName + ".xls");
    // 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。
    // Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xml");
    Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
    Response.Write(@"\r\n<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
    Response.Write(@"\r\n<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
    Response.Write(@"\r\n<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
          <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");
    Response.Write("\r\n</DocumentProperties>");
    Response.Write(@"\r\n<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
    //定义标题样式    
    Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");
    
    //定义边框
    Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
      <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");
      
    Response.Write("</Styles>");


    for (int i = 0; i < SheetCount; i++)
    {
      //计算该 Sheet 中的数据起始行和结束行。
      int start = ItenCountPerSheet * i;
      int end = ItenCountPerSheet * (i + 1);
      if (end > GridView1.Rows.Count) end = GridView1.Rows.Count;
    
      Response.Write("\r\n<Worksheet ss:Name='Sheet" + (i+1) + "'>");
      Response.Write("\r\n<Table x:FullColumns='1' x:FullRows='1'>");
      //输出标题

      Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
        for (int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
        {
          Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + GridView1.HeaderRow.Cells[j].Text + "</Data></Cell>");
        }
          Response.Write("\r\n</Row>");

       for (int j = start; j < end; j++)
        {
          Response.Write("\r\n<Row>");
          for (int c = 0; c < GridView1.HeaderRow.Cells.Count; c++)
          {
            //对于数字，采用Number数字类型
            if (c > 1)
            {
              Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + GridView1.Rows[j].Cells[c].Text + "</Data></Cell>");
            }
            else
            {
              Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + GridView1.Rows[j].Cells[c].Text + "</Data></Cell>");
            }
          }
            Response.Write("\r\n</Row>");
        }
      Response.Write("\r\n</Table>");
      Response.Write("\r\n</Worksheet>");
      Response.Flush();
    }
    Response.Write("\r\n</Workbook>");
    Response.End();
  }

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title></title>
</head>
<body>
  <form id="form1" runat="server">
  <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出测试" />
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView>
  </form>
</body>
</html>

另外，请注意：代码里面添加了\r\n换行，是为了生成出来的xml格式有换行，实际可以不用。

如果是DataTable，DataSet，可以直接导出成文件。下面是完整的源代码：

C# 代码
<%@ Page Language="C#" %>

<script runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
    // 下面采用的是DataTable，也可以采用DataSet，其中每个DataTable可以保存成一个 Sheet
    // 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。
    System.Data.DataTable dt = new System.Data.DataTable();
    if (!Page.IsPostBack)
    {
      System.Data.DataRow dr;
      dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));
      dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));
      dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));
      dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));
      System.Random rd = new System.Random();
      for (int i = 0; i < 88; i++)
      {
        dr = dt.NewRow();
        dr[0] = "班级" + i.ToString();
        dr[1] = "【孟子E章】" + i.ToString();
        dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);
        dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);
        dt.Rows.Add(dr);
      }
    }

    //假如每10条数据放在一个 Sheet 里面，先计算需要多少个 Sheet
    int ItenCountPerSheet = 10;
    int SheetCount = Convert.ToInt32(Math.Ceiling((double)dt.Rows.Count / ItenCountPerSheet));
    Response.ClearContent();
    Response.BufferOutput = true;
    Response.Charset = "utf-8";
    Response.ContentType = "application/ms-excel";
    Response.AddHeader("Content-Transfer-Encoding", "binary");
    Response.ContentEncoding = System.Text.Encoding.UTF8;
    //Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");
    // 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。

    String FileName = "孟宪会Excel表格测试";
    if (!String.IsNullOrEmpty(Request.UserAgent))
    {
      // firefox 里面文件名无需编码。
      if (!(Request.UserAgent.IndexOf("Firefox") > -1 && Request.UserAgent.IndexOf("Gecko") > -1))
      {
        FileName = Server.UrlEncode(FileName);
      }
    }
    Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xml");
    Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");
    Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
      xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
      xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");
    Response.Write(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");
    Response.Write(@"<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
          <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");
    Response.Write("</DocumentProperties>");
    Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
      <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");
    //定义标题样式    
    Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
       <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");

    //定义边框
    Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
      <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
      <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");

    Response.Write("</Styles>");

    //SheetCount代表生成的 Sheet 数目。
    for (int i = 0; i < SheetCount; i++)
    {
      //计算该 Sheet 中的数据起始行和结束行。
      int start = ItenCountPerSheet * i;
      int end = ItenCountPerSheet * (i + 1);
      if (end > dt.Rows.Count) end = dt.Rows.Count;

      Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "'>");
      Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");

      //输出标题
      Response.Write("\r\n<Row ss:AutoFitHeight='1'>");
      for (int j = 0; j < dt.Columns.Count; j++)
      {
        Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + dt.Columns[j].ColumnName + "</Data></Cell>");
      }
      Response.Write("\r\n</Row>");
      

      for (int j = start; j < end; j++)
      {
        Response.Write("<Row>");
        for (int c = 0; c < 6; c++)
        {
          //对于数字，采用Number数字类型
          if (c > 1)
          {
            Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");
          }
          else
          {
            Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");
          }
        }
        Response.Write("</Row>");
      }
      Response.Write("</Table>");
      Response.Write("</Worksheet>");
      Response.Flush();
    }
    Response.Write("</Workbook>");
    Response.End();
  }
</script>


孟子E章 http://dotnet.aspx.cc/file/Export-Gridview-To-Excel-With-Multi-Sheet.aspx

我知你好未再书包网 我知你好未再书包网
原创粉丝点击
热门IT博客
手机版办公软件手机版办公软件
什么是淘宝店铺维护什么是淘宝店铺维护
网络电视怎么看本港台
唯歌生民病 愿得天子知
数据有效性检验 挖掘
java实现zip的解压
淘宝优惠券在哪里看
淘宝代金券最多
知乎kindle推送
zdm软件绘制纵断面
微信拓客软件
超星尔雅网络课程心得
微商必备软件
威斯汀酒店知味餐厅
js filereader文件路径
链家java开发
哪里注册域名最好
网络语713是什么意思
实验支撑岗 知乎
歪歪是什么软件
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 煮米溢出来怎么办 调肉馅有腥味怎么办 吃了病死猪肉怎么办 新买的不绣钢杯怎么办 玉柴发动机没劲怎么办 鼠标感应器坏了怎么办 雨刷刷不干净怎么办 裙子容易打褶怎么办 新无锈钢瓶的黑怎么办 不锈钢烧黑了怎么办 烧菜锅底下很黑怎么办 有机肥施多了怎么办 盆栽苹果树生虫怎么办 苹果树生红色虫怎么办 万年青植物烧根怎么办 没发酵烧根怎么办 茶花轻度肥害怎么办 肥料上多了怎么办 葡萄树肥多了怎么办 葡萄树叶子烧了怎么办 钾肥施多了怎么办 花生化肥烧根怎么办 玉米化肥烧苗怎么办 月季花施肥多了怎么办 花复合肥施多了怎么办 茶花施肥大了怎么办 母猪下崽后便秘怎么办 机械手表跑快怎么办 沙糖桔挂果树弱怎么办 盆栽葡萄不发芽怎么办 眼镜片膜掉了怎么办 塑料粒含水率高怎么办 鸭拉白色粪便怎么办 山茶花施了尿素怎么办 猪拉暗红色粪便怎么办 猪拉黑色粪便怎么办 猪不吃粪便干硬怎么办 用假的有机肥怎么办 上嘴皮突然肿了怎么办 零食不脆了怎么办 椒盐蘑菇皮了怎么办

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里