Creating LibreOffice Calc document from XML (or some other file?)

users
#1

Hi, all.

Once in the past I stumbled upon a blog where owner explained how one
can create a Calc document from the data that's in the XML file. I'm not
sure if XML contained only the data which then populated the Calc
template, or the whole Calc data was in that XML (not even sure if the
source was XML or was it some other human-readable file), but I haven't
been able to locate that blog ever since.

I'd appreciate any info or pointers on how above mentioned can be achieved.

I need to create several daily reports on some processes I'm overseeing,
and entering all that data into the Calc spreadsheet is cumbersome - the
report resembles of a tax-return/invoice sheet, where various fields
need to be populated - and I'm looking for a way to automate this.

  Mario

P.S. I apologize if I posted this to the wrong mailinglist, please
redirect me if necessary.

#2

Hi Mario,

I'm sure others on this list can advise you more completely, but I'll
have a quick stab at it. As I understand it, the LO document format is a
zipped file consisting of XML files for the contents. One way of
doing what you need should be to create a file with the layout you need
and some sample data, then save that. Then rename the file extension to
zip if need be, and open it in an archive manager. Extract the contents
to a working directory and examine the files. You should find one with
the sample data in XML format. You should be able to use that as a
template, and create a script or program to replace the sample data
with live data and re-zip the files into a working Calc file.

Hope this is what you were looking for.

Paul

#3

Hi Mario,

Mario Splivalo schrieb:

Hi, all.

Once in the past I stumbled upon a blog where owner explained how one
can create a Calc document from the data that's in the XML file. I'm not
sure if XML contained only the data which then populated the Calc
template, or the whole Calc data was in that XML (not even sure if the
source was XML or was it some other human-readable file), but I haven't
been able to locate that blog ever since.

I'd appreciate any info or pointers on how above mentioned can be achieved.

I need to create several daily reports on some processes I'm overseeing,
and entering all that data into the Calc spreadsheet is cumbersome - the
report resembles of a tax-return/invoice sheet, where various fields
need to be populated - and I'm looking for a way to automate this.

In which form do you get the data? Perhaps there is nothing like XML needed, but you can import the data directly.

Do you want to write macros for Calc or do you want to use another application that manipulates the .ods file source?

  Mario

P.S. I apologize if I posted this to the wrong mailinglist, please
redirect me if necessary.

"users" is OK.

Kind regards
Regina

#4

I have done this for some special logging I do, by simplying creating my
own template, saving the data in a csv (comma separated value format)
and then loading the data into the template. I do this manually, but a
macro could be created to do it as well.

Regards,
Les H

#5

"Mario Splivalo":

Once in the past I stumbled upon a blog where owner explained how one
can create a Calc document from the data that's in the XML file.

You may try XSLT to get a Flat ODS file.

#6

I was hoping for a method so I don't need to poke with .ods files
directly :slight_smile: The XML source can, I guess, be any source...

  Mario

#7

I need to create several daily reports on some processes I'm overseeing,
and entering all that data into the Calc spreadsheet is cumbersome - the
report resembles of a tax-return/invoice sheet, where various fields
need to be populated - and I'm looking for a way to automate this.

In which form do you get the data? Perhaps there is nothing like XML
needed, but you can import the data directly.

Well, since I'm getting the data, I can put them in anything I like,
more or less. I just think I remember seeing the XML, that's why I
mentioned it...

Do you want to write macros for Calc or do you want to use another
application that manipulates the .ods file source?

I don't know :slight_smile: Actually, anything that gets the job done.
If I remember correctly that blog post, author was not mentioning a
separate application, nor modifying the .ods source. But I really do not
remember at this time, that's why I'm asking for a suggestion.

The reason I can't use CSV is because the report is funky-formated -
parts of it can be CVSed, but most of the document can not.

  Mario

#8

I can't use CSV because the ODS document won't work with it well. For
instance, first few rows contain information about the test subjects,
varios fields/group names, some parametars that were configured during
the test and so on.
Then there is a table, with various measurements taken at particular
times - and those could be CSVed.
Then again a lot of fields that resemble more of a key-value pairs, than
a CSV list.

I there, maybe, a way to use XSLT transformation so that I can have my
data in XML and then somehow 'fill in' the template with XSLT?

  Mario

#9

Hi Mario,

Sorry, by your description I understood you to mean you *did* want to
poke into the ods file directly.

I would consider scripting something to create the files
automatically the best option, but then again, I'm a programmer, so it
would be my first thought :slight_smile:

I see a few possible menu items in Calc that might provide what you
want, unfortunately I don't know them, so someone else will have to
comment on their suitability. I'll just list them, and you can research
further.

View | Data Sources
Insert | Link To External Data
Data | XML Source

Regards

