[XML Filter] Create XSLT filters for import and export

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

[XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Introduction

Since OpenOffice.org 2.0 documents are stored in the OASIS OpenDocument file format as compressed XML files. Additionally OOo supports a lot of other widely used file formats. If all the available file formats do not suffice, OOo offers the possibility to add new import and export filters.

In this example I show how you can create your own OOo Calc import and export filters.



XML example input file

For this example I use a simple XML file which contains 4 different types of data:
  1. purpose: a simple text
  2. amount: a number with 2 decimal places
  3. tax: a number with 4 decimal places (although taxes with this number of decimal places are a little unrealistic)
  4. maturity: a date
The XML file of this example consists of several payments containing these fields:

Code: Select all

<?xml version="1.0"?>
<payments>
  <payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
  </payment>
  <payment>
    <purpose>DVD</purpose>
    <amount>19.95</amount>
    <tax>19.4321</tax>
    <maturity>2008-03-02</maturity>
  </payment>
  <payment>
    <purpose>Clothes</purpose>
    <amount>99.95</amount>
    <tax>18.5678</tax>
    <maturity>2008-03-03</maturity>
  </payment>
  <payment>
    <purpose>Book</purpose>
    <amount>9.49</amount>
    <tax>18.9876</tax>
    <maturity>2008-03-04</maturity>
  </payment>
</payments>
Save this as payment.xml for example in the folder C:\Temp for future use.



XSLT import filter

To import this example payments XML file into OOo Calc we need an XSLT stylesheet which creates an XML file complying OOo Calc's file format:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" office:version="1.0">

   <office:automatic-styles>

    <!-- Column styles (co1: column with 6 cm width, co1: column with 3 cm width) -->
    <style:style style:name="co1" style:family="table-column">
     <style:table-column-properties fo:break-before="auto" style:column-width="6.000cm"/>
    </style:style>
    <style:style style:name="co2" style:family="table-column">
     <style:table-column-properties fo:break-before="auto" style:column-width="3.000cm"/>
    </style:style>

    <!-- Number format styles (N36: date with DD.MM.YYYY, N107: float with 0,0000) -->
    <number:date-style style:name="N36" number:automatic-order="true">
     <number:day number:style="long"/>
     <number:text>.</number:text>
     <number:month number:style="long"/>
     <number:text>.</number:text>
     <number:year number:style="long"/>
    </number:date-style>
    <number:number-style style:name="N107">
     <number:number number:decimal-places="4" number:min-integer-digits="1"/>
    </number:number-style>

    <!-- Cell styles (ce1: right aligned, ce2: float with 4 decimal places, ce3: date) -->
    <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
     <style:table-cell-properties style:text-align-source="fix" style:repeat-content="false"/>
     <style:paragraph-properties fo:text-align="end"/>
    </style:style>
    <style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N107"/>
    <style:style style:name="ce3" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N36"/>

   </office:automatic-styles>

   <office:body>
    <office:spreadsheet>
     <table:table>

      <!-- Format the first 4 columns of the table -->
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="ce2"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="ce3"/>

      <!-- Insert column labels, first label with default style, the remaining 3 labels right aligned -->
      <table:table-row>
       <table:table-cell><text:p>Purpose</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Amount</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Tax</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Maturity</text:p></table:table-cell>
      </table:table-row>

      <!-- Process XML input: Insert one row for each payment -->
      <xsl:for-each select="payments/payment">
       <table:table-row>

        <!-- Insert string payment purpose -->
        <table:table-cell>
         <text:p><xsl:value-of select="purpose"/></text:p>
        </table:table-cell>

        <!-- Insert float payment amount -->
        <table:table-cell office:value-type="float">
         <xsl:attribute name="office:value"><xsl:value-of select="amount"/></xsl:attribute>
         <text:p><xsl:value-of select="amount"/></text:p>
        </table:table-cell>

        <!-- Insert float payment tax -->
        <table:table-cell office:value-type="float">
         <xsl:attribute name="office:value"><xsl:value-of select="tax"/></xsl:attribute>
         <text:p><xsl:value-of select="tax"/></text:p>
        </table:table-cell>

        <!-- Insert date payment maturity -->
        <table:table-cell office:value-type="date">
         <xsl:attribute name="office:date-value"><xsl:value-of select="maturity"/></xsl:attribute>
         <text:p><xsl:value-of select="maturity"/></text:p>
        </table:table-cell>

       </table:table-row>
      </xsl:for-each>
     </table:table>
    </office:spreadsheet>
   </office:body>
  </office:document-content>
 </xsl:template>
</xsl:stylesheet>


XSLT export filter

To export this example payments XML file from OOo Calc we need an XSLT stylesheet which processes an XML file complying OOo Calc's file format:

Code: Select all

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

 <!-- By setting the PropertyValue "URL" in the properties used in storeToURL(), -->
 <!-- we can pass a single parameter to this stylesheet.                         -->
 <!-- Caveat: If we use the "URL" property in the stylesheet and call in OOo     -->
 <!-- from the menu "File" > "Export...", OOo assigns a target URL. And that     -->
 <!-- might not be what we want.                                                 -->
 <xsl:param name="targetURL"/>

 <xsl:variable name="exportDate">
  <xsl:choose>
   <xsl:when test="string-length(substring-before($targetURL,';'))=10">
    <xsl:value-of select="substring-before($targetURL,';')"/>
   </xsl:when>
   <xsl:when test="string-length($targetURL)=10">
    <xsl:value-of select="$targetURL"/>
   </xsl:when>
  </xsl:choose>
 </xsl:variable>

 <xsl:variable name="exportUser">
  <xsl:if test="string-length(substring-after($targetURL,';'))>0">
   <xsl:value-of select="substring-after($targetURL,';')"/>
  </xsl:if>
 </xsl:variable>

 <!-- Process the document model -->
 <xsl:template match="/">
  <payments>
   <xsl:attribute name="export-date"><xsl:value-of select="$exportDate"/></xsl:attribute>
   <xsl:attribute name="export-user"><xsl:value-of select="$exportUser"/></xsl:attribute>
   <!-- Process all tables -->
   <xsl:apply-templates select="//table:table"/>
  </payments>
 </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:if test="position()>1">
    <payment>
     <!-- Process the first for columns containing purpose, amount, tax and maturity -->
     <xsl:for-each select="table:table-cell">
      <xsl:choose>
       <xsl:when test="position()=1">
	    <purpose><xsl:value-of select="text:p"/></purpose>
       </xsl:when>
       <xsl:when test="position()=2">
 	    <amount><xsl:value-of select="@office:value"/></amount>
 	   </xsl:when>
       <xsl:when test="position()=3">
	    <tax><xsl:value-of select="@office:value"/></tax>
	   </xsl:when>
       <xsl:when test="position()=4">
	    <maturity><xsl:value-of select="@office:date-value"/></maturity>
	   </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </payment>
   </xsl:if>
  </xsl:for-each>
 </xsl:template>

</xsl:stylesheet>


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:

Code: Select all

<?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>
  <payment>
    <purpose>DVD</purpose>
    <amount>19.95</amount>
    <tax>19.4321</tax>
    <maturity>2008-03-02</maturity>
  </payment>
  <payment>
    <purpose>Clothes</purpose>
    <amount>99.95</amount>
    <tax>18.5678</tax>
    <maturity>2008-03-03</maturity>
  </payment>
  <payment>
    <purpose>Book</purpose>
    <amount>9.49</amount>
    <tax>18.9876</tax>
    <maturity>2008-03-04</maturity>
  </payment>
</payments>
Using <xsl:attribute name="my-attribute-name"><xsl:value-of select="$someValue"/></xsl:attribute> in the XSLT export filter you can add attributes to all elements you like.



Install the XSLT filters for import and export

To install our example import and export filters needs the following steps:
  1. Open OOo
  2. Call "Tools" > "XML Filter Settings..."
  3. Press "New..."
  4. In the "General" tab enter the Filter name "Calc_Payments", select the Application "OpenOffice.org Calc (.ods)", enter the Name of file type "Payments", don't change the File extension "xml".
  5. In the "Transformation" tab select the above created XSLT filters for XSLT for export and XSLT for import
  6. That's all, so press OK and Close
Now the above created import and export filters are ready for use.



Call the XSLT filters from OpenOffice.org menu's

To import the simple payments XML file the following steps are necessary:
  1. Call "File" > "Open..."
  2. Move to the directory where the XML file payments.xml is saved (for example C:\Temp)
  3. Select the File type "Payments (*.xml)" (third drop down box)
  4. Select the XML file payments.xml
  5. Press Open
If everything is ok, you see now 4 column labels and the 4 imported records.

Now we add a fifth record (Software / 39,95 / 19 / 05.03.2008) and export the payments XML file:
  1. Call "File" > "Save As..."
  2. Move to the directory where the XML file payments.xml is saved (for example C:\Temp)
  3. Select the File type "Payments (*.xml)" (second drop down box)
  4. Enter the File name "payments-export-menu.xml"
  5. Press Save


Call the XSLT filters from OpenOffice.org Basic

Importing this example payments XML file through a simple OOo Basic macro looks like this:

Code: Select all

REM  *****  BASIC  *****

Sub Main

rem -------------------------------------------------------
rem - Init import URL
dim xmlurl as string
xmlurl = "file:///C:/Temp/payments.xml"

rem -------------------------------------------------------
rem - Import payments
dim properties(0) as new com.sun.star.beans.PropertyValue
properties(0).Name = "FilterName"
properties(0).Value = "Calc_Payments"

dim doc As Object 
doc = StarDesktop.loadComponentFromURL(xmlurl, "_blank", 0, properties())

End Sub
Exporting this example payments XML file is possible through an OOo script, too. A simple OOo Basic macro for exporting looks like this:

Code: Select all

REM  *****  BASIC  *****

Sub Main

rem -------------------------------------------------------
rem - Get access to the document
dim document as object
document = ThisComponent

rem -------------------------------------------------------
rem - Init export URL
dim xmlurl as string
xmlurl = "file:///C:/Temp/payments-export.xml"

rem -------------------------------------------------------
rem - Export payments
dim properties(5) as new com.sun.star.beans.PropertyValue
properties(0).Name = "FilterName"
properties(0).Value = "Calc_Payments"
properties(1).Name = "URL"
properties(1).Value = "2008-01-01;hol.sten"
rem properties(1).Value = xmlurl
document.storeToURL(xmlurl, properties())

End Sub

References
  1. OpenOffice.org filters using the XML based file format
  2. OASIS OpenDocument file format
  3. Filters and Conversions based on OpenDocument/OpenOffice.org XML
Last edited by hol.sten on Sat Nov 08, 2008 8:59 pm, edited 1 time in total.
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: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

08. Nov. 2008: Usage of <xsl:attribute name="my-attribute-name"><xsl:value-of select="$someValue"/></xsl:attribute> in the XSLT export filter added.
13. Mar. 2008: Creation of the XSLT filters for import and export
Last edited by hol.sten on Sat Nov 08, 2008 9:01 pm, edited 1 time in total.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
lizardbpi
Posts: 3
Joined: Thu Mar 20, 2008 9:41 am

Re: [XML Filter] Create XSLT filters for import and export

Post by lizardbpi »

Thanks for this stuff, it will serve great! It was not working for me, stuck at importing.
The only thing im not sure doing, is saving the xslt files.
Is it okay, if I copied the text you provided, and saved it from notepad?
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

lizardbpi wrote:It was not working for me,
Was not working or is not working?
lizardbpi wrote:stuck at importing.
Where exactly? Shall I improve the description somehow?
lizardbpi wrote:The only thing im not sure doing, is saving the xslt files. Is it okay, if I copied the text you provided, and saved it from notepad?
I created and saved the XML and XSLT files with Notepad ++ (version 4.7.5). But notepad might work, too. Although I'm not sure how notepad deals with encoding="UTF-8".
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
lizardbpi
Posts: 3
Joined: Thu Mar 20, 2008 9:41 am

Re: [XML Filter] Create XSLT filters for import and export

Post by lizardbpi »

First of all thanks for the response! I see I have to be more clear.
It is not working for me, because it's freezing when I import my xml file to OO calc.
The loadbar appears, but it stops halfway, and then the program is not responding anymore.
I was using notepad, and saved the xslt files in utf-8.
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

lizardbpi wrote:it's freezing when I import my xml file to OO calc.
How does YOUR xml file look like?
Can you import MY payments example XML file?
Can you EXPORT an XML file and then try to IMPORT it back?
lizardbpi wrote:The loadbar appears, but it stops halfway, and then the program is not responding anymore.
One of the disadvantages of OOo's XSLT filter support. You get no feedback what went wrong. All you know if OOo behaves as you describe it here is, that something is wrong. And that's not very helpful to nail the problem down.
lizardbpi wrote:I was using notepad, and saved the xslt files in utf-8.
That should not cause a problem.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
lizardbpi
Posts: 3
Joined: Thu Mar 20, 2008 9:41 am

Re: [XML Filter] Create XSLT filters for import and export

Post by lizardbpi »

I wanted to import your payment xml. I give it another try, thanks for help!
Steffen Opel
Posts: 1
Joined: Wed Apr 23, 2008 4:26 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Steffen Opel »

lizardbpi wrote:I wanted to import your payment xml. I give it another try, thanks for help!
Guess you have succeeded, just in case somebody stumbles upon the same issue here:
  • If the code is copied from the forum supplied CODE block via the 'SELECT ALL' functionality and pasted into e.g. Notepad thereafter, there will be leading whitespace in the copied code, which is rendering the XSLT file invalid and in turn triggers the OpenOffice.org Calc freeze encountered by you. (Opening the XSLT in a dedicated editor should result in an appropriate error message, e.g. XML Notepad 2007 yields 'Unexpected XML declaration. The XML declaration must be the first node in the document, and no white space characters are allowed to appear before it.')
  • Consequently this can be avoided/fixed by either selecting the code by hand instead, which omits the whitespace (might be an error in the 'select all' functionality), or by reformatting the code in an editor capable of doing this.
Ciao,
Steffen
nicgiard
Posts: 7
Joined: Mon Feb 25, 2008 12:09 am

Re: [XML Filter] Create XSLT filters for import and export

Post by nicgiard »

It is possible to create a Dom Document from a XML string ???
And then it is possible to save Dom Xml to a variable , and also save in a file .xml ???
Thank you
moseby
Posts: 1
Joined: Wed Apr 30, 2008 6:29 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by moseby »

My experience trying this filter is with OOo 2.4.0 on Linux. When I try to import the example file, I get a blank spreadsheet. I ran the "Test XSLTs..." with the "Display source" option. In the source window I ran "Validate: which gives the following errors:

2: Document is invalid: no grammar found.
2: Document root element "office:document-content", must match DOCTYPE root "null".

After upgrading to the latest OOo 2.4.1, I get the same error message, however the data does show up in the spreadsheet.

It would be nice to resolve the error messages before I embark upon creating my own by modifying the filter. Any ideas?
igor24
Posts: 2
Joined: Fri Jul 11, 2008 9:23 am

Re: [XML Filter] Create XSLT filters for import and export

Post by igor24 »

Hi,

Thaks for your work, very interresting. It works.
I haven't understand some points.
When you do the first transformation, does Calc transform the XML file to ODS Format ?
Is The second transformation an ODS to XML ?

I'll explain what I would like to do :
1. I generate an xml file, and I want to open it with openOffice Calc or Excel. I want to do it only once. So, I think I can do it like the exemple.
2. Modify the file in Calc and transform it into and XML file and package it in my application. This operation may be done every time a user want to change some parameter ... so I would like to do this transformation automatically.

I wish, you've understand. I know my english is vey bad ... :oops:

thanks
OOo 2.3.X on Ubuntu 7.x
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

igor24 wrote:When you do the first transformation, does Calc transform the XML file to ODS Format ?
Yes. An XML import filter transforms an XML file to an OOo file, in this example in an ODS file.
igor24 wrote:Is The second transformation an ODS to XML ?
Yes. An XML export filter transforms an OOo file to an XML file, in this example from an ODS file.
igor24 wrote:1. I generate an xml file, and I want to open it with openOffice Calc or Excel. I want to do it only once. So, I think I can do it like the exemple.
Yes.
igor24 wrote:2. Modify the file in Calc and transform it into and XML file and package it in my application. This operation may be done every time a user want to change some parameter ... so I would like to do this transformation automatically.
I think I didn't get the point of this sentence. What is the question?
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
CESlater
Posts: 3
Joined: Wed Nov 12, 2008 5:21 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by CESlater »

I have this example working but just want to add some information for the XSLT novices like me:

For a record missing data like:

<payment>
<purpose>Missing All</purpose>
</payment>

the file loads fine but "default" values are assigned as in

Missing All 0 0.0000 12/30/1899

For a record with extra data like:

<payment>
<purpose>DVD</purpose>
<amount>19.95</amount>
<tax>19.4321</tax>
<recipe>Vegetable soup</recipe>
</payment>

it seems to be a "no-harm, no-foul" situation, with the recipe just not showing up.

Thanks for the hard work of putting the example together.
OOo 2.4.X on Ms Windows XP
CESlater
Posts: 3
Joined: Wed Nov 12, 2008 5:21 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by CESlater »

Back again .... here are two equivalent XML files one works one does not.

Works:

<?xml version="1.0"?>
<payments>
<payment>
<purpose>CD</purpose>
<amount>12.95</amount>
<tax>19.1234</tax>
<maturity>2008-03-01</maturity>
</payment>
</payments>

Does not work:

<?xml version="1.0"?>
<payments>
<payment purpose="CD" amount="12.95" tax="19.1234" maturity="2008-03-01" />
</payments>

I checked the "validity" of the 2 via Firefox and "XML Notepad 2007"

Why would one work and not the other?
OOo 2.4.X on Ms Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

CESlater wrote:Works:

Code: Select all

<?xml version="1.0"?>
<payments>
  <payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
  </payment>
</payments>
Works, because all values are within the appropriate elements as expected by the XSLT import filter.
CESlater wrote:Does not work:

Code: Select all

<?xml version="1.0"?>
<payments>
  <payment	purpose="CD" amount="12.95" tax="19.1234" maturity="2008-03-01"  />
</payments>
Does not work, because all values are within attributes, which are not expected by the XSLT import filter. Although it is possible to create an XSLT import filter for this XML structure, too.
CESlater wrote:I checked the "validity" of the 2 via Firefox and "XML Notepad 2007"
Yes, you are right, both XML files are valid. But only the first one suits the XSLT filter from my example.
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: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

CESlater wrote:For a record missing data like:

Code: Select all

<payment>
 <purpose>Missing All</purpose>
</payment>
the file loads fine but "default" values are assigned as in

Missing All 0 0.0000 12/30/1899
You can change the "defaults" by using something like <xsl:choose><xsl:when test="..."> in your XSLT import filter.
CESlater wrote:Back again .... here are two equivalent XML files one works one does not.

Works:

Code: Select all

<?xml version="1.0"?>
<payments>
 <payment>
  <purpose>CD</purpose>
  <amount>12.95</amount>
  <tax>19.1234</tax>
  <maturity>2008-03-01</maturity>
 </payment>
</payments>
Does not work:

Code: Select all

<?xml version="1.0"?>
<payments>
 <payment purpose="CD" amount="12.95" tax="19.1234" maturity="2008-03-01" />
</payments>
You can even create an XSLT filter which can handle both files!

I complete this post with another working XSLT import filter. The given payments file may look like this:

Code: Select all

<?xml version="1.0"?>
<payments>
  <payment>
    <purpose>CD</purpose>
    <amount>12.95</amount>
    <tax>19.1234</tax>
    <maturity>2008-03-01</maturity>
  </payment>
  <payment purpose="DVD" amount="19.95" tax="19.4321" maturity="2008-03-02"/>
  <payment purpose="Clothes" maturity="2008-03-03">
    <amount>99.95</amount>
    <tax>18.5678</tax>
  </payment>
  <payment/>
  <payment purpose="Total">
    <amount>132.85</amount>
    <tax>57.1233</tax>
  </payment>
  <payment/>
  <payment>
    <purpose>EndOfPayments</purpose>
  </payment>
</payments>
The first payment has all the data in several elements. The second has them all in several attributes. The third uses a mix of elements and attributes. The forth has no data. The fifth has no maturity date. The sixth is empty, too. And the last contains only a purpose element.

If you want to import this XML file you can do this with this XSLT import filter:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" office:version="1.0">

   <office:automatic-styles>

    <!-- Column styles (co1: column with 6 cm width, co1: column with 3 cm width) -->
    <style:style style:name="co1" style:family="table-column">
     <style:table-column-properties fo:break-before="auto" style:column-width="6.000cm"/>
    </style:style>
    <style:style style:name="co2" style:family="table-column">
     <style:table-column-properties fo:break-before="auto" style:column-width="3.000cm"/>
    </style:style>

    <!-- Number format styles (N36: date with DD.MM.YYYY, N107: float with 0,0000) -->
    <number:date-style style:name="N36" number:automatic-order="true">
     <number:day number:style="long"/>
     <number:text>.</number:text>
     <number:month number:style="long"/>
     <number:text>.</number:text>
     <number:year number:style="long"/>
    </number:date-style>
    <number:number-style style:name="N107">
     <number:number number:decimal-places="4" number:min-integer-digits="1"/>
    </number:number-style>

    <!-- Cell styles (ce1: right aligned, ce2: float with 4 decimal places, ce3: date) -->
    <style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default">
     <style:table-cell-properties style:text-align-source="fix" style:repeat-content="false"/>
     <style:paragraph-properties fo:text-align="end"/>
    </style:style>
    <style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N107"/>
    <style:style style:name="ce3" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N36"/>

   </office:automatic-styles>

   <office:body>
    <office:spreadsheet>
     <table:table>

      <!-- Format the first 4 columns of the table -->
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="ce2"/>
      <table:table-column table:style-name="co2" table:default-cell-style-name="ce3"/>

      <!-- Insert column labels, first label with default style, the remaining 3 labels right aligned -->
      <table:table-row>
       <table:table-cell><text:p>Purpose</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Amount</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Tax</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Maturity</text:p></table:table-cell>
      </table:table-row>

      <!-- Process XML input: Insert one row for each payment -->
      <xsl:for-each select="payments/payment">
       <table:table-row>

        <!-- Insert string payment purpose -->
        <xsl:choose>
         <xsl:when test="@purpose">
          <table:table-cell>
           <text:p><xsl:value-of select="@purpose"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:when test="purpose">
          <table:table-cell>
           <text:p><xsl:value-of select="purpose"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:otherwise>
          <table:table-cell>
           <text:p/>
          </table:table-cell>
         </xsl:otherwise>
        </xsl:choose>

        <!-- Insert float payment amount -->
        <xsl:choose>
         <xsl:when test="@amount">
          <table:table-cell office:value-type="float">
           <xsl:attribute name="office:value"><xsl:value-of select="@amount"/></xsl:attribute>
           <text:p><xsl:value-of select="@amount"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:when test="amount">
          <table:table-cell office:value-type="float">
           <xsl:attribute name="office:value"><xsl:value-of select="amount"/></xsl:attribute>
           <text:p><xsl:value-of select="amount"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:otherwise>
          <table:table-cell>
           <text:p/>
          </table:table-cell>
         </xsl:otherwise>
        </xsl:choose>

        <!-- Insert float payment tax -->
        <xsl:choose>
         <xsl:when test="@tax">
          <table:table-cell office:value-type="float">
           <xsl:attribute name="office:value"><xsl:value-of select="@tax"/></xsl:attribute>
           <text:p><xsl:value-of select="@tax"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:when test="tax">
          <table:table-cell office:value-type="float">
           <xsl:attribute name="office:value"><xsl:value-of select="tax"/></xsl:attribute>
           <text:p><xsl:value-of select="tax"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:otherwise>
          <table:table-cell>
           <text:p/>
          </table:table-cell>
         </xsl:otherwise>
        </xsl:choose>

        <!-- Insert date payment maturity -->
        <xsl:choose>
         <xsl:when test="@maturity">
          <table:table-cell office:value-type="date">
           <xsl:attribute name="office:date-value"><xsl:value-of select="@maturity"/></xsl:attribute>
           <text:p><xsl:value-of select="@maturity"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:when test="maturity">
          <table:table-cell office:value-type="date">
           <xsl:attribute name="office:date-value"><xsl:value-of select="maturity"/></xsl:attribute>
           <text:p><xsl:value-of select="maturity"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:otherwise>
          <table:table-cell>
           <text:p/>
          </table:table-cell>
         </xsl:otherwise>
        </xsl:choose>

       </table:table-row>
      </xsl:for-each>
     </table:table>
    </office:spreadsheet>
   </office:body>
  </office:document-content>
 </xsl:template>
</xsl:stylesheet>
It works here on Windows XP with OOo 3.0.0.

The XSLT import filter looks mostly like the original one. So let's focus on the crucial difference. For changing the handling of "purpose" I changed this lines:

Code: Select all

        <!-- Insert string payment purpose -->
        <table:table-cell>
         <text:p><xsl:value-of select="purpose"/></text:p>
        </table:table-cell>
to

Code: Select all

        <!-- Insert string payment purpose -->
        <xsl:choose>
         <xsl:when test="@purpose">
          <table:table-cell>
           <text:p><xsl:value-of select="@purpose"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:when test="purpose">
          <table:table-cell>
           <text:p><xsl:value-of select="purpose"/></text:p>
          </table:table-cell>
         </xsl:when>
         <xsl:otherwise>
          <table:table-cell>
           <text:p/>
          </table:table-cell>
         </xsl:otherwise>
        </xsl:choose>
The first <xsl:when test="@purpose"> checks, if the payment contains a purpose attribute and uses it, if it exists. If the payment contains no purpose attribute the second <xsl:when test="purpose"> checks, if the payment contains a purpose element and uses it, if it exists. The <xsl:otherwise> adds an empty cell, if the payment contains no purpose data at all. I did the same with amount, tax an maturity. That's all.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
coneHead
Posts: 2
Joined: Mon Nov 24, 2008 3:58 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by coneHead »

Moin hol.sten,

first thank you for your great XML filters.

The import filter works great for me after I modified it for my own needs.

I only have a strange problem with the export filter and I hope you can help me out with this.

I take your example xml file for import in OO-Calc and then save it as .ods, then I will save it with your export filter back to xml.
The basic export with your example works perfect, but when I add some columns in Calc and add this in the export filter xsl file then I get the problems.

I added three columns to the ods file in Calc, A, B, and C and also added to read these values in the export filter.

Code: Select all

     ...
      <xsl:when test="position()=5">
       <a><xsl:value-of select="text:p"/></a>
      </xsl:when> 
      <xsl:when test="position()=6">
       <b><xsl:value-of select="text:p"/></b>
      </xsl:when>
      <xsl:when test="position()=7">
       <c><xsl:value-of select="text:p"/></c>
      </xsl:when>
      ...
So far so good, but when I export the file to xml, the <b /> and <c /> tags are missing, only the empty <a /> tag is shown besides the ones that were already in your example.

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<payments>
   <payment>
      <purpose>CD</purpose>
      <amount>12,95</amount>
      <tax>19,1234</tax>
      <maturity>01.03.2008</maturity>
      <a/>
   </payment>
...
Next I inserted something in the last column (c) in the ods file. Now the exported xml file shows again an empty <a /> tag (which is correct) and then a <b> tag with the inserted data, where it should be an empty <b /> tag and the <c> tag with the data.

Image

gives:

Code: Select all

<payment>
      <purpose>DVD</purpose>
      <amount>19,95</amount>
      <tax>19,4321</tax>
      <maturity>02.03.2008</maturity>
      <a/>
      <b>Test</b>
   </payment>
When I insert data into all three columns then all three tags are shown, but only if I insert three different things, so the following...

Image

gives me:

Code: Select all

<payment>
      <purpose>DVD</purpose>
      <amount>19,95</amount>
      <tax>19,4321</tax>
      <maturity>02.03.2008</maturity>
      <a>Test</a>
   </payment>
the <b> and <c> tags don't appear at all. As I wrote, only if I insert the same data in all columns, like you see in the screenshot.

Do you have any idea how I can export all the columns to xml, no matter if I have some data in them or not? So that all tags are shown, even if they are empty.

I took your example, only modified it to read more columns.

Hope you can help me out.

Thanks ahead....
OOo 3.0.X on Ms Windows XP + MacOSX
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

coneHead wrote:Moin hol.sten,
Moin? Where are you from? Sounds very familar :-)
coneHead wrote:Hope you can help me out.
Yes, I can! Here is a tread that deals at the end with the problem you now found, too: [Solved] Complex XSLT filter: http://user.services.openoffice.org/en/ ... =9&t=11865. Be cautions with the thread subject. Due to the problem I discussed there concerning table:number-columns-repeated="...", the thread is no longer solved! Your problem is caused by table:number-columns-repeated="...", too!
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
coneHead
Posts: 2
Joined: Mon Nov 24, 2008 3:58 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by coneHead »

