[XML Filter] Create XSLT filters for import and export

[Captain Jack]

T H A N K S!

This was the answer.
My filter was installed under: /Volumes/temp/xml-export.xml
/Volumes/temp is a mounted networkfolder.

For me everything was OK because OO let me choose the file from my harddisk and
that was exactly what I did.
And the script worked - at least a part of it.

If I install it with your JAR File the filter goes to
/Users/xxxmynamexxx/Library/Application Support/OpenOffice.org/3/user/xslt/xml-export/xml-export.xml
That seems to be the right place - at leat on the Mac.

Thanks again for your help.

[btoplak]

Does anyone else have problem to get this examples (primary the IMPORT part) to work in OOo 3.1 ?? ... or am I missing something because I simply cannot get it work by simply copy-pasting XML and Import XSL to files. Is there a file format needed (UTF8-DOS / UTF8-UNIX) ?

[juijui]

I'm trying to help my brother use calc for xml and this is nuts. It takes 30 seconds to open an xml file in Excel.

Isn't there a simpler way to do this in ooo calc?

[hol.sten]

Isn't there a simpler way to do this in ooo calc?

I don't think so.

[sanchou]

Hello,

I try to adapt the XSLT provided in this guide for my use but I have a problem.

My export XSLT allows me to get a good exported XML:

- XSLT :

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="/">
      <p:rulesLibary xmlns:p="http:///my_address/resources/rules.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http:///my_address/resources/rules.xsd rules.xsd " >
       <!-- Process all tables -->
       <xsl:apply-templates select="//table:table"/>
      </p:rulesLibary>
    </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">
        <rule>
         <!-- Process the first for columns containing ruleName, ruleArgument, ruleFqn and property -->
         <xsl:for-each select="table:table-cell">
          <xsl:choose>
           <xsl:when test="position()=1">
           	<ruleFqn><xsl:value-of select="text:p"/></ruleFqn>
           </xsl:when>
           <xsl:when test="position()=2">
           	<ruleName><xsl:value-of select="text:p"/></ruleName>
           </xsl:when>
           <xsl:when test="position()=3">
           	<ruleArgument><xsl:value-of select="text:p"/></ruleArgument>
           </xsl:when>
          </xsl:choose>
         </xsl:for-each>
        </rule>
       </xsl:if>
      </xsl:for-each>
    </xsl:template>

    </xsl:stylesheet>

- Exported XML (well generated):

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<p:rulesLibary xmlns:p="http:///my_address/resources/rules.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http:///my_address/resources/rules.xsd rules.xsd ">
<rule>
<ruleFqn>aaa</ruleFqn>
<ruleName>bbb</ruleName>
<ruleArgument>ccc</ruleArgument>
</rule>
<rule>
<ruleFqn>ddd</ruleFqn>
<ruleName>eee</ruleName>
<ruleArgument>fff ggg</ruleArgument>
</rule>
</p:rulesLibary>

But I can't open this XML file with my import XSLT. Only the first line describing each column is imported.

My import XSLT:

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 10 cm width, co2: column with 8 cm width) -->
        <style:style style:name="co1" style:family="table-column">
         <style:table-column-properties fo:break-before="auto" style:column-width="10.000cm"/>
        </style:style>
        <style:style style:name="co2" style:family="table-column">
         <style:table-column-properties fo:break-before="auto" style:column-width="8.000cm"/>
        </style:style>

       </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="Default"/>
          
          <!-- Insert column labels, first label with default style, the remaining 3 labels right aligned -->
          <table:table-row>
           <table:table-cell><text:p>ruleFqn</text:p></table:table-cell>
           <table:table-cell><text:p>ruleName</text:p></table:table-cell>
           <table:table-cell><text:p>ruleArgument</text:p></table:table-cell>
          </table:table-row>

          <!-- Process XML input: Insert one row for each rule -->
          <xsl:for-each select="p:rulesLibrary" xmlns:p="http:///my_address/resources/rules.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http:///my_address/resources/rules.xsd rules.xsd " >
           <xsl:for-each select="rule">
           <table:table-row>

            <!-- Insert string rule fqn -->
            <table:table-cell>
             <text:p><xsl:value-of select="ruleFqn"/></text:p>
            </table:table-cell>

	    <!-- Insert string rule name -->
            <table:table-cell>
             <text:p><xsl:value-of select="ruleName"/></text:p>
            </table:table-cell>

	    <!-- Insert string rule arg -->
            <table:table-cell>
             <text:p><xsl:value-of select="ruleArgument"/></text:p>
            </table:table-cell>

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

