Page 1 of 1

Reading .ods Files with PHP?

Posted: Mon Sep 07, 2015 12:24 am
by nickbaer
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.

Re: Reading .ods Files with PHP?

Posted: Mon Sep 07, 2015 5:39 am
by RusselB
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.

Re: Reading .ods Files with PHP?

Posted: Mon Sep 07, 2015 6:36 am
by nickbaer
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.

Re: Reading .ods Files with PHP?

Posted: Wed Sep 16, 2015 1:23 am
by rudolfo
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.

Re: Reading .ods Files with PHP?

Posted: Wed Sep 16, 2015 9:43 am
by floris v