Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[10982] trunk/octave-forge/main/io
|
From: <prn...@us...> - 2012-09-07 20:55:06
|
Revision: 10982
http://octave.svn.sourceforge.net/octave/?rev=10982&view=rev
Author: prnienhuis
Date: 2012-09-07 20:54:56 +0000 (Fri, 07 Sep 2012)
Log Message:
-----------
Unintended auto-indenting fixed
Modified Paths:
--------------
trunk/octave-forge/main/io/INDEX
trunk/octave-forge/main/io/NEWS
trunk/octave-forge/main/io/PKG_ADD
trunk/octave-forge/main/io/PKG_DEL
trunk/octave-forge/main/io/doc/READ-ODS.html
trunk/octave-forge/main/io/doc/READ-XLS.html
trunk/octave-forge/main/io/inst/calccelladdress.m
trunk/octave-forge/main/io/inst/chk_spreadsheet_support.m
trunk/octave-forge/main/io/inst/getusedrange.m
trunk/octave-forge/main/io/inst/io_ods_testscript.m
trunk/octave-forge/main/io/inst/io_xls_testscript.m
trunk/octave-forge/main/io/inst/object2json.m
trunk/octave-forge/main/io/inst/oct2ods.m
trunk/octave-forge/main/io/inst/oct2xls.m
trunk/octave-forge/main/io/inst/ods2oct.m
trunk/octave-forge/main/io/inst/odsclose.m
trunk/octave-forge/main/io/inst/odsfinfo.m
trunk/octave-forge/main/io/inst/odsopen.m
trunk/octave-forge/main/io/inst/odsread.m
trunk/octave-forge/main/io/inst/odswrite.m
trunk/octave-forge/main/io/inst/parse_sp_range.m
trunk/octave-forge/main/io/inst/parsecell.m
trunk/octave-forge/main/io/inst/pch2mat.m
trunk/octave-forge/main/io/inst/spsh_chkrange.m
trunk/octave-forge/main/io/inst/spsh_prstype.m
trunk/octave-forge/main/io/inst/xls2oct.m
trunk/octave-forge/main/io/inst/xlsclose.m
trunk/octave-forge/main/io/inst/xlsfinfo.m
trunk/octave-forge/main/io/inst/xlswrite.m
trunk/octave-forge/main/io/src/cell2csv.cc
trunk/octave-forge/main/io/src/csv2cell.cc
trunk/octave-forge/main/io/src/csvconcat.cc
trunk/octave-forge/main/io/src/csvexplode.cc
trunk/octave-forge/main/io/src/xmlread.cc
trunk/octave-forge/main/io/src/xmltree.c
trunk/octave-forge/main/io/src/xmltree.h
trunk/octave-forge/main/io/src/xmltree_read.c
trunk/octave-forge/main/io/src/xmltree_read.h
Modified: trunk/octave-forge/main/io/INDEX
===================================================================
--- trunk/octave-forge/main/io/INDEX 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/INDEX 2012-09-07 20:54:56 UTC (rev 10982)
@@ -1,45 +1,44 @@
io >> Input from and output to external formats
- File I/O
- append_save
- fexist
- Miscellaneous conversion functions
- object2json
- pch2mat
- CSV file functions
- csv2cell
- csvconcat
- csvexplode
- cell2csv
- XML I/O
- xmlread
- xmlwrite
- Spreadsheet I/O user functions for MS-Excel
- oct2xls
- xls2oct
- xlsclose
- xlsfinfo
- xlsopen
- xlsread
- xlswrite
- Spreadsheet I/O user functions for OpenOffice.org Calc
- oct2ods
- ods2oct
- odsclose
- odsfinfo
- odsopen
- odsread
- odswrite
- Spreadsheet utility functions
- calccelladdress
- chk_spreadsheet_support
- parsecell
- Spreadsheet I/O internal functions
- getusedrange
- parse_sp_range
- spsh_chkrange
- spsh_prstype
- Spreadsheet function test scripts
- io_xls_testscript
- io_ods_testscript
-
-
\ No newline at end of file
+File I/O
+ append_save
+ fexist
+Miscellaneous conversion functions
+ object2json
+ pch2mat
+CSV file functions
+ csv2cell
+ csvconcat
+ csvexplode
+ cell2csv
+XML I/O
+ xmlread
+ xmlwrite
+Spreadsheet I/O user functions for MS-Excel
+ oct2xls
+ xls2oct
+ xlsclose
+ xlsfinfo
+ xlsopen
+ xlsread
+ xlswrite
+Spreadsheet I/O user functions for OpenOffice.org Calc
+ oct2ods
+ ods2oct
+ odsclose
+ odsfinfo
+ odsopen
+ odsread
+ odswrite
+Spreadsheet utility functions
+ calccelladdress
+ chk_spreadsheet_support
+ parsecell
+Spreadsheet I/O internal functions
+ getusedrange
+ parse_sp_range
+ spsh_chkrange
+ spsh_prstype
+Spreadsheet function test scripts
+ io_xls_testscript
+ io_ods_testscript
+
Modified: trunk/octave-forge/main/io/NEWS
===================================================================
--- trunk/octave-forge/main/io/NEWS 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/NEWS 2012-09-07 20:54:56 UTC (rev 10982)
@@ -1,147 +1,146 @@
Summary of important user-visible changes for releases of the io package
-
- ===============================================================================
- io-1.0.20 Release Date: 2012-09-07 Release Manager: Philip Nienhuis
- ===============================================================================
-
- ** Bug fixes:
- --- xlsopen, xlsclose, odsopen, odsclose: replaced canonicalize_file_name call
- with make_absolute_filename for non-windows systems (canonicalize_file_name
- doesn't work with non-existent (new) files on *nix) (bug #36677);
- Web addresses (URLs) only need two rather than three slashes;
- --- xlsopen: matching .jar names to javaclasspath entries worked the wrong way
- --- io_xls_testscript / io_ods_testscript: added small delay for UNO calls to
- avoid lock-ups with recent LibreOffice (3.6.x)
-
- ** The annoying flashing LibreOffice splash screens have been fixed upstream;
- with LibreOffice 3.6.1 I didn't see them anymore
-
- ** Extended file rename section in odsclose similar to that in xlsclose
-
- ===============================================================================
- io-1.0.19 Release Date: 2012-06-08 Release Manager: Philip Nienhuis
- ===============================================================================
-
- ** Bug fixes:
- --- getusedrange subfunc getusedrange_jod: str2num applied to indices rather
- than the substring. Must have been there for > 2 years, only surfaced
- with jopendocument v 1.3b1
- --- oct2xls, oct2ods: cast all numeric types in input array to double as
- spreadsheets have only double, boolean or character string type. This bug
- has been there from the very beginning of the spreadsheet functions >8-O
-
- --- Support for reading back formulas from .xls spreadsheets using ActiveX/COM
-
- ** Compatible with jOpenDocument version 1.3b1
- getUsedRange() method added (MUCH faster than the old hack)
-
- ** Compatible with odfdom-java-0.8.8-incubator.jar (ODF Toolkit 0.5-incubating)
-
- ** Compatible with Apache POI 3.8 final
-
- ===============================================================================
- io-1.0.18 Release Date: 2012-03-22 Release Manager: Philip Nienhuis
- ===============================================================================
-
- ** The following functions have been imported from the miscellaneous package:
- cell2csv csvconcat xmlread
- csv2cell csvexplode xmlwrite
- Their error messages and help messages have been cleaned up a bit.
-
- ** Bug fixes:
- --- odsfinfo: fixed "wrong type argument `cell'" bug when run interactively.
- --- xlsopen, odsopen: fixed messed up screen output due to UNO usage warning.
- --- csv2cell: checks if file is empty and if so, return an empty cell.
- --- xlsopen: better Java detection logic, more informative error messages
-
- ** Adapted to internal LibreOffice-3.5-final changes.
- Some bugs (flashing LO screens) still have to be fixed upstream - see here:
- https://bugs.freedesktop.org/show_bug.cgi?id=42470
-
- ** Tried OpenXLS-6.0.7.jar. Reads OK, still unusable for writing .xls files.
-
- ===============================================================================
- io-1.0.17 Release Date: 2012-02-27 Release Manager: Philip Nienhuis
- ===============================================================================
-
- ** Bug fixes:
- --- oct2ods, oct2xls, odswrite default range input arg. These functions may not
- have worked properly for two years (!)
-
- ** Fixed support for odfdom v.0.8.7 (ODS). Note: the OTK interface only works
- well with xercesImpl.jar 2.9.1 (Sep 14, 2009)
-
- ** Many small bug fixes & documentation updated to actual functionality.
-
- ** Fixed "seealso" texinfo header string in almost all functions.
-
- ** Added formal test scripts to "internal functions" section.
-
- ===============================================================================
- io-1.0.16 Release Date: 2012-01-19 Release Manager: Philip Nienhuis
- ===============================================================================
-
- ** Bug fixing release
-
- ** PKG_ADD now expects Java spreadsheet class libs (.jars) in /lib/java
- (for MinGW)
-
- ===============================================================================
- io-1.0.15 Release Date: 2011-10-02 Release Manager: Philip Nienhuis
- ===============================================================================
-
- io-1.0.15 is primarily a bug fix release and a snapshot / wrap-up of current
- development status (some still a bit experimental). It mainly comprises:
-
- ** A number of bug fixes (incl. some serious ones, notably with .ods/OOo Calc);
-
- ** Some mainly cosmetic improvements to existing code; less verbosity;
-
- ** pch2mat (reading & transforming Nastran PCH files, contributed by
- B. Oytun Peksel);
-
- ** object2json.m (creating a json description string of objects, contributed
- by Daniel Torre). This was already silently introduced in io-1.0.14;
-
- ** A scripted troubleshooting / classpath setup tool for spreadsheet I/O
- support (chk_spreadsheet_support.m);
-
- ** Experimental OXS support (OpenXLS) for reading Excel xls (BIFF8).
- OpenXLS is -let's say- a little bit lacking: For reading it is faster than
- JXL. However, while OXS write support has been coded (and works) I had to
- disable it as the OXS Java classes won't release the file handle so Octave
- will hang upon closing :-( I'm stuck with this so I just release it as-is;
-
- ** Experimental UNO support, i.e. invoking OpenOffice.org (or clones like
- LibreOffice) behind the scenes to read spreadsheet files, much like
- ActiveX/COM for MS-Excel. This is also based on Java. The first time you
- use UNO, OOo has to be loaded and you'll have to be patient, but once loaded
- (and in the OS cache) you'll see the pros:
- --* Very fast;
- --* Much lower Java memory usage as OOo loads the spreadsheet in its own
- memory chunk (not Octave's) => much bigger spreadsheet capacity;
- --* You can read *all* formats supported by OOo: .ods, .xls, .csv, .xlsx,
- .sxc, .dbf, Lotus wk1, Quattro Pro, ......; and it doesn't really matter
- whether xlsopen of odsopen is used.
- Of course all this wonderful stuff comes at a prize:
- --* After closing the spreadsheet file (odsclose, xlsclose) ALL OOo
- invocations will be closed, also those started outside Octave. This is
- due to "the way OpenOffice works" (quoted from OOo dev forum), especially
- through Java. There are other ways to close OOo but they'll hang Octave;
- --* The Java UNO classes supplied with e.g. LibreOffice aren't kept quite
- up-to-date with the main program. As a consequence, with e.g.,
- LibreOffice 3.4 the main LO window will pop up (it can't be hidden). I
- filed a bug report for this
- (https://bugs.freedesktop.org/show_bug.cgi?id=40991) but I haven't seen
- it being picked up yet. Another example: while LO 3.3.1's row capacity
- was already > 10^6, it took until LO 3.4 before this capacity was
- implemented in the Java UNO classes.
- Like with OXS, I'm a bit stuck here - all this has to be fixed upstream.
-
- Hint:
- for older Octave versions (< 3.4.0) you can install io-1.0.15 using the -nodeps
- flag. You'll then loose the old and buggy textread and csv/dlm-read/write
- functions but I'd consider that as no big loss.
-
- <please scroll up/back to see rest of io NEWS>
-
\ No newline at end of file
+
+===============================================================================
+io-1.0.20 Release Date: 2012-09-07 Release Manager: Philip Nienhuis
+===============================================================================
+
+** Bug fixes:
+--- xlsopen, xlsclose, odsopen, odsclose: replaced canonicalize_file_name call
+ with make_absolute_filename for non-windows systems (canonicalize_file_name
+ doesn't work with non-existent (new) files on *nix) (bug #36677);
+ Web addresses (URLs) only need two rather than three slashes;
+--- xlsopen: matching .jar names to javaclasspath entries worked the wrong way
+--- io_xls_testscript / io_ods_testscript: added small delay for UNO calls to
+ avoid lock-ups with recent LibreOffice (3.6.x)
+
+** The annoying flashing LibreOffice splash screens have been fixed upstream;
+ with LibreOffice 3.6.1 I didn't see them anymore
+
+** Extended file rename section in odsclose similar to that in xlsclose
+
+===============================================================================
+io-1.0.19 Release Date: 2012-06-08 Release Manager: Philip Nienhuis
+===============================================================================
+
+** Bug fixes:
+--- getusedrange subfunc getusedrange_jod: str2num applied to indices rather
+ than the substring. Must have been there for > 2 years, only surfaced
+ with jopendocument v 1.3b1
+--- oct2xls, oct2ods: cast all numeric types in input array to double as
+ spreadsheets have only double, boolean or character string type. This bug
+ has been there from the very beginning of the spreadsheet functions >8-O
+
+--- Support for reading back formulas from .xls spreadsheets using ActiveX/COM
+
+** Compatible with jOpenDocument version 1.3b1
+ getUsedRange() method added (MUCH faster than the old hack)
+
+** Compatible with odfdom-java-0.8.8-incubator.jar (ODF Toolkit 0.5-incubating)
+
+** Compatible with Apache POI 3.8 final
+
+===============================================================================
+io-1.0.18 Release Date: 2012-03-22 Release Manager: Philip Nienhuis
+===============================================================================
+
+** The following functions have been imported from the miscellaneous package:
+ cell2csv csvconcat xmlread
+ csv2cell csvexplode xmlwrite
+ Their error messages and help messages have been cleaned up a bit.
+
+** Bug fixes:
+--- odsfinfo: fixed "wrong type argument `cell'" bug when run interactively.
+--- xlsopen, odsopen: fixed messed up screen output due to UNO usage warning.
+--- csv2cell: checks if file is empty and if so, return an empty cell.
+--- xlsopen: better Java detection logic, more informative error messages
+
+** Adapted to internal LibreOffice-3.5-final changes.
+ Some bugs (flashing LO screens) still have to be fixed upstream - see here:
+ https://bugs.freedesktop.org/show_bug.cgi?id=42470
+
+** Tried OpenXLS-6.0.7.jar. Reads OK, still unusable for writing .xls files.
+
+===============================================================================
+io-1.0.17 Release Date: 2012-02-27 Release Manager: Philip Nienhuis
+===============================================================================
+
+** Bug fixes:
+--- oct2ods, oct2xls, odswrite default range input arg. These functions may not
+ have worked properly for two years (!)
+
+** Fixed support for odfdom v.0.8.7 (ODS). Note: the OTK interface only works
+ well with xercesImpl.jar 2.9.1 (Sep 14, 2009)
+
+** Many small bug fixes & documentation updated to actual functionality.
+
+** Fixed "seealso" texinfo header string in almost all functions.
+
+** Added formal test scripts to "internal functions" section.
+
+===============================================================================
+io-1.0.16 Release Date: 2012-01-19 Release Manager: Philip Nienhuis
+===============================================================================
+
+** Bug fixing release
+
+** PKG_ADD now expects Java spreadsheet class libs (.jars) in /lib/java
+ (for MinGW)
+
+===============================================================================
+io-1.0.15 Release Date: 2011-10-02 Release Manager: Philip Nienhuis
+===============================================================================
+
+io-1.0.15 is primarily a bug fix release and a snapshot / wrap-up of current
+ development status (some still a bit experimental). It mainly comprises:
+
+** A number of bug fixes (incl. some serious ones, notably with .ods/OOo Calc);
+
+** Some mainly cosmetic improvements to existing code; less verbosity;
+
+** pch2mat (reading & transforming Nastran PCH files, contributed by
+ B. Oytun Peksel);
+
+** object2json.m (creating a json description string of objects, contributed
+ by Daniel Torre). This was already silently introduced in io-1.0.14;
+
+** A scripted troubleshooting / classpath setup tool for spreadsheet I/O
+ support (chk_spreadsheet_support.m);
+
+** Experimental OXS support (OpenXLS) for reading Excel xls (BIFF8).
+ OpenXLS is -let's say- a little bit lacking: For reading it is faster than
+ JXL. However, while OXS write support has been coded (and works) I had to
+ disable it as the OXS Java classes won't release the file handle so Octave
+ will hang upon closing :-( I'm stuck with this so I just release it as-is;
+
+** Experimental UNO support, i.e. invoking OpenOffice.org (or clones like
+ LibreOffice) behind the scenes to read spreadsheet files, much like
+ ActiveX/COM for MS-Excel. This is also based on Java. The first time you
+ use UNO, OOo has to be loaded and you'll have to be patient, but once loaded
+ (and in the OS cache) you'll see the pros:
+ --* Very fast;
+ --* Much lower Java memory usage as OOo loads the spreadsheet in its own
+ memory chunk (not Octave's) => much bigger spreadsheet capacity;
+ --* You can read *all* formats supported by OOo: .ods, .xls, .csv, .xlsx,
+ .sxc, .dbf, Lotus wk1, Quattro Pro, ......; and it doesn't really matter
+ whether xlsopen of odsopen is used.
+ Of course all this wonderful stuff comes at a prize:
+ --* After closing the spreadsheet file (odsclose, xlsclose) ALL OOo
+ invocations will be closed, also those started outside Octave. This is
+ due to "the way OpenOffice works" (quoted from OOo dev forum), especially
+ through Java. There are other ways to close OOo but they'll hang Octave;
+ --* The Java UNO classes supplied with e.g. LibreOffice aren't kept quite
+ up-to-date with the main program. As a consequence, with e.g.,
+ LibreOffice 3.4 the main LO window will pop up (it can't be hidden). I
+ filed a bug report for this
+ (https://bugs.freedesktop.org/show_bug.cgi?id=40991) but I haven't seen
+ it being picked up yet. Another example: while LO 3.3.1's row capacity
+ was already > 10^6, it took until LO 3.4 before this capacity was
+ implemented in the Java UNO classes.
+ Like with OXS, I'm a bit stuck here - all this has to be fixed upstream.
+
+Hint:
+for older Octave versions (< 3.4.0) you can install io-1.0.15 using the -nodeps
+ flag. You'll then loose the old and buggy textread and csv/dlm-read/write
+ functions but I'd consider that as no big loss.
+
+ <please scroll up/back to see rest of io NEWS>
Modified: trunk/octave-forge/main/io/PKG_ADD
===================================================================
--- trunk/octave-forge/main/io/PKG_ADD 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/PKG_ADD 2012-09-07 20:54:56 UTC (rev 10982)
@@ -1,16 +1,16 @@
## PKG_ADD
- ## add paths of io pkg java jars if java pkg is installed and loaded
- pkglist = pkg ("list");
- javapkgind = find (cellfun (@(x) strcmp(x.name, "java"), pkglist), 1, "first");
- if (! isempty (javapkgind))
- javapkg = pkglist{javapkgind};
- if (javapkg.loaded)
- # Assume jar files are in /lib/java and let chk_spreadsheet_support sort it out
- libdir = octave_config_info ("libdir");
- ## Allow time for package to be fully loaded
- pause (0.25);
- spr_status = chk_spreadsheet_support ([ libdir filesep "java" ]);
- endif
- endif
- ## Clean up
- clear javapkgind javapkg libdir spr_status pkglist ;
\ No newline at end of file
+## add paths of io pkg java jars if java pkg is installed and loaded
+pkglist = pkg ("list");
+javapkgind = find (cellfun (@(x) strcmp(x.name, "java"), pkglist), 1, "first");
+if (! isempty (javapkgind))
+ javapkg = pkglist{javapkgind};
+ if (javapkg.loaded)
+ # Assume jar files are in /lib/java and let chk_spreadsheet_support sort it out
+ libdir = octave_config_info ("libdir");
+ ## Allow time for package to be fully loaded
+ pause (0.25);
+ spr_status = chk_spreadsheet_support ([ libdir filesep "java" ]);
+ endif
+endif
+## Clean up
+clear javapkgind javapkg libdir spr_status pkglist ;
\ No newline at end of file
Modified: trunk/octave-forge/main/io/PKG_DEL
===================================================================
--- trunk/octave-forge/main/io/PKG_DEL 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/PKG_DEL 2012-09-07 20:54:56 UTC (rev 10982)
@@ -1,8 +1,7 @@
## PKG_DEL
- ##
- ## Below are base names of spreadsheet I/O Java class libs.
- ## They'll stay (harmless) in the javaclasspath until termination of the current Octave session.
- ## {'poi-3', 'poi-ooxml-3', xbean', 'poi-ooxml-schemas', 'dom4j-1.6.1', 'jxl', 'openxls', ...
- ## 'odfdom', 'xercesImpl', 'jopendocument', ...
- ## 'program', 'unoil', 'jurt', 'juh', 'unoloader', 'ridl'}
-
\ No newline at end of file
+##
+## Below are base names of spreadsheet I/O Java class libs.
+## They'll stay (harmless) in the javaclasspath until termination of the current Octave session.
+## {'poi-3', 'poi-ooxml-3', xbean', 'poi-ooxml-schemas', 'dom4j-1.6.1', 'jxl', 'openxls', ...
+## 'odfdom', 'xercesImpl', 'jopendocument', ...
+## 'program', 'unoil', 'jurt', 'juh', 'unoloader', 'ridl'}
Modified: trunk/octave-forge/main/io/doc/READ-ODS.html
===================================================================
--- trunk/octave-forge/main/io/doc/READ-ODS.html 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/doc/READ-ODS.html 2012-09-07 20:54:56 UTC (rev 10982)
@@ -170,8 +170,7 @@
<b>odsopen</b> / <b>ods2oct</b> / \x85 / <b>oct2ods</b> / \x85.
/ <b>odsclose</b>, <b><u><i>DO NOT FORGET</i></u></b> to invoke <b>odsclose</b> 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
+ 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.</font></font></p></dt><dt><br>
</dt></dl>
<p align="center"><font face="Arial, sans-serif"><font size="4"><u><b>SPREADSHEET
Modified: trunk/octave-forge/main/io/doc/READ-XLS.html
===================================================================
--- trunk/octave-forge/main/io/doc/READ-XLS.html 2012-09-07 15:44:24 UTC (rev 10981)
+++ trunk/octave-forge/main/io/doc/READ-XLS.html 2012-09-07 20:54:56 UTC (rev 10982)
@@ -1,333 +1,332 @@
<HTML>
- <HEAD>
- <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
- <META NAME="Generator" CONTENT="Microsoft Word 97">
- <META NAME="CREATED" CONTENT="20091211;17230700">
- <META NAME="CHANGEDBY" CONTENT="Philip Nienhuis">
- <META NAME="CHANGED" CONTENT="20120226;18083900">
- </HEAD>
- <BODY LINK="#0000ff" VLINK="#800080">
-
- <P><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"></P>
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P ALIGN="CENTER">README for Excel spreadsheet file r/w access scripts for octave (> 3.4.0)<BR>
- <BR>
- Copyright (C) 2009 - 2012 Philip Nienhuis <prnienhuis at users.sf.net><BR>
- <BR>
- This version September 7, 2012</P>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">EXCEL .XLS SUPPORT FILES</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT><BR>
- <B>doc/README-XLS.html</B><BR>
- This file.</FONT></FONT> </DT><BR>
- <B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DT>xlsread.m</DT>
- </B><DT>All-in-one function for reading data from one specific worksheet in an Excel spreadsheet file. This script has Matlab-compatible functionality. <BR>
- </DT><BR>
- <B>xlswrite.m</B>
- <DT>All-in-one function for writing data to one specific worksheet in an Excel spreadsheet file. This script has Matlab-compatible functionality.<BR>
- <BR>
- <B>xlsfinfo.m</B>
- <DT>All-in-one function for exploring basic properties of an Excel spreadsheet file. This script has Matlab-compatible functionality. <BR>
- <BR>
- <B>xlsopen.m</B></DT>
- <DT>Function for "opening" (= providing a handle to) an Excel spreadsheet file ("workbook"). This function sorts out which interface to use for .xls access (i.e.,COM; Java & Apache POI; JexcelAPI; OpenXLS; etc.), but it's choice can be overridden. <BR>
- <BR>
- <B>xls2oct.m</B></DT>
- <DT>Function for reading data from a specific worksheet pointed to in a struct created by xlsopen.m. xls2oct can be called multiple times consecutively using the same pointer struct, each time allowing to read data from different ranges and/or worksheets. Data are returned in the form of a 2D heterogeneous cell array that can be parsed by parsecell.m. xls2oct is a mere wrapper for interface-dependent scripts that do the actual low-level reading. <BR>
- <BR>
- <B>oct2xls.m</B></DT>
- <DT>Function for writing data to a specific worksheet pointed to in a struct created by xlsopen.m. octxls can be called multiple times consecutively using the same pointer struct, each time allowing to write data to different ranges and/or worksheets. oct2xls is a mere wrapper for interface-dependent scripts that do the actual low-level writing. <BR>
- <BR>
- <B>xlsclose.m</B></DT>
- <DT>Function for closing (the handle to) an Excel workbook. When data have been written to the workbook oct2xls will write the workbook to disk. Otherwise, the file pointer is simply closed and possibly used interfaces for Excel access (COM/ActiveX/Excel.exe) will be shut down properly. <BR>
- <BR>
- <B>parsecell.m</B></DT>
- <DT>Function for separating the data in raw arrays returned by xls2oct, into numerical/logical and text (cell) arrays. <BR>
- <BR>
- <B>chk_spreadsheet_support.m</B></DT>
- <DT>Internal function for (1) checking, (2) setting up, (3) debugging spreadsheet support. While not specifically meant for direct invocation from the Octave prompt (it is more useful during initialization of Octave itself) it can be very helpful when hunting down issues with spreadsheet support in Octave.</DT>
- </DL>
- <B>spsh_chkrange.m</B>,</FONT></FONT> <B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>spsh_prstype.m</B>,</FONT></FONT> <B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>getusedrange.m</B>,</FONT></FONT> <B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>calccelladdress.m</B>,</FONT></FONT> <B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>parse_sp_range.m
- </B>
- <DT>Support files called by the scripts and not meant for direct invocation by users.<BR></DT>
- <BR>
- <DT><B>io_xls_testscript.m</B></DT>
- <DT>Script for testing basic features of the spreadsheet scripts.</DT>
- </DL>
- <P ALIGN="CENTER"><BR>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4>REQUIRED SUPPORT SOFTWARE</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT><BR>
- For the Excel/COM interface:</DT>
- </DL>
-
- <UL>
- <LI>A windows computer with Excel installed</LI>
- <LI>Octave-forge Windows-1.0.8 (WITH LATEST SVN PATCHES APPLIED) or later package (preferrably 1.1.10 or later)</LI></UL>
-
- <P>For the Java / Apache POI / JExcelAPI interfaces (general):</FONT></FONT> </P>
-
- <UL>
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><LI>octave-forge java-1.2.8 package or later version on Linux</LI>
- <LI>octave-forge java-1.2.8 with latest svn fixes, or later version on Windows/MingW</LI>
- <LI>Java JRE or JDK > 1.6.0 (hasn't been tested with earlier versions)</LI></UL>
-
- <DL>
- <DT>Apache POI specific:</DT>
- </DL>
-
- <UL>
- <LI>class .jars: <B>poi-3.5-FINAL-<date>.jar</B> & <B>poi-ooxml-3.5-FINAL-<date>.jar</B> (or later versions) in classpath</LI>
- <LI>Get it here: </FONT></FONT><A HREF="http://poi.apache.org/download.html"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://poi.apache.org/download.html</FONT></FONT></A></LI></UL>
- <DIR>
- <DIR>
-
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P>and here:</P></DIR>
- </DIR>
-
-
- <UL>
- <LI>for OOXML support (only available with Apache POI): </LI></UL>
- <DIR>
- <DIR>
-
- <B><P>poi-ooxml-schemas-<version>.jar</B>, <B>xbean.jar</B>, <B>dom4j-1.6.1.jar</B> in javaclasspath. </P>
- <P>Get them here:</P>
- </FONT></FONT><P><A HREF="http://poi.apache.org/download.html"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://poi.apache.org/download.html</FONT></FONT></A><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2> ("xmlbeans" and poi-ooxml-schemas)</P>
- </FONT></FONT><P><A HREF="http://sourceforge.net/projects/dom4j/files"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://sourceforge.net/projects/dom4j/files</FONT></FONT></A><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2> (dom4j-<version>)</P></DIR>
- </DIR>
-
- <DL>
- <DT>JExcelAPI specific:</DT>
- </DL>
-
- <UL>
- <LI>class .jar: <B>jxl.jar</B> in classpath</LI>
- <LI>Get it here: </FONT></FONT><A HREF="http://sourceforge.net/projects/jexcelapi/files/"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://sourceforge.net/projects/jexcelapi/files/</FONT></FONT></A></LI></UL>
-
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT>OpenXLS specific:</DT>
- </DL>
-
- <UL>
- <LI>class .jar: <B>jxl.jar</B> in classpath</LI>
- <LI>Get it here: </FONT></FONT><A HREF="http://sourceforge.net/projects/openxls/"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://sourceforge.net/projects/openxls/</FONT></FONT></A></LI></UL>
-
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT>These class libs must be referenced with full pathnames in your javaclasspath.<br>They had best be put in /<libdir>/java where <libdir> on Linux is usually /usr/lib; on MinGW it is usually /lib. The PKG_ADD command 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.</DT></DL>
- <DL><DT>UNO specific (invoking OpenOffice.org (or clones) behind the scenes):<BR>
- NOTE: EXPERIMENTAL!! A working OpenOffice.org installation. The utility function chk_spreadsheet_support can be used to add the needed entries to the javaclasspath.<BR></DT>
- </DL>
- <P ALIGN="CENTER"><BR>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4>USAGE</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT><BR>
- <B>xlsread</B> and <B>xlswrite</B> are mere wrappers for <B>xlsopen</B>-<B>xls2oct</B>-<B>xlsclose</B>-<B>parsecell</B> and <B>xlsopen</B>-<B>oct2xls</B>-<B>xlsclose</B> sequences, resp. They exist for the sake of Matlab compatibility.<BR>
- <BR>
- <B>xlsfinf</B>o 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' (case-insensitive) and -obviously- the complete POI interface must have been installed.<BR>
- <BR>
- Invoking <B>xlsopen</B>/..../<B>xlsclose</B> directly provides for much more flexibility, speed, and robustness than <B>xlsread</B> / <B>xlswrite</B>. Indeed, using the same file handle (pointer struct) you can mix reading & writing before writing the workbook out to disk using xlsclose.<BR>
- And: <B>xlsopen </B>/ <B>xlsclose</B> hide the gory interface details from the user.<BR>
- 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 must be installed (and probably the REQINTF parameter specified with a value of 'poi').<BR>
- <BR>
- When using <B>xlsopen</B>....<B>xlsclose</B> be sure to keep track of the file handle struct.<BR>
- <BR>
- A possible scenario:<BR>
- <BR>
- <B>xlh = xlsopen (<excel_filename> , [rw], [<requested interface>])</B><BR>
- <I># Set rw to 1 if you want to write to a workbook immediately.<BR>
- # In that case the check for file existence is skipped and<BR>
- # -if needed- a new workbook created.<BR>
- # If you really want an other interface than auto-selected<BR>
- # by xlsopen you can request that. But xlsopen still checks<BR>
- # proper support for your choice.</I><BR>
- <BR>
- <I># Read some data</I><BR>
- <B>[ rawarr1, xlh ] = xls2oct (xlh, <SomeWorksheet>, <Range>)</B><BR>
- <I># Be sure to specify xlh as output argument as xls2oct keeps<BR>
- # track of changes and the need to write the workbook to disk <BR>
- # in the xlhstruct. And the origin range is conveyed through<BR>
- # the xlh pointer struct.</I><BR>
- <BR>
- <I># Separate data into numeric and text data</I><BR>
- <B>[ numarr1, txtarr1, lim1 ] = parsecell (rawarr1)</B><BR>
- <I><BR>
- </I># Get more data from another worksheet in the same workbook<BR>
- <B>[ rawarr2, xlh ] = xls2oct (xlh, <SomeOtherWorksheet>, <Range>)</B><BR>
- <B>[ numarr2, txtarr2, lim2 ] = parsecell (rawarr2)</B><BR>
- <BR>
- # <... Analysis and preparation of new data in cell array Newdata....><BR>
- <BR>
- <I># Add new data to spreadsheet</I><BR>
- <B>xlh = oct2xls (Newdata, xlh, <AnotherWorksheet>, <Range>)</B><BR>
- <BR>
- <I># Close the workbook and write it to disk; then clear the handle</I><BR>
- <B>xlh = xlsclose (xlh)</B><BR>
- <B>clear xlh</DT>
- </B><DT>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.</DT>
- <DT>When using JExcelAPI (JXL), after writing into a worksheet you MUST save the file \x96 adding data to the same or another worksheet is no more possible after the first call to oct2xls(). This is a limitation of JExcelAPI.</DT>
- </DL>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">SPREADSHEET FORMULA SUPPORT</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT><BR>
- When using the COM, POI, JXL, and UNO interfaces you can:</DT>
- </DL>
-
- <UL>
- <LI>(When reading, xls2oct) either read evaluated spreadsheet formula results, or the literal formula text strings;</LI>
- <LI>(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.</LI></UL>
-
- <P>In short, you can enter spreadsheet formulas and in a later stage read them back, change them and re-enter them in the worksheet. </P>
- <DL>
- <DT>The behaviour is controlled by an option structure <B>options</B></FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>which for now has only one (logical) field:</DT>
- <B><DT>options.formulas_as_text</B></FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>= 0 (the default) implies enter formulas as formulas and read back formula results</DT>
- <B><DT>options.formulas_as_text </B>=1 (or any positive integer) means enter formulas as text strings and read them back as text strings.</DT>
- <DT>Be aware that there's no formula evaluator in JExcelAPI (JXL). So if you create formulas in your spreadsheet using oct2xls or xlswrite with 'JXL', do not expect meaningful results when reading those files later on <B>unless</B></FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>you open them in Excel and write them back to disk.</DT>
- <DT>While both Apache POI and JExcelAPI feature a formula validator, not all spreadsheet functions present in Excel have been implemented (yet).</DT>
- <DT>Worse, older Excel versions feature less functions than newer versions. So be wary as this may make for interesting confusion.</DT>
- </DL>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">MATLAB COMPATIBILITY</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT><BR>
- <B>xlsread</B>, <B>xlswrite</B> and <B>xlsfinfo</B> are for the most part Matlab-compatible. Some small differences are mentioned below. When using the Java interfaces octave supplies some formula manipulation support.<BR>
- <BR>
- <B><U>xlsread</B></U><BR>
- Matlab's <B>xlsread</B> supports invoking extra functions while reading ("passing function handle"); octave not. But this can be simulated outside <B>xlsread</B>.<BR>
- <BR>
- Matlab's <B>xlsread</B> flags some spreadsheet errors, octave-forge just returns blank cells.<BR>
- <BR>
- Octave's xlsread 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 :-)</DT>
- <DT>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 <B>xlsfinfo</B>.m returns wrong data ranges it is most often an overestimation.</DT>
- <DT>Matlab's <B>xlsread</B> ignores all non-numeric data values outside the smallest rectangle encompassing all numerical values. Octave's <B>xlsread</B> doesn't. This means that Matlab ignores all row/column headers, not very user-friendly IMO. <BR>
- <BR>
- 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 <B>xlswrite</B>'s 'Basic" option) \x96 and then differently than under Windows.....</DT>
- <DT>Matlab's <B>xlsread</B> 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 \x96 you can easily transfer them into proper octave date values yourself using e.g. datestr(), see bottom of this document for more info).<BR>
- <BR>
- Matlab's <B>xlsread</B> invokes <B>csvread</B> if no Excel interface is present. Octave's <B>xlsread</B> doesn't.<BR>
- <BR>
- <B><U>xlswrite</B></U><BR>
- Octave's <B>xlswrite</B> works on systems w/o Excel support, Matlab's doesn't (properly).</DT>
- <DT>When specifying a sheet number larger than the number of existing sheets in an .xls file, Matlab's <B>xlswrite</B> adds empty sheets until the new sheet number is created; Octave's <B>xlswrite</B> only adds one sheet called "Sheet<number>" where <number> is the specified sheet number. </DT>
- <DT>Even better (IMO) while M's <B>xlswrite</B> always creates Sheet1/Sheet2/Sheet3 when creating a new spreadsheet, octave's <B>xlswrite</B> 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.)</DT>
- <DT>Oh and octave doesn't touch the "active sheet" - but that's not automatically an advantage.</DT>
- <DT>If the specified write range is larger than the actual data array, Matlab's <B>xlswrite</B> adds #N/A cells to fill up the lowermost rows and rightmost columns; octave-forge's <B>xlswrite</B> doesn't. <BR>
- <BR>
- <B>xlsfinfo</B><BR>
- When invoking Excel/COM interface, octave's <B>xlsfinfo</B> also echoes the type of sheet (worksheet, chart), not just the sheet names. Using Java I haven't found similar functionality (yet).</DT>
- </DL>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">COMPARISON OF INTERFACES & USAGE</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT>Using Excel itself (through <b>COM / ActiveX</b> 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. <BR>
- A tiny problem is that one cannot find out easily through COM what file types are supported; xls, wks, wk1, xlsx, etc. <BR>
- Another -obvious- limitation is that COM Excel access only works on Windows systems where Excel is installed. <BR>
- <BR>
- <b>JExcelAPI</b> (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 <B>in place</B> 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.</DT>
- <DT>JExcelAPI supports BIFF5 (only reading) and BIFF8 (Excel 95 and Excel 97-2003, respectively). Upon overwriting, BIFF5 spreadsheets are converted silently to BIFF8.</DT>
- <DT>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. <BR>
- <BR>
- <b>Apache POI</b> (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 \x96 2003) and OOXML (Excel 2007).</DT>
- <DT>It is slower than native JXL let alone Excel & COM but it features active formula evaluation, although at the moment (v. 3.8) still not all Excel functions have been implemented. 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. <BR>
- <BR>
- <b>OpenXLS</b> (an open source version of Extentech's commercial Java-xls product) is still experimental. It seems to work faster than JExcelAPI, but it has other issues - i.e., it locks the .xls file and the unlocking mechanism is a bit wonky. Sometimes xls files keep being locked until Octave is shut down. Currently OXS write support is disabled (but the code is there). OpenXLS doesn't support reading back formulas as text strings.<BR>
- <BR>
- <b>UNO</b> (invoking OpenOffice.org or clones behind the scenes, a la ActiveX) is experimental. It works FAST (i.e., once OOo itself is loaded 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.<BR>
- 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.<br>
- 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.<br>
- A way out hasn't been found yet.<br><br>
- 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, ...)</DT>
- <DT><br>Some notes on the choice for Java:</DT>
- </DL>
- <OL>
-
- <LI>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.</LI>
- <LI>A BIG advantage is that a Java-based solution is platform-independent ("portable").</LI>
- <LI>But Java is known to be not very conservative with resources, especially not when processing XML-based formats.</LI></OL>
-
- <DL>
- <DT>So Java is a compromise between portability and rapid development time versus capacity (and speed).</DT>
- <DT>But IMO data sets larger than 5.10<SUP>5</SUP> cells should not be kept in spreadsheets anyway. Better use real databases for such data sets.</DT>
- </DL>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">A NOTE ON JAVA MEMORY USAGE</P>
- </U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P>Java memory pool allocation size</P>
- </B><DL>
- <DT>The Java virtual machine (JVM), when initialized by octave, reserves 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 16 MB. On Linux this allocated size might differ (e.g., my Mandriva box with openJDK has a 512 MB default max setting). This part of memory is where the Java-based XLS/ODS octave routines live and keep their variables etc.</DT>
- <DT>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 500,000 cells I needed a memory pool size of 512 MB.</DT>
- <DT>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:</DT>
- </FONT></FONT><B><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2><DT>-Xms16m<BR>
- -Xmx512m</DT>
- </B></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DT>(where 16 = initial size, 512 = maximum size (in this example), m stands for Megabyte. This maximum is system-dependent. E.g., I have a 1 GB setting).</DT>
- <DT>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 <I>allocated</I> (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.</DT>
- </DL>
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">TROUBLESHOOTING</P>
- </B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT>Some hints for troubleshooting Excel support are contained in this thread:</DT>
- </FONT></FONT><DT><A HREF="http://sourceforge.net/mailarchive/forum.php?thread_name=4C61B649.9090802%40hccnet.nl&forum_name=octave-dev"><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>http://sourceforge.net/mailarchive/forum.php?thread_name=4C61B649.9090802%40hccnet.nl&forum_name=octave-dev</FONT></FONT></A></DT>
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DT>dated August 10, 2010.</DT>
- <DT>A more structured approach is below.<BR>
- <BR>
- Since April 2011 a special purpose setup file has been included in the io package (chk_spreadsheet_support.m). Large parts of the approach below (starting at Step 2) have been automated in this script. When running it with the second input argument (debug level) set to 3 a lot of useful diagnostic output will be printed to screen.</DT>
- </DL>
- <OL>
-
- <LI>Check if COM / ActiveXworks (only under Windows OS). Do a </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>pkg list </FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>and see</LI>
- </FONT></FONT><P>a. <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>If there's a windows package mentioned (then it's installed). If not, install it.</P>
- <P>b. If there's an asterisk on the windows package line (then the package is loaded). If not, do a </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>pkg load windows</FONT></FONT> </P>
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><LI>Check if the ActiveX server works. Do:</LI>
- </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2><P>exl = actxserver ('Excel.Application')</FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>## Note the period between "Excel" and "Application"</P>
- <P>If a COM object is returned, ActiveX / COM / Excel works. Do: </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>exl.Quit(); delete (exl)</FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>to shut down the (hidden) Excel invocation.</P>
- <P>If you get an error message, your last resort is re-installing the windows package, or trying the Java-based interfaces.</P>
- <LI>Check if java works. Do a </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>pkg list </FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>and see</LI></OL>
- <DIR>
- <DIR>
-
- </FONT></FONT><P>a. <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>If there's a java package mentioned (then it's installed). If not, install it.</P>
- <P>b. If there's an asterisk on the java package line (then the package is loaded). If not, do a </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>pkg rebuild -auto java</P></DIR>
- </DIR>
-
- <OL START=4>
-
- </FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><LI>Check Java memory settings. Try </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>javamem</LI>
- </FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P>a. If it works, check if it reports sufficiently large max memory (had better be 200 MiB, the bigger the better)</P>
- <P>b. If it doesn't work, do:</FONT></FONT> </P>
- <FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2><P>rt = java_invoke ('java.lang.Runtime', 'getRuntime')<BR>
- rt.gc<BR>
- rt.maxMemory ().doubleValue () / 1024 / 1024</P>
- </FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P>The last command will show MaxMemory in MiB.<br></P>
- <P>c. In case you have insufficient memory, see in "GOTCHAS", "Java memory pool allocation size", how to increase java's memory pre-reservation.</P>
- <LI>Check if all classes (.jarfiles) are in class path. Do a '</FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>javaclasspath</FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>' (under unix/linux, do '</FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>tmp = javaclasspath; strsplit (tmp,":")</FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>' (w/o quotes). See above under "REQUIRED SUPPORT SOFTWARE" what classes should be mentioned.</LI></OL>
- <DIR>
- <DIR>
-
- <P>If classes (.jar files) are missing, download and put them somewhere and add them to the javaclass path with their fully qualified pathname (in quotes) using </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>javaaddpath()</FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>.</P></DIR>
- </DIR>
-
- <P>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.</P>
- <OL START=6>
-
- <LI>Try opening an xls file: </LI></OL>
- <DIR>
- <DIR>
-
- </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2><P>xls1 = xlsopen ('test.xls', 1, 'poi')</FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>. If this works and xls1 is a struct with various fields containing objects, the Apache POI interface (POI) works. Do an </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>xls1 = xlsclose (xls1)</FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>to close the file.</P>
- </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2><P>xls2 = xlsopen ('test.xls', 1, 'jxl')</FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>. 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 </FONT></FONT><FONT FACE="Courier New, monospace" SIZE=2><FONT FACE="Courier New, monospace" SIZE=2>xls2 = xlsclose (xls2)</FONT></FONT> <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2>to close the file.</P></DIR>
- </DIR>
-
- </FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">DEVELOPMENT</P>
- </U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DT>xlsopen</B>/<B>xlsclos</B>e and friends have been written so that adding other interfaces (Perl? native octave? ...?) should be very easily accomplished. <B>Xlsopen.m</B> merely needs two stanzas, <B>xlsfinfo.m</B> and <B>getusedrange.m</B> each need an additional elseif stanza, and <B>xlsclose.m</B> needs a small stanza for closing the pointer struct and writing to disk. </DT>
- <DT>The real work lies in creating the relevant <B><I>xls2<...>2oct</B></I> & <B><I>oct2<...>2xls</B></I> & <B><I><getusedrange_...></B></I> subfunction scripts in <B>xls2oct.m</B>, <B>oct2xls.m</B> and <B>getusedrange.m</B>, 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.<BR>
- <BR>
- Some other options for development (who?):</DT>
- </DL>
-
- <UL>
- <LI>Speeding up, especially Java worksheet/cell access. For cracks, not me.</LI>
- <LI>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?</LI>
- <LI>Creating Excel graphs (a significant enterprise to write from scratch).</LI>
- <LI>Support for "passing function handle" in xlsread.</LI></UL>
-
- <P>Enjoy!</FONT></FONT> </P>
- <FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
- <DD ALIGN="CENTER">Philip Nienhuis, September 7, 2012</DD>
- </DL></FONT></FONT></BODY>
- </HTML>
-
\ No newline at end of file
+<HEAD>
+<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252">
+<META NAME="Generator" CONTENT="Microsoft Word 97">
+<META NAME="CREATED" CONTENT="20091211;17230700">
+<META NAME="CHANGEDBY" CONTENT="Philip Nienhuis">
+<META NAME="CHANGED" CONTENT="20120226;18083900">
+</HEAD>
+<BODY LINK="#0000ff" VLINK="#800080">
+
+<P><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"></P>
+<FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><P ALIGN="CENTER">README for Excel spreadsheet file r/w access scripts for octave (> 3.4.0)<BR>
+<BR>
+Copyright (C) 2009 - 2012 Philip Nienhuis <prnienhuis at users.sf.net><BR>
+<BR>
+This version September 7, 2012</P>
+</FONT></FONT><B><U><FONT FACE="Arial, sans-serif" SIZE=4><FONT FACE="Arial, sans-serif" SIZE=4><P ALIGN="CENTER">EXCEL .XLS SUPPORT FILES</P>
+</B></U></FONT></FONT><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-serif" SIZE=2><DL>
+<DT><BR>
+<B>doc/README-XLS.html</B><BR>
+This file.</FONT></FONT> </DT><BR>
+<B><FONT FACE="Arial, sans-serif" SIZE=2><FONT FACE="Arial, sans-s...
[truncated message content] |