Do you see what is the problem with my import XSLT?
Can you help me?

Thanks in advance,
Nicolas

[hol.sten]

Do you see what is the problem with my import XSLT?

It's only a typo! Your XML export filter creates the root tag <p:rulesLibary...>, although the root tag should most obviously be <p:rulesLibrary...>, because that is the expected root tag of your import filter.

[eBookLuke]

Hi all,

I need to add a couple of .xsl filters in my OOo extension.
Someone can suggest to me how can I do to install a xsl file in OOo from a basic macro?

Thanks

Luke

[hol.sten]

I need to add a couple of .xsl filters in my OOo extension.
Someone can suggest to me how can I do to install a xsl file in OOo from a basic macro?

No, I've never done anything like that. But it is always a much better idea to start a new thread for a new problem. Give that a try. Perhaps you'll get then a more useful response.

[chadkelly]

As a Ubuntu 10.04 x64 users (clean install), all the examples provided above worked perfectly, ONLY after installing the following from synaptic package manager: jodconverter (which will automatically install libjodconverter-java, openoffice.org, openoffice.org-filter-binfilter, openoffice.org-filter-mobiledev). However, I wasted 8 hours of company time in the process of trying to discover this additional requirement.

[akendrick451]

@chadkelly, thankyou so much for your help, got mine working after installing jodconverter also! Wonder why no mention of this is made in the OO documentation - or did I just miss it? Thanks again

[JD4x4]

Well I thought I was making progress in my understanding of both OO and XSLT after having found this thread, but no joy.
Using the examples I get a blank spreadsheet. When I test the xslt I get this output (on two lines..this was copied from the output window and 'tidied' with TidyCOM):

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<office:document-content xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" office:version="1.0">
	<office:automatic-styles>
		<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: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>
		<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>
				<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"/>
				<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>
				<table:table-row>
					<table:table-cell>
						<text:p>CD</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="12.95">
						<text:p>12.95</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="19.1234">
						<text:p>19.1234</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="date" office:date-value="2008-03-01">
						<text:p>2008-03-01</text:p>
					</table:table-cell>
				</table:table-row>
				<table:table-row>
					<table:table-cell>
						<text:p>DVD</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="19.95">
						<text:p>19.95</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="19.4321">
						<text:p>19.4321</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="date" office:date-value="2008-03-02">
						<text:p>2008-03-02</text:p>
					</table:table-cell>
				</table:table-row>
				<table:table-row>
					<table:table-cell>
						<text:p>Clothes</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="99.95">
						<text:p>99.95</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="18.5678">
						<text:p>18.5678</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="date" office:date-value="2008-03-03">
						<text:p>2008-03-03</text:p>
					</table:table-cell>
				</table:table-row>
				<table:table-row>
					<table:table-cell>
						<text:p>Book</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="9.49">
						<text:p>9.49</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="float" office:value="18.9876">
						<text:p>18.9876</text:p>
					</table:table-cell>
					<table:table-cell office:value-type="date" office:date-value="2008-03-04">
						<text:p>2008-03-04</text:p>
					</table:table-cell>
				</table:table-row>
			</table:table>
		</office:spreadsheet>
	</office:body>
</office:document-content>

In the original XML Filter output window <?xml version="1.0" encoding="UTF-8"?> is the first line and everything else is on the second line.

Using the "Validate" button when in the XML Filter output window returns:
2: Document is invalid:No grammer found.
2: Document root element "office:document-content", must match DOCTYPE root "null".

I'm afraid I don't know enough about XSL to understand what's missing here, but I suspect it's either something simple or it's an issue with OO 3.2.1?

[rudolfo]

