[Solved] Complex XSLT filter

Discuss the spreadsheet application

[Solved] Complex XSLT filter

Postby AP Jager » Sat Nov 08, 2008 6:35 pm

Hello,

I am trying to export a spreadsheet to xml based on a specific schema.

I understand I need to create a xslt filter in order to export the spreadsheet to xml and read the topic on XSLT filters (viewtopic.php?f=45&t=3490) and understand the basics.
However this example does not take in account attributes in the imported/exported xml. Only the functions.

Seen my schema is slightly more complicated and my data is quite extensive (413 rows 18 columns including headers) I wondered how I could have my xslt take account for attributes.
Last edited by AP Jager on Fri Nov 14, 2008 4:05 am, edited 1 time in total.
OOo 3.0.X on Ms Windows XP
AP Jager
 
Posts: 4
Joined: Sat Nov 08, 2008 5:32 pm

Re: Complex XSLT filter

Postby hol.sten » Sat Nov 08, 2008 9:05 pm

AP Jager wrote:I wondered how I could have my xslt take account for attributes.

The post you referred to already contained attributes in the XSLT export filter. I only forgot to explain that. Is the usage of
Code: Select all   Expand viewCollapse view
<xsl:attribute name="my-attribute-name"><xsl:value-of select="$someValue"/></xsl:attribute>
in the XSLT export filter what you are looking for?
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: Complex XSLT filter

Postby AP Jager » Sun Nov 09, 2008 1:42 am

I think it's the basis for what I want. Though it doesn't exactly do what I want.

In the example of the exported payment.xml you have this:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<payments>
<payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
</payment>
</payments>

So basically the tags are the headers of my table and the text are my cell values.

What I am looking to do is:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="utf-8"?>
<payments>
<payment>
    <property name="purpose" value="cd"/>
    <property name="amount" value="12.95"/>
    <property name="tax" value="19.1234"/>
    <property name="maturity" value="2008-03-01"/>
</payment>
</payments>

And so on.
OOo 3.0.X on Ms Windows XP
AP Jager
 
Posts: 4
Joined: Sat Nov 08, 2008 5:32 pm

Re: Complex XSLT filter

Postby hol.sten » Sun Nov 09, 2008 5:48 pm

AP Jager wrote:I think it's the basis for what I want. Though it doesn't exactly do what I want.

Yes, it does!

AP Jager wrote:In the example of the exported payment.xml you have this:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<payments>
<payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
</payment>
</payments>

Please take a closer look at my example! The above is not from my exported payment.xml. It's from my XML example input file.

This is from my XML example output file:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<payments export-date="2008-01-01" export-user="hol.sten">
  <payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
  </payment>
  ...
</payments>
And this is a quote from the text above the XML example output file: "The XML example output file looks a little different, because I added for demonstration purpose the two attributes export-date and export-user to the root element payments:"

AP Jager wrote:What I am looking to do is:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="utf-8"?>
<payments>
<payment>
    <property name="purpose" value="cd"/>
    <property name="amount" value="12.95"/>
    <property name="tax" value="19.1234"/>
    <property name="maturity" value="2008-03-01"/>
</payment>
</payments>

Ok, good example. You want to change for example <purpose>CD</purpose> to <property name="purpose" value="CD"/>. That should be possible. Take my XSLT export filter and change the lines
Code: Select all   Expand viewCollapse view
       <xsl:when test="position()=1">
        <purpose><xsl:value-of select="text:p"/></purpose>
       </xsl:when>
to
Code: Select all   Expand viewCollapse view
       <xsl:when test="position()=1">
        <property name="purpose">
         <xsl:attribute name="value"><xsl:value-of select="text:p"/></xsl:attribute>
        </property>
       </xsl:when>
or
Code: Select all   Expand viewCollapse view
       <xsl:when test="position()=1">
        <property>
         <xsl:attribute name="name">purpose</xsl:attribute>
         <xsl:attribute name="value"><xsl:value-of select="text:p"/></xsl:attribute>
        </property>
       </xsl:when>