Thanks hol.sten.

I am German, but not from Hamburg. Just wanted you to feel like home ;)
OOo 3.0.X on Ms Windows XP + MacOSX
Katerine
Posts: 2
Joined: Tue Oct 28, 2008 2:59 am

Re: [XML Filter] Create XSLT filters for import and export

Post by Katerine »

Hi,
I have a question now. :)

I've attempted to create a custom export filter in Calc (OOo 3.0.0, Windows XP), and am now having problems.

I have an ods file (saved as ods) whose sole purpose is to easily generate an XML file... although I'm now starting to think that it would have been easier to just type the XML file from scratch. :roll: The file has no header row, and two columns. The first column has one or two letters in it, and the second column contains a number. Very simple.

My goal is to have an XML file that looks something like this:

Code: Select all

<?xml version="1.0"?>
<tileset>
 <tile id="1">
  <letter>A</letter>
  <score>1</score>
 </tile>
 <tile id="2">
  <letter>A</letter>
  <score>1</score>
 </tile>
 <tile id="3">
  <letter>A</letter>
  <score>1</score>
 </tile>
 <tile id="4">
  <letter>B</letter>
  <score>3</score>
 </tile>
</tileset>
The tile id is derived from the row number. Column 1 contains the letter, column 2 contains the score.