Your code section is a bit irritating: it is the xml content that should be transformed, not the xslt file that you have put together. But i am pretty sure the "No grammar found" is a warning that you get when you try to validate the XSLT stylesheet. I rember that I always received this when trying to validate the xslt file with Ant.
I used Ant wihtin Emacs. I never got it sorted out, but I don't need it anymore as I figured out that the nxml-mode that comes with Emacs-23 includes a Relax NG scheme that works like a charm when it comes to validating xslt. (Okay, Eclipse is probably also able to validate XSLT, but my limited memory (512MB) told me "It's either Eclipse or OpenOffice, but not both of them!")

I am pretty sure that your warning messages are originated in the jar files with the parsing libraries. As far as I know anyting related with XSLT is more or less immediately handed over to the standard java libraries xalan, sax and friends that do the parsing and validation in all other Java applications, as well.

[JD4x4]

rudolfo- Thanks for the reply..I think.
What I posted was the output (after the transformation) that is supposed to be the spreadsheet that Calc loads in. The messages come from the 'Test XSLTs' button in Calc's 'XML Filter settings' dialog.

I know nothing about Ant, Emacs, xalan, or sax! Way over my head. I do know a tiny bit about XML & transforms, but not enough to know what it is about this spreadsheet structure that Calc doesn't like. When I run the filter on the XMl file from Calc, I get no error messages, but nothing populates the spreadsheet, although the name changes to the name of the XML file I'm trying to import. The XML sample file and the Import XSLT 'filter' were cut & paste from the sample code at the beginning of this thread.

[JD4x4]

Woo Hoo! I'm an idiot .. but my issue is solved!!
I mistakenly had the import filter set to type OpenOffice.org Calc (.xsc) rather than OpenOffice.org Calc (.ods)!!
Works brilliantly, and I've even managed to write a working filter for another xml type variant. Thank you SO much hol.sten for the example. Pieces of the puzzle are starting to fall into place for me.

[hol.sten]

but my issue is solved!!

Thank you for your confirmation! I didn't had the time to give the filter a new test with OOo 3.2.1. But reading your last post in this thread, it now seems no longer necessary.

[giorg]

Hi,

just tried your example (OOo 3.3 on Mac) and it works, even if in the source I get (as somebody else stated before):

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

Now my question. I have a little more complex xml, like this:

Code: Select all

<?xml version="1.0"?>
<certlist>
  <cert>
    <prop1>value1</prop1>
    <prop2>value2</prop2>
    <achne>
       <prop3>value3</prop3>
       <prop4>value4</prop4>
    </achne>
    <prop5>value5</prop5>
  </cert>
</certlist>

Now, I imagine i should put a foreach in order to get every cert, but how to get the properties inside <achne>? I tried with:

Code: Select all

<xsl:template match="achne">

but with no luck, my ods file is empty...

Thanks a lot

[hol.sten]

Now my question. I have a little more complex xml, like this:

I expanded your example to this:

Code: Select all

<?xml version="1.0"?>
<certlist>
  <cert>
    <prop1>cert1 prop1</prop1>
    <prop2>cert1 prop2</prop2>
    <achne>
      <prop3>cert1 prop3 of achne</prop3>
      <prop4>cert1 prop4 of achne</prop4>
    </achne>
    <prop5>cert1 prop5</prop5>
  </cert>
  <cert>
    <prop1>cert2 prop1</prop1>
    <prop2>cert2 prop2</prop2>
    <achne>
      <prop3>cert2 prop3 of achne</prop3>
      <prop4>cert2 prop4 of achne</prop4>
    </achne>
    <prop5>cert2 prop5</prop5>
  </cert>
  <cert>
    <prop1>cert3 prop1</prop1>
    <prop2>cert3 prop2</prop2>
    <achne>
      <prop3>cert3 prop3 of achne</prop3>
      <prop4>cert3 prop4 of achne</prop4>
    </achne>
    <prop5>cert3 prop5</prop5>
  </cert>
</certlist>

and saved it as certlist.xml.

