IO package: Difference between revisions

The IO package is part of the octave-forge project and provides input/output from/in external formats.

(ODS = Open Document Format spreadsheet data format, used by e.g., LibreOffice and OpenOffice.org)

* '''odsread.m''' — no-hassle read script for reading from an ODS file and parsing the numeric and text data into separate arrays.

* '''odswrite.m''' — no-hassle write script for writing to an ODS file.

For the OCT interface (since 1.2.4/1.2.5, read-only support!):

* Nothing except unzip

Alternatively, the io package contains a function script file "chk_spreadsheet_support.m" which can set up the Java classpath.

(see “help ods<function_filename>” in octave terminal.)

If you use odsopen / ods2oct / … / oct2ods / …. / odsclose, DO NOT FORGET to invoke odsclose in the end. The file pointers can contain an enormous amount of data and may needlessly keep precious memory allocated. In case of the UNO interface, the hidden OpenOffice.org invocation (soffice.bin) can even block proper closing of Octave.

When using the OTK or UNO interface you can:

The only exception is if you select the UNO interface, as that invokes OpenOffice.org behind the scenes, and OOo obviously has a validator and evaluator built-in.

I know of one big gotcha: i.e. reading dates (& time). A less obvious one is Java memory pool allocation size.

Octave (as does Matlab) stores dates as a number representing the number of days since January 1, 0 (and as an aside ignores a.o. Pope Gregorius' intervention in 1582 when 10 days were simply skipped).

While adding data and time values has been implemented in the write scripts, the wait is for clever solutions to distinguish dates from floats in octave cell arrays.

The Java virtual machine (JVM) initializes one big chunk of your computer's RAM in which all Java classes and methods etc. are to be loaded: the Java memory pool. It does this because Java has a very sophisticated “garbage collection” system. At least on Windows, the initial size is 2MB and the maximum size is 64MB. On Linux this allocated size is much bigger. This part of memory is where the Java-based ODS octave routines (and the Java-based ods routines) live and keep their variables etc.

After processing a large chunk of spreadsheet information you might notice that octave's memory footprint does not shrink so it looks like Java's memory pool does not shrink back; but rest assured, the memory footprint is the allocated (reserved) memory size, not the actual used size. After the JVM has done its garbage collection, only the so-called “working set” of the memory allocation is really in use and that is a trimmed-down part of the memory allocation pool. On Windows systems it often suffices to minimize the octave terminal for a few seconds to get a more reasonable memory footprint.

Spreadsheet cells containing erroneous stuff are transferred to Octave as NaNs. But not all errors can be catched. Cells showing #Value# in OpenOffice.org Calc due to e.g., invalid formulas, may have a 0 (zero) value stored in the value fields. It is impossible to catch this as there is no run-time formula evaluator (yet) in ODF Toolkit nor jOpenDocument (like there is in Apache POI for Excel).

* jOpenDocument doesn't set the so-called <office:value-type='string'> attribute in cells containing text; as a consequence ODF Toolkit will treat them as empty cells. OOo will read them OK.

AFAIK there's no similar functionality in Matlab (yet?), only for reading and then very limited.

odsread is fairly function-compatible to xlsread, however.

Same goes for odswrite, odsfinfo and xlsfinfo – however odsfinfo has better functionality IMO.

The OCT interface (present as of io-1.2.4) offers read support for ODS 1.2, complete with all the options of ODFtoolkit and UNO, but fairly slow.

However, UNO is not stable yet (see below).

Some hints for troubleshooting ODS support are given here.

Since April 2011 the function chk_spreadsheet_support() has been included in the io package. Calling it with arguments ('', 3) (empty string and debug level 3) will echo a lot of diagnostics to the screen. Large parts of the steps outlined below have been automated in this script.

** The exact case (URE or ure, Basis or basis), name ("Basis3.2" or just "basis") and subdirectory tree (URE/java or URE/share/java) varies across OOo versions and -clones, so chk_spreadsheet_support.m can have a hard time finding all needed classes. In particularly bad cases, when chk_spreadsheet_support cannot find them, you might need to add one or more of these these classes manually to the javaclasspath.

As with the Excel r/w stuff, adding new interfaces should be easy and straightforward. Add relevant stanzas in odsopen, odsclose, odsfinfo & getusedrange and add new subfunctions (for the real work) to getusedrange_<INTF>, oct2ods and ods2oct.

But IMO data sets larger than 5.105 cells should not be kept in spreadsheets anyway. Use real databases for such data sets.

I have tried various odfdom version. As to 0.8 & 0.8.5, while the API has been simplified enormously (finally one can address cells by spreadsheet address rather than find out yourself by parsing the table-column/-row/-cell structure), many irrecoverable bugs have been introduced :-((

In addition processing ODS files became significantly slower (up to 7 times!).

* oct2ods.m (revision 7159)

* '''xlsread.m''' &mdash; All-in-one function for reading data from one specific worksheet in an Excel spreadsheet file. This script has Matlab-compatible functionality.

* '''xlswrite.m''' &mdash; All-in-one function for writing data to one specific worksheet in an Excel spreadsheet file. This script has Matlab-compatible functionality.

* '''spsh_chkrange.m''', '''spsh_prstype.m''', '''getusedrange.m''', '''calccelladdress.m''', '''parse_sp_range.m''' &mdash; Support files called by the scripts and not meant for direct invocation by users.

For the OCT interface (since 1.2.4/1.2.5, read-only support for OOXML (.xlsx)!):

* Nothing except unzip

NOTE: EXPERIMENTAL!! A working OpenOffice.org installation. The utility function chk_spreadsheet_support can be used to add the needed entries to the javaclasspath.

'''xlsread''' and '''xlswrite''' are mere wrappers for '''xlsopen - xls2oct - xlsclose - parsecell''' and '''xlsopen - oct2xls - xlsclose''' sequences, resp. They exist for the sake of Matlab compatibility.

When using JExcelAPI (JXL), after writing into a worksheet you MUST save the file – adding data to the same or another worksheet is no more possible after the first call to oct2xls(). This is a limitation of JExcelAPI.

When using the COM, POI, JXL, and UNO interfaces you can:

* (When reading, xls2oct) either read spreadsheet formula results, or the literal formula text strings (also works with OCT interface);

Worse, older Excel versions feature less functions than newer versions. So be wary as this may make for interesting confusion.

'''xlsread''', '''xlswrite''' and '''xlsfinfo''' are for the most part Matlab-compatible. Some small differences are mentioned below.

** When invoking Excel/COM interface, octave-forge's xlsfinfo also echoes the type of sheet (worksheet, chart), not just the sheet names. Using Java I haven't found similar functionality (yet).

Using Excel itself (through '''COM''' / '''ActiveX''' on Windows systems) is probably the most robust and versatile and especially FAST option. There's one gotcha: in case of some type of COM errors Excel will keep running invisibly; you can only end it through Task Manager. A tiny problem is that one cannot find out easily through COM what file types are supported; xls, wks, wk1, xlsx, etc. Another -obvious- limitation is that COM Excel access only works on Windows systems where Excel is installed.

So Java is a compromise between portability and rapid development time versus capacity (and speed). But IMO data sets larger than 5.10^5 cells should not be kept in spreadsheets anyway. Better use real databases for such data sets.

Some hints for troubleshooting Excel support are contained in this thread: http://sourceforge.net/mailarchive/forum.php?thread_name=4C61B649.9090802%40hccnet.nl&forum_name=octave-dev dated August 10, 2010. A more structured approach is below.

#: xls2 = xlsopen ('test.xls', 1, 'jxl'). If this works and xls2 is a struct with various fields containing objects, the JExcelAPI interface (JXL) works as well. Don't forget to do xls2 = xlsclose (xls2) to close the file.

xlsopen/xlsclose and friends have been written so that adding other interfaces (Perl? native octave? ...?) should be very easily accomplished. Xlsopen.m merely needs two stanzas, xlsfinfo.m and getusedrange.m each need an additional elseif stanza, and xlsclose.m needs a small stanza for closing the pointer struct and writing to disk. The real work lies in creating the relevant xls2<...>2oct & oct2<...>2xls & <getusedrange_...> subfunction scripts in xls2oct.m, oct2xls.m and getusedrange.m, resp., but that shouldn't be really hard, depending on the interface support libraries' quality and documentation. Separating the file access functions and the actual reading/writing from/to the workbook in memory has made developer's life (I mean: my time developing this stuff) much easier.

*Support for "passing function handle" in xlsread.

Since io package version 1.2.4, an interface called "OCT" was added. Except for unzip, it has no dependencies. It's still experimental but fast! Feel free to test it and give us a feedback.