README for Excel spreadsheet file r/w access scripts for octave (> 3.8.x)

Copyright (C) 2009 - 2014 Philip Nienhuis <prnienhuis at users.sf.net>

This version May 1, 2014

For the native Octave interface (OCT)

(read/write support for OOXML (Excel 2007+), ODS 1.2 (LibreOffice/OpenOffice.org Calc), and Gnumeric)
NO external support software is required!

For the Excel/COM interface:

• A windows computer with Excel installed
• Octave-forge Windows-1.2.1

Apache POI specific:

• for OOXML support with Apache POI:

JExcelAPI specific:

OpenXLS specific:

These class libs must be referenced with full pathnames in your javaclasspath.
When the io package gets loaded, a utility function (PKG_ADD) tries to automatically find the Java class libs and adds the ones it found to the javaclasspath; When the io package gets unloaded, these same class libs will be removed from the javaclasspath.

On MinGW the required Java class libs had best be put in /<libdir>/java (where <libdir> on MinGW is usually /lib); on Linux system supplied Java class libs usually reside in /usr/share/java. Alternatively, you can put them in your HOME directory in a subdirectory java (mind case!) - on *nix that would be ~./java, on Windows %USERPROFILE%/java (same level as My Documents). The PKG_ADD routine, that gets run each time the io package is loaded, expects the class libs there; if they are elsewhere, add them in ./share/octave/<version>/m/startup/octaverc using appropriate javaaddpath statements or a chk_spreadsheet_support() call.
In addition, you can specify a subdirectory using the environment variable OCTAVE_IO_JAVALIBS.
Once a particular Java class lib has been added to the javaclasspath, it won't be searched anymore nor reloaded from the next search location. The search order is:

1. Specified by the environment variable OCTAVE_IO_JAVALIBS
2. <HOME_DIR>/java
3. /usr/share/java (*nix) or /lib/java (MinGW)

If you do not want PKG_ADD to load the Java class libs, specify a value of "no", "false" or "0" for the OCTAVE_IO_JAVALIBS environment variable before starting Octave.

UNO specific (invoking OpenOffice.org (or clones) behind the scenes):
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.

xlsfinfo can be used for finding out what worksheet names exist in the file. For OOXML files you either need MS-Excel 2007 for Windows (or later version) installed, and/or the input parameter REQINTF should be specified with a value of 'poi', 'oxs' or 'uno' (case-insensitive) and -obviously- either the complete POI interface must have been installed, and/or OpenXLS, and/or LibreOffice/OpenOffice.org.

Invoking xlsopen/..../xlsclose directly provides for much more flexibility, speed, and robustness than xlsread / xlswrite. Indeed, using the same file handle (pointer struct) you can mix reading & writing before writing the workbook out to disk using xlsclose.
And: xlsopen / xlsclose hide the gory interface details from the user.
Currently only .xls files (BIFF8) can be read/written; using JExcelAPI BIFF5 can be read as well. For OOXML files either Excel 2007 for Windows (or higher) and/or the complete Apache POI interface, and/or OpenXLS v 10, or LibreOffice must be installed (and probably the REQINTF parameter specified with a value of 'poi', 'oxs', or 'uno').

When using xlsopen....xlsclose be sure to keep track of the file handle struct.

A possible scenario:

xlh = xlsopen (<excel_filename> , [rw], [<requested interface>])
# Set rw to 1 if you want to write to a workbook immediately.
# In that case the check for file existence is skipped and
# -if needed- a new workbook created.
# If you really want an other interface than auto-selected
# by xlsopen you can request that. But xlsopen still checks
# proper support for your choice.

# Read some data
[ rawarr1, xlh ] = xls2oct (xlh, <SomeWorksheet>, <Range>)
# Be sure to specify xlh as output argument as xls2oct keeps
# track of changes and the need to write the workbook to disk
# in the xlhstruct. And the origin range is conveyed through
# the xlh pointer struct.

# Separate data into numeric and text data
[ numarr1, txtarr1, lim1 ] = parsecell (rawarr1)

# Get more data from another worksheet in the same workbook
[ rawarr2, xlh ] = xls2oct (xlh, <SomeOtherWorksheet>, <Range>)
[ numarr2, txtarr2, lim2 ] = parsecell (rawarr2)

# <... Analysis and preparation of new data in cell array Newdata....>

# Add new data to spreadsheet
xlh = oct2xls (Newdata, xlh, <AnotherWorksheet>, <Range>)

# Close the workbook and write it to disk; then clear the handle
xlh = xlsclose (xlh)
clear xlh