Then I slightly modified my import filter to this:

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 5 columns of the table -->
        <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
        <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
        <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
        <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
        <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>

        <!-- Insert column labels -->
        <table:table-row>
         <table:table-cell><text:p>prop1</text:p></table:table-cell>
         <table:table-cell><text:p>prop2</text:p></table:table-cell>
         <table:table-cell><text:p>achne/prop3</text:p></table:table-cell>
         <table:table-cell><text:p>achne/prop4</text:p></table:table-cell>
         <table:table-cell><text:p>prop5</text:p></table:table-cell>
        </table:table-row>

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

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

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

          <!-- Insert string cert achne/prop3 -->
          <table:table-cell>
           <text:p><xsl:value-of select="achne/prop3"/></text:p>
          </table:table-cell>

          <!-- Insert string cert achne/prop4 -->
          <table:table-cell>
           <text:p><xsl:value-of select="achne/prop4"/></text:p>
          </table:table-cell>

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

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

and saved it as certlist.xml.

The stylsheet certlist.xsl used in LibreOffice or OpenOffice.org as Calc import filter applied to the file certlist.xml creates a spreadsheet like this:

Code: Select all

+--------------+---------------+------------------------+------------------------+--------------+
I prop1        I  prop2        I  achne/prop3           I  achne/prop4           I  prop5       I
+--------------+---------------+------------------------+------------------------+--------------+
I cert1 prop1  I  cert1 prop2  I  cert1 prop3 of achne  I  cert1 prop4 of achne  I  cert1 prop5 I
+--------------+---------------+------------------------+------------------------+--------------+
I cert2 prop1  I  cert2 prop2  I  cert2 prop3 of achne  I  cert2 prop4 of achne  I  cert2 prop5 I
+--------------+---------------+------------------------+------------------------+--------------+
I cert3 prop1  I  cert3 prop2  I  cert3 prop3 of achne  I  cert3 prop4 of achne  I  cert3 prop5 I
+--------------+---------------+------------------------+------------------------+--------------+

[giorg]

Hi hol,

first of all thank you very much for your answer. I'm still stuck, cannot understand what I am doing wrong. This is my xslt:

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:year number:style="long"/>
     <number:text>-</number:text>
     <number:month number:style="long"/>
     <number:text>-</number:text>
     <number:day 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>

      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>
      <table:table-column table:style-name="co1" table:default-cell-style-name="Default"/>

      <table:table-row>
       <table:table-cell><text:p>Codice fiscale azienda</text:p></table:table-cell>
       <table:table-cell><text:p>Codice sede</text:p></table:table-cell>
       <table:table-cell><text:p>Id certificato</text:p></table:table-cell>
       <table:table-cell><text:p>Cod Fisc Medico</text:p></table:table-cell>
       <table:table-cell><text:p>Cognome Medico</text:p></table:table-cell>
       <table:table-cell><text:p>Nome Medico</text:p></table:table-cell>
       <table:table-cell><text:p>Cod Reg Medico</text:p></table:table-cell>
       <table:table-cell><text:p>Cod Asl Medico</text:p></table:table-cell>
       <table:table-cell><text:p>Cod Fisc lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Cognome lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Nome lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Sesso lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Data nascita lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Comune nascita lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Prov nascita lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>via res lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>cap res lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Comune res lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Prov res lavoratore</text:p></table:table-cell>
       <table:table-cell><text:p>Data rilascio</text:p></table:table-cell>
       <table:table-cell><text:p>Data inizio</text:p></table:table-cell>
       <table:table-cell><text:p>Data fine</text:p></table:table-cell>
       <table:table-cell><text:p>Tipo</text:p></table:table-cell>
      </table:table-row>

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

        <table:table-cell>
         <text:p><xsl:value-of select="codFiscAzienda"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="codSede"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="idCertificato"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="medico/codiceFiscale"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="medico/cognome"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="medico/nome"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="medico/codiceRegione"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="medico/codiceAsl"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/codiceFiscale"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/cognome"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/nome"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/sesso"/></text:p>
        </table:table-cell>

        <table:table-cell office:value-type="date">
         <xsl:attribute name="office:date-value"><xsl:value-of select="lavoratore/dataNascita"/></xsl:attribute>
         <text:p><xsl:value-of select="lavoratore/dataNascita"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/comuneNascita"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="lavoratore/provinciaNascita"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="residenza/via"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="residenza/cap"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="residenza/comune"/></text:p>
        </table:table-cell>

        <table:table-cell>
         <text:p><xsl:value-of select="residenza/provincia"/></text:p>
        </table:table-cell>

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

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

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

        <table:table-cell>
         <text:p><xsl:value-of select="tipoCertificato"/></text:p>
        </table:table-cell>

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