I've adapted an xsl page from what you provided (thanks, hol.sten!), and the xsl syntax checks out according to IE. Here it is:

Code: Select all

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

<!-- By setting the PropertyValue "URL" in the properties used in storeToURL(), -->
<!-- we can pass a single parameter to this stylesheet.                         -->
<!-- Caveat: If we use the "URL" property in the stylesheet and call in OOo     -->
<!-- from the menu "File" > "Export...", OOo assigns a target URL. And that     -->
<!-- might not be what we want.                                                 -->
<!-- <xsl:param name="targetURL"/> -->

<!-- Process the document model -->
<xsl:template match="/">
  <tileset>
   <!-- Process all tables -->
   <xsl:apply-templates select="//table:table"/>
  </tileset>
</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">
  <!-- note: changed this from "position()>1" because there are no column labels in row 1 -->
   <xsl:if test="position()>0">
    <tile>
     <xsl:attribute name="id"><xsl:value-of select="$row"/></xsl:attribute>
     <!-- Process the first two columns containing the letter and score -->
     <xsl:for-each select="table:table-cell">
      <xsl:choose>
       <xsl:when test="position()=1">
       <letter><xsl:value-of select="text:p"/></letter>
       </xsl:when>
       <xsl:when test="position()=2">
       <score><xsl:value-of select="@office:value"/></score>
       </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </tile>
   </xsl:if>
  </xsl:for-each>