The result should now be <property name="purpose" value="cd"></property>, what is very similar to your request. And you can change the other "properties" likewise.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: Complex XSLT filter

Postby AP Jager » Thu Nov 13, 2008 5:25 am

Sorry had overseen those lines of code and was focusing on the lower part.

Now this does indeed work. Better than anticipated even as the second line of code gives me exactly this:
Code: Select all   Expand viewCollapse view
<payment>
      <property name="purpose" value="CD"/>
<payment>

Only thing here is that purpose is actually being defined in the filter as a fixed value and not as the table header. If I were to change the name of table's headers I would have to change all the values within the filter aswell before exporting.

I tried to change:
Code: Select all   Expand viewCollapse view
<xsl:attribute name="name">purpose</xsl:attribute>

to
Code: Select all   Expand viewCollapse view
<xsl:attribute name="name"><xsl:value-of select="???"/></xsl:attribute>

Where I tried different things for ???. But somehow I can't reference it to being the header of my table.
OOo 3.0.X on Ms Windows XP
AP Jager
 
Posts: 4
Joined: Sat Nov 08, 2008 5:32 pm

Re: Complex XSLT filter

Postby hol.sten » Fri Nov 14, 2008 12:02 am

AP Jager wrote:If I were to change the name of table's headers I would have to change all the values within the filter aswell before exporting.

Intriguing question!

AP Jager wrote:I tried to change:
Code: Select all   Expand viewCollapse view
<xsl:attribute name="name">purpose</xsl:attribute>

to
Code: Select all   Expand viewCollapse view
<xsl:attribute name="name"><xsl:value-of select="???"/></xsl:attribute>

Where I tried different things for ???. But somehow I can't reference it to being the header of my table.

There are several possibilities:
1) Define and use a variable:
Code: Select all   Expand viewCollapse view
<!-- Get the column headers -->
<xsl:variable name="column1Header">
  <xsl:value-of select="//table:table/table:table-row[1]/table:table-cell[1]/text:p"/>
</xsl:variable>

<xsl:variable name="column2Header">
  <xsl:value-of select="//table:table/table:table-row[1]/table:table-cell[2]/text:p"/>
</xsl:variable>

<xsl:variable name="column3Header">
  <xsl:value-of select="//table:table/table:table-row[1]/table:table-cell[3]/text:p"/>
</xsl:variable>

<xsl:variable name="column4Header">
  <xsl:value-of select="//table:table/table:table-row[1]/table:table-cell[4]/text:p"/>
</xsl:variable>

...

     <!-- Process the first four columns -->
     <property>
      <xsl:attribute name="name"><xsl:value-of select="$column1Header"/></xsl:attribute>
      <xsl:attribute name="value"><xsl:apply-templates select="table:table-cell[1]"/></xsl:attribute>
     </property>
     <property>
      <xsl:attribute name="name"><xsl:value-of select="$column2Header"/></xsl:attribute>
      <xsl:attribute name="value"><xsl:apply-templates select="table:table-cell[2]"/></xsl:attribute>
     </property>
     <property>
      <xsl:attribute name="name"><xsl:value-of select="$column3Header"/></xsl:attribute>
      <xsl:attribute name="value"><xsl:apply-templates select="table:table-cell[3]"/></xsl:attribute>
     </property>
     <property>
      <xsl:attribute name="name"><xsl:value-of select="$column4Header"/></xsl:attribute>
      <xsl:attribute name="value"><xsl:apply-templates select="table:table-cell[4]"/></xsl:attribute>
     </property>
This example is not complete and will for that reason not work. Although you might get the point of defining a variable (which is more constant in XSLT than in a lot of other programming languages) and using it where you tried different ???.

2) Try this much more flexible example:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<!-- We must define several namespaces, because we need them to access -->
<!-- the document model of the in-memory OpenOffice.org document.      -->
<!-- If we want to access more parts of the document model, we must    -->
<!-- add there namesspaces here, too.                                  -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   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">

