运用CodeSmith代码生引擎生成XML报表文件。
前段时间做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数据客户信息模板吧
===========================================================================
<%--
Name:
Author:
Description:
%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" Src="" Inherits="" Debug="False" Description="Template description here." %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="System.Data" %>
<%@ Assembly Name ="HFSoft.Exports" %>
<%@ Import Namespace="HFSoft.Exports"%>
<%@ Property Name="DataSource" Type="System.Data.DataSet" Optional="False" Category="Strings" Description="This is a sample string property." %>
<%
OnInit();
%>
<?xml version="1.0" encoding="GB2312"?>
<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>HFSoft-Henry</Author>
<LastAuthor>HFSoft-Henry</LastAuthor>
<Created>2005-01-16T10:24:48Z</Created>
<Company>HFSoft</Company>
<Version>10.2625</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///H:\office\OfficeXP\OfficeXP\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>9450</WindowHeight>
<WindowWidth>12780</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>15</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>
<Style ss:ID="m15730010">
<Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="2"/>
</Borders>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="14" ss:Color="#FFFFFF"
ss:Bold="1"/>
<Interior ss:Color="#008080" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="m15730020">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="m15730030">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="m15741120">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="m15741130">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="m15741140">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s21">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#FF0000"/>
</Style>
<Style ss:ID="s40">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s45">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s48">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s50">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s84">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s85">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s88">
<Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="2"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="<%=DM.Records.Count*8%>" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:AutoFitWidth="0" ss:Width="80.25"/>
<Column ss:Index="4" ss:AutoFitWidth="0" ss:Width="71.25"/>
<Column ss:AutoFitWidth="0" ss:Width="108"/>
<%
for(int i=0;i<DM.Records.Count;i++)
{
%>
<Row ss:AutoFitHeight="0" ss:Height="24.75">
<Cell ss:MergeAcross="4" ss:StyleID="m15730010"><Data ss:Type="String">客户明细信息</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s45"><Data ss:Type="String">公司名称</Data></Cell>
<Cell ss:MergeAcross="3" ss:StyleID="m15741130"><Data ss:Type="String"><%=DM.Records[i]["CompanyName"]%></Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s45"><Data ss:Type="String">联系人</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="m15741120"><Data ss:Type="String"><%=DM.Records[i]["ContactName"]%></Data></Cell>
<Cell ss:StyleID="s40"><Data ss:Type="String">联系人职务</Data></Cell>
<Cell ss:StyleID="s84"><Data ss:Type="String"><%=DM.Records[i]["ContactTitle"]%></Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s45"><Data ss:Type="String">地址</Data></Cell>
<Cell ss:MergeAcross="3" ss:StyleID="m15741140"><Data ss:Type="String"><%=DM.Records[i]["Address"]%></Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s45"><Data ss:Type="String">邮政编码</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="s85"><Data ss:Type="String"><%=DM.Records[i]["PostalCode"]%></Data></Cell>
<Cell ss:StyleID="s40"><Data ss:Type="String">城市</Data></Cell>
<Cell ss:StyleID="s84"><Data ss:Type="String"><%=DM.Records[i]["City"]%></Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s45"><Data ss:Type="String">国家</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="m15730030"><Data ss:Type="String"><%=DM.Records[i]["Country"]%></Data></Cell>
<Cell ss:StyleID="s40"><Data ss:Type="String">地区</Data></Cell>
<Cell ss:StyleID="s84"><Data ss:Type="String"><%=DM.Records[i]["Region"]%></Data></Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="20.0625">
<Cell ss:StyleID="s48"><Data ss:Type="String">电话</Data></Cell>
<Cell ss:MergeAcross="1" ss:StyleID="m15730020"><Data ss:Type="String"><%=DM.Records[i]["Phone"]%></Data></Cell>
<Cell ss:StyleID="s50"><Data ss:Type="String">传真</Data></Cell>
<Cell ss:StyleID="s88"><Data ss:Type="String"><%=DM.Records[i]["Fax"]%></Data></Cell>
</Row>
<Row ss:Height="20">
<Cell ss:StyleID="s21"/>
</Row>
<%}%>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>300</HorizontalResolution>
<VerticalResolution>300</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>8</ActiveRow>
<ActiveCol>2</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">
<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">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
<script runat="template">
// My methods here.
public HFSoft.Exports.DataManager DM = new HFSoft.Exports.DataManager();
public void OnInit()
{
DM.DataSource = DataSource;
HFSoft.Exports.DataAnalyse analyse = new HFSoft.Exports.DataAnalyse();
DM.Execute(analyse);
}
</script>
===============================================================================
<%%>标记的原理和ASP是一样的。
天啊这么复杂的XML自己如何写啊？我们不要忘了Excel，完全可以用Excel做模板导出XML的。
模板里的HFSoft.Exports.DataManager是我自己写的一个数据管理类，用于数据获取和分组的。
模板已经出来的，那我如何调用他来生成XML文件呢？
以下是WEBFORM的一些调用代码。
private void Report(string Template,System.Data.DataSet ds)
{
HFSoft.Exports.ExportAdapter export = new HFSoft.Exports.ExportAdapter();
export.DataSource = ds;
string filename = Guid.NewGuid().ToString().Replace("-","") +".xml";
export.SaveFile =Request.PhysicalApplicationPath +@"Reports\"+ filename;
export.Template =Request.PhysicalApplicationPath +@"Reports\" + Template;
HFSoft.Exports.WebExcelReportControl excel = new HFSoft.Exports.WebExcelReportControl(export);
excel.HttpFile = "http://"+ Request.ServerVariables["SERVER_NAME"]+ Request.ApplicationPath +"/Reports/" + filename;
excel.Execute();
this.RegisterStartupScript(Guid.NewGuid().ToString(),excel.ReportJScript);
}
private void cmdCustomeList_Click(object sender, System.EventArgs e)
{
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from Customers",ConnectionString);
System.Data.DataSet myDS = new DataSet();
da.Fill(myDS);
Report("CustomerList.cst",myDS);
}
这样用XML导出EXCLE的事例就结束了。
其原理很简单就是数据结合XML生成最终XML文档给EXCEL打开，实现这样功能的做法也有很多。
对自己来说当然是选取自己感觉最方便的方法去实现。
我为了方便选用了第三工具做最烦琐的事件：）
这里不可以上传附件，如果想要具体例子代码或交流可以发邮件或MSN：henryfan@msn.com;
打印效果图：
