Xml+Xslt导出Excel实例

 

　　最近研究了一下基于xml+Xslt的统计报表导出Excel功能，原有的设计是用JS实现，优点是实现起来比较简单，缺点同样很明显，对导出复杂的表格无能为力，而且需要客户端安装Excel，另一种办法是在服务器端实现，调用office的DLL直接操作Excel，可控范围较广，但是遇到大数据量的统计报表则响应较慢，这对于客户来说是无法忍受的。于是，就有了基于xml+xslt的解决方案。由于Excel可另存为后缀名为.xml的数据格式，这就为这种解决方案提供了实现的可能性。
　　实现原理：由于统计报表是基于xml+xslt实现，从数据库取出的数据已保存在xml中，我们只需要将这xml暂存下来，然后用导出Excel所用的xslt文件转换即可。
　　实现过程：
      用于导出Excel所用的DepositExcel.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 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

<?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.
　　

#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组件等解决方案的优点，实乃一大杀器，哈哈^_^