<xsl:output method = "xml" indent = "yes" encoding = "UTF-8" omit-xml-declaration = "no"/>

<!-- Process the document model -->
<xsl:template match="/">
  <rows>
   <!-- Process all tables -->
   <xsl:apply-templates select="//table:table"/>
  </rows>
</xsl:template>

<xsl:template match="table:table">
  <!-- Process all table-rows after the column labels in table-row 1 -->
  <xsl:for-each select="table:table-row">
 
   <xsl:variable name="row"><xsl:number/></xsl:variable>

   <xsl:if test="position()>1">
    <row>
     <!-- Process all table-row cells -->
     <xsl:for-each select="table:table-cell">
      <xsl:apply-templates select=".">
       <xsl:with-param name="row"><xsl:value-of select="$row"/></xsl:with-param>
       <xsl:with-param name="column"><xsl:number/></xsl:with-param>
      </xsl:apply-templates>
     </xsl:for-each>
    </row>
   </xsl:if>
  </xsl:for-each>
</xsl:template>

<xsl:template match="table:table-cell">
  <xsl:param name="row"/>
  <xsl:param name="column"/>

  <!-- Process table-cell -->
  <xsl:if test="@office:date-value or @office:date-value or normalize-space(text:p) != ''">
   <property>
    <xsl:attribute name="name">
     <xsl:for-each select="//table:table/table:table-row[1]/table:table-cell">
      <xsl:if test="position() = $column">
       <xsl:value-of select="text:p"/>
      </xsl:if>
     </xsl:for-each>
    </xsl:attribute>
    <xsl:attribute name="value">
     <xsl:choose>
      <xsl:when test="@office:date-value">
       <xsl:value-of select="@office:date-value"/>
      </xsl:when>
      <xsl:when test="@office:value">
       <xsl:value-of select="@office:value"/>
      </xsl:when>
      <xsl:when test="normalize-space(text:p) != ''">
       <xsl:value-of select="text:p"/>
      </xsl:when>
     </xsl:choose>
    </xsl:attribute>
    <xsl:attribute name="row"><xsl:value-of select="$row"/></xsl:attribute>
    <xsl:attribute name="col"><xsl:value-of select="$column"/></xsl:attribute>
   </property>
  </xsl:if>
</xsl:template>

</xsl:stylesheet>
This XSLT filter works at least for me with OOo 3.0.0. It can process an arbitrary number of rows AND columns. And it uses for every column the header of the first row. The if of the "table:table-cell" template checks if the cell contains any data. The whens of the "table:table-cell" template check which type of data the cell contains. Just for demonstration purpose I added the parameters row and column so that you can keep track of where you got your data from.

Read more on XSLT here: http://www.dpawson.co.uk/xsl/sect2/sect21.html. I'm browsing there a lot to deal with XSLT problems. You'll find there a lot of useful examples.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: Complex XSLT filter

Postby AP Jager » Fri Nov 14, 2008 4:04 am

Thanks Hol.sten

With a bit of tweaking it now does exactly what it's supposed to do.
Many many thanks.
OOo 3.0.X on Ms Windows XP
AP Jager
 
Posts: 4
Joined: Sat Nov 08, 2008 5:32 pm

Re: Complex XSLT filter

Postby hol.sten » Mon Nov 17, 2008 9:41 pm

AP Jager wrote:With a bit of tweaking it now does exactly what it's supposed to do.

Thank you very much for letting me know of the filter setback you found! Your private message contained a quite disturbing problem:
AP Jager wrote:I did encounter a small hiccup though when processing my table to XML.
Overall it works perfectly except that when 3 or more adjacent cells are empty inside the table it will export 2 of them then ignore all other empty cells until the next cell containing text

As an example:
Let's say we have a 8 column 2 row table like:
A B C D E F G H
1 2 3 X X X X 4
(where X represents an empty cell)

