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