将Dataset中的多个DataTable导入到一个Excel文件的多个Sheet中

http://bbs.csdn.net/topics/380041223

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;

         

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

         System.Data.DataTable dt2 =  new  DataTable();

         dt2.Columns.Add( new  System.Data.DataColumn( "学生班级" ,  typeof (System.String)));

         dt2.Columns.Add( new  System.Data.DataColumn( "学生姓名" ,  typeof (System.String)));

         dt2.Columns.Add( new  System.Data.DataColumn( "语文" ,  typeof (System.Decimal)));

         dt2.Columns.Add( new  System.Data.DataColumn( "数学" ,  typeof (System.Decimal)));

         dt2.Columns.Add( new  System.Data.DataColumn( "英语" ,  typeof (System.Decimal)));

         System.Data.DataRow dr2;

         System.Random rd2 =  new  System.Random();

         for  ( int  i = 0; i < 88; i++)

         {

             dr2 = dt2.NewRow();

             dr2[0] =  "班级"  + i.ToString();

             dr2[1] =  "【孟子E章】"  + i.ToString();

             dr2[2] = System.Math.Round(rd2.NextDouble() * 102, 0);

             dr2[3] = System.Math.Round(rd2.NextDouble() * 102, 0);

             dr2[4] = System.Math.Round(rd2.NextDouble() * 102, 0);

             dt2.Rows.Add(dr2);

            

         }

         DataSet ds =  new  DataSet();

         ds.Tables.Clear();

         ds.Tables.Add(dt);

         ds.Tables.Add(dt2);

         //SheetCount代表生成的 Sheet 数目。

         for  ( int  k = 0; k < ds.Tables.Count; k++)

         {

             int  SheetCount = Convert.ToInt32(Math.Ceiling(( double )ds.Tables[k].Rows.Count / ItenCountPerSheet));

             for  ( int  i = 0; i < SheetCount; i++)

             {

                 //计算该 Sheet 中的数据起始行和结束行。

                 int  start = ItenCountPerSheet * i;

                 int  end = ItenCountPerSheet * (i + 1);

                 if  (end > ds.Tables[k].Rows.Count) end = ds.Tables[k].Rows.Count;

 

                 Response.Write( "<Worksheet ss:Name='Sheet"  + (k*SheetCount + i+1) +  "'>" );

                 Response.Write( "<Table x:FullColumns='1' x:FullRows='1'>" );

 

                 //输出标题

                 Response.Write( "\r\n<Row ss:AutoFitHeight='1'>" );

                 for  ( int  j = 0; j < 5; j++)

                 {

                     Response.Write( "<Cell ss:StyleID='Header'><Data ss:Type='String'>"  + ds.Tables[k].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 < ds.Tables[k].Columns.Count; c++)

                     {

                         //对于数字，采用Number数字类型

                         if  (c > 1)

                         {

                             Response.Write( "<Cell ss:StyleID='border'><Data ss:Type='Number'>"  + ds.Tables[k].Rows[j][c].ToString() +  "</Data></Cell>" );

                         }

                         else

                         {

                             Response.Write( "<Cell ss:StyleID='border'><Data ss:Type='String'>"  + ds.Tables[k].Rows[j][c].ToString() +  "</Data></Cell>" );

                         }

                     }

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

                 }

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

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

                 Response.Flush();

             }

         }

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

         Response.End();