I couldn't believe at first what I read. The export filter should skip empty cells? I gave it a try and found that the problem is even far more worse!Let's say we have a 3 row 8 column table like:
Code: Select all   Expand viewCollapse view
A B C D E F G H
1 2 3 X X X X 4
1 1 2 2 X X 4 4
Where X represents not empty cells but X itself! So the cells are not empty. This is what you get by using my faulty export filter:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<rows>
   <row>
      <property name="A" value="1" row="2" col="1"/>
      <property name="B" value="2" row="2" col="2"/>
      <property name="C" value="3" row="2" col="3"/>
      <property name="D" value="X" row="2" col="4"/>
      <property name="E" value="4" row="2" col="5"/>
   </row>
   <row>
      <property name="A" value="1" row="3" col="1"/>
      <property name="B" value="2" row="3" col="2"/>
      <property name="C" value="3" row="3" col="3"/>
      <property name="D" value="X" row="3" col="4"/>
   </row>
</rows>
Row "2" contains only one X, misses 3 X and the "4" has the wrong name. Row "3" is even worse, because it misses 4 cells and only one cell has the correct name. So I dug into the problem and found this in the content.xml of the above OOo Calc spreadsheet:
Code: Select all   Expand viewCollapse view
<table:table-cell table:number-columns-repeated="4" office:value-type="string">
<text:p>X</text:p>
</table:table-cell>
The content.xml does not contain 4 <table:table-cell><text:p>X</text:p></table:table-cell>, but one with an attribute table:number-columns-repeated="4". So row two of the spreadsheet does not contain 8 cells but only 5. The same applies to the last row which contains only 4 cells, each of them with table:number-columns-repeated="2".

I tried to get around the problem but couldn't solve it completely. The following export filter uses recursion to get all the headers and the content of every cell:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<!-- We must define several namespaces, because we need them to access -->
<!-- the document model of the in-memory OpenOffice.org document.      -->
<!-- If we want to access more parts of the document model, we must    -->
<!-- add there namesspaces here, too.                                  -->
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   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">


<xsl:output method = "xml" indent = "yes" encoding = "UTF-8" omit-xml-declaration = "no"/>


<xsl:template match="/">

  <!-- Process all tables -->
  <tables>
   <xsl:apply-templates select="//table:table"/>
  </tables>
</xsl:template>


<xsl:template match="table:table">

  <!-- Process table -->
  <table>
   <xsl:attribute name="name"><xsl:value-of select="@table:name"/></xsl:attribute>

   <!-- Process all table rows -->
   <xsl:for-each select="table:table-row">
 
    <xsl:variable name="row"><xsl:number/></xsl:variable>

    <xsl:choose>
     <xsl:when test="position() = 1">
      <header>
       <xsl:attribute name="row"><xsl:value-of select="$row"/></xsl:attribute>

       <!-- Process all header cells -->
       <xsl:for-each select="table:table-cell">
        <xsl:apply-templates select=".">
         <xsl:with-param name="quantity">
          <xsl:choose>
           <xsl:when test="@table:number-columns-repeated">
            <xsl:value-of select="@table:number-columns-repeated"/>
           </xsl:when>
           <xsl:otherwise>1</xsl:otherwise>
          </xsl:choose>
         </xsl:with-param>
        </xsl:apply-templates>
       </xsl:for-each>
      </header>
     </xsl:when>
     <xsl:otherwise>
      <content>
       <xsl:attribute name="row"><xsl:value-of select="$row"/></xsl:attribute>

       <!-- Process all content cells -->
       <xsl:for-each select="table:table-cell">
        <xsl:apply-templates select=".">
         <xsl:with-param name="quantity">
          <xsl:choose>
           <xsl:when test="@table:number-columns-repeated">
            <xsl:value-of select="@table:number-columns-repeated"/>
           </xsl:when>
           <xsl:otherwise>1</xsl:otherwise>
          </xsl:choose>
         </xsl:with-param>
        </xsl:apply-templates>
       </xsl:for-each>
      </content>
     </xsl:otherwise>
    </xsl:choose>
   </xsl:for-each>
 
  </table>