and this is my xml to import:

Code: Select all

  <?xml version="1.0"?>
- <listaAttestati>
- <attestato>
  <codFiscAzienda>80022230371</codFiscAzienda> 
  <codSede>00000</codSede> 
  <idCertificato>2759360</idCertificato> 
- <medico>
  <codiceFiscale>RSNSNO57A53A944J</codiceFiscale> 
  <cognome>ORSONI</cognome> 
  <nome>SONIA</nome> 
  <codiceRegione>000</codiceRegione> 
  <codiceAsl>000</codiceAsl> 
  </medico>
- <lavoratore>
  <codiceFiscale>LZZNNL68B42A558J</codiceFiscale> 
  <cognome>LAZZARONI</cognome> 
  <nome>ANTONELLA</nome> 
  <sesso>F</sesso> 
  <dataNascita>1968-02-02</dataNascita> 
  <comuneNascita>A558</comuneNascita> 
  <provinciaNascita>BO</provinciaNascita> 
  </lavoratore>
- <residenza>
  <via>DELLA FERRIERA 8</via> 
  <cap>40133</cap> 
  <comune>A944</comune> 
  <provincia>BO</provincia> 
  </residenza>
  <dataRilascio>2011-01-04</dataRilascio> 
  <dataInizio>2011-01-03</dataInizio> 
  <dataFine>2011-01-05</dataFine> 
  <tipoCertificato>I</tipoCertificato> 
  </attestato>
- <attestato>
  <codFiscAzienda>80022230371</codFiscAzienda> 
  <codSede>00000</codSede> 
  <idCertificato>2726039</idCertificato> 
- <medico>
  <codiceFiscale>BLLMLV55D44A944U</codiceFiscale> 
  <cognome>BOLELLI</cognome> 
  <nome>MILVIA</nome> 
  <codiceRegione>000</codiceRegione> 
  <codiceAsl>000</codiceAsl> 
  </medico>
- <lavoratore>
  <codiceFiscale>RMGPRZ61E43A944X</codiceFiscale> 
  <cognome>ROMAGNOLI</cognome> 
  <nome>PATRIZIA</nome> 
  <sesso>F</sesso> 
  <dataNascita>1961-05-03</dataNascita> 
  <comuneNascita>A944</comuneNascita> 
  <provinciaNascita>BO</provinciaNascita> 
  </lavoratore>
- <residenza>
  <via>VIA DEL BECCACCINO</via> 
  <cap>40133</cap> 
  <comune>A944</comune> 
  <provincia>BO</provincia> 
  </residenza>
  <dataRilascio>2011-01-03</dataRilascio> 
  <dataInizio>2011-01-03</dataInizio> 
  <dataFine>2011-01-04</dataFine> 
  <tipoCertificato>I</tipoCertificato> 
  </attestato>
- <attestato>
  <codFiscAzienda>80022230371</codFiscAzienda> 
  <codSede>00000</codSede> 
  <idCertificato>1157760</idCertificato> 
- <medico>
  <codiceFiscale>RMGGCR45R28A944K</codiceFiscale> 
  <cognome>ROMAGNOLI</cognome> 
  <nome>GIANCARLO</nome> 
  <codiceRegione>000</codiceRegione> 
  <codiceAsl>000</codiceAsl> 
  </medico>
- <lavoratore>
  <codiceFiscale>SCRLNE69A42D643Q</codiceFiscale> 
  <cognome>SCARCELLA</cognome> 
  <nome>ELENA</nome> 
  <sesso>F</sesso> 
  <dataNascita>1969-01-02</dataNascita> 
  <comuneNascita>D643</comuneNascita> 
  <provinciaNascita>FG</provinciaNascita> 
  </lavoratore>
