(转)ASP.NET读出XML数据生成Excel

概要描述：读取XML数据，最终生成Excel数据。
一.新建一个book.xml,代码内容如下：

<?xml version="1.0" encoding="utf-8" ?>
2

<bookstore xmlns="http://tempuri.org/Book.xsd">
3

<book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0">
4

<title>C# Program</title>
5

<author>
6

<first-name>Wang</first-name>
7

<last-name>Lei</last-name>
8

</author>
9

<price>10</price>
10

</book>
11

<book genre="novel" publicationdate="1967" ISBN="0-201-63361-2">
12

<title>VB.Net Program</title>
13

<author>
14

<first-name>James</first-name>
15

<last-name>Wang</last-name>
16

</author>
17

<price>8</price>
18

</book>
19

<book genre="philosophy" publicationdate="1991" ISBN="1-861001-57-6">
20

<title>Sql server 2005 database manager</title>
21

<author>
22

<first-name>Elvis</first-name>
23

<last-name>Yu</last-name>
24

</author>
25

<price>5</price>
26

</book>
27

</bookstore>

二.新建一个Default.aspx的页面，在Default.aspx页面上拖放一个按钮，双击进入按钮的事件，代码如下：

using System;
2

using System.Data;
3

using System.Configuration;
4

using System.Web;
5

using System.Web.Security;
6

using System.Web.UI;
7

using System.Web.UI.WebControls;
8

using System.Web.UI.WebControls.WebParts;
9

using System.Web.UI.HtmlControls;
10

using System.IO;
11

public partial class _Default : System.Web.UI.Page
13

{
14

protected void Page_Load(object sender, EventArgs e)
15

{
16

}
18

protected void Button1_Click(object sender, EventArgs e)
19

{
20

try
21

{
22

//要转换的XML文件
23

string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xml");
24

DataSet dsBook = new DataSet();
25

dsBook.ReadXml(XMLFileName);
26

int rows = dsBook.Tables[0].Rows.Count + 1;
27

int cols = dsBook.Tables[0].Columns.Count;
28

//将要生成的Excel文件
30

string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "book.xls");
31

if (File.Exists(ExcelFileName))
32

{
33

File.Delete(ExcelFileName);
34

}
35

StreamWriter writer = new StreamWriter(ExcelFileName, false);
36

writer.WriteLine("<?xml version=\"1.0\"?>");
37

writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
38

writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
39

writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
40

writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
41

writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
42

writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
43

writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
44

writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
45

writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
46

writer.WriteLine(" <Company>Your Company Here</Company>");
47

writer.WriteLine(" <Version>11.6408</Version>");
48

writer.WriteLine(" </DocumentProperties>");
49

writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
50

writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
51

writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
52

writer.WriteLine(" <WindowTopX>480</WindowTopX>");
53

writer.WriteLine(" <WindowTopY>15</WindowTopY>");
54

writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
55

writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
56

writer.WriteLine(" </ExcelWorkbook>");
57

writer.WriteLine(" <Styles>");
58

writer.WriteLine(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
59

writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\"/>");
60

writer.WriteLine(" <Borders/>");
61

writer.WriteLine(" <Font/>");
62

writer.WriteLine(" <Interior/>");
63

writer.WriteLine(" <Protection/>");
64

writer.WriteLine(" </Style>");
65

writer.WriteLine(" <Style ss:ID=\"s21\">");
66

writer.WriteLine(" <Alignment ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
67

writer.WriteLine(" </Style>");
68

writer.WriteLine(" </Styles>");
69

writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
70

writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
71

writer.WriteLine(" x:FullRows=\"1\">");
72

//生成标题
73

writer.WriteLine("<Row>");
74

foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
75

{
76

writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
77

writer.Write(eachCloumn.ColumnName.ToString());
78

writer.WriteLine("</Data></Cell>");
79

}
80

writer.WriteLine("</Row>");
81

//生成数据记录
83

foreach (DataRow eachRow in dsBook.Tables[0].Rows)
84

{
85

writer.WriteLine("<Row>");
86

for (int currentRow = 0; currentRow != cols; currentRow++)
87

{
88

writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
89

writer.Write(eachRow[currentRow].ToString());
90

writer.WriteLine("</Data></Cell>");
91

}
92

writer.WriteLine("</Row>");
93

}
94

writer.WriteLine(" </Table>");
95

writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
96

writer.WriteLine(" <Selected/>");
97

writer.WriteLine(" <Panes>");
98

writer.WriteLine(" <Pane>");
99

writer.WriteLine(" <Number>3</Number>");
100

writer.WriteLine(" <ActiveRow>1</ActiveRow>");
101

writer.WriteLine(" </Pane>");
102

writer.WriteLine(" </Panes>");
103

writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
104

writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
105

writer.WriteLine(" </WorksheetOptions>");
106

writer.WriteLine(" </Worksheet>");
107

writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
108

writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
109

writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
110

writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
111

writer.WriteLine(" </WorksheetOptions>");
112

writer.WriteLine(" </Worksheet>");
113

writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
114

writer.WriteLine(" <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
115

writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
116

writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
117

writer.WriteLine(" </WorksheetOptions>");
118

writer.WriteLine(" </Worksheet>");
119

writer.WriteLine("</Workbook>");
120

writer.Close();
121

Response.Write("<script language=\"javascript\">" + "alert('" + "转换成功! 转换后的Excel文件名为: " + ExcelFileName + "')" + "</script>");
122

}
123

catch (Exception ex)
124

{
125

Response.Write("<script language=\"javascript\">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
126

}
127

}
128

}
129