利用XSL将DataSet数据转换为Excel文档

最近在一个ASP.NET项目的开发中有一个模块需要采用OWC控件呈现Excel样式的表格内容，表格内容比较复杂，包括锁定表头、公式、小计、合计、排序等。

以前采用客户端操作OWC实例一个单元格一个单元格的去写，那麻烦程度可想而知，于是便尝试采用新方法，利用XSL转换实现需要的功能。

经过几天一穷二白为基础的学习和摸索，总结出一点心得，还有一点疑问和未处理的问题，放在园子里，如果总结的有什么不正确的地方，还望大家指出。

我采用这种方法前，首先确认了一些限制条件的满足：

1、 在项目中可以统一使用MS Office 2003版本的OWC控件

2、 我们的客户都在使用MS Office 2003，这样这种做法也可以作为直接生成客户端Excel文档的方法

我的目标格式为以下Excel文档（部分）：

 

这个表的特点是：

1、 列数固定

2、 表头锁定

3、 每个部门的费用明细数量不定

4、 小计统计各个部门所属的费用金额之和

5、 合计统计所有部门所有费用的总金额

6、 小计和合计应该设为公式，这样在修改费用明细的时候，小计和合计可以即时反映变化

首先，需要在Excel里面做好一个模板，然后另存为“XML电子表格”，模板内容如下：

 

这个模板包含了几乎所有需要的元素，包括单元格锁定、各种外观样式、行级别的公式设定（比如J4=I4-H4）、一行明细、一行小计、一行合计等一切可以在“设计时”确定的内容。

通过分析这个模板，我们观察到，完成这个模板的数据填充需要进行2层循环：

l 外层循环：需要针对每个部门进行循环，在循环之内填充每个部门的费用明细，并且为每个部门的小计进行公式设置

l 内层循环：在每个部门内，循环填充每个部门的费用明细

为了使用XSL简单起见，我们的DataSet就包含2个DataTable，第一个DataTable（命名为DepNames）包含需要填充的数据中的部门信息；第二个DataTable（命名为FeeDetail）包含所有需要填充的数据（包括部门标识和费用信息）。当然，从原则上来说，只需要第二个DataTable的数据就可以了，第一个DataTable中的数据完全可以通过XSL的某些操作得到，但是我没有查到如何通过XSL的某些操作可以达到Sql语句中类似SELECT DISTINCT SomeField FROM SomeTable的效果（那位大虾知道？），因此只能退而求其次。

DepNames表包含的信息如下：

DepCode         DepName

10101                A部门

10102                B部门

……                  ……

 

FeeDetail表包含的信息如下：

DepCode         FeeCode          ……

10101                550101     ……

10102                640101     ……

……                  ……                  ……

通过DataSet的GetXml()方法得到的XML文档格式如下：

< NewDataSet >
  < DepNames >
     < DepCode > 101010005 </ DepCode >
     < DepName > 一个部门 </ DepName >
  </ DepNames >
  < DepNames >
     < DepCode > 101010006 </ DepCode >
     < DepName > 又一个部门 </ DepName >
  </ DepNames >
  < FeeDetail >
     < DepCode > 101010005 </ DepCode >
     < FeeCode > 66010102 </ FeeCode >
     < FeeName > 一项费用 </ FeeName >
< ExamineAddUpMoney > 0.0000 </ ExamineAddUpMoney >
  </ FeeDetail >
     < FeeDetail >
     < DepCode > 101010006 </ DepCode >
     < FeeCode > 66010103 </ FeeCode >
     < FeeName > 另一项费用 </ FeeName >
   < ExamineAddUpMoney > 0.0000 </ ExamineAddUpMoney >
  </ FeeDetail >
</ NewDataSet >

可见，得到的DataSet数据中，<NewDataSet>节点是DataSet的名称；<DepNames>节点和<FeeDetail>节点分别代表DepNames结果集的行数据和FeeDetail结果集的行数据，它们的子节点则代表每行的各列数据值。

XSL各种语法很丰富，不过通常我们只需要利用其中的一小部分就能达到我们的目的。下面我们来分析一下模板背后的XML文档格式。

Excel文件另存的XML文档，似乎很长，内容很复杂，但是，借助VS2005以及IE对XML文档中节点良好的“折叠”特性，我们可以很容易的分析出文档中的一些主要元素，另外MS也提供了MS Office 2003的XML Schema参考，对我们也有很大帮助。

我们主要需要关注XML文档中的<Worksheet ss:Name="WorksheetName">节点及其子节点，很明显，它代表Excel文档中的各个Worksheet，对于我们的文档，&lt;Worksheet>节点中包含了一个<table>节点，样例如下：

 