</xsl:template>


<xsl:template match="table:table-cell">
  <xsl:param name="quantity"/>

  <!-- Process table cell -->
  <property>
   <xsl:attribute name="value">
    <xsl:choose>
     <xsl:when test="@office:date-value">
      <xsl:value-of select="@office:date-value"/>
     </xsl:when>
     <xsl:when test="@office:value">
      <xsl:value-of select="@office:value"/>
     </xsl:when>
     <xsl:when test="normalize-space(text:p) != ''">
      <xsl:value-of select="text:p"/>
     </xsl:when>
     <xsl:otherwise><xsl:text> </xsl:text></xsl:otherwise>
    </xsl:choose>
   </xsl:attribute>
  </property>
 
  <!-- Repeat processing table cell, if there are identical table cells -->
  <xsl:if test="$quantity > 1">
   <xsl:apply-templates select=".">
    <xsl:with-param name="quantity"><xsl:value-of select="$quantity - 1"/></xsl:with-param>
   </xsl:apply-templates>
  </xsl:if>
</xsl:template>


</xsl:stylesheet>
But this export filter has another drawback. I'm no longer able to provide the correct header for each cell. So all the export filter produces for the example above is this:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<tables>
   <table name="Sheet1">
      <header row="1">
         <property value="a"/>
         <property value="b"/>
         <property value="c"/>
         <property value="d"/>
         <property value="e"/>
         <property value="f"/>
         <property value="g"/>
         <property value="h"/>
      </header>
      <content row="2">
         <property value="1"/>
         <property value="2"/>
         <property value="3"/>
         <property value="x"/>
         <property value="x"/>
         <property value="x"/>
         <property value="x"/>
         <property value="4"/>
      </content>
      <content row="3">
         <property value="1"/>
         <property value="1"/>
         <property value="2"/>
         <property value="2"/>
         <property value="3"/>
         <property value="3"/>
         <property value="x"/>
         <property value="x"/>
      </content>
   </table>
</tables>
But, having this it is now possible by applying a second XSL to this XML to get what you expect. But as far as I know, this is no longer possible with OOo directly, unless you write a macro...
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [Solved] Complex XSLT filter

Postby blabj » Mon Mar 22, 2010 6:53 pm

I know this is an old thread, but I found this while trying to solve a problem related to the "number-rows-repeated" and "number-columns-repeated" situation mentioned above.

I was able to solve this - just wanted to share the solution.

Essentially what I needed was a command line utility to extract the cell value from an ODS file. Ie. Given sheet x, row y, and column z, return the cell value.

What I thought was going to be easy with the xmlstarlet and a simple XPath expression (/table:table[x]/table:table-row[y]/table:table-cell[z]) on content.xml turned out not to be so easy, due to the "number-rows-repeated" and "number-columns-repeated" (which are very important to reducing the size of the document).

So I went about developing a stylesheet to transform content.xml into an indexable grid (so that /table:table[x]/table:table-row[y]/table:table-cell[z] can actually work reliably).

xmlstarlet only supports XPath 1.0 (well, limitation is in libxml2 actually), so using a select="1 to number(@table:number-rows-repeated)" was out of the question.

I took the following recursive call-template approach:

Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   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">


<xsl:output method = "xml" indent = "yes" encoding = "UTF-8" omit-xml-declaration = "no"/>

<xsl:template match="table:table-cell">
 
        <xsl:choose>
        <xsl:when test="@table:number-columns-repeated">
          <xsl:variable name="numcols" select="number(@table:number-columns-repeated)"/>
          <xsl:call-template name="processcol">
      <xsl:with-param name="currentCol" select="0"/>
      <xsl:with-param name="totalCols" select="$numcols"/>
          </xsl:call-template>
        </xsl:when>
        <xsl:otherwise>
          <xsl:copy-of select="."/>
        </xsl:otherwise>
        </xsl:choose>
