(一). 概要
从读取XML文件数据, 生成Excel文件.
(二).运行效果图示例
1. 要转换数据的XML文件
2. 生成的Excel文件显示效果
(三). 代码
1try
2 {
3 //要转换的XML文件
4 string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xml");
5 DataSet dsBook = new DataSet();
6 dsBook.ReadXml( XMLFileName );
7 int rows = dsBook.Tables[0].Rows.Count + 1;
8 int cols = dsBook.Tables[0].Columns.Count;
9
10 //将要生成的Excel文件
11 string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xls");
12 if (File.Exists(ExcelFileName))
13 {
14 File.Delete(ExcelFileName);
15 }
16 StreamWriter writer = new StreamWriter(ExcelFileName, false);
17 writer.WriteLine("<?xml version=/"1.0/"?>");
18 writer.WriteLine("<?mso-application progid=/"Excel.Sheet/"?>");
19 writer.WriteLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
20 writer.WriteLine(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
21 writer.WriteLine(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
22 writer.WriteLine(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
23 writer.WriteLine(" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
24 writer.WriteLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
25 writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
26 writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
27 writer.WriteLine(" <Company>Your Company Here</Company>");
28 writer.WriteLine(" <Version>11.6408</Version>");
29 writer.WriteLine(" </DocumentProperties>");
30 writer.WriteLine(" <ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
31 writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
32 writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
33 writer.WriteLine(" <WindowTopX>480</WindowTopX>");
34 writer.WriteLine(" <WindowTopY>15</WindowTopY>");
35 writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
36 writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
37 writer.WriteLine(" </ExcelWorkbook>");
38 writer.WriteLine(" <Styles>");
39 writer.WriteLine(" <Style ss:ID=/"Default/" ss:Name=/"Normal/">");
40 writer.WriteLine(" <Alignment ss:Vertical=/"Bottom/"/>");
41 writer.WriteLine(" <Borders/>");
42 writer.WriteLine(" <Font/>");
43 writer.WriteLine(" <Interior/>");
44 writer.WriteLine(" <Protection/>");
45 writer.WriteLine(" </Style>");
46 writer.WriteLine(" <Style ss:ID=/"s21/">");
47 writer.WriteLine(" <Alignment ss:Vertical=/"Bottom/" ss:WrapText=/"1/"/>");
48 writer.WriteLine(" </Style>");
49 writer.WriteLine(" </Styles>");
50 writer.WriteLine(" <Worksheet ss:Name=/"MyReport/">");
51 writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", cols.ToString(), rows.ToString()));
52 writer.WriteLine(" x:FullRows=/"1/">");
53
54 //生成标题行
55 writer.WriteLine("<Row>");
56 foreach(DataColumn eachCloumn in dsBook.Tables[0].Columns)
57 {
58 writer.Write("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
59 writer.Write(eachCloumn.ColumnName.ToString());
60 writer.WriteLine("</Data></Cell>");
61 }
62 writer.WriteLine("</Row>");
63
64 //生成数据记录行
65 foreach (DataRow eachRow in dsBook.Tables[0].Rows)
66 {
67 writer.WriteLine("<Row>");
68 for(int currentRow = 0; currentRow != cols; currentRow++)
69 {
70 writer.Write("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
71 writer.Write(eachRow[currentRow].ToString());
72 writer.WriteLine("</Data></Cell>");
73 }
74 writer.WriteLine("</Row>");
75 }
76 writer.WriteLine(" </Table>");
77 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
78 writer.WriteLine(" <Selected/>");
79 writer.WriteLine(" <Panes>");
80 writer.WriteLine(" <Pane>");
81 writer.WriteLine(" <Number>3</Number>");
82 writer.WriteLine(" <ActiveRow>1</ActiveRow>");
83 writer.WriteLine(" </Pane>");
84 writer.WriteLine(" </Panes>");
85 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
86 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
87 writer.WriteLine(" </WorksheetOptions>");
88 writer.WriteLine(" </Worksheet>");
89 writer.WriteLine(" <Worksheet ss:Name=/"Sheet2/">");
90 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
91 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
92 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
93 writer.WriteLine(" </WorksheetOptions>");
94 writer.WriteLine(" </Worksheet>");
95 writer.WriteLine(" <Worksheet ss:Name=/"Sheet3/">");
96 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
97 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
98 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
99 writer.WriteLine(" </WorksheetOptions>");
100 writer.WriteLine(" </Worksheet>");
101 writer.WriteLine("</Workbook>");
102 writer.Close();
103 Response.Write("<script language=/"javascript/">" + "alert('" + "转换成功! 转换后的Excel文件名为: " + ExcelFileName + "')" +"</script>");
104 }
105 catch (Exception ex)
106 {
107 Response.Write("<script language=/"javascript/">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
108 }
2 {
3 //要转换的XML文件
4 string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xml");
5 DataSet dsBook = new DataSet();
6 dsBook.ReadXml( XMLFileName );
7 int rows = dsBook.Tables[0].Rows.Count + 1;
8 int cols = dsBook.Tables[0].Columns.Count;
9
10 //将要生成的Excel文件
11 string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xls");
12 if (File.Exists(ExcelFileName))
13 {
14 File.Delete(ExcelFileName);
15 }
16 StreamWriter writer = new StreamWriter(ExcelFileName, false);
17 writer.WriteLine("<?xml version=/"1.0/"?>");
18 writer.WriteLine("<?mso-application progid=/"Excel.Sheet/"?>");
19 writer.WriteLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
20 writer.WriteLine(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
21 writer.WriteLine(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
22 writer.WriteLine(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
23 writer.WriteLine(" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
24 writer.WriteLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
25 writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
26 writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
27 writer.WriteLine(" <Company>Your Company Here</Company>");
28 writer.WriteLine(" <Version>11.6408</Version>");
29 writer.WriteLine(" </DocumentProperties>");
30 writer.WriteLine(" <ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
31 writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
32 writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
33 writer.WriteLine(" <WindowTopX>480</WindowTopX>");
34 writer.WriteLine(" <WindowTopY>15</WindowTopY>");
35 writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
36 writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
37 writer.WriteLine(" </ExcelWorkbook>");
38 writer.WriteLine(" <Styles>");
39 writer.WriteLine(" <Style ss:ID=/"Default/" ss:Name=/"Normal/">");
40 writer.WriteLine(" <Alignment ss:Vertical=/"Bottom/"/>");
41 writer.WriteLine(" <Borders/>");
42 writer.WriteLine(" <Font/>");
43 writer.WriteLine(" <Interior/>");
44 writer.WriteLine(" <Protection/>");
45 writer.WriteLine(" </Style>");
46 writer.WriteLine(" <Style ss:ID=/"s21/">");
47 writer.WriteLine(" <Alignment ss:Vertical=/"Bottom/" ss:WrapText=/"1/"/>");
48 writer.WriteLine(" </Style>");
49 writer.WriteLine(" </Styles>");
50 writer.WriteLine(" <Worksheet ss:Name=/"MyReport/">");
51 writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", cols.ToString(), rows.ToString()));
52 writer.WriteLine(" x:FullRows=/"1/">");
53
54 //生成标题行
55 writer.WriteLine("<Row>");
56 foreach(DataColumn eachCloumn in dsBook.Tables[0].Columns)
57 {
58 writer.Write("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
59 writer.Write(eachCloumn.ColumnName.ToString());
60 writer.WriteLine("</Data></Cell>");
61 }
62 writer.WriteLine("</Row>");
63
64 //生成数据记录行
65 foreach (DataRow eachRow in dsBook.Tables[0].Rows)
66 {
67 writer.WriteLine("<Row>");
68 for(int currentRow = 0; currentRow != cols; currentRow++)
69 {
70 writer.Write("<Cell ss:StyleID=/"s21/"><Data ss:Type=/"String/">");
71 writer.Write(eachRow[currentRow].ToString());
72 writer.WriteLine("</Data></Cell>");
73 }
74 writer.WriteLine("</Row>");
75 }
76 writer.WriteLine(" </Table>");
77 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
78 writer.WriteLine(" <Selected/>");
79 writer.WriteLine(" <Panes>");
80 writer.WriteLine(" <Pane>");
81 writer.WriteLine(" <Number>3</Number>");
82 writer.WriteLine(" <ActiveRow>1</ActiveRow>");
83 writer.WriteLine(" </Pane>");
84 writer.WriteLine(" </Panes>");
85 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
86 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
87 writer.WriteLine(" </WorksheetOptions>");
88 writer.WriteLine(" </Worksheet>");
89 writer.WriteLine(" <Worksheet ss:Name=/"Sheet2/">");
90 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
91 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
92 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
93 writer.WriteLine(" </WorksheetOptions>");
94 writer.WriteLine(" </Worksheet>");
95 writer.WriteLine(" <Worksheet ss:Name=/"Sheet3/">");
96 writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
97 writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
98 writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
99 writer.WriteLine(" </WorksheetOptions>");
100 writer.WriteLine(" </Worksheet>");
101 writer.WriteLine("</Workbook>");
102 writer.Close();
103 Response.Write("<script language=/"javascript/">" + "alert('" + "转换成功! 转换后的Excel文件名为: " + ExcelFileName + "')" +"</script>");
104 }
105 catch (Exception ex)
106 {
107 Response.Write("<script language=/"javascript/">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
108 }
(四). 示例代码下载