Paul

#10

Hi Mario,

Mario Splivalo schrieb:

I need to create several daily reports on some processes I'm overseeing,
and entering all that data into the Calc spreadsheet is cumbersome - the
report resembles of a tax-return/invoice sheet, where various fields
need to be populated - and I'm looking for a way to automate this.

In which form do you get the data? Perhaps there is nothing like XML
needed, but you can import the data directly.

Well, since I'm getting the data, I can put them in anything I like,
more or less. I just think I remember seeing the XML, that's why I
mentioned it...

Do you want to write macros for Calc or do you want to use another
application that manipulates the .ods file source?

I don't know :slight_smile: Actually, anything that gets the job done.
If I remember correctly that blog post, author was not mentioning a
separate application, nor modifying the .ods source. But I really do not
remember at this time, that's why I'm asking for a suggestion.

The reason I can't use CSV is because the report is funky-formated -
parts of it can be CVSed, but most of the document can not.

Perhaps you can put it in HTML. If you write that parts, which can be "CVSed" into a table element, you can use these tables as import or linked in Calc.

If you need the other parts of the data too, you can try to write .fods directly. Obviously you now how to work with XML and .fods is a pure XML file. If you save a minimal spreadsheet to Flat XML (.fods) you get all the parts like namespaces, settings, styles, which you can copy simply. So you only need to write the table elements newly.

Kind regards
Regina

#11

Hi Mario,

Sorry, by your description I understood you to mean you *did* want to
poke into the ods file directly.

Well, that is also an option, if everything else fails :slight_smile:

I would consider scripting something to create the files
automatically the best option, but then again, I'm a programmer, so it
would be my first thought :slight_smile:

Indeed! :slight_smile: I stumbled upon this: http://simple-odspy.sourceforge.net/
Maybe this is the way to go.
Or that XSLT transformation Urmas suggested.

  Mario

#12

Is there a standard library that provides such XSLT transformation? I
tried to find some, but nothing stood up.

  Mario

#13

Hi.

"Mario Splivalo":

Once in the past I stumbled upon a blog where owner explained how one
can create a Calc document from the data that's in the XML file.

You may try XSLT to get a Flat ODS file.

Is there a standard library that provides such XSLT transformation? I
tried to find some, but nothing stood up.

  Mario

I use XSLT import filters to import my xml files. This may not be what you are looking for.
This is some information but I think you can google another more comprehensive tutorial somewhere.
https://forum.openoffice.org/en/forum/viewtopic.php?t=3490

I also import (open) CSV files with different structures. I use tab delimited files where the data may contain commas (i.e. addresses). It does not matter if the data lines have differing numbers of fields as long as you end each line with a line feed.

steve

#14

Hi Mario,

not sure if it's **my** blog you stumbled upon, but here it is:

http://freesoftware.zona-m.net/tag/odf-scripting/

HTH,
  Marco

#15

Hi, all.

Once in the past I stumbled upon a blog where owner explained how one
can create a Calc document from the data that's in the XML file. I'm not
sure if XML contained only the data which then populated the Calc
template, or the whole Calc data was in that XML (not even sure if the
source was XML or was it some other human-readable file), but I haven't
been able to locate that blog ever since.

The web is like a river; catch that fish when you see it, or it will
be gone forever! :slight_smile:

I'd appreciate any info or pointers on how above mentioned can be achieved.

Create a flat ods file ('spreadsheet.fods') and examine the xml; you
could use xslt to convert your xml to a spreadsheet.

#16

Hi.
There is also this quite old one here too.
http://www.ibm.com/developerworks/xml/tutorials/x-xsltopenoff/
steve

#17

Hi Mario,

It is unfortunate, but Excel does a far better job of importing/opening random XML files than LO. I download XML files from a public administration I work with, and in Excel I can just open them by double-clicking on them. Excel creates an on-the-fly ad-hoc namespace for the document if it doesn't recognize the namespace of the XML or doesn't have an import filter for it, and also preserves the tree structure of the elements and attributes, using the elements as filter headers. Quite simply put, this is brilliant.

LO, on the otherhand, imports everything as simple text and dumps it willy-nilly into the spreadsheet in the first column, losing all element/attribute information. If you want the same thing in LO, you have to design and implement a separate XSLT filter for each XML type that you want to open...

Alex

#18

HA!

This might very well be it! Thank you, Marco! :slight_smile:

I appreciate all the comments, I'll figure something out. At this time
using that pypy library I pasted earlier seems like the most viable
solutoin - I will update the list once I have my solution ready.

Thank you all.

  Mario

#19

you're welcome. In any case, please let me know how you end up, it may
become another post to add to that series.

Marco

#20

Hi Alex,

Have you filed a feature request for this? It sounds useful.

Paul