</xsl:template>

<xsl:template match="table:table-row">
 
        <xsl:choose>
        <xsl:when test="@table:number-rows-repeated">
     <xsl:variable name="numrows" select="number(@table:number-rows-repeated)"/>
          <xsl:call-template name="processrow">
      <xsl:with-param name="currentRow" select="0"/>
      <xsl:with-param name="totalRows" select="$numrows"/>
          </xsl:call-template>
   </xsl:when>
        <xsl:otherwise>
          <xsl:call-template name="processrow">
      <xsl:with-param name="currentRow" select="0"/>
      <xsl:with-param name="totalRows" select="1"/>
          </xsl:call-template>
        </xsl:otherwise>
        </xsl:choose>

</xsl:template>

<xsl:template match="@*|*">
   <xsl:copy>
     <xsl:apply-templates select="@*|node()"/>
   </xsl:copy>
</xsl:template>

<xsl:template name="processrow">
   <xsl:param name="currentRow"/>
   <xsl:param name="totalRows"/>
        <xsl:choose>
   <xsl:when test="$currentRow &lt; $totalRows">
<xsl:copy>
<xsl:apply-templates select="table:table-cell"/>
</xsl:copy>
        <xsl:call-template name="processrow">
      <xsl:with-param name="currentRow" select="$currentRow + 1"/>
      <xsl:with-param name="totalRows" select="$totalRows"/>
        </xsl:call-template>
   </xsl:when>
   </xsl:choose>
</xsl:template>

<xsl:template name="processcol">
   <xsl:param name="currentCol"/>
   <xsl:param name="totalCols"/>
        <xsl:choose>
   <xsl:when test="$currentCol &lt; $totalCols">
<xsl:copy-of select="."/>
        <xsl:call-template name="processcol">
      <xsl:with-param name="currentCol" select="$currentCol + 1"/>
      <xsl:with-param name="totalCols" select="$totalCols"/>
        </xsl:call-template>
   </xsl:when>
   </xsl:choose>
</xsl:template>

</xsl:stylesheet>


This can be applied direct to content.xml.. but is a one-way transformation.. and is an intermediate step to being able to perform a simple Xpath query on the resultant.

NOTE: The resultant XML potentially can be much larger than the original content.xml, and applying the same stylesheet to the resultant xml will produce exponentially larger files.

-Bob
OpenOffice 3.0.1 Ubuntu 9.04
blabj
 
Posts: 4
Joined: Mon Mar 22, 2010 5:44 pm

Re: [Solved] Complex XSLT filter

Postby blabj » Mon Mar 22, 2010 9:15 pm

Occasionally you'll get an ODS with the following:

Code: Select all   Expand viewCollapse view
        <table:table-row table:style-name="ro1" table:number-rows-repeated="65499">
          <table:table-cell table:number-columns-repeated="7"/>
        </table:table-row>
        <table:table-row table:style-name="ro1">
          <table:table-cell table:number-columns-repeated="7"/>
        </table:table-row>
      </table:table>


This number-rows-repeated exceeds the allowable recursive depth of most XSLT processors... so I've augmented my XSLT basically ignore this for anything over 3000:

Code: Select all   Expand viewCollapse view
<xsl:template match="table:table-row">

        <xsl:choose>
        <xsl:when test="@table:number-rows-repeated">
          <xsl:variable name="numrows" select="number(@table:number-rows-repeated)"/>
          <xsl:choose>
          <xsl:when test="$numrows &gt; 3000">
                <xsl:call-template name="processrow">
                        <xsl:with-param name="currentRow" select="0"/>
                        <xsl:with-param name="totalRows" select="1"/>
                </xsl:call-template>
          </xsl:when>
          <xsl:otherwise>
                <xsl:call-template name="processrow">
                        <xsl:with-param name="currentRow" select="0"/>
                        <xsl:with-param name="totalRows" select="$numrows"/>
                 </xsl:call-template>
          </xsl:otherwise>
          </xsl:choose>
        </xsl:when>
        <xsl:otherwise>
          <xsl:call-template name="processrow">
                <xsl:with-param name="currentRow" select="0"/>
                <xsl:with-param name="totalRows" select="1"/>
          </xsl:call-template>
        </xsl:otherwise>
        </xsl:choose>

