一个dataSet有两个表，如何把两个表导入excel的两个sheet中？

Question

asp.net要求点击导入时 输入excel文件名，excel的两个sheet名称就是datatable的表名，找了很多资料都搞不定

Answer 1

导出到 Excel 中多个工作表（Sheet） 的方法如果是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 + ".xls");
    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>

Answer 2

DataSet，可以直接导出成文件。下面是完整的源代码：有没有现成的，是不是让我 对照这上面改？

Answer 3

我有Gridview到处成excel表的实例：地址

Answer 4

孟大侠，有没有可以采用DataSet，其中每个DataTable可以保存成一个 Sheet

Answer 5

本帖最后由 net_lover 于 2012-09-21 09:01:17 编辑

  

Answer 6

使用NPOI比较简单些，先创建文档，再创建sheet，再创建行。再创建列。从网上下载个挺好用的也很容易理解

Answer 7

DataSet这样
  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 + ".xls");
  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 < DataSet1.Tables.Count; i++)
  {
DataTable dt = DataSet1.Tables[i];
    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 = 0; j < dt.Rows.Count; j++)
    {
      Response.Write("<Row>");
      for (int c = 0; c < dt.Columns.Count; 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();

Answer 8

修正完整的版本System.Data.DataSet ds = new System.Data.DataSet();
System.Data.DataTable dataTable1 = new System.Data.DataTable("BlogUser");
System.Data.DataRow dr;
dataTable1.Columns.Add(new System.Data.DataColumn("UserId", typeof(System.Int32)));
dataTable1.Columns.Add(new System.Data.DataColumn("UserName", typeof(System.String)));
dataTable1.PrimaryKey = new System.Data.DataColumn[] { dataTable1.Columns["UserId"] };
for (int i = 0; i < 8; i++)
{
    dr = dataTable1.NewRow();
    dr[0] = i;
    dr[1] = "用户姓名【孟子E章】测试" + i.ToString();
    dataTable1.Rows.Add(dr);
}
System.Data.DataTable dataTable2 = new System.Data.DataTable("BlogArticle");
dataTable2.Columns.Add(new System.Data.DataColumn("ArticleId", typeof(System.Int32)));
dataTable2.Columns.Add(new System.Data.DataColumn("Title", typeof(System.String)));
dataTable2.Columns.Add(new System.Data.DataColumn("UserId", typeof(System.Int32)));
dataTable2.PrimaryKey = new System.Data.DataColumn[] { dataTable1.Columns["ArticleId"] };
Random rd = new Random();
for (int i = 0; i < 20; i++)
{
    dr = dataTable2.NewRow();
    dr[0] = i;
    dr[1] = "文章标题例子" + i.ToString();
    dr[2] = rd.Next(0, 7);
    dataTable2.Rows.Add(dr);
}
ds.Tables.Add(dataTable1);
ds.Tables.Add(dataTable2);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;
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 + ".xls");
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 < ds.Tables.Count; i++)
{
    System.Data.DataTable dt = ds.Tables[i];
    Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "_" + ds.Tables[i].TableName + "'>");
    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 = 0; j < dt.Rows.Count; j++)
    {
        Response.Write("<Row>");
        for (int c = 0; c < dt.Columns.Count; 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.Write("</Workbook>");
Response.End();
}

Answer 9

太狠了，就这样，相信很多朋友都会碰到这个问题，只是有一点，如果表的字段类型为int的话，就要把下面的这个去掉，不然excel打不开//对于数字，采用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>");
                        //}