Excel 可以保存成 xml 格式,并且支持Sheet功能,因此,我们就可以利用这个功能将 Gridview 导出到多个 Sheet 中去。而且可以很好地控制导出的格式。下面就是完整的代码(注意:本站的代码都是可以直接复制、保存成aspx文件运行的。):
ASPX 代码
1 <%@ Page Language=
"
C#
" EnableViewState=
"
true
" %>
2
3 <!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
4
5 <script runat= " server ">
6
7 protected void Page_Load( object sender, EventArgs e)
8 {
9 if (!Page.IsPostBack)
10 {
11 System.Data.DataTable dt = new System.Data.DataTable();
12 System.Data.DataRow dr;
13 dt.Columns.Add( new System.Data.DataColumn( " 学生班级 ", typeof(System.String)));
14 dt.Columns.Add( new System.Data.DataColumn( " 学生姓名 ", typeof(System.String)));
15 dt.Columns.Add( new System.Data.DataColumn( " 语文 ", typeof(System.Decimal)));
16 dt.Columns.Add( new System.Data.DataColumn( " 数学 ", typeof(System.Decimal)));
17 dt.Columns.Add( new System.Data.DataColumn( " 英语 ", typeof(System.Decimal)));
18 dt.Columns.Add( new System.Data.DataColumn( " 计算机 ", typeof(System.Decimal)));
19 System.Random rd = new System.Random();
20 for ( int i = 0; i < 88; i++)
21 {
22 dr = dt.NewRow();
23 dr[ 0] = " 班级 " + i.ToString();
24 dr[ 1] = " 【孟子E章】 " + i.ToString();
25 dr[ 2] = System.Math.Round(rd.NextDouble() * 100, 0);
26 dr[ 3] = System.Math.Round(rd.NextDouble() * 100, 0);
27 dr[ 4] = System.Math.Round(rd.NextDouble() * 100, 0);
28 dr[ 5] = System.Math.Round(rd.NextDouble() * 100, 0);
29 dt.Rows.Add(dr);
30 }
31 GridView1.DataSource = dt;
32 GridView1.DataBind();
33 }
34 }
35
36 protected void Button1_Click( object sender, EventArgs e)
37 {
38 // 假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
39 int ItenCountPerSheet = 10;
40 int SheetCount = Convert.ToInt32(Math.Ceiling(( double)GridView1.Rows.Count / ItenCountPerSheet));
41
42 String ExportFileName = " 孟宪会Excel表格测试 ";
43 if (Request.Browser.Browser.IndexOf( " MSIE ") > - 1)
44 {
45 ExportFileName = Server.UrlEncode(ExportFileName);
46 }
47 Response.ClearContent();
48 Response.BufferOutput = true;
49 Response.Charset = " utf-8 ";
50 Response.ContentType = " text/xml ";
51 Response.ContentEncoding = System.Text.Encoding.UTF8;
52 Response.AppendHeader( " Content-Disposition ", " attachment;filename= " + ExportFileName + " .xls ");
53 // 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。
54 // Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xml");
55 Response.Write( " <?xml version='1.0'?><?mso-application progid='Excel.Sheet'?> ");
56 Response.Write( @" \r\n<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
57 xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
58 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'> ");
59 Response.Write( @" \r\n<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'> ");
60 Response.Write( @" \r\n<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
61 <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version> ");
62 Response.Write( " \r\n</DocumentProperties> ");
63 Response.Write( @" \r\n<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
64 <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style> ");
65 // 定义标题样式
66 Response.Write( @" <Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
67 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
68 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
69 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
70 <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style> ");
71
72 // 定义边框
73 Response.Write( @" <Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
74 <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
75 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
76 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
77 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style> ");
78
79 Response.Write( " </Styles> ");
80
81
82 for ( int i = 0; i < SheetCount; i++)
83 {
84 // 计算该 Sheet 中的数据起始行和结束行。
85 int start = ItenCountPerSheet * i;
86 int end = ItenCountPerSheet * (i + 1);
87 if (end > GridView1.Rows.Count) end = GridView1.Rows.Count;
88
89 Response.Write( " \r\n<Worksheet ss:Name='Sheet " + (i+ 1) + " '> ");
90 Response.Write( " \r\n<Table x:FullColumns='1' x:FullRows='1'> ");
91 // 输出标题
92
93 Response.Write( " \r\n<Row ss:AutoFitHeight='1'> ");
94 for ( int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
95 {
96 Response.Write( " <Cell ss:StyleID='Header'><Data ss:Type='String'> " + GridView1.HeaderRow.Cells[j].Text + " </Data></Cell> ");
97 }
98 Response.Write( " \r\n</Row> ");
99
100 for ( int j = start; j < end; j++)
101 {
102 Response.Write( " \r\n<Row> ");
103 for ( int c = 0; c < GridView1.HeaderRow.Cells.Count; c++)
104 {
105 // 对于数字,采用Number数字类型
106 if (c > 1)
107 {
108 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='Number'> " + GridView1.Rows[j].Cells[c].Text + " </Data></Cell> ");
109 }
110 else
111 {
112 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='String'> " + GridView1.Rows[j].Cells[c].Text + " </Data></Cell> ");
113 }
114 }
115 Response.Write( " \r\n</Row> ");
116 }
117 Response.Write( " \r\n</Table> ");
118 Response.Write( " \r\n</Worksheet> ");
119 Response.Flush();
120 }
121 Response.Write( " \r\n</Workbook> ");
122 Response.End();
123 }
124
125 </script>
126
127 <html xmlns= " http://www.w3.org/1999/xhtml ">
128 <head runat= " server ">
129 <title></title>
130 </head>
131 <body>
132 <form id= " form1 " runat= " server ">
133 <asp:Button ID= " Button1 " runat= " server " OnClick= " Button1_Click " Text= " 导出测试 " />
134 <asp:GridView ID= " GridView1 " runat= " server ">
135 </asp:GridView>
136 </form>
137 </body>
138 </html>
2
3 <!DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
4
5 <script runat= " server ">
6
7 protected void Page_Load( object sender, EventArgs e)
8 {
9 if (!Page.IsPostBack)
10 {
11 System.Data.DataTable dt = new System.Data.DataTable();
12 System.Data.DataRow dr;
13 dt.Columns.Add( new System.Data.DataColumn( " 学生班级 ", typeof(System.String)));
14 dt.Columns.Add( new System.Data.DataColumn( " 学生姓名 ", typeof(System.String)));
15 dt.Columns.Add( new System.Data.DataColumn( " 语文 ", typeof(System.Decimal)));
16 dt.Columns.Add( new System.Data.DataColumn( " 数学 ", typeof(System.Decimal)));
17 dt.Columns.Add( new System.Data.DataColumn( " 英语 ", typeof(System.Decimal)));
18 dt.Columns.Add( new System.Data.DataColumn( " 计算机 ", typeof(System.Decimal)));
19 System.Random rd = new System.Random();
20 for ( int i = 0; i < 88; i++)
21 {
22 dr = dt.NewRow();
23 dr[ 0] = " 班级 " + i.ToString();
24 dr[ 1] = " 【孟子E章】 " + i.ToString();
25 dr[ 2] = System.Math.Round(rd.NextDouble() * 100, 0);
26 dr[ 3] = System.Math.Round(rd.NextDouble() * 100, 0);
27 dr[ 4] = System.Math.Round(rd.NextDouble() * 100, 0);
28 dr[ 5] = System.Math.Round(rd.NextDouble() * 100, 0);
29 dt.Rows.Add(dr);
30 }
31 GridView1.DataSource = dt;
32 GridView1.DataBind();
33 }
34 }
35
36 protected void Button1_Click( object sender, EventArgs e)
37 {
38 // 假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
39 int ItenCountPerSheet = 10;
40 int SheetCount = Convert.ToInt32(Math.Ceiling(( double)GridView1.Rows.Count / ItenCountPerSheet));
41
42 String ExportFileName = " 孟宪会Excel表格测试 ";
43 if (Request.Browser.Browser.IndexOf( " MSIE ") > - 1)
44 {
45 ExportFileName = Server.UrlEncode(ExportFileName);
46 }
47 Response.ClearContent();
48 Response.BufferOutput = true;
49 Response.Charset = " utf-8 ";
50 Response.ContentType = " text/xml ";
51 Response.ContentEncoding = System.Text.Encoding.UTF8;
52 Response.AppendHeader( " Content-Disposition ", " attachment;filename= " + ExportFileName + " .xls ");
53 // 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。
54 // Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xml");
55 Response.Write( " <?xml version='1.0'?><?mso-application progid='Excel.Sheet'?> ");
56 Response.Write( @" \r\n<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
57 xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
58 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'> ");
59 Response.Write( @" \r\n<DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'> ");
60 Response.Write( @" \r\n<Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
61 <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version> ");
62 Response.Write( " \r\n</DocumentProperties> ");
63 Response.Write( @" \r\n<Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
64 <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style> ");
65 // 定义标题样式
66 Response.Write( @" <Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
67 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
68 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
69 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
70 <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style> ");
71
72 // 定义边框
73 Response.Write( @" <Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
74 <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
75 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
76 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
77 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style> ");
78
79 Response.Write( " </Styles> ");
80
81
82 for ( int i = 0; i < SheetCount; i++)
83 {
84 // 计算该 Sheet 中的数据起始行和结束行。
85 int start = ItenCountPerSheet * i;
86 int end = ItenCountPerSheet * (i + 1);
87 if (end > GridView1.Rows.Count) end = GridView1.Rows.Count;
88
89 Response.Write( " \r\n<Worksheet ss:Name='Sheet " + (i+ 1) + " '> ");
90 Response.Write( " \r\n<Table x:FullColumns='1' x:FullRows='1'> ");
91 // 输出标题
92
93 Response.Write( " \r\n<Row ss:AutoFitHeight='1'> ");
94 for ( int j = 0; j < GridView1.HeaderRow.Cells.Count; j++)
95 {
96 Response.Write( " <Cell ss:StyleID='Header'><Data ss:Type='String'> " + GridView1.HeaderRow.Cells[j].Text + " </Data></Cell> ");
97 }
98 Response.Write( " \r\n</Row> ");
99
100 for ( int j = start; j < end; j++)
101 {
102 Response.Write( " \r\n<Row> ");
103 for ( int c = 0; c < GridView1.HeaderRow.Cells.Count; c++)
104 {
105 // 对于数字,采用Number数字类型
106 if (c > 1)
107 {
108 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='Number'> " + GridView1.Rows[j].Cells[c].Text + " </Data></Cell> ");
109 }
110 else
111 {
112 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='String'> " + GridView1.Rows[j].Cells[c].Text + " </Data></Cell> ");
113 }
114 }
115 Response.Write( " \r\n</Row> ");
116 }
117 Response.Write( " \r\n</Table> ");
118 Response.Write( " \r\n</Worksheet> ");
119 Response.Flush();
120 }
121 Response.Write( " \r\n</Workbook> ");
122 Response.End();
123 }
124
125 </script>
126
127 <html xmlns= " http://www.w3.org/1999/xhtml ">
128 <head runat= " server ">
129 <title></title>
130 </head>
131 <body>
132 <form id= " form1 " runat= " server ">
133 <asp:Button ID= " Button1 " runat= " server " OnClick= " Button1_Click " Text= " 导出测试 " />
134 <asp:GridView ID= " GridView1 " runat= " server ">
135 </asp:GridView>
136 </form>
137 </body>
138 </html>
另外,请注意:代码里面添加了\r\n换行,是为了生成出来的xml格式有换行,实际可以不用。
如果是DataTable,DataSet,可以直接导出成文件。下面是完整的源代码:
C# 代码
1
protected
void Page_Load(
object sender, EventArgs e)
2 {
3 // 下面采用的是DataTable,也可以采用DataSet,其中每个DataTable可以保存成一个 Sheet
4 // 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。
5 System.Data.DataTable dt = new System.Data.DataTable();
6 if (!Page.IsPostBack)
7 {
8 System.Data.DataRow dr;
9 dt.Columns.Add( new System.Data.DataColumn( " 学生班级 ", typeof(System.String)));
10 dt.Columns.Add( new System.Data.DataColumn( " 学生姓名 ", typeof(System.String)));
11 dt.Columns.Add( new System.Data.DataColumn( " 语文 ", typeof(System.Decimal)));
12 dt.Columns.Add( new System.Data.DataColumn( " 数学 ", typeof(System.Decimal)));
13 dt.Columns.Add( new System.Data.DataColumn( " 英语 ", typeof(System.Decimal)));
14 dt.Columns.Add( new System.Data.DataColumn( " 计算机 ", typeof(System.Decimal)));
15 System.Random rd = new System.Random();
16 for ( int i = 0; i < 88; i++)
17 {
18 dr = dt.NewRow();
19 dr[ 0] = " 班级 " + i.ToString();
20 dr[ 1] = " 【孟子E章】 " + i.ToString();
21 dr[ 2] = System.Math.Round(rd.NextDouble() * 100, 0);
22 dr[ 3] = System.Math.Round(rd.NextDouble() * 100, 0);
23 dr[ 4] = System.Math.Round(rd.NextDouble() * 100, 0);
24 dr[ 5] = System.Math.Round(rd.NextDouble() * 100, 0);
25 dt.Rows.Add(dr);
26 }
27 }
28
29 // 假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
30 int ItenCountPerSheet = 10;
31 int SheetCount = Convert.ToInt32(Math.Ceiling(( double)dt.Rows.Count / ItenCountPerSheet));
32 Response.ClearContent();
33 Response.BufferOutput = true;
34 Response.Charset = " utf-8 ";
35 Response.ContentType = " application/ms-excel ";
36 Response.AddHeader( " Content-Transfer-Encoding ", " binary ");
37 Response.ContentEncoding = System.Text.Encoding.UTF8;
38 // Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");
39 // 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。
40
41 String FileName = " 孟宪会Excel表格测试 ";
42 if (!String.IsNullOrEmpty(Request.UserAgent))
43 {
44 // firefox 里面文件名无需编码。
45 if (!(Request.UserAgent.IndexOf( " Firefox ") > - 1 && Request.UserAgent.IndexOf( " Gecko ") > - 1))
46 {
47 FileName = Server.UrlEncode(FileName);
48 }
49 }
50 Response.AppendHeader( " Content-Disposition ", " attachment;filename= " + FileName + " .xml ");
51 Response.Write( " <?xml version='1.0'?><?mso-application progid='Excel.Sheet'?> ");
52 Response.Write( @" <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
53 xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
54 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'> ");
55 Response.Write( @" <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'> ");
56 Response.Write( @" <Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
57 <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version> ");
58 Response.Write( " </DocumentProperties> ");
59 Response.Write( @" <Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
60 <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style> ");
61 // 定义标题样式
62 Response.Write( @" <Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
63 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
64 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
65 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
66 <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style> ");
67
68 // 定义边框
69 Response.Write( @" <Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
70 <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
71 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
72 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
73 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style> ");
74
75 Response.Write( " </Styles> ");
76
77 // SheetCount代表生成的 Sheet 数目。
78 for ( int i = 0; i < SheetCount; i++)
79 {
80 // 计算该 Sheet 中的数据起始行和结束行。
81 int start = ItenCountPerSheet * i;
82 int end = ItenCountPerSheet * (i + 1);
83 if (end > dt.Rows.Count) end = dt.Rows.Count;
84
85 Response.Write( " <Worksheet ss:Name='Sheet " + (i + 1) + " '> ");
86 Response.Write( " <Table x:FullColumns='1' x:FullRows='1'> ");
87
88 // 输出标题
89 Response.Write( " \r\n<Row ss:AutoFitHeight='1'> ");
90 for ( int j = 0; j < dt.Columns.Count; j++)
91 {
92 Response.Write( " <Cell ss:StyleID='Header'><Data ss:Type='String'> " + dt.Columns[j].ColumnName + " </Data></Cell> ");
93 }
94 Response.Write( " \r\n</Row> ");
95
96
97 for ( int j = start; j < end; j++)
98 {
99 Response.Write( " <Row> ");
100 for ( int c = 0; c < 6; c++)
101 {
102 // 对于数字,采用Number数字类型
103 if (c > 1)
104 {
105 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='Number'> " + dt.Rows[j][c].ToString() + " </Data></Cell> ");
106 }
107 else
108 {
109 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='String'> " + dt.Rows[j][c].ToString() + " </Data></Cell> ");
110 }
111 }
112 Response.Write( " </Row> ");
113 }
114 Response.Write( " </Table> ");
115 Response.Write( " </Worksheet> ");
116 Response.Flush();
117 }
118 Response.Write( " </Workbook> ");
119 Response.End();
120 }
2 {
3 // 下面采用的是DataTable,也可以采用DataSet,其中每个DataTable可以保存成一个 Sheet
4 // 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。
5 System.Data.DataTable dt = new System.Data.DataTable();
6 if (!Page.IsPostBack)
7 {
8 System.Data.DataRow dr;
9 dt.Columns.Add( new System.Data.DataColumn( " 学生班级 ", typeof(System.String)));
10 dt.Columns.Add( new System.Data.DataColumn( " 学生姓名 ", typeof(System.String)));
11 dt.Columns.Add( new System.Data.DataColumn( " 语文 ", typeof(System.Decimal)));
12 dt.Columns.Add( new System.Data.DataColumn( " 数学 ", typeof(System.Decimal)));
13 dt.Columns.Add( new System.Data.DataColumn( " 英语 ", typeof(System.Decimal)));
14 dt.Columns.Add( new System.Data.DataColumn( " 计算机 ", typeof(System.Decimal)));
15 System.Random rd = new System.Random();
16 for ( int i = 0; i < 88; i++)
17 {
18 dr = dt.NewRow();
19 dr[ 0] = " 班级 " + i.ToString();
20 dr[ 1] = " 【孟子E章】 " + i.ToString();
21 dr[ 2] = System.Math.Round(rd.NextDouble() * 100, 0);
22 dr[ 3] = System.Math.Round(rd.NextDouble() * 100, 0);
23 dr[ 4] = System.Math.Round(rd.NextDouble() * 100, 0);
24 dr[ 5] = System.Math.Round(rd.NextDouble() * 100, 0);
25 dt.Rows.Add(dr);
26 }
27 }
28
29 // 假如每10条数据放在一个 Sheet 里面,先计算需要多少个 Sheet
30 int ItenCountPerSheet = 10;
31 int SheetCount = Convert.ToInt32(Math.Ceiling(( double)dt.Rows.Count / ItenCountPerSheet));
32 Response.ClearContent();
33 Response.BufferOutput = true;
34 Response.Charset = " utf-8 ";
35 Response.ContentType = " application/ms-excel ";
36 Response.AddHeader( " Content-Transfer-Encoding ", " binary ");
37 Response.ContentEncoding = System.Text.Encoding.UTF8;
38 // Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");
39 // 采用下面的格式,将兼容 Excel 2003,Excel 2007, Excel 2010。
40
41 String FileName = " 孟宪会Excel表格测试 ";
42 if (!String.IsNullOrEmpty(Request.UserAgent))
43 {
44 // firefox 里面文件名无需编码。
45 if (!(Request.UserAgent.IndexOf( " Firefox ") > - 1 && Request.UserAgent.IndexOf( " Gecko ") > - 1))
46 {
47 FileName = Server.UrlEncode(FileName);
48 }
49 }
50 Response.AppendHeader( " Content-Disposition ", " attachment;filename= " + FileName + " .xml ");
51 Response.Write( " <?xml version='1.0'?><?mso-application progid='Excel.Sheet'?> ");
52 Response.Write( @" <Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'
53 xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'
54 xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'> ");
55 Response.Write( @" <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'> ");
56 Response.Write( @" <Author>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>
57 <Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version> ");
58 Response.Write( " </DocumentProperties> ");
59 Response.Write( @" <Styles><Style ss:ID='Default' ss:Name='Normal'><Alignment ss:Vertical='Center'/>
60 <Borders/><Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/><Interior/><NumberFormat/><Protection/></Style> ");
61 // 定义标题样式
62 Response.Write( @" <Style ss:ID='Header'><Borders><Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
63 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
64 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
65 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders>
66 <Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'/></Style> ");
67
68 // 定义边框
69 Response.Write( @" <Style ss:ID='border'><NumberFormat ss:Format='@'/><Borders>
70 <Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/>
71 <Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/>
72 <Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/>
73 <Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'/></Borders></Style> ");
74
75 Response.Write( " </Styles> ");
76
77 // SheetCount代表生成的 Sheet 数目。
78 for ( int i = 0; i < SheetCount; i++)
79 {
80 // 计算该 Sheet 中的数据起始行和结束行。
81 int start = ItenCountPerSheet * i;
82 int end = ItenCountPerSheet * (i + 1);
83 if (end > dt.Rows.Count) end = dt.Rows.Count;
84
85 Response.Write( " <Worksheet ss:Name='Sheet " + (i + 1) + " '> ");
86 Response.Write( " <Table x:FullColumns='1' x:FullRows='1'> ");
87
88 // 输出标题
89 Response.Write( " \r\n<Row ss:AutoFitHeight='1'> ");
90 for ( int j = 0; j < dt.Columns.Count; j++)
91 {
92 Response.Write( " <Cell ss:StyleID='Header'><Data ss:Type='String'> " + dt.Columns[j].ColumnName + " </Data></Cell> ");
93 }
94 Response.Write( " \r\n</Row> ");
95
96
97 for ( int j = start; j < end; j++)
98 {
99 Response.Write( " <Row> ");
100 for ( int c = 0; c < 6; c++)
101 {
102 // 对于数字,采用Number数字类型
103 if (c > 1)
104 {
105 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='Number'> " + dt.Rows[j][c].ToString() + " </Data></Cell> ");
106 }
107 else
108 {
109 Response.Write( " <Cell ss:StyleID='border'><Data ss:Type='String'> " + dt.Rows[j][c].ToString() + " </Data></Cell> ");
110 }
111 }
112 Response.Write( " </Row> ");
113 }
114 Response.Write( " </Table> ");
115 Response.Write( " </Worksheet> ");
116 Response.Flush();
117 }
118 Response.Write( " </Workbook> ");
119 Response.End();
120 }