- <residenza>
  <via>VIA EMILIA LEVANTE 100</via> 
  <cap>40139</cap> 
  <comune>A944</comune> 
  <provincia>BO</provincia> 
  </residenza>
  <dataRilascio>2010-10-26</dataRilascio> 
  <dataInizio>2010-10-25</dataInizio> 
  <dataFine>2011-01-23</dataFine> 
  <tipoCertificato>C</tipoCertificato> 
  </attestato>
- <attestato>
  <codFiscAzienda>80022230371</codFiscAzienda> 
  <codSede>00000</codSede> 
  <idCertificato>2622443</idCertificato> 
- <medico>
  <codiceFiscale>MROMRA52C51E135H</codiceFiscale> 
  <cognome>MORI</cognome> 
  <nome>MARA</nome> 
  <codiceRegione>000</codiceRegione> 
  <codiceAsl>000</codiceAsl> 
  </medico>
- <lavoratore>
  <codiceFiscale>SCRNLS61M49B969J</codiceFiscale> 
  <cognome>SCARPELLINI</cognome> 
  <nome>ANNALISA</nome> 
  <sesso>F</sesso> 
  <dataNascita>1961-08-09</dataNascita> 
  <comuneNascita>B969</comuneNascita> 
  <provinciaNascita>BO</provinciaNascita> 
  </lavoratore>
- <residenza>
  <via>VIA CAMPOFERRAIO 42</via> 
  <cap>40046</cap> 
  <comune>A558</comune> 
  <provincia>BO</provincia> 
  </residenza>
  <dataRilascio>2010-12-28</dataRilascio> 
  <dataInizio>2010-10-15</dataInizio> 
  <dataFine>2011-01-15</dataFine> 
  <tipoCertificato>C</tipoCertificato> 
  </attestato>
  </listaAttestati>

I still get an empty sheet. Can u tell me what I am doing wrong?

Vielen dank

[giorg]

By the way I've just realized that your example works if I test it from the filter xml dialog, but if I do file -> open it opens Writer... How does OOo know that it should open calc instead?

Thanks a lot

[hol.sten]

I still get an empty sheet. Can u tell me what I am doing wrong?

First: Your import filter contains no error. I downloaded it from your post together with the example xml file. Then I installed the xsl import filter like I described it in my first post in this thread in the section Install the XSLT filters for import and export. Finally I loaded your example xml like I described it in my first post in this thread in the section Call the XSLT filters from OpenOffice.org menu's and got one headline in the spreadsheet table and for data rows.

By the way I've just realized that your example works if I test it from the filter xml dialog, but if I do file -> open it opens Writer... How does OOo know that it should open calc instead?

In my opinion all you have to do is to verify how you did install your xsl filter. Follow my instructions and your problems from both of your last two previous posts should be solved.

[giorg]

alles klar,

everything solved

Thanks a lot

[hol.sten]

everything solved

Would be good to know how you finally solved your problem and which part of my description was hard to understand so that you needed an additional pointer.

[giorg]

The problem was not in the filter but in the original xml: there were many - symbols on the first column, outside any xml tag, I did not realize that was not the folding/unfolding option of my editor... sorry.

[hol.sten]

The problem was not in the filter but in the original xml: there were many - symbols on the first column, outside any xml tag, I did not realize that was not the folding/unfolding option of my editor

You can check whether an XML document is well formed or not simply by opening it with a web browser like Firefox. If the XML document is well formed, it's structure is displayed in Firefox, otherwise you get an error pointing to the row and column of the error.

[djupton9]

I would like to be able to use Open Office to import an XML file as a spreadsheet. I was able to do this with Microsoft Excel but now am no longer able to use their trial version

The XML file I would like to import is located at http://www.nationstates.net/pages/regions.xml.gz. It is quite large so I will include a sample of the XML file here,

Code: Select all

<?xml version="1.0" encoding="iso-8859-1" ?> 
- <REGIONS api_version="1">
- <REGION>
  <NAME>Dhara</NAME> 
