GOTCHAS

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

Date and time in ODS

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).

OpenOffice.org stores dates as text strings like “yyyy-mm-dd”.

MS-Excel stores dates as a number representing the number of days since January 1, 1900 (and as an aside, erroneously assumes 1900 to be a leap year).

Now, converting OpenOffice.org date cell values (actually, character strings flagged by “date” attributes) into Octave looks pretty straightforward. But when the ODS spreadsheet was originally an Excel spreadsheet converted by OpenOffice.org, the date cells can either be OOo date values (i.e.,strings) OR old numerical values from the Excel spreadsheet.

So: you should carefully check what happens to date cells.

As octave has no ”date” or “time” data type, octave date values (usually numerical data) are simply transferred as “floats” to ODS spreadsheets. You'll have to convert the values into dates yourself from within OpenOffice.org.

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.

Java memory pool allocation size

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.

For transferring large pieces of information to and from spreadsheets you might hit the limits of this pool. E.g. to be able to handle I/O of an array of around 50,000 cells I needed a memory pool size of 512 MB.

The memory size can be increased by inserting a file called “java.opts” (without quotes) in the directory ./share/octave/packages/java-<version> (where the script file javaclasspath.m is located), containing just the following lines:

-Xms16m
-Xmx512m

(where 16 = initial size, 512 = maximum size (in this example), m stands for Megabyte. This number is system-dependent).

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.

Reading cells containing errors

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 often contain invalid formulas but may have a 0 (null) 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).

Smaller gotcha's (only with jOpenDocument 1.2b2, fixed in 1.2b3+ and 1.2 final):

• While reading, empty cells are sometimes not skipped but interpreted with numerical value 0 (zero).

• A valid range MUST be specified, I haven't found a way to discover the actual occupied rows and columns (jOpenDocument can give the physical ones (= capacity) but that doesn't help).

NOT fixed in version 1.2 final nor 1.3b1:

• 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.

MATLAB COMPATIBILITY

Depending on the MS-Excel version on the same computer, Matlab may read/write ODS files using xlsread/xlswrite. Note that decent ODS 1.2 support only started with Excel 2013.
odsread is fairly function-compatible to xlsread, however.

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

COMPARISON OF INTERFACES

The ODFtoolkit is the one that gives the best (but slow) results at present. However, parsing xml trees into rectangular arrays is not quite straightforward and the other way round is a real nightmare; odftoolkit up til 0.7.5. did little to hide the gory details for the developers.

While reading ODS is still OK, writing implies checking whether cells already exist explicitly (in table:table-cells) or implicitly (in number-columns-repeated or number-rows-repeated nodes) or not at all yet in which case you'll need to add various types of parent nodes. Inserting new cells (“nodes”) or deleting nodes implies rebuilding possibly large parts of the tree in memory - nothing for the faint-of-heart. Only with ODFToolkit (odfdom) 0.8.6, 0.8.7 and 0.8.8 things have been simplified for developers.
Unfortunately, with odftoolkit-0.6.0-incubating and odftoolkit-0.6.1-incubating (corresponding to odfdom-0.8.9 and 0.8.10) unresolved dependencies ("jenasin") have been introduced that break their functionality for Octave.

The jOpenDocument interface is more promising, as it does shield the xml tree details and presents developers something which looks like a spreadsheet model.
However, unfortunately the developers decided to shield essential methods by making them 'protected' (e.g. the vital getCellType). JopenDocument does support writing. But OTOH many obvious methods are still lacking and formula support is absent (although announced for future version 1.4).
And last (but not least) the jOpenDocument developers state that their development is primarily driven by requests from customers who pay for support. I do sympathize with this business model but for Octave needs this may hamper progress for a while.
In addition, jOpenDocument 1.2 and 1.3b1 still have bugs here and there. For one, it doesn't write appropriate OfficeValueType attributes to the cells, so there's no way to reliably read and distinguish boolean, string and integer values.

The (still experimental) UNO interface, based on a Java/UNO bridge linking a hidden OpenOffice.org invocation to Octave, is the most promising:

• Admittedly OOo needs some tens of seconds to start for the first time, but once OOo is in the operating system's disk cache, it operates much faster than ODF or JOD;
• It has built-in formula validator and evaluator;
• It has a much more reliable data parser;
• It can read much more spreadsheet formats than just ODS; .sxc (older OOo and StarOffice), but also .xls, .xlsx (Excel), .wk1 (Lotus 123), dbf, etc.
• It consumes only a fraction of the JVM heap memory that the other Java ODS spreadsheet solutions need because OOo reads the spreadsheet in its own memory chunk in RAM. The other solutions read, expand, parse and manipulate all data in the JVM. In addition, OOo's code is outside the JVM (and Octave) while the ODF Toolkit and jOpenDocument classes also reside in the JVM.

However, UNO is not stable yet (see below). As stated above, the arch type (32-bit or 64-bit) must match that of Octave..

The OCT (native Octave) interface is also promising as it is completely under control of Octave (-Forge) developers. Currently it only offers read and -experimental- write support for ODS (relatively slow), gnumeric (faster) and OOXML (very fast). An immense advantage is that no other external software is required. Write support has not extensively tested yet, however.