I have a very large spreadsheet that I'm trying to export to XML. Any row with repeating cells are skipped over during the transformation. So if a row has 1 2 2 2 3 4 5 only the first 2 will be copied to the XML file. I've found out this is because of how the spreadsheet stores repeating rows. I need my XSLT file to handle repeating cells in a row instead of skipping them.
I'm using LibreOffice Calc to do the transformations.
Here's the part of my sample .xslt file that does the heavy lifting:
<xsl:template match="office:spreadsheet/table:table">
<xsl:for-each select="table:table-row[position() > 0]">
<xsl:text>
</xsl:text>
<xsl:text>
</xsl:text>
<Column>
<xsl:text>
</xsl:text>
<p1><xsl:value-of select="table:table-cell[1]/text:p" /></p1>
<p1><xsl:value-of select="table:table-cell[2]/text:p" /></p1>
<p2><xsl:value-of select="table:table-cell[3]/text:p" /></p2>
<p3><xsl:value-of select="table:table-cell[4]/text:p" /></p3>
<p4><xsl:value-of select="table:table-cell[5]/text:p" /></p4>
<p5><xsl:value-of select="table:table-cell[6]/text:p" /></p5>
<p6><xsl:value-of select="table:table-cell[7]/text:p" /></p6>
<xsl:text>
</xsl:text>
</Column>
</xsl:for-each>
</xsl:template>
Edit: Here's what my sample spreadsheet looks like in LibreOffice:
1 2 3 4 5
6 7 8 9 10
12 12 13 14 15
15 15 15 15 15
17 17 18 18 17
And the resulting XML file:
<?xml version="1.0"?>
<root>
<Column>
<p1>1</p1><p1>2</p1><p2>3</p2><p3>4</p3><p4>5</p4><p5/><p6/>
</Column>
<Column>
<p1>6</p1><p1>7</p1><p2>8</p2><p3>9</p3><p4>10</p4><p5/><p6/>
</Column>
<Column>
<p1>12</p1><p1>13</p1><p2>14</p2><p3>15</p3><p4/><p5/><p6/>
</Column>
<Column>
<p1>15</p1><p1/><p2/><p3/><p4/><p5/><p6/>
</Column>
<Column>
<p1>17</p1><p1>18</p1><p2>17</p2><p3/><p4/><p5/><p6/>
</Column></root>
In Open Document
*.ods
files cells which are identically are not stored redundant. Instead the first cell will have a attributetable:number-columns-repeated
which tells the count of repeating:<table:table-cell table:number-columns-repeated="2" ...>
.So
XSLT
which transforms this toXML
which needs all elements must generating elements repeatedly. Since we need usingXSLT 1.0
, this is not as simple as it should. We need a template which calls itself recursively.Also your numbered element names
p1
,p2
, ... does not making things easier. To achieve this and having the numbering according the resulting output elements instead of the input elements we must collecting the output elements in a variable first and then numbering that collection.And your elements names are confusing. What you are calling
Column
are rows and thep
elements are cells. So I have renamed them accordingly.So having the following sheet:
and using export XSLT of:
results in: