Wrong results in Libreoffice Calc of exporting ods file to xml via xsl

[Boboms]

Good afternoon!

I faced strange results converting ods table to xml file via xlst:
<xsl:for-each select=“table:table-row[position() > 1]” />

when selecting current row cells by index:
<xsl:value-of select=“table:table-cell[1]/text:p” />
or:
<xsl:value-of select=“table:table-cell[3]/text:p” />

provides wrong indexing of cells in first two rows.

I have no idea where to dig, strange error, tried different cells format (text, number etc), no result.

Please help to resolve.

Libreoffice Version: 24.2.3.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
CPU threads: 2; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

Thank you in advance.
source.ods (14.5 KB)

PS: how to attach xml nd xsl - forum blocks these files?

PPS:
Used following help:
https://stackoverflow.com/questions/32817081/convert-calcexcel-data-in-xml-in-openoffice

PPPS:
Output of wrong xml is:
https://pastebin.com/dvtXbaJx

XSL file to convert:
https://pastebin.com/piAiZdVY

[fpy]

@Boboms

wrong indexing of cells in first two rows

not similar to Convert Calc(Excel) Data in XML in OpenOffice - Stack Overflow ?

[Boboms]

Seems like very close to mine…

Thank you very much!

Should I write bug report to bug tracker?

[mikekaganski]

No you don’t need to file a bug report in this specific case (I mean, in the case of the linked Stack Overflow issue - I only checked that, and didn’t actually look at your case, sorry).

The Stack Overflow issue is not a “bug in Calc”, but simply a bug in the XSLT. The person who wrote the example XSLT there in the currently most voted answer did a great job, and didn’t over-complicate the example; but they didn’t consider the corner cases. But if you inspect the ODF generated by Calc (just save your data to FODS), you would see that for duplicating values, it would produce a single <table:table-cell table:number-columns-repeated="2">; and so, the naive XSLT with

   <CustData>
    <FirstName><xsl:value-of select="table:table-cell[2]/text:p" /></FirstName> 
    <MiddleName><xsl:value-of select="table:table-cell[3]/text:p" /></MiddleName>
    <LastName><xsl:value-of select="table:table-cell[4]/text:p" /></LastName>   
    <EMail><xsl:value-of select="table:table-cell[5]/text:p" /></EMail>            
    <PhoneNumber><xsl:value-of select="table:table-cell[6]/text:p" /></PhoneNumber>
   </CustData>

would indeed fail on that.

EDIT: aha, yes, your case is identical. You can’t just index the table:table-cell elements, you may have to first process all these elements into an internal array, taking into account the mentioned attribute value. Or there may be other XSLT tricks, I’m not good in the language - but definitely this case needs special handling.

I assume that you inspect the XML of the ODF that causes you troubles, and you indeed saw those table:number-columns-repeated, didn’t you?

EDIT 2: Apache OpenOffice Community Forum - [Solved] Complex XSLT filter - (View topic)

EDIT 3: a workaround, to keep it simple, would be to format your ODS, to have columns e.g. with different colors (like alternating white and grey columns) - that would force the ODF use separate table:table-cell elements, because they would have to use different table:style-name attributes for each.

[mikekaganski]