</xsl:template>

</xsl:stylesheet>
I then went Tools | XML Filter Settings, New filter...
Called the new filter "CustomTileExport" (no quotes). Application "OpenOffice.org Calc (.ods)". Name of file type "Tileset". File extension "xml". Set the XSLT for export, and hit Ok.
Went into "Test XSLTs", clicked the "current document" button, and nothing happened.
Closed out of XML Filter Settings.

Went into File | Export (the option isn't there under "Save As,"). Selected the File format, "Tileset (.xml)". Gave it the file name "Tiles.xml" Hit Save. It said it was saving. And then it crashed.

Opened the ods file again. Closed it again just to make sure it was sufficiently recovered.

Opened the ods file again. Went to File | Export and did the same thing as before. It started to save, and then it gave me the following error:
Error saving the document ScrabbleTiles:
Write Error.
The file could not be written.

(note: ScrabbleTiles is the name of the original ods document, not the name I gave the xml file).

The xml file did not get exported.

A couple of notes:
  • I commented out the <!-- <xsl:param name="targetURL"/> --> line AFTER I started getting this error... that was a relatively late development, and by the time I commented it out, I'd already had the above problem many times.
  • Likewise, I also changed the <xsl:if test="position()>1"> line (to <xsl:if test="position()>0">) AFTER I started getting this error. I suddenly realized that since I had no header row, I might miss the first row if I didn't change the line.
Help please. :)
OOo 3.0.X on Ms Windows XP + Ubuntu (future)
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Katerine wrote:The xml file did not get exported.
Your XSL contains one error: You use $row although you didn't define the variable row. You somehow removed the line from my filter example in your filter.
Instead you can delete the lines <xsl:if test="position()>0"> and the corresponding </xsl:if>.

