Reading .ods Files with PHP?

Java, C++, C#, Delphi, ??? - Using the UNO bridges

Reading .ods Files with PHP?

Postby nickbaer » Mon Sep 07, 2015 12:24 am

I am inquiring as to how you are reading .ods data files in PHP or JavaScript?

I have dozens of PHP scripts that read .csv files, but I have some csv files that look awful, because the column formatting is not retained when saved as .csv.

I want to evolve to reading .ods files, because it is easier to just leave the files as .ods, and not the .csv steps (dragging the column widths so I can see more than one column per the width of my screen!, and the tedious saving of .ods to .csv) every time I edit the file(s).

I have Google'd the topic, and PHP Excel and a couple of scripts like that, are either too old, OR they are just incomplete snippets, with insufficient information about what code to wrap around the snippets.

I will Google Java techniques.

Nonetheless, I wanted to check to see what some of you are doing.

PHP Documentation says .ods files are zip archives of .xml documents... But Stuffit thumbs its nose at my attempts to unstuff the .ods file. It is only one sheet.
Open Office 4.1.1, On Apple Mac OSX 10.10.5 (updated when available).
nickbaer
 
Posts: 2
Joined: Mon Sep 07, 2015 12:10 am

Re: Reading .ods Files with PHP?

Postby RusselB » Mon Sep 07, 2015 5:39 am

The .ods file is a compressed archive. You can easily verify this by making a .ods file, then renaming it to change the extension to .zip, then using whatever compressed file extractor you prefer.
When you look at the contents of the .zip file, you'll see that there are several files in it.
An example from one of my files shows 3 directories (Configuration2, META-INF, & Thumbnails), and 6 files (content.xml, manifest.rdf, meta.xnl, mimetype, settings.xml & styles.xml)
The Configuration2 directory has several sub-directories in it, and each of the other two directories contains a single file.
I don't use a Mac so I'm not familiar with Stuffit, but Winzip and WinRar work fine for showing/extracting the directories/files mentioned, so you might want to see if there's a Mac equivalent.
OpenOffice 4.1.7 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5517
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Reading .ods Files with PHP?

Postby nickbaer » Mon Sep 07, 2015 6:36 am

I've spent a few hours looking into js-xlsx.

There's many search results for http://oss.sheetjs.com/js-xlsx/ , but not one example of how to use a known filename within a .php script, not like this page with the drag and drop into the drop box of a index.html script running in a browser.


Per your kind and correct response:

I tried changing my *.ods file to *.zip. I can unStuff that *.zip file on my desktop, and see the new directory and files. The only file close that I am looking for is "content.xml", but of course it is all marked up with XML, whereas I want rows of comma separated data.

After testing, I want to run this from my LINUX server, to get the rows with comma separated data.

Just like http://oss.sheetjs.com/js-xlsx/ outputs rows with comma separated data on my screen... but I want it in an Array() within my PHP script, that PHP can walk through.

I'm surprised Stuffit can't unstuff a .ods file. As a eBook publisher, I can unstuff .ePub files, which are similarly .zip archives- without needing to change the filetype.
Open Office 4.1.1, On Apple Mac OSX 10.10.5 (updated when available).
nickbaer
 
Posts: 2
Joined: Mon Sep 07, 2015 12:10 am

Re: Reading .ods Files with PHP?

Postby rudolfo » Wed Sep 16, 2015 1:23 am

Have a look at SimpleXML and particularly at the first two user content examples that talk about converting an XML file/string into a PHP array. In your case you would need to feed the internal content.xml into simplexml_load_file(). And most probably it is best if you let do PHP the unzipping of the .ods file as well.

The crucial point here is that it does not make sense to transform a valid, clearly structured XML file into a csv and then turn the csv file into an array. This is like printing an email and then take the sheet of paper and place it on a scanner to scan it and in a last step use a OCR program to bring the text into Word or some other wordprocessor. Instead you would surely just copy and paste the email body into the word processor.

Right, the first user added post on the PHP documentatin side, does exactly this. But it is far more intelligent to use JSON as intermediate format than to use CSV as intermediate format.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Reading .ods Files with PHP?

Postby floris v » Wed Sep 16, 2015 9:43 am

AOO 4.1.6 op Linux Mint
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
floris v
Moderator
 
Posts: 4159
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands


Return to External Programs

Who is online

Users browsing this forum: No registered users and 0 guests