[Solved] Problem Opening an XML File in Calc

Discuss the spreadsheet application

[Solved] Problem Opening an XML File in Calc

Postby sophos » Mon Oct 12, 2009 3:56 am

I am using Open Office v3.1 on Windows Vista 32b
I followed the tutorial posted here completely and found if very informative.
However, when I open an XML file, nothing happens. It just opens a blank spreadsheet.

I have found this has been an issue for at least 3 other people (here, here, and here), with no answer in sight. I can speculate that it could be a problem with this version of Open Office, but it could be that I'm doing something wrong.

What I've tried:
-hol.sten's tutorial
-several other tutorials
-several other xml files with their corresponding xlst files
-learning how to write xml and an xlst file myself, then making one using this tutorial
-tested and made sure my files worked fine with this XSLT file tester

Steps I took to open my xml file:
1) set up xlst filter
Tools > XML Filter Settings... > New >
Filter name: EVE marketstat_xml
Application: OpenOffice.org Calc (.ods)
Name of file type: EVE marketstat_xml
File extension: xml
XSLT for import: D:\EVE dumps\EVE Market Dumps\marketstat.xslt
2) open xml file
File > Open >
-selected EVE marketstat_xml from drop down list
-navigated to marketstat.xml
-hit open
-a blank spreadsheet named marketstat.xml opens
-???

Here is my XML file:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<!-- Automatically generated data from EVE-Central.com -->
<!-- This is the new API :-) -->
<evec_api version="2.0" method="marketstat_xml">
<marketstat>



<type id="34">

  <all>
    <volume>44535858756</volume>
<avg>3.32005454545</avg>
<max>48.0</max>
<min>0.3</min>
<stddev>2.03123599286</stddev>
<median>3.0</median>

  </all>
  <buy>
    <volume>13622049998</volume>
<avg>2.51534152346</avg>
<max>2.75</max>
<min>0.3</min>
<stddev>0.525498565054</stddev>
<median>2.59</median>

  </buy>
  <sell>
    <volume>30913808758</volume>
<avg>3.32396375443</avg>
<max>48.0</max>
<min>2.69</min>
<stddev>2.14567907</stddev>
<median>3.21</median>

  </sell>
</type>



<type id="35">

  <all>
    <volume>8311419936</volume>
<avg>4.32410822511</avg>
<max>30.0</max>
<min>1.01</min>
<stddev>2.4816563396</stddev>
<median>4.2</median>

  </all>
  <buy>
    <volume>5597452174</volume>
<avg>3.86002751794</avg>
<max>4.6</max>
<min>1.01</min>
<stddev>0.683825842835</stddev>
<median>3.765</median>

  </buy>
  <sell>
    <volume>2713967762</volume>
<avg>4.79337253657</avg>
<max>30.0</max>
<min>3.0</min>
<stddev>3.16653968577</stddev>
<median>4.3</median>

  </sell>
</type>


</marketstat>
</evec_api>



Here is my XLST file:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
  <html>
  <body>
  <h2>Market Stats</h2>
    <table border="0">
      <tr>
        <th>typeID</th>
        <th>all</th>
        <th>buy</th>
        <th>sell</th>
      </tr>
      <xsl:for-each select="evec_api/marketstat/type">
      <tr>
        <td><xsl:value-of select="@id"/></td>
        <td><xsl:value-of select="all/avg"/></td>
        <td><xsl:value-of select="buy/avg"/></td>
        <td><xsl:value-of select="sell/avg"/></td>
      </tr>
      </xsl:for-each>
    </table>
  </body>
  </html>
</xsl:template>
</xsl:stylesheet>



If someone can tell me why this and other XMLs are not opening correctly, I would love to hear the answer. Meanwhile, I'm going to try installing older versions of Open Office.
Last edited by sophos on Mon Oct 12, 2009 5:57 pm, edited 1 time in total.
OpenOffice 3.1 on Windows Vista
sophos
 
Posts: 1
Joined: Mon Oct 12, 2009 3:47 am

Re: Problem Opening an XML File in Calc

Postby hol.sten » Mon Oct 12, 2009 11:51 am

sophos wrote:If someone can tell me why this and other XMLs are not opening correctly

Well, the problem seems to be that you didn't take a closer look at my XSLT import filter example. A working XSLT import filter for the columns @id, all/avg, buy/avg and sell/avg looks like this:
Code: Select all   Expand viewCollapse view
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<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 2 cm width, co2: 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="2.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="ce2"/>
      <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="ce2"/>

      <!-- Insert column labels, first label with default style, the remaining 3 labels right aligned -->
      <table:table-row>
       <table:table-cell><text:p>ID</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>All average</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Buy average</text:p></table:table-cell>
       <table:table-cell table:style-name="ce1" office:value-type="string"><text:p>Sell average</text:p></table:table-cell>
      </table:table-row>

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

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

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

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

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

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

This XSLT import filter creates this OOo Calc spreadsheet from your Evec API XML file:
evec_api.png
evec_api.png (5.75 KiB) Viewed 7389 times


All your XSLT filter does is to create a simple HTML document. But OOo Calc can do nothing with HTML, so the resulting spreadsheet is empty.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
 
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [Solved] Problem Opening an XML File in Calc

Postby omysyk » Fri Mar 19, 2010 5:17 pm

Hi,

I tried to use the example from this post on OpenOffice 3.2. For some reason it did not work for me. The xml file opened in Writer, not in Calc application.

I thought I followed the exact steps:

1. I cut and pasted the xml file into data.xml on my computer.
2. I cut and pasted the xslt example into xml_import_filter.xslt.
3. I opened the XML Filter Settings and created the following:
    Filter name: XML Import
    Application: OpenOffice.org Calc (.ods)
    Name of file type: test
    File extension: xml
    Under transformation tab:
    DocType: test
    DTD: blank
    XSLT for export: blank
    XSLT for import: $path/xml_import_filter.xslt
    Template for import: blank
4. Import the data.xml file

Result: the file opened in OpenOffice Writer. Just like I would open a text file, all xml tags visible. Is there a bug? Or I did not follow the instructions properly?
MS Windows 64-bit
OpenOffice 3.2
omysyk
 
Posts: 1
Joined: Fri Mar 19, 2010 5:05 pm

Re: [Solved] Problem Opening an XML File in Calc

Postby hol.sten » Sat Mar 20, 2010 6:36 pm

omysyk wrote:4. Import the data.xml file

So far your settinsg are looking good. Can you be a little more specific here? Did you follow the steps from "2) open xml file" in the first post of this thread?
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


Return to Calc

Who is online

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