前段时间做WEB项目时接到一个任务，报表都导出EXCEL。
不能用第三方的报表工具，那只能操作EXCEL Library来解决了。
第一种：在服务端调用COM写EXCEL发关到客户端；
这样不太可行因为系统的用户数量也很多,C#操作Excel(COM)效率不高，还很消耗服务器资源。
第二种:就是通过JSCRIPT来操作ActiveX,原理和第一种一样；只是操作数据操作放到客户端进行。
这两种方法操作和维护都很烦琐，而且效率也不高。

正在烦恼的时间突然想到了Excel到底能不能打开XML呢？
试了一下发现是可以，然后在Excel画了一些表格另存为XML；
打开后看了一下，笑了我可以通过动态生成XML不就可以啦。
动态生成XML？就那些标记的组合就够烦，那会有直接操作COM来得方便直接？
这里就要提一个工具了CodeSmith，它是一个基于模板的代码生成，
模板的语法和ASP差不多，如果写过ASP一看就知道他的原理。
这个工具在这里就不多介绍，有兴趣的朋友可以去了解一下。(当然还有其他代码生成工具，原理一样)
注意：Excel必须是XP或更高版本
HFSoft.Exports都是我自己封装的类
先来看下模NorthWind数据客户信息模板吧

1--   
2Name:   
3Author:   
4Description:

1@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here."

1@ Assembly Name="System.Data"

1@ Import Namespace="System.Data"

1@ Assembly Name ="HFSoft.Exports"

1@ Import Namespace="HFSoft.Exports"

1@ Property Name="DataSource" Type="System.Data.DataSet" Optional="False" Category="Strings" Description="This is a sample string property."

1   
2OnInit();

  1<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html=" http://www.w3.org/TR/REC-html40 " xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
  2<documentproperties xmlns="urn:schemas-microsoft-com:office:office">
  3<author>HFSoft-Henry</author>
  4<lastauthor>HFSoft-Henry</lastauthor>
  5<created>2005-01-16T10:24:48Z</created>
  6<company>HFSoft</company>
  7<version>10.2625</version>
  8</documentproperties>
  9<officedocumentsettings xmlns="urn:schemas-microsoft-com:office:office">
 10<downloadcomponents></downloadcomponents>
 11<locationofcomponents href=" file:///H:\office\OfficeXP\OfficeXP\">
 12</locationofcomponents></officedocumentsettings>
 13<excelworkbook xmlns="urn:schemas-microsoft-com:office:excel">
 14<windowheight>9450</windowheight>
 15<windowwidth>12780</windowwidth>
 16<windowtopx>480</windowtopx>
 17<windowtopy>15</windowtopy>
 18<protectstructure>False</protectstructure>
 19<protectwindows>False</protectwindows>
 20</excelworkbook>
 21<styles>
 22<style ss:id="Default" ss:name="Normal">   
 23<Alignment ss:Vertical="Center"/>   
 24<Borders/>   
 25<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>   
 26<Interior/>   
 27<NumberFormat/>   
 28<Protection/>   
 29</style>
 30<style ss:id="m15730010">   
 31<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>   
 32<Borders>   
 33<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 34<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>   
 35<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>   
 36<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/>   
 37</Borders>   
 38<Font ss:FontName="宋体" x:CharSet="134" ss:Size="14" ss:Color="#FFFFFF"   
 39ss:Bold="1"/>   
 40<Interior ss:Color="#008080" ss:Pattern="Solid"/>   
 41</style>
 42<style ss:id="m15730020">   
 43<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
 44<Borders>   
 45<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>   
 46<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 47<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
 48</Borders>   
 49</style>
 50<style ss:id="m15730030">   
 51<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
 52<Borders>   
 53<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 54<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 55<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
 56<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
 57</Borders>   
 58</style>
 59<style ss:id="m15741120">   
 60<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
 61<Borders>   
 62<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 63<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 64<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
 65<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
 66</Borders>   
 67</style>
 68<style ss:id="m15741130">   
 69<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
 70<Borders>   
 71<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 72<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 73<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>   
 74<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
 75</Borders>   
 76</style>
 77<style ss:id="m15741140">   
 78<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
 79<Borders>   
 80<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 81<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 82<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>   
 83<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
 84</Borders>   
 85</style>
 86<style ss:id="s21">   
 87<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#FF0000"/>   
 88</style>
 89<style ss:id="s40">   
 90<Borders>   
 91<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
 92<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
 93<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
 94<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
 95</Borders>   
 96<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>   
 97</style>
 98<style ss:id="s45">   
 99<Borders>   