- <FACTBOOK>
- <![CDATA[ 
Rules & Consequences

1) No offensive language - I will eject and ban you if you violate it.

  ]]> 
  </FACTBOOK>
  <NUMNATIONS>4</NUMNATIONS> 
  <NATIONS>eissori:haddasa_grace:roxzors:lost_empire</NATIONS> 
  <DELEGATE>0</DELEGATE> 
  <DELEGATEVOTES>0</DELEGATEVOTES> 
  <FOUNDER>eissori</FOUNDER> 
  <POWER>Low</POWER> 
  <FLAG /> 
  </REGION>
</REGIONS>

I tried to use the XSLT stylesheet in this topic, changing a few things. The XLST stylesheet I made is as follows,

Code: Select all

    <?xml version="1.0" encoding="iso-8859-1"?>
    <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 11 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"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce4"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce5"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce6"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce7"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce8"/>
	  <table:table-column table:style-name="co2" table:default-cell-style-name="ce9"/>

          <!-- Insert column labels, first label with default style, the remaining 10 labels right aligned -->
          <table:table-row>
           <table:table-cell><text:p>Name</text:p></table:table-cell>
           <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Name</text:p></table:table-cell>
           <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Factbook</text:p></table:table-cell>
           <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Numnations</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Nations</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Delegate</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Delegatevotes</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Founder</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Power</text:p></table:table-cell>
	   <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Flag</text:p></table:table-cell>
          </table:table-row>

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

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

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

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

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

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

	     <!-- Insert float region delegatevotes -->
            <table:table-cell office:value-type="float">
             <xsl:attribute name="office:value"><xsl:value-of select="delegatevotes"/></xsl:attribute>
             <text:p><xsl:value-of select="delegatevotes"/></text:p>
            </table:table-cell>
           
	    <!-- Insert string region founder -->
            <table:table-cell>
             <text:p><xsl:value-of select="founder"/></text:p>
            </table:table-cell>

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

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

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

I tried to use this in Open Office, yet there was no result. It continued to open the XML file in the writer. I have no idea what may be wrong in this XSLT stylesheet since I do not understand any of the code.

I would appreciate it very much if someone can identify the errors, or alternatively create a working XSLT stylesheet and post it here.

Thanks.

[mallorn]

Hi,
My xml file to be imported looks a little different:
the rows names are as described in one of the examples, but the values are right next to them.
Can anyone help me with Calc import filter?

Code: Select all

  <?xml version="1.0" encoding="UTF-8" standalone="no" ?> 
- <SPCSInhouse xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Tables>
- <Table Name="Customer">
- <Record>
  <RecordCol Name="CustomerNo">100</RecordCol> 
  <RecordCol Name="Name">CUSTOMER NAME1</RecordCol> 
  <RecordCol Name="Address1">Street No.1</RecordCol>
  <RecordCol Name="Address2" xsi:nil="true" /> 
  <RecordCol Name="PostCode">100 10</RecordCol> 
  <RecordCol Name="PostOffice">NY</RecordCol> 
  <RecordCol Name="PaymentTermsCode">10</RecordCol> 
  <RecordCol Name="CurrencyCode">USD</RecordCol> 
  <RecordCol Name="LanguageCode">EN</RecordCol> 
  <RecordCol Name="Reminder">T</RecordCol> 
  <RecordCol Name="ReminderCharge">T</RecordCol> 
  <RecordCol Name="InterestInvoice">T</RecordCol> 
  <RecordCol Name="HandlingCharge">F</RecordCol> 
  <RecordCol Name="Rest">F</RecordCol> 
  <RecordCol Name="Export">F</RecordCol> 
  <RecordCol Name="InvoiceDate">2000-12-31</RecordCol> 
  <RecordCol Name="Freight">F</RecordCol> 
  </Record>
  