< Table  ss:ExpandedColumnCount ="27"  ss:ExpandedRowCount ="6"  x:FullColumns ="1"
 x:FullRows ="1"  ss:DefaultColumnWidth ="54"  ss:DefaultRowHeight ="13.5" >

 

其中，我们主要需要关注2个属性：

l ExpandedColumnCount：代表Excel文档中的列数

l ExpandedRowCount：代表Excel文档中的行数

以上2个属性如果比文档中实际数据的行数和列数小，通常就会导致文档错误而无法用Excel打开，因此我们生成的文档，需要根据实际数据行数来更新ExpandedRowCount属性的值，或者把他们设置为一个非常大的数。

<Table>节点的子节点包括若干<Column>节点和<Row>节点，这很清楚代表表格中的行信息和列信息，对于我们这次的任务，只需要关心<Row>节点，一个简单的<Row>节点类似以下格式：

 

< Row >
          < Cell  ss:StyleID ="s123" >
                    < Data  ss:Type ="String" > 这是单元格的内容 </ Data >
          </ Cell >
</ Row >

 

其中，<Cell>节点的ss:StyleID属性代表单元格的样式ID，引用Styles节点中的样式节点ID，<Data>节点的ss:Type属性代表单元格值的类型，可以是String(字符串)、Number(数值)、DateTime(日期)等类型。

我们找到样例中示例数据所在的<Row>节点，结合DataSet的结构并利用XSL循环语法完成2层循环操作：

 

< xsl:for-each  select ="//DepNames" >
          < xsl:variable  name ="DepCode"  select ="DepCode"   />
          < xsl:for-each  select ="//FeeDetail[DepCode=$DepCode]" >
                    < xsl:sort  order ="ascending"  select ="FeeCode"   />  
                    < Row  ss:AutoFitHeight ="0"  ss:Height ="11.25"  ss:StyleID ="s80" > <! —费用明细信息-- >
          </ xsl:for-each >
          < Row  ss:AutoFitHeight ="0"  ss:Height ="11.25"  ss:StyleID ="s80" ></ xsl:for-each > <! —费用小计-- >
</ xsl:for-each >
< Row  ss:AutoFitHeight ="0" > <! —费用合计-- >

 

以下对上面的语句逐一解释：

<xsl:for-each select="//DepNames">：

外层循环，选取DataSet中的所有DepNames节点进行循环，也就是说对每一个部门进行循环

<xsl:variable name="DepCode" select="DepCode" />：

声明一个变量，这个变量存放第一层循环中的当前行的部门编码，用以控制第二层循环只获取当前部门所属的费用

<xsl:for-each select="//FeeDetail[DepCode=$DepCode]">：

内层循环，选取DataSet中部门编码为第一层循环当前部门编码的费用明细项进行循环，注意表达这种意思的XPath语法，其中$DepCode即为我们上面所定义的那个变量

<xsl:sort order="ascending" select="FeeCode" />：

对选取的费用明细按照费用编码排序

<Row ss:AutoFitHeight="0" ss:Height="11.25" ss:StyleID="s80"><!—费用明细信息à：

这是费用明细行，下面会说明<Row>的子节点（单元格）的填充。

</xsl:for-each>：

内层循环结束

<Row ss:AutoFitHeight="0" ss:Height="11.25" ss:StyleID="s80"></xsl:for-each><!—费用小计à：

费用小计行，后面会说明费用小计的单元格如何填充

</xsl:for-each>：

外层循环结束

<Row ss:AutoFitHeight="0"><!—费用合计à：

最后的费用合计

 

通过上面的2层循环框架，我们可以得到需要的结果格式。下面，我们简单说明一下费用明细和费用小计的<Row>节点的填充。

<Row>节点的填充比较简单，利用XSL的value-of语法取代模板中的示例值将DataSet中的内容直接填充上去就可以了：

 

< Cell  ss:StyleID ="s81" >
  < Data  ss:Type ="Number" >
  < xsl:value-of  select ="BudgetYear"   />   <! —填充了预算年度-- >
  </ Data >
</ Cell >

 

其中select属性的内容是相对于循环所在的当前节点的相对路径。

这里主要需要说明的是对Excel中公式的处理。由于我们的Excel行数不定，因此小计和合计不能简单地写一个固定的公式就能搞定，有一定动态性、相对性。

好在Excel的公式描述形式就是动态的（当然，利用绝对单元格引用的公式除外）。我们来看一个简单的公式：