</xsl:template>


Not sure if the same situation happens in the "number-columns-repeated" attribute, but fix would be similar.
OpenOffice 3.0.1 Ubuntu 9.04
blabj
 
Posts: 4
Joined: Mon Mar 22, 2010 5:44 pm

Re: [Solved] Complex XSLT filter

Postby rohitrs » Wed May 04, 2011 10:40 am

Faced with the same problem, I required a simple index-based solution which allows a more flexible xml generation. Here is what I tried to work out. I have used xslt 2.0 to make use of user defined functions. Here is the stylesheet ...

Code: Select all   Expand viewCollapse view
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
   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"
   xmlns:my="http://user.com/my"
   exclude-result-prefixes="office table text my">

   <xsl:output method="xml" indent="yes" encoding="UTF-8" omit-xml-declaration="no"/>

   <xsl:function name="my:getColumnValue">
      <xsl:param name="tableRow" as="node()"/>
      <xsl:param name="colIndex"/>
      <xsl:param name="currentIndex"/>
      <xsl:choose>
         <xsl:when test="$currentIndex &lt; $colIndex">
            <xsl:variable name="repeatColumns" select="$tableRow/table:table-cell[$currentIndex]/@table:number-columns-repeated"/>
            <xsl:choose>
               <xsl:when test="$repeatColumns">
                  <xsl:choose>
                     <xsl:when test="$currentIndex + $repeatColumns - 1 &gt;= $colIndex"><xsl:value-of select="$tableRow/table:table-cell[$currentIndex]"/></xsl:when>
                     <xsl:otherwise><xsl:value-of select="my:getColumnValue($tableRow, $colIndex - $repeatColumns + 1, $currentIndex + 1)"/></xsl:otherwise>
                  </xsl:choose>
               </xsl:when>
               <xsl:otherwise><xsl:value-of select="my:getColumnValue($tableRow, $colIndex, $currentIndex + 1)"/></xsl:otherwise>
            </xsl:choose>
         </xsl:when>
         <xsl:otherwise><xsl:value-of select="$tableRow/table:table-cell[$colIndex]"/></xsl:otherwise>
      </xsl:choose>
   </xsl:function>

   <xsl:template match="//table:table">
      <Tests>
         <!-- Process all table rows -->
         <xsl:variable name="colCount" select="count(table:table-row[1]/table:table-cell)"/>
         <xsl:for-each select="table:table-row">
            <xsl:if test="position() > 1">
            <Test>
               <SrNo><xsl:value-of select="my:getColumnValue(.,1,1)"/></SrNo>
               <Name><xsl:value-of select="my:getColumnValue(.,2,1)"/></Name>
               <Age><xsl:value-of select="my:getColumnValue(.,3,1)"/></Age>
               <Height><xsl:value-of select="my:getColumnValue(.,4,1)"/></Height>
               <Address><xsl:value-of select="my:getColumnValue(.,5,1)"/></Address>
            </Test>
            </xsl:if>
         </xsl:for-each>
      </Tests>
   </xsl:template>
</xsl:stylesheet>

The tags used above are just placeholders. Please replace them with appropriate ones in your xslt. This solution is limited by number of recursive calls allowed by the xslt processor.

If xslt 1.0 supports sending node as params, then we can try to replace the above udf to get a template based solution. If you find any bugs, do let me know.
OpenOffice 3.1.1, Windows 7
rohitrs
 
Posts: 13
Joined: Sun Nov 29, 2009 2:03 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 26 guests