Try this XSLT:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exsl="http://exslt.org/common" xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" exclude-result-prefixes="office table text exsl">

 <xsl:template name="repeat_row_impl">
  <xsl:param name="i"/>
  <xsl:param name="row"/>

  <xsl:if test="$i &gt; 0">
   <xsl:copy-of select="$row"/>

   <xsl:call-template name="repeat_row_impl">
    <xsl:with-param name="i" select="$i - 1"/>
    <xsl:with-param name="row" select="$row"/>
   </xsl:call-template>
  </xsl:if>
 </xsl:template>

 <xsl:template name="repeat_row">
  <xsl:param name="i"/>
  <xsl:param name="recursion_remainder"/>
  <xsl:param name="row"/>

  <xsl:if test="$i &gt; 0">
   <xsl:if test="$recursion_remainder &lt; 1">
    <xsl:message terminate="yes">Recursion depth exceeded</xsl:message>
   </xsl:if>

   <xsl:variable name="output">
    <xsl:choose>
     <xsl:when test="$i &gt; $recursion_remainder"><xsl:value-of select="$recursion_remainder"/></xsl:when>
     <xsl:otherwise><xsl:value-of select="$i"/></xsl:otherwise>
    </xsl:choose>
   </xsl:variable>

   <xsl:call-template name="repeat_row_impl">
    <xsl:with-param name="i" select="$output"/>
    <xsl:with-param name="row" select="$row"/>
   </xsl:call-template>

   <xsl:call-template name="repeat_row">
    <xsl:with-param name="i" select="$i - $output"/>
    <xsl:with-param name="recursion_remainder" select="$recursion_remainder - 1"/>
    <xsl:with-param name="row" select="$row"/>
   </xsl:call-template>
  </xsl:if>
 </xsl:template>

 <xsl:template name="repeat_cell">
  <xsl:param name="i"/>

  <xsl:if test="$i &gt; 0">
   <xsl:copy-of select="current()"/>
   <xsl:call-template name="repeat_cell">
    <xsl:with-param name="i" select="$i - 1"/>
   </xsl:call-template>
  </xsl:if>
 </xsl:template>

 <xsl:template name="normalize_row">
  <xsl:for-each select="table:table-cell">
   <xsl:call-template name="repeat_cell">
    <xsl:with-param name="i">
     <xsl:choose>
      <xsl:when test="current()[not(@table:number-columns-repeated)]">1</xsl:when>
      <xsl:otherwise><xsl:value-of select="current()/@table:number-columns-repeated"/></xsl:otherwise>
     </xsl:choose>
    </xsl:with-param>
   </xsl:call-template>
  </xsl:for-each>
 </xsl:template>

 <xsl:template match="/">
  <xsl:apply-templates select="/*/office:body" />
 </xsl:template>

 <xsl:template match="office:body">
  <xsl:apply-templates />
 </xsl:template>

 <xsl:template match="office:spreadsheet">
  <xsl:apply-templates />
 </xsl:template>

 <xsl:template match="office:spreadsheet/table:table">
  <memorials>
   <memorial health="true" ord="1">
    <name>Base</name>
    <notes></notes>

    <xsl:for-each select="table:table-row[position() &gt; 1]">
     <xsl:variable name="row">
      <xsl:variable name="normalized_row">
       <xsl:call-template name="normalize_row"/>
      </xsl:variable>
      <records>
       <xsl:variable name="mygender1" select="exsl:node-set($normalized_row)/table:table-cell[1]/text:p"/>
       <xsl:variable name="myord1" select="exsl:node-set($normalized_row)/table:table-cell[2]/text:p"/>
       <xsl:variable name="mystatus1" select="exsl:node-set($normalized_row)/table:table-cell[3]/text:p"/>
       <xsl:variable name="myprefix1" select="exsl:node-set($normalized_row)/table:table-cell[4]/text:p"/>

       <record gender="{$mygender1}" ord="{$myord1}" status="{$mystatus1}" prefix="{$myprefix1}">
        <name><xsl:value-of select="exsl:node-set($normalized_row)/table:table-cell[5]/text:p"/></name>
        <comment><xsl:value-of select="exsl:node-set($normalized_row)/table:table-cell[6]/text:p"/></comment> 
       </record>
      </records>
     </xsl:variable>

     <xsl:if test="string($row)">
      <xsl:call-template name="repeat_row">
       <xsl:with-param name="i">
        <xsl:choose>
         <xsl:when test="current()[not(@table:number-rows-repeated)]">1</xsl:when>
         <xsl:otherwise><xsl:value-of select="current()/@table:number-rows-repeated"/></xsl:otherwise>
        </xsl:choose>
       </xsl:with-param>
       <xsl:with-param name="recursion_remainder" select="1500"/>
       <xsl:with-param name="row" select="$row"/>
      </xsl:call-template>
     </xsl:if>
    </xsl:for-each>

   </memorial>
  </memorials>
 </xsl:template>
</xsl:stylesheet>

It handles both repeating rows and columns. It allows to handle up to 1100000 repeating rows using limited recursion depth.

[Boboms]

Ooo, Mike!!! Extremely thank you very much for your help and super exhaustive answers!

I suppose that it helps many “strangers” to do the best.

Now it’s clear, but, I think, it was not obvious thing - I was confused what to particularly find.

Again, many thanks and wish you good luck!

[fpy]

for reference (from Tip of the Day) :

Extending the Swiss Army knife - an overview about writing of filters for...