ASP.NET导出Excel时,可选择导出列怎么实现?

本文介绍了一个使用ASPX页面导出数据到Excel的方法,包括如何创建数据表、设置样式及分页输出等关键步骤。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

      
    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="+Server.UrlEncode("孟宪会Excel表格测试")+".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>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值