使用 XSLT 为 Excel 转换现有的 XML [英] Transform existing XML for Excel with XSLT

问题描述

我需要更改现有的 xml 以便从中创建一个 excel 表.

I need to change existing xml in order to make an excel table from it.

我需要修改的xml:

<rows>
  <row>
    <cell image="blank.gif">Row1</cell>
    <cell>1</cell>
    <cell>2</cell>
    <cell>3</cell>
    <cell>4</cell>
    <cell>5</cell>
    <cell>6</cell>
    <cell>7</cell>
    <row>
      <cell image="blank.gif">Row1_1</cell>
      <cell>8</cell>
      <cell>9</cell>
      <cell>10</cell>
      <cell>11</cell>
      <cell>12</cell>
      <cell>13</cell>
      <cell>14</cell>
    </row>
    <row>
      <cell image="blank.gif">Row1_2</cell>
      <cell>15</cell>
      <cell>16</cell>
      <cell>17</cell>
      <cell>18</cell>
      <cell>19</cell>
      <cell>20</cell>
      <cell>21</cell>
    </row>
  </row>
  <row>
    <cell image="blank.gif">Row2</cell>
    <cell>22</cell>
    <cell>23</cell>
    <cell>24</cell>
    <cell>25</cell>
    <cell>26</cell>
    <cell>27</cell>
    <cell>28</cell>
  </row>
</rows>

我当前的 XSLT:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:output method="xml" indent="yes"/>



  <xsl:template match="rows">
    <xsl:element name="sheetData">
      <xsl:apply-templates select="row"></xsl:apply-templates>
    </xsl:element>
  </xsl:template>



  <xsl:template match="row">
    <xsl:variable name="rowID">
      <xsl:number value="position()" format="1"/>
    </xsl:variable>
    <xsl:element name="row">
      <xsl:attribute name="r">
        <xsl:value-of select="$rowID"/>
      </xsl:attribute>



      <xsl:for-each select="*">
        <xsl:element name="c">
          <xsl:variable name="colID">
            <xsl:number value="position()" format="A"/>
          </xsl:variable>
          <xsl:attribute name="r">
            <xsl:value-of  select="concat(string($colID),string($rowID))"/>
          </xsl:attribute>
          <xsl:attribute name="t">
            <xsl:text>inlineStr</xsl:text>
          </xsl:attribute>
          <xsl:element name="is">
            <xsl:element name="t">
              <xsl:value-of select="."/>
            </xsl:element>
          </xsl:element>
        </xsl:element>
      </xsl:for-each>

    </xsl:element>
  </xsl:template>

</xsl:stylesheet>

我的转换输出:

<sheetData>
 <row r="1">
  <c r="A1" t="inlineStr">
      <is>
      <t>Row1</t> 
      </is>
  </c>
  <c r="B1" t="inlineStr">
      <is>
      <t>1</t> 
      </is>
  </c>
  <c r="C1" t="inlineStr">
      <is>
      <t>2</t> 
      </is>
  </c>
  <c r="D1" t="inlineStr">
      <is>
      <t>3</t> 
      </is>
  </c>
  <c r="E1" t="inlineStr">
      <is>
      <t>4</t> 
      </is>
  </c>
  <c r="F1" t="inlineStr">
      <is>
      <t>5</t> 
      </is>
  </c>
  <c r="G1" t="inlineStr">
      <is>
      <t>6</t> 
      </is>
  </c>
  <c r="H1" t="inlineStr">
      <is>
      <t>7</t> 
      </is>
  </c>
  <c r="I1" t="inlineStr">
      <is>
      <t>Row1_1891011121314</t> 
      </is>
  </c>
  <c r="J1" t="inlineStr">
      <is>
      <t>Row1_215161718192021</t> 
      </is>
  </c>
  </row>
  <row r="2">
  <c r="A2" t="inlineStr">
      <is>
      <t>Row2</t> 
      </is>
  </c>
  <c r="B2" t="inlineStr">
      <is>
      <t>22</t> 
      </is>
  </c>
  <c r="C2" t="inlineStr">
      <is>
      <t>23</t> 
      </is>
  </c>
  <c r="D2" t="inlineStr">
      <is>
      <t>24</t> 
      </is>
  </c>
  <c r="E2" t="inlineStr">
      <is>
      <t>25</t> 
      </is>
  </c>
  <c r="F2" t="inlineStr">
      <is>
      <t>26</t> 
      </is>
  </c>
  <c r="G2" t="inlineStr">
      <is>
      <t>27</t> 
      </is>
  </c>
  <c r="H2" t="inlineStr">
      <is>
      <t>28</t> 
      </is>
  </c>
 </row>
</sheetData>

以及生成的excel文档的截图:

And a screenshot from the generated excel document:

所以我的问题是嵌套行，我需要像 Row1 和 Row2 一样显示它们

So my problem is the nested rows i need to show them exactly like Row1 and Row2

推荐答案

我认为您的意思是您希望输入 XML 中的每一行在输出中都有一个单独的行，即使行元素是嵌套的.

I think what you are saying is that you want a separate row in the output for each row in the input XML, even though the row elements are nested.

从 then 开始，你需要替换这一行

To start with then, you need to replace this line

<xsl:for-each select="*">

有了这个...

<xsl:for-each select="cell">

