[XML Filter] Create XSLT filters for import and export
Posted: Thu Mar 13, 2008 11:03 pm
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:
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:
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:
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:
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:
Call the XSLT filters from OpenOffice.org menu's
To import the simple payments XML file the following steps are necessary:
Now we add a fifth record (Software / 39,95 / 19 / 05.03.2008) and export the payments XML file:
Call the XSLT filters from OpenOffice.org Basic
Importing this example payments XML file through a simple OOo Basic macro looks like this:
Exporting this example payments XML file is possible through an OOo script, too. A simple OOo Basic macro for exporting looks like this:
References
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:
- purpose: a simple text
- amount: a number with 2 decimal places
- tax: a number with 4 decimal places (although taxes with this number of decimal places are a little unrealistic)
- maturity: a date
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>
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>
Install the XSLT filters for import and export
To install our example import and export filters needs the following steps:
- Open OOo
- Call "Tools" > "XML Filter Settings..."
- Press "New..."
- 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".
- In the "Transformation" tab select the above created XSLT filters for XSLT for export and XSLT for import
- That's all, so press OK and Close
Call the XSLT filters from OpenOffice.org menu's
To import the simple payments XML file the following steps are necessary:
- Call "File" > "Open..."
- Move to the directory where the XML file payments.xml is saved (for example C:\Temp)
- Select the File type "Payments (*.xml)" (third drop down box)
- Select the XML file payments.xml
- Press Open
Now we add a fifth record (Software / 39,95 / 19 / 05.03.2008) and export the payments XML file:
- Call "File" > "Save As..."
- Move to the directory where the XML file payments.xml is saved (for example C:\Temp)
- Select the File type "Payments (*.xml)" (second drop down box)
- Enter the File name "payments-export-menu.xml"
- 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
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