最近研究了一下基于xml+Xslt的统计报表导出Excel功能,原有的设计是用JS实现,优点是实现起来比较简单,缺点同样很明显,对导出复杂的表格无能为力,而且需要客户端安装Excel,另一种办法是在服务器端实现,调用office的DLL直接操作Excel,可控范围较广,但是遇到大数据量的统计报表则响应较慢,这对于客户来说是无法忍受的。于是,就有了基于xml+xslt的解决方案。由于Excel可另存为后缀名为.xml的数据格式,这就为这种解决方案提供了实现的可能性。
实现原理:由于统计报表是基于xml+xslt实现,从数据库取出的数据已保存在xml中,我们只需要将这xml暂存下来,然后用导出Excel所用的xslt文件转换即可。
实现过程:
用于导出Excel所用的DepositExcel.xslt
xslt
<?xml version="1.0" encoding="gb2312" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="zkSuperMap"> <?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"> <Styles> <Style ss:ID="Default" ss:Name="Normal"> <Alignment ss:Vertical="Center"/> <Borders/> <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/> <Interior/> <NumberFormat/> <Protection/> </Style> <Style ss:ID="s10"> <Alignment ss:Horizontal="Center" ss:Vertical="Center"/> </Style> <Style ss:ID="s11"> <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="1"/> <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/> <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/> </Borders> </Style> </Styles> <Worksheet ss:Name="Sheet1"> <Table ss:ExpandedColumnCount="9" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5"> <Row ss:AutoFitHeight="0"> <Cell ss:MergeAcross="8" ss:StyleID="s10"> <Data ss:Type="String">商品房专项维修资金缴存、使用、变更周报表</Data> </Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:MergeAcross="8" ss:StyleID="s10"> <Data ss:Type="String"> <xsl:value-of select="BEGINTEIM" /> 至 <xsl:value-of select="ENDTIME" /> </Data> </Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:MergeDown="1" ss:StyleID="s11"> <Data ss:Type="String">序号</Data> </Cell> <Cell ss:MergeDown="1" ss:StyleID="s11"> <Data ss:Type="String">小区名称</Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s11"> <Data ss:Type="String">缴 存</Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s11"> <Data ss:Type="String">使 用</Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String">变 更</Data> </Cell> <Cell ss:MergeAcross="1" ss:MergeDown="1" ss:StyleID="s11"> <Data ss:Type="String">备 注</Data> </Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:StyleID="s11" ss:Index="3"> <Data ss:Type="String">户 数</Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String">金 额(元)</Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String">户 数</Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String">金 额(元)</Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String">户 数</Data> </Cell> </Row> <xsl:for-each select="FUNDINFO"> <Row ss:AutoFitHeight="0"> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="position()" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="DISTRICT_NAME" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="HOUSEFUND_COUNT" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="HOUSEFUND_MONEY" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="FUNDUSE_COUNT" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="FUNDUSE_MONEY" /> </Data> </Cell > <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="FUNDMODIFY_COUNT" /> </Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s11"> <Data ss:Type="String"> </Data> </Cell> </Row> </xsl:for-each> <Row ss:AutoFitHeight="0"> <Cell ss:StyleID="s11"> <Data ss:Type="String"> 总计: </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="SUM_HOUSEFUND_COUNT" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="SUM_HOUSEFUND_MONEY" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="SUM_FUNDUSE_COUNT" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="SUM_FUNDUSE_MONEY" /> </Data> </Cell> <Cell ss:StyleID="s11"> <Data ss:Type="String"> <xsl:value-of select="SUM_FUNDMODIFY_COUNT" /> </Data> </Cell> <Cell ss:MergeAcross="1" ss:StyleID="s11"> <Data ss:Type="String"> </Data> </Cell> </Row> <Row ss:AutoFitHeight="0"> <Cell ss:MergeAcross="2" > <Data ss:Type="String">负责人:</Data> </Cell> <Cell ss:MergeAcross="2" > <Data ss:Type="String">复核人:</Data> </Cell> <Cell ss:MergeAcross="2" > <Data ss:Type="String">填表:</Data> </Cell> </Row> </Table> </Worksheet> </Workbook> </xsl:template> </xsl:stylesheet>
数据文件DepositStatistic.xml
XML
<?xml version="1.0" encoding="gb2312"?> <?xml-stylesheet type='text/xsl' href=''?> <zkSuperMap> <BEGINTEIM>2012/7/1</BEGINTEIM> <ENDTIME>2012/8/3</ENDTIME> <FUNDINFO> <DISTRICT_NAME>安顺家园</DISTRICT_NAME> <HOUSEFUND_COUNT>22</HOUSEFUND_COUNT> <HOUSEFUND_MONEY>541519</HOUSEFUND_MONEY> <FUNDUSE_COUNT>0</FUNDUSE_COUNT> <FUNDUSE_MONEY>0</FUNDUSE_MONEY> <FUNDMODIFY_COUNT>0</FUNDMODIFY_COUNT> </FUNDINFO> <SUM_HOUSEFUND_COUNT>22</SUM_HOUSEFUND_COUNT> <SUM_HOUSEFUND_MONEY>541519</SUM_HOUSEFUND_MONEY> <SUM_FUNDUSE_COUNT>0</SUM_FUNDUSE_COUNT> <SUM_FUNDUSE_MONEY>0</SUM_FUNDUSE_MONEY> <SUM_FUNDMODIFY_COUNT>0</SUM_FUNDMODIFY_COUNT> </zkSuperMap>
呈现统计报表的DepositStatistic.xslt
xslt
<?xml version="1.0" encoding="gb2312" ?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="zkSuperMap"> <xsl:call-template name="DepositStatistic"></xsl:call-template> </xsl:template> <xsl:template name="DepositStatistic"> <xsl:element name="style"> <![CDATA[ .AcceptTable { border-collapse:collapse; table-layout:fixed; } .AcceptTable td { border: solid black 1px; font-family: 宋体; padding: 5px; vertical-align: middle; font-size: 10pt; font-weight: 600; } .AcceptTable td.AcceptTitle { text-align:center; font-weight: 900; font-size: 18pt; border: none; } .AcceptTable td.AcceptHeader { font-weight: 900; font-size:10pt; padding: 2px; border: none; } .AcceptTable tr { height:35px; } .AcceptTable td.AcceptFooter { border:none; vertical-align:middle; } .AcceptTable td p { margin-top:5px; margin-bottom:10px; } .AcceptTable td p.AcceptMater { margin-left:20px; margin-right:20px; margin-top:5px; margin-bottom:3px; border-bottom:1px dashed #dcdcdc; word-break: break-all; } .pageSplit{PAGE-BREAK-AFTER:always;} ]]> </xsl:element> <table cellSpacing="0" cellPadding="0" border="0" class="AcceptTable" align="center"> <tr style="height:0;"> <td style="width:80px;border:none;"></td> <td style="width:130px;border:none;"></td> <td style="width:80px;border:none;"></td> <td style="width:100px;border:none;"></td> <td style="width:80px;border:none;"></td> <td style="width:90px;border:none;"></td> <td style="width:80px;border:none;"></td> <td style="width:120px;border:none;"></td> <td style="width:80px;border:none;"></td> </tr> <tr> <td colspan="9" class="AcceptTitle">商品房专项维修资金缴存、使用、变更周报表</td> </tr> <tr> <td colspan="9" style="text-align:center;border:none;"> <xsl:value-of select="BEGINTEIM" /> 至 <xsl:value-of select="ENDTIME" /> </td> </tr> <tr> <td rowspan="2" style="text-align:center;">序号</td> <td rowspan="2" style="text-align:center;">小区名称</td> <td colspan="2" style="text-align:center;">缴 存</td> <td colspan="2" style="text-align:center;">使 用</td> <td style="text-align:center;">变 更</td> <td rowspan="2" colspan="2" style="text-align:center;">备 注</td> </tr> <tr> <td style="text-align:center;">户 数</td> <td style="text-align:center;">金 额(元)</td> <td style="text-align:center;">户 数</td> <td style="text-align:center;">金 额(元)</td> <td style="text-align:center;">户 数</td> </tr> <xsl:for-each select="FUNDINFO"> <xsl:if test="position() mod 16=0"> <tr style="height:0;"> <td colspan="8" style="border:0px;"> <div class="pageSplit"></div> </td> </tr> </xsl:if> <tr> <td style="text-align:center;"> <xsl:value-of select="position()" /> </td> <td style="text-align:center;" > <xsl:value-of select="DISTRICT_NAME" /> </td> <td style="text-align:center;" > <xsl:value-of select="HOUSEFUND_COUNT" /> </td> <td style="text-align:center;" > <xsl:value-of select="HOUSEFUND_MONEY" /> </td> <td style="text-align:center;" > <xsl:value-of select="FUNDUSE_COUNT" /> </td> <td style="text-align:center;" > <xsl:value-of select="FUNDUSE_MONEY" /> </td> <td style="text-align:center;" > <xsl:value-of select="FUNDMODIFY_COUNT" /> </td> <td style="text-align:center;" colspan="2"> </td> </tr> </xsl:for-each> <tr> <td style="text-align:center;">总 计:</td> <td></td> <td style="text-align:center;"> <xsl:value-of select="SUM_HOUSEFUND_COUNT" /> </td> <td style="text-align:center;"> <xsl:value-of select="SUM_HOUSEFUND_MONEY" /> </td> <td style="text-align:center;"> <xsl:value-of select="SUM_FUNDUSE_COUNT" /> </td> <td style="text-align:center;"> <xsl:value-of select="SUM_FUNDUSE_MONEY" /> </td> <td style="text-align:center;"> <xsl:value-of select="SUM_FUNDMODIFY_COUNT" /> </td> <td style="text-align:center;" colspan="2"></td> </tr> <tr > <td colspan="3" style="text-align:left;border:none;"> 负责人: </td> <td colspan="3" style="text-align:left;border:none;"> 复核人: </td> <td colspan="3" style="text-align:left;border:none;"> 填表: </td> </tr> </table> </xsl:template> </xsl:stylesheet>
这里有个小窍门,一般我们设计统计报表,都是先制作完成DepositStatistic.xslt,与xml结合后的报表呈现无误后,即可在页面上复制报表,粘贴到Excel,再在Excel里另存为xml 电子表格格式,再打开这个文件,去掉冗余的代码,即可得到DepositExcel.xslt.
导出Excel
#region ButtonExportExcel_Click protected void ButtonExportExcel_Click(object sender, EventArgs e) { XmlDocument _XmlDocument = new XmlDocument(); System.Xml.Xsl.XslCompiledTransform xslt = new System.Xml.Xsl.XslCompiledTransform(); string _BuildPath = zkSuperMap.Web.Configuration.PathSetting.TemporaryPath + "WeekStatisticXML\\"; if (!Directory.Exists(_BuildPath)) Directory.CreateDirectory(_BuildPath); _BuildPath += "WeekStatistic_" + BeginTime.Text + ".xml"; if (File.Exists(_BuildPath)) { StringWriter stringWrite = new System.IO.StringWriter(); xslt.Load(Server.MapPath("Xsl/DepositExcel.xslt")); xslt.Transform(_BuildPath, null, stringWrite); //清除客户端当前显示 HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Charset = "gb2312"; HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(BeginTime.Date.ToShortDateString(), System.Text.Encoding.UTF8))); HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 HttpContext.Current.Response.Write(stringWrite.ToString()); HttpContext.Current.Response.End(); HttpContext.Current.Response.Close(); stringWrite.Close(); } } #endregion
最后呈现出来的报表:
基于xml+xslt的导出Excel解决方案,整合了JS跟office组件等解决方案的优点,实乃一大杀器,哈哈^_^