<Cell ss:StyleID="s82" ss:Formula="=Sum(RC[-2]:RC[-1])"></Cell>

公式中，单元格是以R[X]C[X]的形式标识的，其中，R和C代表行(Row)和列(Column)，而中括号中的数字则代表相对于当前单元格的目标单元格的位置，

比如RC[-2]代表这样一个单元格：与当前单元格在同一行，列在当前单元格所在列的前2列的那个单元格。

我们首先应该考虑如何描述费用小计的公式，然后把我们的描述转化为具体的公式。

我们可以这样描述我们的费用小计共识：费用小计=对 当前单元格的上一单元格 与 当前单元格的上费用明细个数个单元格 的求和。

其中费用明细个数，是指费用小计所属的部门下的费用明细个数。

然后我们可以将它转化为以下XSL描述：

 

< Cell  ss:StyleID ="s84" >
  < xsl:attribute  name ="ss:Formula" >
 =SUM(R[-1]C:R[- 
  < xsl:value-of  select ="count(//FeeDetail[DepCode=$DepCode])"   />  
 ]C) 
  </ xsl:attribute >
  < Data  ss:Type ="Number" >
  < xsl:value-of  select ="sum(//FeeDetail[DepCode=$DepCode]/ApAddUpMoney)"   />  
  </ Data >
  </ Cell >

 

注意XSL中为一个节点追加属性的做法，通过<xsl: attribute>来实现

 对于合计，公式比较难设，本来没想到什么很好的办法，不过我一个同事提到了，可以将所有数据行（包括费用明细和费用小计行）求和然后除以2，就差不多能达到效果，这是个不错的注意，真是数学高手，头脑够灵活，佩服，佩服……具体我就不写了。

 