100<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
101<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>   
102<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
103<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
104</Borders>   
105<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>   
106</style>
107<style ss:id="s48">   
108<Borders>   
109<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>   
110<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>   
111<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
112<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
113</Borders>   
114<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>   
115</style>
116<style ss:id="s50">   
117<Borders>   
118<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>   
119<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
120<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
121<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
122</Borders>   
123<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>   
124</style>
125<style ss:id="s84">   
126<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
127<Borders>   
128<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
129<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
130<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>   
131<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
132</Borders>   
133</style>
134<style ss:id="s85">   
135<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
136<Borders>   
137<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>   
138<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
139<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>   
140<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
141</Borders>   
142</style>
143<style ss:id="s88">   
144<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>   
145<Borders>   
146<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>   
147<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>   
148<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>   
149<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>   
150</Borders>   
151</style>
152</styles>
153<worksheet ss:name="Sheet1">
154<table ss:defaultcolumnwidth="54" ss:defaultrowheight="14.25" ss:expandedcolumncount="5" ss:expandedrowcount="```
155=DM.Records.Count*8
156```" x:fullcolumns="1" x:fullrows="1">
157<column ss:autofitwidth="0" ss:width="80.25"></column>
158<column ss:autofitwidth="0" ss:index="4" ss:width="71.25"></column>
159<column ss:autofitwidth="0" ss:width="108"></column>

for(int i=0;i<dm.records.count;i++) <row="" ```="" ss:autofitheight="0" ss:height="24.75" {=""> 客户明细信息

公司名称 ``` =DM.Records[i]["CompanyName"] ``` 联系人 ``` =DM.Records[i]["ContactName"] ``` 联系人职务 ``` =DM.Records[i]["ContactTitle"] ``` 地址 ``` =DM.Records[i]["Address"] ``` 邮政编码 ``` =DM.Records[i]["PostalCode"] ``` 城市 ``` =DM.Records[i]["City"] ``` 国家 ``` =DM.Records[i]["Country"] ``` 地区 ``` =DM.Records[i]["Region"] ``` 电话 ``` =DM.Records[i]["Phone"] ``` 传真 ``` =DM.Records[i]["Fax"] ``` ``` } ``` 9 300 300 3 8 2 False False

False False

False False ``` ``` ``` =============================================================================== ```

 1天啊这么复杂的XML自己如何写啊？我们不要忘了Excel，完全可以用Excel做模板导出XML的。   
 2模板里的HFSoft.Exports.DataManager是我自己写的一个数据管理类，用于数据获取和分组的。   
 3模板已经出来的，那我如何调用他来生成XML文件呢？   
 4以下是WEBFORM的一些调用代码。   
 5private void Report(string Template,System.Data.DataSet ds)   
 6{   
 7HFSoft.Exports.ExportAdapter export = new HFSoft.Exports.ExportAdapter();   
 8export.DataSource = ds;   
 9string filename = Guid.NewGuid().ToString().Replace("-","") +".xml";   
10export.SaveFile =Request.PhysicalApplicationPath +@"Reports\ "+ filename;   
11export.Template =Request.PhysicalApplicationPath +@"Reports\ " + Template;   
12HFSoft.Exports.WebExcelReportControl excel = new HFSoft.Exports.WebExcelReportControl(export);   
13excel.HttpFile = "http://"+ Request.ServerVariables["SERVER_NAME"]+ Request.ApplicationPath +"/Reports/" + filename;   
14excel.Execute();   
15this.RegisterStartupScript(Guid.NewGuid().ToString(),excel.ReportJScript);   
16} 
17
18private void cmdCustomeList_Click(object sender, System.EventArgs e)   
19{   
20System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from Customers",ConnectionString);   
21System.Data.DataSet myDS = new DataSet();   
22da.Fill(myDS);   
23Report("CustomerList.cst",myDS);   
24} 
25
26这样用XML导出EXCLE的事例就结束了。   
27其原理很简单就是数据结合XML生成最终XML文档给EXCEL打开，实现这样功能的做法也有很多。   
28对自己来说当然是选取自己感觉最方便的方法去实现。   
29我为了方便选用了第三工具做最烦琐的事件：） 
30
31这里不可以上传附件，如果想要具体例子代码或交流可以发邮件或MSN： [email protected] ; 
32
33打印效果图：   
34![](http://dev.csdn.net/article/60/C:/XmlReport.gif)   
35  
36![](http://dev.csdn.net/article/60/C:/XmlReport1.gif)

运用CodeSmith代码生引擎生成XML报表文件。