I gave it a try and this filter works for me creating the file format you described:

Code: Select all

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

<!-- By setting the PropertyValue "URL" in the properties used in storeToURL(), -->
<!-- we can pass a single parameter to this stylesheet.                         -->
<!-- Caveat: If we use the "URL" property in the stylesheet and call in OOo     -->
<!-- from the menu "File" > "Export...", OOo assigns a target URL. And that     -->
<!-- might not be what we want.                                                 -->
<!-- <xsl:param name="targetURL"/> -->

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

<xsl:template match="table:table">
 <!-- Process all table-rows -->
 <xsl:for-each select="table:table-row">

  <xsl:variable name="row"><xsl:number/></xsl:variable>

  <tile>
   <xsl:attribute name="id"><xsl:value-of select="$row"/></xsl:attribute>

   <!-- Process the first two columns containing the letter and score -->
   <xsl:for-each select="table:table-cell">
    <xsl:choose>
     <xsl:when test="position()=1">
      <letter><xsl:value-of select="text:p"/></letter>
     </xsl:when>
     <xsl:when test="position()=2">
      <score><xsl:value-of select="@office:value"/></score>
     </xsl:when>
    </xsl:choose>
   </xsl:for-each>
  </tile>
 </xsl:for-each>
</xsl:template>

</xsl:stylesheet>
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
Captain Jack
Posts: 6
Joined: Tue Mar 17, 2009 11:25 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Captain Jack »