<?xml version="1.0" encoding="gb2312"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output encoding="utf-16"/>
 <xsl:template match="/">
  <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>林立</Author>
    <LastAuthor>林立</LastAuthor>
    <Created>2007-01-16T07:17:25Z</Created>
    <LastSaved>2007-01-16T09:20:39Z</LastSaved>
    <Company>Microsoft</Company>
    <Version>12.00</Version>
   </DocumentProperties>
   <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <RelyOnVML/>
    <AllowPNG/>
    <TargetScreenSize>1024x768</TargetScreenSize>
   </OfficeDocumentSettings>
   <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>4590</WindowHeight>
    <WindowWidth>15420</WindowWidth>
    <WindowTopX>-15</WindowTopX>
    <WindowTopY>4545</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="11" ss:Color="#000000"/>
     <Interior/>
     <NumberFormat/>
     <Protection/>
    </Style>
    <Style ss:ID="m36707616">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707636">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707392">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707412">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707432">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707452">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707472">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="m36707492">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s62">
     <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
     <Interior/>
    </Style>
    <Style ss:ID="s63">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s74">
     <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
     <Borders>
      <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s75">
     <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
     <Borders>
      <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
      ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s76">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
      ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s77">
     <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"/>
     </Borders>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s78">
     <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"/>
     </Borders>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"
      ss:Bold="1"/>
     <Interior ss:Color="#D8D8D8" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s79">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000"/>
     <Interior ss:Color="#8DB4E3" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s80">
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s81">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s82">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
     <NumberFormat ss:Format="#,##0.00_ "/>
    </Style>
    <Style ss:ID="s83">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
     <Interior ss:Color="#D7E4BC" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s84">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
     <Interior ss:Color="#D7E4BC" ss:Pattern="Solid"/>
     <NumberFormat ss:Format="#,##0.00_ "/>
    </Style>
    <Style ss:ID="s85">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
     <Interior ss:Color="#92D050" ss:Pattern="Solid"/>
    </Style>
    <Style ss:ID="s86">
     <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>
     <Font ss:FontName="宋体" x:CharSet="134" ss:Size="9" ss:Color="#000000"/>
     <Interior ss:Color="#92D050" ss:Pattern="Solid"/>
     <NumberFormat ss:Format="#,##0.00_ "/>
    </Style>
   </Styles>
   <Worksheet ss:Name="月度预算上限">
    <Table ss:ExpandedColumnCount="28" x:FullColumns="1"
     x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
     <xsl:attribute name="ss:ExpandedRowCount">
      <xsl:value-of select="count(//DepNames) + count(//FeeDetail) + 1 + 3"/>
     </xsl:attribute>
     <Column ss:AutoFitWidth="0" ss:Width="30" ss:Span="1"/>
     <Column ss:AutoFitWidth="0" ss:Index="3" ss:Width="51"/>
     <Column ss:AutoFitWidth="0" ss:Width="65.25"/>
     <Column ss:AutoFitWidth="0" ss:Width="58.5"/>
     <Column ss:AutoFitWidth="0" ss:Width="30"/>
     <Column ss:AutoFitWidth="0" ss:Width="90.75"/>
     <Column ss:AutoFitWidth="0" ss:Width="75.75" ss:Span="1"/>
     <Column ss:AutoFitWidth="0" ss:Index="10"  ss:Width="66.75" ss:Span="1"/>
     <Column ss:AutoFitWidth="0" ss:Index="12" ss:Width="90"/>
     <Column ss:AutoFitWidth="0" ss:Width="69.75"/>
     <Column ss:AutoFitWidth="0" ss:Width="60"/>
     <Column ss:AutoFitWidth="0" ss:Width="67.5"/>
     <Column ss:AutoFitWidth="0" ss:Width="66"/>
     <Column ss:AutoFitWidth="0" ss:Index="27" ss:Hidden="1" />
     <Column ss:AutoFitWidth="0" ss:Index="28" ss:Hidden="1" />
     <Row ss:AutoFitHeight="0" ss:StyleID="s62">
      <Cell ss:MergeDown="1" ss:StyleID="m36707392">
       <Data ss:Type="String">年度</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707412">
       <Data ss:Type="String">月份</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707432">
       <Data ss:Type="String">费用编码</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707452">
       <Data ss:Type="String">费用明细</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707472">
       <Data ss:Type="String">费用类别</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707492">
       <Data ss:Type="String">属性</Data>
      </Cell>
      <Cell ss:MergeDown="1" ss:StyleID="m36707616">
       <Data ss:Type="String">责任部门</Data>
      </Cell>
      <Cell ss:MergeAcross="2" ss:StyleID="m36707636">
       <Data ss:Type="String">前期费用预算分析</Data>
      </Cell>
      <Cell ss:StyleID="s74">
       <Data ss:Type="String">年度预算</Data>
      </Cell>
      <Cell ss:StyleID="s75">
       <Data ss:Type="String">上月止累计年度</Data>
      </Cell>
      <Cell ss:StyleID="s75">
       <Data ss:Type="String">上月止累计</Data>
      </Cell>
      <Cell ss:StyleID="s75">
       <Data ss:Type="String">上月止累计</Data>
      </Cell>
      <Cell ss:StyleID="s75">
       <Data ss:Type="String">本月费用</Data>
      </Cell>
      <Cell ss:StyleID="s75">
       <Data ss:Type="String">审核确认</Data>
      </Cell>
      <Cell ss:Index="27">
       <Data ss:Type="String">DepartmentLineID</Data>
      </Cell>
      <Cell ss:Index="28">
       <Data ss:Type="String">DepCode</Data>
      </Cell>
     </Row>
     <Row ss:AutoFitHeight="0" ss:StyleID="s62">
      <Cell ss:Index="8" ss:StyleID="s76">
       <Data ss:Type="String">累计预算(T-1)</Data>
      </Cell>
      <Cell ss:StyleID="s76">
       <Data ss:Type="String">累计实际(T-1)</Data>
      </Cell>
      <Cell ss:StyleID="s63">
       <Data ss:Type="String">节</Data>
      </Cell>
      <Cell ss:StyleID="s77">
       <Data ss:Type="String">分解到月</Data>
      </Cell>
      <Cell ss:StyleID="s78">
       <Data ss:Type="String">预算分解到月金额</Data>
      </Cell>
      <Cell ss:StyleID="s78">
       <Data ss:Type="String">实际发生金额</Data>
      </Cell>
      <Cell ss:StyleID="s78">
       <Data ss:Type="String">结余金额</Data>
      </Cell>
      <Cell ss:StyleID="s78">
       <Data ss:Type="String">预算金额</Data>
      </Cell>
      <Cell ss:StyleID="s78">
       <Data ss:Type="String">预算金额</Data>
      </Cell>
     </Row>
     <Row ss:AutoFitHeight="0">
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79"/>
      <Cell ss:StyleID="s79">
       <Data ss:Type="Number">1</Data>
      </Cell>
      <Cell ss:StyleID="s79">
       <Data ss:Type="Number">2</Data>
      </Cell>
      <Cell ss:StyleID="s79">
       <Data ss:Type="Number">3</Data>
      </Cell>
      <Cell ss:StyleID="s79">
       <Data ss:Type="String">4=3-2</Data>
      </Cell>
      <Cell ss:StyleID="s79">
       <Data ss:Type="String">5=1+4</Data>
      </Cell>
      <Cell ss:StyleID="s79"/>
     </Row>
     <xsl:for-each select="//DepNames">
      <xsl:variable name="DepCode" select="DepCode" />
      <xsl:for-each select="//FeeDetail[DepCode=$DepCode]">
       <xsl:sort order="ascending" select="FeeCode"/>
       <Row ss:AutoFitHeight="0" ss:Height="11.25" ss:StyleID="s80">
        <Cell ss:StyleID="s81">
         <Data ss:Type="Number">
          <xsl:value-of select="BudgetYear"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="Number">
          <xsl:value-of select="BudgetMonth"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="Number">
          <xsl:value-of select="FeeCode"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="String">
          <xsl:value-of select="FeeName"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="String">
          <xsl:value-of select="FeeClassName"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="String">
          <xsl:value-of select="FeeType"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s81">
         <Data ss:Type="String">
          <xsl:value-of select="DepName"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="ExamineAddUpMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="ApAddUpMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82" ss:Formula="=RC[-2]-RC[-1]">
         <Data ss:Type="Number">
          <xsl:value-of select="AddUpMoneyLeft"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="YearToMonthBudgetMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="BudgetAddUpMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="FactAddUpMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="LeftAddUpMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="CurMonthBudgetMoney"/>
         </Data>
        </Cell>
        <Cell ss:StyleID="s82">
         <Data ss:Type="Number">
          <xsl:value-of select="FinanceConfirmMoney"/>
         </Data>
        </Cell>
        <Cell ss:Index="27">
         <Data ss:Type="String">
          <xsl:value-of select="DepartmentLineID"/>
         </Data>
        </Cell>
        <Cell ss:Index="28">
         <Data ss:Type="String">
          <xsl:value-of select="DepCode"/>
         </Data>
        </Cell>
       </Row>
      </xsl:for-each>
      <Row ss:AutoFitHeight="0" ss:Height="11.25" ss:StyleID="s80">
       <Cell ss:StyleID="s83">
        <Data ss:Type="String">小计</Data>
       </Cell>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s83"/>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/ExamineAddUpMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/ApAddUpMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84" ss:Formula="=RC[-2]-RC[-1]">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/AddUpMoneyLeft)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/YearToMonthBudgetMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/BudgetAddUpMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/FactAddUpMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/LeftAddUpMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/CurMonthBudgetMoney)"/>
        </Data>
       </Cell>
       <Cell ss:StyleID="s84">
        <xsl:attribute name="ss:Formula">=SUM(R[-1]C:R[-<xsl:value-of select="count(//FeeDetail[DepCode=$DepCode])"/>]C)</xsl:attribute>
        <Data ss:Type="Number">
         <xsl:value-of select="sum(//FeeDetail[DepCode=$DepCode]/FinanceConfirmMoney)"/>
        </Data>
       </Cell>
      </Row>
     </xsl:for-each>
     <Row ss:AutoFitHeight="0">
      <Cell ss:StyleID="s85">
       <Data ss:Type="String">合计</Data>
      </Cell>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s85"/>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/ExamineAddUpMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/ApAddUpMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86"  ss:Formula="=RC[-2]-RC[-1]">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/AddUpMoneyLeft)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/YearToMonthBudgetMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/BudgetAddUpMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/FactAddUpMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/LeftAddUpMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/CurMonthBudgetMoney)"/>
       </Data>
      </Cell>
      <Cell ss:StyleID="s86">
       <Data ss:Type="Number">
        <xsl:value-of select="sum(//FeeDetail/FinanceConfirmMoney)"/>
       </Data>
      </Cell>
     </Row>
    </Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
     <PageSetup>
      <Header x:Margin="0.3"/>
      <Footer x:Margin="0.3"/>
      <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
     </PageSetup>
     <Unsynced/>
     <Selected/>
     <FreezePanes/>
     <FrozenNoSplit/>
     <SplitHorizontal>3</SplitHorizontal>
     <TopRowBottomPane>3</TopRowBottomPane>
     <SplitVertical>7</SplitVertical>
     <LeftColumnRightPane>7</LeftColumnRightPane>
     <ActivePane>0</ActivePane>
     <Panes>
      <Pane>
       <Number>3</Number>
      </Pane>
      <Pane>
       <Number>1</Number>
      </Pane>
      <Pane>
       <Number>2</Number>
      </Pane>
      <Pane>
       <Number>0</Number>
      </Pane>
     </Panes>
     <ProtectObjects>False</ProtectObjects>
     <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
   </Worksheet>
  </Workbook>
 </xsl:template>
</xsl:stylesheet>