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

来源：互联网发布：结婚的意义知乎编辑：程序博客网时间：2024/05/10 20:34

2006年08月10日 13:26:00

(一). 概要

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

(二).运行效果图示例

1. 要转换数据的XML文件

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

(三). 代码

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

//将要生成的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

//生成标题行
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

//生成数据记录行
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.csdn.net/TrackBack.aspx?PostId=1045853