Hi, thx for your information but I still have a problem.

I played around with the XML filter but until now I just get this result:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<payments export-date="" export-user=""/>
For me it looks like the template "table:table" will not be processed.
Can someone help me please.

I also tried to "debug" whats going on with <xsl:message>...<xsl:message/>
But if I add this to the filter then OO does nothing if I try to export my data.
OOo 3.0.X on Mac OSx Leopard + Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Captain Jack wrote:I played around with the XML filter but until now I just get this result:
It's hard to guess what might went wrong with the little information you provided. Please be more specific with what you have done, what you did try, which data you work with and what your environment looks like.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
Captain Jack
Posts: 6
Joined: Tue Mar 17, 2009 11:25 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Captain Jack »

Sorry, my environment is:
OSX Leopard, but I tried the same filter under XP.
OO Version is 3.0.1 on both systems.

The table in OO is very simple.
a b c
1 2 3
4 5 6

The filter I use is mainly based on your sample:

Code: Select all

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

<!-- By setting the PropertyValue "URL" in the properties used in storeToURL(), -->
<!-- we can pass a single parameter to this stylesheet.                         -->
<!-- Caveat: If we use the "URL" property in the stylesheet and call in OOo     -->
<!-- from the menu "File" > "Export...", OOo assigns a target URL. And that     -->
<!-- might not be what we want.                                                 -->
<xsl:param name="targetURL"/>