When not using the COM interface, specify a value of 'POI' for parameter REQINTF when accessing OOXML files in xlsread, xlswrite, xlsopen, xlsfinfo (and be sure the complete Apache POI interface is installed). If you haven't got ActiveX installed (i.e., not having MS-Excel under Windows) specifying 'POI' may not be needed as in such cases Apache POI is the next default interface.

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, OXS, UNO and OCT interfaces you can:

• (When reading, xls2oct) either read evaluated spreadsheet formula results, or the literal formula text strings;
• (When writing, oct2xls) either enter text strings in the form of spreadsheet formulas in the worksheet as formulas, or enter them as literal text strings.

In short, you can enter spreadsheet formulas and in a later stage read them back, change them and re-enter them in the worksheet. 

xlsread, xlswrite and xlsfinfo are for the most part Matlab-compatible. Some small differences are mentioned below. When using the Java interfaces octave supplies some formula manipulation support.

xlsread
Matlab's xlsread supports invoking extra functions while reading ("passing function handle"); octave not. But this can be simulated outside xlsread.

Matlab's xlsread flags some spreadsheet errors, octave-forge just returns blank cells.

Octave's xlsread (and for that matter, xlsfinfo as well) returns info about the actual (rather than the requested) cell range where the data came from. Personally I find it very useful to know from what part of a worksheet the data originate so I've put quite some effort in it :-)

Matlab can't, due to Excel automatically trimming returned arrays from empty outer columns and rows. Octave is more clever but the Visual Basic call used for determining the actually used range has some limitations: (1) it relies on cached range values and thus may be out-of-date, and (2) it counts empty formatted cells too. When using ActiveX/COM, if octave's xlsfinfo.m returns wrong data ranges it is most often an overestimation.

Matlab's xlsread ignores all non-numeric data values outside the smallest rectangle encompassing all numerical values. Octave's xlsread doesn't. This means that Matlab ignores all row/column headers, not very user-friendly IMO.

When using the Java interface, reading and writing xls-files by octave's xlsread is platform-independent. On systems w/o installed Excel, Matlab can only read Excel 95 formatted .xls files (written using ML xlswrite's 'Basic" option) – and then differently than under Windows.....

Matlab's xlsread returns strings for cells containing date values. This makes for endless if-then-elseif-else-end constructs to catch all expected date formates. Octave returns numerical data (where 0 = 1/1/1900 – you can easily transfer them into proper octave date values yourself using e.g. datestr(), see bottom of this document for more info).

xlswrite
Octave's xlswrite works on systems w/o Excel support, Matlab's doesn't (properly).

When specifying a sheet number larger than the number of existing sheets in an .xls file, Matlab's xlswrite adds empty sheets until the new sheet number is created; Octave's xlswrite only adds one sheet called "Sheet<number>" where <number> is the specified sheet number.

Even better (IMO) while M's xlswrite always creates Sheet1/Sheet2/Sheet3 when creating a new spreadsheet, octave's xlswrite only creates the requested worksheet. (Did you know that you can instruct Excel to create spreadsheets with just one, or any number of, worksheets? Look in Tools | Options, General tab.)

Oh and octave doesn't touch the "active sheet" - but that's not automatically an advantage.

If the specified write range is larger than the actual data array, Matlab's xlswrite adds #N/A cells to fill up the lowermost rows and rightmost columns; octave-forge's xlswrite doesn't.

xlsfinfo
When invoking Excel/COM interface, Octave's xlsfinfo also echoes the type of sheet (worksheet, chart), not just the sheet names. Using Java I haven't found similar functionality (yet).
Octave's xlsfinfo also shows (and returns) the range of the smallest rectangle encompassing all occupied data ranges in each sheet.

Using Excel itself (through COM / ActiveX on Windows systems) is probably the most robust and versatile and especially FAST option (though for OOXML the OCT interface is probably faster). 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.

JExcelAPI (Java-based and therefore platform-independent) is proven technology but switching between reading and writing is quite involved and memory-hungry when processing large spreadsheets. As the docs state, JExcelAPI is optimized for reading and it does do that well - but still slower than Excel/COM. The fact that upon a switch from reading to writing the existing spreadsheet is overwritten in place by a blank one and that you can only get the contents back wen writing out all of the changes is worrying - and any change after the first write() is lost as a next write() doesn't seem to work, worse yet, you may completely loose the spreadsheet in question. The first is by JExcelAPI design, the second is probably a bug (in octave-forge/Java or JExcelAPI ? I don't know). Adding data to existing spreadsheets does work, but IMO undue user confidence is needed.

JExcelAPI supports BIFF5 (only reading) and BIFF8 (Excel 95 and Excel 97-2003, respectively). Upon overwriting, BIFF5 spreadsheets are converted silently to BIFF8.