这将确保当前行中仅输出 cell 元素，而不会处理嵌套的 row 元素.

This will ensure only the cell elements are output in the current row, and the nested row elements won't be processed.

然后，在仅使用 cell 元素创建 row 元素后，您可以查找嵌套的 row 元素以输出这些

Then, after you have created the row element with just the cell elements, you can look for the nested row elements to output these

<row>
   <!-- Process Cells -->
</row>
<xsl:apply-templates select="row"/>

注意没有必要使用xsl:element来输出静态命名元素，直接写出元素即可.您还可以通过使用属性值模板创建属性来整理您的代码，而不是使用 xsl:attribute

Do note there is no real need to use xsl:element to output static named elements, just write out the element directly. Your code can also be tidied up by making use of Attribute Value Templates to create attributes, rather than using xsl:attribute

尝试以下 XSLT:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes"/>

   <xsl:template match="rows">
      <sheetData>
         <xsl:apply-templates select="row"/>
      </sheetData>
   </xsl:template>

   <xsl:template match="row">
      <xsl:variable name="rowID">
         <xsl:number level="any" format="1"/>
      </xsl:variable>
      <row r="{$rowID}">
         <xsl:for-each select="cell">
            <xsl:variable name="colID">
               <xsl:number value="position()" format="A"/>
            </xsl:variable>
            <c t="inlineStr" r="{string($colID)}{string($rowID)}">
               <is>
                  <t>
                     <xsl:value-of select="."/>
                  </t>
               </is>
            </c>
         </xsl:for-each>
      </row>
      <xsl:apply-templates select="row"/>
   </xsl:template>
</xsl:stylesheet>

应用于您的 XML 时，输出如下

When applied to your XML, the following is output

<sheetData>
   <row r="1">
      <c t="inlineStr" r="A1">
         <is>
            <t>Row1</t>
         </is>
      </c>
      <c t="inlineStr" r="B1">
         <is>
            <t>1</t>
         </is>
      </c>
      <c t="inlineStr" r="C1">
         <is>
            <t>2</t>
         </is>
      </c>
      <c t="inlineStr" r="D1">
         <is>
            <t>3</t>
         </is>
      </c>
      <c t="inlineStr" r="E1">
         <is>
            <t>4</t>
         </is>
      </c>
      <c t="inlineStr" r="F1">
         <is>
            <t>5</t>
         </is>
      </c>
      <c t="inlineStr" r="G1">
         <is>
            <t>6</t>
         </is>
      </c>
      <c t="inlineStr" r="H1">
         <is>
            <t>7</t>
         </is>
      </c>
   </row>
   <row r="2">
      <c t="inlineStr" r="A2">
         <is>
            <t>Row1_1</t>
         </is>
      </c>
      <c t="inlineStr" r="B2">
         <is>
            <t>8</t>
         </is>
      </c>
      <c t="inlineStr" r="C2">
         <is>
            <t>9</t>
         </is>
      </c>
      <c t="inlineStr" r="D2">
         <is>
            <t>10</t>
         </is>
      </c>
      <c t="inlineStr" r="E2">
         <is>
            <t>11</t>
         </is>
      </c>
      <c t="inlineStr" r="F2">
         <is>
            <t>12</t>
         </is>
      </c>
      <c t="inlineStr" r="G2">
         <is>
            <t>13</t>
         </is>
      </c>
      <c t="inlineStr" r="H2">
         <is>
            <t>14</t>
         </is>
      </c>
   </row>
   <row r="3">
      <c t="inlineStr" r="A3">
         <is>
            <t>Row1_2</t>
         </is>
      </c>
      <c t="inlineStr" r="B3">
         <is>
            <t>15</t>
         </is>
      </c>
      <c t="inlineStr" r="C3">
         <is>
            <t>16</t>
         </is>
      </c>
      <c t="inlineStr" r="D3">
         <is>
            <t>17</t>
         </is>
      </c>
      <c t="inlineStr" r="E3">
         <is>
            <t>18</t>
         </is>
      </c>
      <c t="inlineStr" r="F3">
         <is>
            <t>19</t>
         </is>
      </c>
      <c t="inlineStr" r="G3">
         <is>
            <t>20</t>
         </is>
      </c>
      <c t="inlineStr" r="H3">
         <is>
            <t>21</t>
         </is>
      </c>
   </row>
   <row r="4">
      <c t="inlineStr" r="A4">
         <is>
            <t>Row2</t>
         </is>
      </c>
      <c t="inlineStr" r="B4">
         <is>
            <t>22</t>
         </is>
      </c>
      <c t="inlineStr" r="C4">
         <is>
            <t>23</t>
         </is>
      </c>
      <c t="inlineStr" r="D4">
         <is>
            <t>24</t>
         </is>
      </c>
      <c t="inlineStr" r="E4">
         <is>
            <t>25</t>
         </is>
      </c>
      <c t="inlineStr" r="F4">
         <is>
            <t>26</t>
         </is>
      </c>
      <c t="inlineStr" r="G4">
         <is>
            <t>27</t>
         </is>
      </c>
      <c t="inlineStr" r="H4">
         <is>
            <t>28</t>
         </is>
      </c>
   </row>
</sheetData>

这篇关于使用 XSLT 为 Excel 转换现有的 XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！