Asp.net 2.0 装载XML文件中数据到Excel文件中(示例代码下载)

2006年08月10日 13:26:00

(一). 概要

从读取XML文件数据, 生成Excel文件.

(二).运行效果图示例

1. 要转换数据的XML文件

2. 生成的Excel文件显示效果

(三). 代码

1 try
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 /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 /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 /ProtectStructure< ");
36 writer.WriteLine(" >ProtectWindows /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 /ProtectObjects< ");
86 writer.WriteLine(" >ProtectScenarios /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 /ProtectObjects< ");
92 writer.WriteLine(" >ProtectScenarios /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 /ProtectObjects< ");
98 writer.WriteLine(" >ProtectScenarios /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 }

(四). 示例代码下载

http://www.cnblogs.com/Files/ChengKing/XMLChangeToExcel.rar

Trackback: http://tb.blog.youkuaiyun.com/TrackBack.aspx?PostId=1045853