前段时间做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:[email protected];
打印效果图:

 
|