- <Record>
  <RecordCol Name="CustomerNo">101</RecordCol> 
  <RecordCol Name="Name">CUSTOMER NAME2</RecordCol> 
  <RecordCol Name="Address1">Street No.2</RecordCol>
  <RecordCol Name="Address2" xsi:nil="true" /> 
  <RecordCol Name="PostCode">100 11</RecordCol> 
  <RecordCol Name="PostOffice">CA</RecordCol> 
  <RecordCol Name="PaymentTermsCode">10</RecordCol> 
  <RecordCol Name="CurrencyCode">USD</RecordCol> 
  <RecordCol Name="LanguageCode">EN</RecordCol> 
  <RecordCol Name="Reminder">T</RecordCol> 
  <RecordCol Name="ReminderCharge">T</RecordCol> 
  <RecordCol Name="InterestInvoice">T</RecordCol> 
  <RecordCol Name="HandlingCharge">F</RecordCol> 
  <RecordCol Name="Rest">F</RecordCol> 
  <RecordCol Name="Export">F</RecordCol> 
  <RecordCol Name="InvoiceDate">2010-12-31</RecordCol> 
  <RecordCol Name="Freight">F</RecordCol> 
  </Record>
  </Table>
  </Tables>

Thanks

[BestiaH]

Hi there!!

Very good post, but I don't achieve to make it work... I have followed all the steps being careful of not copying the space, but when I try to import the xml after click open button I have a general input/output error, it looks like some OO error

Do you know what is the problem?? How can I fix it??

[ronny.olsson]

If you add the attribute name to the table tag you can make the sheet name be what you like:
<table:table table:name="test">
will give the sheet name test

More OASIS stuff here:
http://www.schemacentral.com/sc/odf/ss.html

[Beevo]

Thanks so much for that very well illustrated tutorial. I followed it and it worked first time! I really appreciate all the time you put into making it easy to understand and follow.

Just one thing you might help me with (and others perhaps) if you have the time; The reason I'm using this method is to make it easier for a friend of mine to update an xml that is used on their website called from a flash swf (so i can't change the format of the xml) (although the file is small he's not the kind to be filling in the text in between the markup - so I'm trying to make it simple for him to edit the file for upload)

In this case it uses more attributes than elements. And I don't know how to make the xlst files (import and export) to cycle through the attributes. Here is the format of this simple xml
<?xml version="1.0" encoding="utf-8"?>
<News fileDate="">
<newsItem newsdate="" headline="headline 1 Goes Here" itemtxt="News Blurb 1 Goes here" />
<newsItem newsdate="" headline="headline 2 Goes Here" itemtxt="News Blurb 2 Goes here" />
<newsItem newsdate="" headline="headline 3 Goes Here" itemtxt="News Blurb 3 Goes here" />
<newsItem newsdate="" headline="headline 4 Goes Here" itemtxt="News Blurb 4 Goes here" />
</News>

I would also like to auto populate the fileDate attrib with a date and time stamp (and read it on the way in of course)
You've already done a great job and if you've no time I'll understand. But if you can manage to demo how to cycle through the attributes in the xlst files that would be great

If i succeed, my next challange will be to use a script to upload the file from within Calc via ftp!
Thanks again.

[SRan]

I'm writing a spreadsheet import filter based on the examples in the first post.
The xml file's structure looks like this:

Code: Select all

<?xml version='1.0' encoding='UTF-8'?>
<dfxml xmloutputversion='1.0'>
  <fileobject>
    <filename>f244471624.ts</filename>
    <filesize>472915968</filesize>
    <byte_runs>
      <byte_run offset='0' img_offset='125379207168' len='472915968'/>
    </byte_runs>
  </fileobject>
</dfxml>

This is the process part of my filter:

Code: Select all

      <!-- Process XML input: Insert one row for each -->
      <xsl:for-each select="dfxml/fileobject">
       <table:table-row>
        <!-- Insert string filename -->
        <table:table-cell>
         <text:p><xsl:value-of select="filename"/></text:p>
        </table:table-cell>
        <!-- Insert string filesize -->
        <table:table-cell>
         <text:p><xsl:value-of select="filesize"/></text:p>
        </table:table-cell>
        <!-- Insert string offset -->
        <table:table-cell>
         <text:p><xsl:value-of select="byte-runs/@img_offset"/></text:p>
        </table:table-cell>
        <!-- Insert string length -->
        <table:table-cell>
         <text:p><xsl:value-of select="byte-runs/@len"/></text:p>
        </table:table-cell>
      </table:table-row>
      </xsl:for-each>

It reads the first two columns but I don't know the syntax for columns 3 and 4 (byte_runs).
Can someone hint how to read those?

-sam