Asp.net将DataTable分多个Sheet导出到Excel

1. /// <summary>  
2.     ///      /// </summary>  
3.     /// <remarks>  
4.     /// 创建人：zhujt<br/>  
5.     /// 创建日期：2012-02-20 09:08:22<br/>  
6.     private void ImportToExcel(DataTable dt)  
7.     {  
8.         // 下面采用的是DataTable，也可以采用DataSet，其中每个DataTable可以保存成一个 Sheet  
9.         // 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。  
10.         //System.Data.DataTable dt = new System.Data.DataTable();  
11.         dt = new System.Data.DataTable();  
12.         if (!Page.IsPostBack)  
13.         {  
14.             System.Data.DataRow dr;  
15.             dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));  
16.             dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));  
17.             dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));  
18.             dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));  
19.             dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));  
20.             dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));  
21.             System.Random rd = new System.Random();  
22.             for (int i = 0; i < 88; i++)  
23.             {  
24.                 dr = dt.NewRow();  
25.                 dr[0] = "班级" + i.ToString();  
26.                 dr[1] = "【孟子E章】" + i.ToString();  
27.                 dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);  
28.                 dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);  
29.                 dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);  
30.                 dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);  
31.                 dt.Rows.Add(dr);  
32.             }  
33.         }  
34.   
35.         //假如每10条数据放在一个 Sheet 里面，先计算需要多少个 Sheet  
36.         int ItenCountPerSheet = 10;  
37.         int SheetCount = Convert.ToInt32(Math.Ceiling((double)dt.Rows.Count / ItenCountPerSheet));  
38.         Response.ClearContent();  
39.         Response.BufferOutput = true;  
40.         Response.Charset = "utf-8";  
41.         Response.ContentType = "application/ms-excel";  
42.         Response.AddHeader("Content-Transfer-Encoding", "binary");  
43.         Response.ContentEncoding = System.Text.Encoding.UTF8;  
44.         //Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");  
45.         // 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。  
46.   
47.         String FileName = "孟宪会Excel表格测试";  
48.         if (!String.IsNullOrEmpty(Request.UserAgent))  
49.         {  
50.             // firefox 里面文件名无需编码。  
51.             if (!(Request.UserAgent.IndexOf("Firefox") > -1 && Request.UserAgent.IndexOf("Gecko") > -1))  
52.             {  
53.                 FileName = Server.UrlEncode(FileName);  
54.             }  
55.         }  
56.         Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");  
57.         Response.Write("<?xml version='1.0'?><?mso-application progid='Excel.Sheet'?>");  
58.         Response.Write(@"<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'  
59.       xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'  
60.       xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'>");  
61.         Response.Write(@"<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>");  
62.         Response.Write(@"<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>  
63.           <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");  
64.         Response.Write("</DocumentProperties>");  
65.         Response.Write(@"<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>  
66.       <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style>");  
67.         //定义标题样式      
68.         Response.Write(@"<Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>  
69.        <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>  
70.        <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>  
71.        <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>  
72.        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style>");  
73.   
74.         //定义边框  
75.         Response.Write(@"<Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>  
76.       <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>  
77.       <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>  
78.       <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>  
79.       <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style>");  
80.   
81.         Response.Write("</Styles>");  
82.   
83.         //SheetCount代表生成的 Sheet 数目。  
84.         for (int i = 0; i < SheetCount; i++)  
85.         {  
86.             //计算该 Sheet 中的数据起始行和结束行。  
87.             int start = ItenCountPerSheet * i;  
88.             int end = ItenCountPerSheet * (i + 1);  
89.             if (end > dt.Rows.Count) end = dt.Rows.Count;  
90.   
91.             Response.Write("<Worksheet ss:Name='Sheet" + (i + 1) + "'>");  
92.             Response.Write("<Table x:FullColumns='1' x:FullRows='1'>");  
93.   
94.             //输出标题  
95.             Response.Write("\r\n<Row ss:AutoFitHeight='1'>");  
96.             for (int j = 0; j < dt.Columns.Count; j++)  
97.             {  
98.                 Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + dt.Columns[j].ColumnName + "</Data></Cell>");  
99.             }  
100.             Response.Write("\r\n</Row>");  
101.   
102.   
103.             for (int j = start; j < end; j++)  
104.             {  
105.                 Response.Write("<Row>");  
106.                 for (int c = 0; c < 6; c++)  
107.                 {  
108.                     //对于数字，采用Number数字类型  
109.                     if (c > 1)  
110.                     {  
111.                         Response.Write("<Cell ss:StyleID='border'><Data ss:Type='Number'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");  
112.                     }  
113.                     else  
114.                     {  
115.                         Response.Write("<Cell ss:StyleID='border'><Data ss:Type='String'>" + dt.Rows[j][c].ToString() + "</Data></Cell>");  
116.                     }  
117.                 }  
118.                 Response.Write("</Row>");  
119.             }  
120.             Response.Write("</Table>");  
121.             Response.Write("</Worksheet>");  
122.             Response.Flush();  
123.         }  
124.         Response.Write("</Workbook>");  
125.         Response.End();  
126.     } 

设置列宽，样式

http://www.cnblogs.com/mikehhs/archive/2012/04/29/2476336.html