JexcelAPI, unlike ApachePOI, doesn't evaluate functions while reading but instead relies on cached results (i.e. results computed by Excel itself). Depending on Excel settings ("Automatic calculation" ON or OFF) this may or may not yield incorrect (or expected) results.

Apache POI (Java-based and platform-independent too) is based on the OpenOffice.org I/O Excel r/w routines. It is a more versatile than JExcelAPI, while it doesn't support BIFF5 it does support BIFF8 (Excel 97 – 2003) and OOXML (Excel 2007).

It is slower than native JXL let alone Excel & COM but it features active formula evaluation, although at the moment (end of 2013, v. 3.9) still not *all* Excel functions have been implemented (a daunting task for the POI devs, as it is hard to keep up with MS here). I've made the relevant subfunction (xls2jpoi2oct) fall back to cached formula results (and yield a suitable warning) for non-implemented Excel functions while reading Excel files.

OpenXLS (an open source version of Extentech's commercial Java-xls product) is still a bit experimental. It seems to work faster than JExcelAPI, but it has other issues - i.e., processing of OOXML files is still unreliable. In addition OpenXLS scatters Extentech.tmp files here and there. For .xls (BIFF8) it works OK.

UNO (invoking OpenOffice.org (OOo) or LibreOffice (LO) or clones behind the scenes, a la ActiveX) is experimental. It works FAST (i.e., once OOo itself is loaded and initialized which can take some time) and can process much larger spreadsheets than the other Java-based interfaces because the data are not entered in the JVM but in OOo's memory.
A big stumbling block is that odsclose() on a UNO xls struct will kill ALL OpenOffice.org invocations, also those that were not related to Octave! This is due to UNO-Java limitations.
The underlying issue is that when Octave starts an OpenOffice.org invocation, OpenOffice.org must be closed for Octave to be able to exit; otherwise Octave will wait for OOo to shut down before it can terminate itself. So Octave must kill OOo to be able to terminate.
A way out hasn't been found yet.

All in all, of the three Java options I'd prefer Apache POI rather than OpenXLS or JexcelAPI. But the latter is indispensable for BIFF5 formats. Once UNO is stable it is to be preferred as it can read ALL file formats supported by OOo (viz. wk1, ods, xlsx, sxc, ...). If you need to process really large spreadsheets, UNO is by far the fastest option (behind COM on Windows systems), but for smaller spreadsheets you'll find that the other interfaces are more efficient.

The OCT interface (native Octave calls) is by far the fastest for OOXML, the only Octave option for gnumeric, but for ODS it is still slower than COM/ActiveX or UNO. Experimental OCT write support is available for OOXML, ODS 1.2 and gnumeric but not extensively tested yet.

Some notes on the choice for Java:

1. It saves a LOT of development time to use ready-baked Java classes rather than developing your own routines and thus effectively reinvent the wheel.
2. A BIG advantage is that a Java-based solution is platform-independent ("portable").
3. The Java classes offer much more options than just reading and writing. Formatting, recalculation options, hiding/merging cell ranges, etc.
4. But Java is known to be not very conservative with resources, especially not when processing XML-based formats.

So Java is a compromise between portability and rapid development time versus capacity (and speed).

But IMO data sets larger than 5.10⁵ cells should not be kept in spreadsheets anyway. Better use real databases for such data sets.

Once all classes are present and in the javaclasspath, the xls interfaces should just work. The only remaining showstoppers are insufficient write privileges for the working directory, a wrecked up octave or some other problem outside octave.

6. Try opening an xls 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, xls2oct.m, oct2xls.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 __<INTF>_spsh_open__.m & __<INTF>_spsh_close__.m, __<INTF>_spsh2oct__.m & __<INTF>_oct2spsh__.m, __<INTF>_spsh_info__.m, and __<INTF>_getusedrange__.m subfunction scripts in ./private subdir, but that shouldn't be really hard, depending on the interface support libraries' quality and documentation. The function scripts in the ./private subdir provide for ample examples.
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.

Some other options for development (who?):

• Speeding up, especially Java worksheet/cell access. For cracks, not me.
• Automatic conversion of Excel date/time values into octave ones and vice versa (adding or subtracting 636960). But then again Excel's dates are 01-01-1900 based (octave's 0-0-0000) and buggy (Excel thinks 1900 is a leap year), and I sometimes have to use dates from before 1900. Maybe as an option?
• Creating Excel graphs (a significant enterprise to write from scratch).
• Support for "passing function handle" in xlsread.

Philip Nienhuis, May 1, 2014