运用CodeSmith代码生引擎生成XML报表文件。-.NET教程,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;

打印效果图：