Transform existing XML for Excel with XSLT

Question

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

My xml that needs to be changed:

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

My current 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>

My output from the transformations:

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

And a screenshot from the generated excel document:

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

Answer 1

Another thing you could do is to import the XML as is.

1. Switch to XML-map (dev tools, xml Source, [the xml map should open on the right handside])
2. Delete all of them
3. Then you can select with rightlick "add" were to input the data into the Excel sheet

Answer 2

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.

To start with then, you need to replace this line

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

With this...

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

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

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"/>

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

Try the following 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>

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>