<xsl:variable name="exportDate">
  <xsl:choose>
   <xsl:when test="string-length(substring-before($targetURL,';'))=10">
    <xsl:value-of select="substring-before($targetURL,';')"/>
   </xsl:when>
   <xsl:when test="string-length($targetURL)=10">
    <xsl:value-of select="$targetURL"/>
   </xsl:when>
  </xsl:choose>
</xsl:variable>

<xsl:variable name="exportUser">
  <xsl:if test="string-length(substring-after($targetURL,';'))>0">
   <xsl:value-of select="substring-after($targetURL,';')"/>
  </xsl:if>
</xsl:variable>

<!-- Process the document model -->
<xsl:template match="/">
  <payments>
   <xsl:attribute name="export-date"><xsl:value-of select="$exportDate"/></xsl:attribute>
   <xsl:attribute name="export-user"><xsl:value-of select="$exportUser"/></xsl:attribute>
   <!-- Process all tables -->
   <xsl:apply-templates select="//table:table" />
  </payments>
</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:if test="position()>1">
    <payment>
     <!-- Process the first for columns containing purpose, amount, tax and maturity -->
     <xsl:for-each select="table:table-cell">
      <xsl:choose>
       <xsl:when test="position()=1">
       <purpose><xsl:value-of select="text:p"/></purpose>
       </xsl:when>
       <xsl:when test="position()=2">
       <amount><xsl:value-of select="text:p"/></amount>
      </xsl:when>
       <xsl:when test="position()=3">
       <tax><xsl:value-of select="text:p"/></tax>
      </xsl:when>
      </xsl:choose>
     </xsl:for-each>
    </payment>
   </xsl:if>
  </xsl:for-each>
