2. 技术人生
    3. XML 报表3

    XML 报表3

    技术2022-05-20  72

    【报表】 XSLT 解析普通XML生成表格型XML（三）

    文章分类:综合技术

    3. XSLT 解析XML为表格型XML的实例（具体请参考附件）   Txt代码   附件内容：     1. DepInOutData.xml --- 源数据xml     2. Pattern.xsl  --- xslt文件     3. DepInOutDataResult.xml ---解析结果       附件内容： 1. DepInOutData.xml --- 源数据xml 2. Pattern.xsl --- xslt文件 3. DepInOutDataResult.xml ---解析结果 * 感兴趣的人可以去下个Eclipse Xslt插件：Orangevolt XSLT。它可以帮助你使用指定的xsl解析指定的xml，无须写java代码。   需求：根据下面xml中的各中心/部门/科室的收入支出数据，生成一张电子表格，要求同一个中心或者同一个部门的数据的单元格要能合并 （这个是非常简单的一个需求，我们就从这个简单需求入手，看看需要如何操作达到目的）   提供的源数据XML：参考附件中的DepInOutData.xml   Xml代码   <?xml version="1.0" encoding="UTF-8"?>    <Report title="测试XSLT" >      <Sections>          <Center name="销售中心">              <Department name="销售1部">                      <Section>                              <SectionName value="销售1科"/>                              <Input value="100.0"/>                              <Output value="10.0"/>                               </Section>                      <Section>                              <SectionName value="销售2科"/>                              <Input value="101.0"/>                              <Output value="12.0"/>                               </Section>                      <Section>                              <SectionName value="销售3科"/>                              <Input value="102.0"/>                              <Output value="11.0"/>                               </Section>              </Department>                               <Department name="销售2部">                      <Section>                              <SectionName value="销售4科"/>                              <Input value="100.0"/>                              <Output value="10.0"/>                               </Section>                      <Section>                              <SectionName value="销售5科"/>                              <Input value="101.0"/>                              <Output value="12.0"/>                               </Section>                      <Section>                              <SectionName value="销售6科"/>                              <Input value="102.0"/>                              <Output value="11.0"/>                               </Section>              </Department>                    </Center>                     <Center name="研发中心">              <Department name="研发1部">                      <Section>                              <SectionName value="研发1科"/>                              <Input value="100.0"/>                              <Output value="10.0"/>                               </Section>                      <Section>                              <SectionName value="研发2科"/>                              <Input value="101.0"/>                              <Output value="12.0"/>                               </Section>                      <Section>                              <SectionName value="研发3科"/>                              <Input value="102.0"/>                              <Output value="11.0"/>                               </Section>              </Department>                               <Department name="研发2部">                      <Section>                              <SectionName value="研发4科"/>                              <Input value="0.0"/>                              <Output value="50.0"/>                               </Section>                      <Section>                              <SectionName value="研发5科"/>                              <Input value="0.0"/>                              <Output value="52.0"/>                               </Section>                      <Section>                              <SectionName value="研发6科"/>                              <Input value="0.0"/>                              <Output value="51.0"/>                               </Section>              </Department>                               <Department name="测试1部">                      <Section>                              <SectionName value="测试1科"/>                              <Input value="0.0"/>                              <Output value="10.0"/>                               </Section>                      <Section>                              <SectionName value="测试2科"/>                              <Input value="0.0"/>                              <Output value="12.0"/>                               </Section>                      <Section>                              <SectionName value="测试3科"/>                              <Input value="0.0"/>                              <Output value="11.0"/>                               </Section>              </Department>                </Center>      </Sections>  </Report>   <?xml version="1.0" encoding="UTF-8"?> <Report title="测试XSLT" > <Sections> <Center name="销售中心"> <Department name="销售1部"> <Section> <SectionName value="销售1科"/> <Input value="100.0"/> <Output value="10.0"/> </Section> <Section> <SectionName value="销售2科"/> <Input value="101.0"/> <Output value="12.0"/> </Section> <Section> <SectionName value="销售3科"/> <Input value="102.0"/> <Output value="11.0"/> </Section> </Department> <Department name="销售2部"> <Section> <SectionName value="销售4科"/> <Input value="100.0"/> <Output value="10.0"/> </Section> <Section> <SectionName value="销售5科"/> <Input value="101.0"/> <Output value="12.0"/> </Section> <Section> <SectionName value="销售6科"/> <Input value="102.0"/> <Output value="11.0"/> </Section> </Department> </Center> <Center name="研发中心"> <Department name="研发1部"> <Section> <SectionName value="研发1科"/> <Input value="100.0"/> <Output value="10.0"/> </Section> <Section> <SectionName value="研发2科"/> <Input value="101.0"/> <Output value="12.0"/> </Section> <Section> <SectionName value="研发3科"/> <Input value="102.0"/> <Output value="11.0"/> </Section> </Department> <Department name="研发2部"> <Section> <SectionName value="研发4科"/> <Input value="0.0"/> <Output value="50.0"/> </Section> <Section> <SectionName value="研发5科"/> <Input value="0.0"/> <Output value="52.0"/> </Section> <Section> <SectionName value="研发6科"/> <Input value="0.0"/> <Output value="51.0"/> </Section> </Department> <Department name="测试1部"> <Section> <SectionName value="测试1科"/> <Input value="0.0"/> <Output value="10.0"/> </Section> <Section> <SectionName value="测试2科"/> <Input value="0.0"/> <Output value="12.0"/> </Section> <Section> <SectionName value="测试3科"/> <Input value="0.0"/> <Output value="11.0"/> </Section> </Department> </Center> </Sections> </Report> 下面我就直接给出关键的XSLT代码（具体请参考附件中的Pattern.xsl）： Xslt代码   <xsl:for-each select="Report/Sections">                      <!-- Create worksheet dynamically -->                      <xsl:element name="Worksheet">                          <xsl:attribute name="ss:Name">部门收入支出费用表</xsl:attribute>                          <xsl:element name="Table">                          <!-- ExpandedColumnCount 是表格的列数，>实际的数据的列数-->                              <xsl:attribute name="ss:ExpandedColumnCount">6</xsl:attribute>                              <!-- ExpandedRowCount 是表格的行数， >实际数据行数即可-->                              <xsl:attribute name="ss:ExpandedRowCount">                                  <xsl:value-of select="count(.//Section)+10"/>                              </xsl:attribute>                              <xsl:attribute name="x:FullColumns">1</xsl:attribute>                              <xsl:attribute name="x:FullRows">1</xsl:attribute>                              <xsl:attribute name="ss:StyleID">s24</xsl:attribute>                              <xsl:attribute name="ss:DefaultColumnWidth">90</xsl:attribute>                              <xsl:attribute name="ss:DefaultRowHeight">18</xsl:attribute>                              <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>                              <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>                              <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>                              <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>                              <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>                              <!-- 表格的Title-->                              <Row ss:AutoFitHeight="1" ss:Height="18">                                  <Cell ss:MergeAcross="4" ss:StyleID="s121">                                      <Data ss:Type="String">                                          <xsl:value-of select="/Report/@title"/>                                      </Data>                                  </Cell>                                                              </Row>                                                       <Row ss:Height="29.25">                                  <Cell ss:StyleID="s28">                                      <Data ss:Type="String">                                          <xsl:value-of select="'总部'"/>                                      </Data>                                  </Cell>                                  <Cell ss:StyleID="s28">                                      <Data ss:Type="String">                                          <xsl:value-of select="'部门'"/>                                      </Data>                                  </Cell>                                  <Cell ss:StyleID="s28">                                      <Data ss:Type="String">                                          <xsl:value-of select="'科室'"/>                                      </Data>                                  </Cell>                                  <Cell ss:StyleID="s29">                                      <Data ss:Type="String">                                          <xsl:value-of select="'收入（万元）'"/>                                      </Data>                                  </Cell>                                  <Cell ss:StyleID="s29">                                      <Data ss:Type="String">                                          <xsl:value-of select="'支出（万元）'"/>                                      </Data>                                  </Cell>                                                                                                          </Row>                              <xsl:if test="count(.//Section) > 0">                                  <xsl:call-template name="Sections"/>                                                                 </xsl:if>                                                    </xsl:element>                                                 </xsl:element>                  </xsl:for-each>             <xsl:for-each select="Report/Sections"> <!-- Create worksheet dynamically --> <xsl:element name="Worksheet"> <xsl:attribute name="ss:Name">部门收入支出费用表</xsl:attribute> <xsl:element name="Table"> <!-- ExpandedColumnCount 是表格的列数，>实际的数据的列数--> <xsl:attribute name="ss:ExpandedColumnCount">6</xsl:attribute> <!-- ExpandedRowCount 是表格的行数， >实际数据行数即可--> <xsl:attribute name="ss:ExpandedRowCount"> <xsl:value-of select="count(.//Section)+10"/> </xsl:attribute> <xsl:attribute name="x:FullColumns">1</xsl:attribute> <xsl:attribute name="x:FullRows">1</xsl:attribute> <xsl:attribute name="ss:StyleID">s24</xsl:attribute> <xsl:attribute name="ss:DefaultColumnWidth">90</xsl:attribute> <xsl:attribute name="ss:DefaultRowHeight">18</xsl:attribute> <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/> <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/> <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/> <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/> <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/> <!-- 表格的Title--> <Row ss:AutoFitHeight="1" ss:Height="18"> <Cell ss:MergeAcross="4" ss:StyleID="s121"> <Data ss:Type="String"> <xsl:value-of select="/Report/@title"/> </Data> </Cell> </Row> <Row ss:Height="29.25"> <Cell ss:StyleID="s28"> <Data ss:Type="String"> <xsl:value-of select="'总部'"/> </Data> </Cell> <Cell ss:StyleID="s28"> <Data ss:Type="String"> <xsl:value-of select="'部门'"/> </Data> </Cell> <Cell ss:StyleID="s28"> <Data ss:Type="String"> <xsl:value-of select="'科室'"/> </Data> </Cell> <Cell ss:StyleID="s29"> <Data ss:Type="String"> <xsl:value-of select="'收入（万元）'"/> </Data> </Cell> <Cell ss:StyleID="s29"> <Data ss:Type="String"> <xsl:value-of select="'支出（万元）'"/> </Data> </Cell> </Row> <xsl:if test="count(.//Section) > 0"> <xsl:call-template name="Sections"/> </xsl:if> </xsl:element> </xsl:element> </xsl:for-each>       附件中（放心，俺的开发机杀毒软件具备，一般都杀完毒才放心发布的） 有解析后的结果：DepInOutDataResult.xml，使用MS Office2003以上版本打开可以查看。   首先有必要将上面的几个重要的常量解释下：     1. ss:ExpandedColumnCount是定义表格的列数     测试中我发现这个数值必须 大于实际的列数，例如在本例中，我的实际列数是 5列(中心/部门/科室/收入/支出)，那么赋值的时候使用的是 6， 如果使用的是5的话，解析的结果文件使用Office无法打开，提示格式错误信息，不信的同学可以尝试下：     Xslt代码   <xsl:attribute name="ss:ExpandedColumnCount">6</xsl:attribute>   <xsl:attribute name="ss:ExpandedColumnCount">6</xsl:attribute>     2.  ss:ExpandedRowCount是定义表格的行数，当然肯定要 大于实际的行数，这里我做了个算式： 统计出源数据xml中Section个数+10 Xslt代码   <xsl:attribute name="ss:ExpandedRowCount">                                  <xsl:value-of select="count(.//Section)+10"/>                              </xsl:attribute>   <xsl:attribute name="ss:ExpandedRowCount"> <xsl:value-of select="count(.//Section)+10"/> </xsl:attribute>     3. Column... 标签是定义每一列的长宽等属性，实际列数是5列，那么就定义5个Column标签    Xslt代码   <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>   <Column ss:StyleID="s24" ss:AutoFitWidth="1" ss:Width="90"/>     4. Row... 标签是定义每一行的长宽等属性，实际行数是多少，那么就定义多少个Row标签    Xslt代码   <Row ss:AutoFitHeight="1" ss:Height="18">                                </Row>   <Row ss:AutoFitHeight="1" ss:Height="18"> </Row>     5. Cell... 标签是Row的子标签，用于定义每一行中各单元格的数据及样式等等    Xslt代码   <Cell ss:StyleID="s28">                                      <Data ss:Type="String">                                          <xsl:value-of select="'总部'"/>                                      </Data>                                  </Cell>   <Cell ss:StyleID="s28"> <Data ss:Type="String"> <xsl:value-of select="'总部'"/> </Data> </Cell>    6. xsl:call-template标签是调用函数的， 具体调用哪一个函数则通过属性 name指定。       本例中填充各项数据，合并单元格等操作都是在函数“Sections”及其子函数中完成    Xslt代码   <xsl:call-template name="Sections"/>   <xsl:call-template name="Sections"/>    7. ss:MergeAcross是Cell的属性， 用于单元格的横向合并       下面的例子表示：合并当前单元格右边的4个单元格（即横向区域总共5个单元格被合并）       Xslt代码   <Cell ss:MergeAcross="4" ss:StyleID="s121">   <Cell ss:MergeAcross="4" ss:StyleID="s121">    8. ss:MergeDown是Cell的属性， 用于单元格的纵向合并       下面的例子表示：合并当前单元格下边的4个单元格（即纵向区域总共5个单元格被合并）       Xslt代码   <Cell ss:MergeDown="4" ss:StyleID="s121">   <Cell ss:MergeDown="4" ss:StyleID="s121">   以上即是关键部分，原本想就后面的函数多解释解释，但想来是没大必要的，不仅仅是简单的缘故，相信大家都是牛**的developer,所以解释的太多反倒不美。所以就此打住，刚接触xslt的同学参考例子多思考思考，多写写就可以了     XsltTest.zip (5.4 KB)

     

    转自：http://woniu1983.iteye.com/blog/700749

    专利

    最新回复(0)