(转)ASP.NET读出XML数据生成Excel
概要描述:读取XML数据,最终生成Excel数据。
一.新建一个book.xml,代码内容如下:
1
<?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页面上拖放一个按钮,双击进入按钮的事件,代码如下:![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
public partial class _Default : System.Web.UI.Page
13
{
14
protected void Page_Load(object sender, EventArgs e)
15
{
16![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
}
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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
//将要生成的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![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
82
//生成数据记录
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
文章转载:http://blog.csdn.net/chengking/archive/2006/08/10/1045853.aspx
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)