序:
最近在给客户做一个管理系统的时候,客户提出要将查询结果导出到Excel。对于还是实习生的我倍感压力,于是找了点资料。网上有个示例,其中方法十分简单。于是照猫画虎,把方法和心得与大家分享。OK,Let`s go
第一步:
创建一个Excel文件(就是 普通的Excel),在第一个单元格输入“city”,然后选择“另存为”,此时弹出保存窗口。注意:将保持格式选择为“XML 表格(*.xml)”,点击保存。完毕后用记事本打开这个Excel文件。你将看到如下的代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
<? xml version = "1.0" ?>
<? mso-application progid = "Excel.Sheet" ?>
< Workbook xmlns = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o = "urn:schemas-microsoft-com:office:office"
xmlns:x = "urn:schemas-microsoft-com:office:excel"
xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html = "http://www.w3.org/TR/REC-html40" >
< DocumentProperties xmlns = "urn:schemas-microsoft-com:office:office" >
< Author >MC SYSTEM</ Author >
< LastAuthor >MC SYSTEM</ LastAuthor >
< Created >2009-05-28T16:20:57Z</ Created >
< Company >MC SYSTEM</ Company >
< Version >11.5606</ Version >
</ DocumentProperties >
< ExcelWorkbook xmlns = "urn:schemas-microsoft-com:office:excel" >
< WindowHeight >11250</ WindowHeight >
< WindowWidth >18180</ WindowWidth >
< WindowTopX >120</ WindowTopX >
< WindowTopY >75</ WindowTopY >
< ProtectStructure >False</ ProtectStructure >
< ProtectWindows >False</ ProtectWindows >
</ ExcelWorkbook >
< Styles >
< Style ss:ID = "Default" ss:Name = "Normal" >
< Alignment ss:Vertical = "Center" />
< Borders />
< Font ss:FontName = "宋体" x:CharSet = "134" ss:Size = "12" />
< Interior />
< NumberFormat />
< Protection />
</ Style >
</ Styles >
< Worksheet ss:Name = "Sheet1" >
< Table ss:ExpandedColumnCount = "1" ss:ExpandedRowCount = "1" x:FullColumns = "1"
x:FullRows = "1" ss:DefaultColumnWidth = "54" ss:DefaultRowHeight = "14.25" >
< Row ss:>
< Cell >< Data ss:Type = "String" >org</ Data ></ Cell >
</ Row >
</ Table >
< WorksheetOptions xmlns = "urn:schemas-microsoft-com:office:excel" >
< Unsynced />
< Selected />
< Panes >
< Pane >
< Number >3</ Number >
< ActiveRow >3</ ActiveRow >
< ActiveCol >1</ ActiveCol >
</ Pane >
</ Panes >
< ProtectObjects >False</ ProtectObjects >
< ProtectScenarios >False</ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
< Worksheet ss:Name = "Sheet2" >
< Table ss:ExpandedColumnCount = "0" ss:ExpandedRowCount = "0" x:FullColumns = "1"
x:FullRows = "1" ss:DefaultColumnWidth = "54" ss:DefaultRowHeight = "14.25" />
< WorksheetOptions xmlns = "urn:schemas-microsoft-com:office:excel" >
< Unsynced />
< ProtectObjects >False</ ProtectObjects >
< ProtectScenarios >False</ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
< Worksheet ss:Name = "Sheet3" >
< Table ss:ExpandedColumnCount = "0" ss:ExpandedRowCount = "0" x:FullColumns = "1"
x:FullRows = "1" ss:DefaultColumnWidth = "54" ss:DefaultRowHeight = "14.25" />
< WorksheetOptions xmlns = "urn:schemas-microsoft-com:office:excel" >
< Unsynced />
< ProtectObjects >False</ ProtectObjects >
< ProtectScenarios >False</ ProtectScenarios >
</ WorksheetOptions >
</ Worksheet >
</ Workbook >
|
其实这个就是将XML转换成Excel最关键的部分。实际上这就是Excel对应的XML格式。也就是说按照这个格式来写一个XML文件,然后用Excel打开,Excel会将这个文件以Excel的样子正确的现实出来。
第二步:
在.net项目中添加一个xslt文件。学过xslt的朋友都知道通过xslt可以将xml转换成其他的格式。可能有的朋友还没有理解我的意思。其实我们通过xslt将xml转换成“第一步”中的格式,然后保存或者输出到客户端就完成了导出Excel的功能了。
对于第一步中的XML代码我们要进行一些修改,因为这是Excel自动生成的,其中包含了大量的无用信息。修改后的代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
<? xml version = "1.0" ?>
<? mso-application progid = "Excel.Sheet" ?>
< Workbook xmlns = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o = "urn:schemas-microsoft-com:office:office"
xmlns:x = "urn:schemas-microsoft-com:office:excel"
xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html = "http://www.w3.org/TR/REC-html40" >
< Worksheet ss:Name = "Sheet1" >
< Table ss:ExpandedColumnCount = "1" ss:ExpandedRowCount = "1" x:FullColumns = "1"
x:FullRows = "1" ss:DefaultColumnWidth = "54" ss:DefaultRowHeight = "14.25" >
< Row ss:>
< Cell >
< Data ss:Type = "String" >
city <!--还记得吗?这是第一步中输入的city -->
</ Data >
</ Cell >
</ Row >
</ Table >
</ Worksheet >
</ Workbook >
|
到目前为止,这个代码还没有实际的用处。我们将这个代码copy到创建的xslt文件中,并将xslt的一些语法加入到上面的代码中,最后xslt文件将是这个样子:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
<? xml version = "1.0" ?>
< xsl:stylesheet version = "1.0" xmlns:xsl = "http://www.w3.org/1999/XSL/Transform" >
< xsl:template match = "/" >
<? mso-application progid = "Excel.Sheet" ?>
< Workbook xmlns = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o = "urn:schemas-microsoft-com:office:office"
xmlns:x = "urn:schemas-microsoft-com:office:excel"
xmlns:ss = "urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html = "http://www.w3.org/TR/REC-html40" >
< Worksheet ss:Name = "Sheet1" >
< Table >
< xsl:for-each select = "*" >
< Row >
< Cell >
< Data ss:Type = "String" >
< xsl:value-of select = "." />
</ Data >
</ Cell >
< Cell >
< Data ss:Type = "String" >
< xsl:value-of select = "." />
</ Data >
</ Cell >
</ Row >
</ xsl:for-each >
</ Table >
</ Worksheet >
</ Workbook >
</ xsl:template >
</ xsl:stylesheet >
|
保存这个xslt文件。
第三步:
编写.net后台代码,我是通过一个按钮事件触发导出的。代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
private void button1_Click( object sender, EventArgs e)
{
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml( "<records><record><org>北京</org></record></records>" );
XslCompiledTransform xct = new XslCompiledTransform();
xct.Load(Application.StartupPath+ "\\excel.xslt" );
XmlTextWriter writer = new XmlTextWriter( "output.xls" , null );
writer.WriteProcessingInstruction( "xml" , "version=" 1.0 "" );
xct.Transform(xdoc, null ,writer);
writer.Close();
}
|
总结:
其实这个方法的核心就是利用.net的xslt转换功能将xml以一种特殊格式输出出去。而这种格式可以利用Excel软件自动生成