</xsl:template>

</xsl:stylesheet>
I tried to export my CALC-data and I also tried "XSLTs test" under XML-Filter settings - both with the same result.
Is there a way to debug the file to see what's going on?

thx
OOo 3.0.X on Mac OSx Leopard + Windows XP
Captain Jack
Posts: 6
Joined: Tue Mar 17, 2009 11:25 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Captain Jack »

Such a simple thing an no one can help me? :(

I am wondering why there is now export option for tables as flat xml-files.
Should be really easy to implement.
OOo 3.0.X on Mac OSx Leopard + Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Captain Jack wrote:I tried the same filter under XP.
OO Version is 3.0.1 on both systems.
I gave your filter a try with OOo 3.0.0 and OOo 3.0.1 on Windos XP.
Captain Jack wrote:The table in OO is very simple.
a b c
1 2 3
4 5 6
Regardless of whatever I did, using your filter I always got

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<payments export-date="" export-user="">
   <payment>
      <purpose>1</purpose>
      <amount>2</amount>
      <tax>3</tax>
   </payment>
   <payment>
      <purpose>4</purpose>
      <amount>5</amount>
      <tax>6</tax>
   </payment>
</payments>
I have no clue why it is not working for you.
Captain Jack wrote:I tried to export my CALC-data and I also tried "XSLTs test" under XML-Filter settings - both with the same result.
Same here: Both with the same result as posted above.
Captain Jack wrote:Is there a way to debug the file to see what's going on?
No.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
Captain Jack
Posts: 6
Joined: Tue Mar 17, 2009 11:25 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Captain Jack »

Thank's for the answer.
That thing will drive me crazy.

I tried it under OSX with OO 3.0.1 - failed
XP OO 3.0.0, Java 1.6.7 - failed
XP OO 3.0.0, J 1.6.11 - failed
OOo 3.0.X on Mac OSx Leopard + Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Captain Jack wrote:Thank's for the answer.
That thing will drive me crazy.
Perhaps you can create a small example which is not working for you, save it as ODS and attach it to a post for further investigation. Just to give a file you created a try on my system.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
Captain Jack
Posts: 6
Joined: Tue Mar 17, 2009 11:25 pm

Re: [XML Filter] Create XSLT filters for import and export

Post by Captain Jack »

Great!
I added the VERY simple ods file.
I already decompressed the ods and had a look into content.xml.
For me, everything looks fine.

If I remove the select statement then the filter runs through.
It gives a weird result but at least a result is a result.

For me it has something todo with the table:table statement. Either in the
select, in the match or in the XML-document - but what?

thx
Attachments
xml-export.xml.zip
Simple xml-filter for the export
(1.25 KiB) Downloaded 1465 times
baseforfilter.ods
Simple OO file
(8 KiB) Downloaded 1799 times
OOo 3.0.X on Mac OSx Leopard + Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [XML Filter] Create XSLT filters for import and export

Post by hol.sten »

Captain Jack wrote:I added the VERY simple ods file.
I already decompressed the ods and had a look into content.xml.
For me, everything looks fine.
Yes, your file is ok. It works here like I described it above. So it's not the file itself.
Captain Jack wrote:For me it has something todo with the table:table statement. Either in the
select, in the match or in the XML-document - but what?
Last idea: There is something wrong with how you installed the XML filter. So you can try the following:
- Download the attached xml-export.jar
- Open your simple baseforfilter.ods file
- Go to "Tools" -> "XML Filter Settings..." -> Press "Open Package...", browse to the downloaded xml-export.jar and press "Open"
- Give it another try by pressing "Test XSLTs..." and "Current Document"

If it still does not work, try to save your package by pressing "Save as Package..." and attach the result in your next post.
Attachments
xml-export.jar
(2.02 KiB) Downloaded 1670 times
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
Post Reply