Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[6637] trunk/octave-forge/main/io
|
From: <prn...@us...> - 2009-12-11 22:05:59
|
Revision: 6637
http://octave.svn.sourceforge.net/octave/?rev=6637&view=rev
Author: prnienhuis
Date: 2009-12-11 22:05:43 +0000 (Fri, 11 Dec 2009)
Log Message:
-----------
Commit of the new Excel xls r/w m-files.
Added Paths:
-----------
trunk/octave-forge/main/io/doc/
trunk/octave-forge/main/io/doc/READ-XLS.html
trunk/octave-forge/main/io/inst/oct2com2xls.m
trunk/octave-forge/main/io/inst/oct2jpoi2xls.m
trunk/octave-forge/main/io/inst/oct2jxla2xls.m
trunk/octave-forge/main/io/inst/oct2xls.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/xls2com2oct.m
trunk/octave-forge/main/io/inst/xls2jpoi2oct.m
trunk/octave-forge/main/io/inst/xls2jxla2oct.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/xlsopen.m
trunk/octave-forge/main/io/inst/xlsread_old.m
trunk/octave-forge/main/io/inst/xlswrite.m
Added: trunk/octave-forge/main/io/doc/READ-XLS.html
===================================================================
--- trunk/octave-forge/main/io/doc/READ-XLS.html (rev 0)
+++ trunk/octave-forge/main/io/doc/READ-XLS.html 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,301 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
+<HTML>
+<HEAD>
+ <META HTTP-EQUIV="CONTENT-TYPE" CONTENT="text/html; charset=windows-1252">
+ <TITLE></TITLE>
+ <META NAME="GENERATOR" CONTENT="OpenOffice.org 3.0 (Win32)">
+ <META NAME="AUTHOR" CONTENT="Philip Nienhuis">
+ <META NAME="CREATED" CONTENT="20091211;17230700">
+ <META NAME="CHANGEDBY" CONTENT="Philip Nienhuis">
+ <META NAME="CHANGED" CONTENT="20091211;22461600">
+ <META NAME="Info 1" CONTENT="">
+ <META NAME="Info 2" CONTENT="">
+ <META NAME="Info 3" CONTENT="">
+ <META NAME="Info 4" CONTENT="">
+ <STYLE TYPE="text/css">
+ <!--
+ @page { margin: 0.79in }
+ P { margin-bottom: 0.08in }
+ A:link { so-language: zxx }
+ -->
+ </STYLE>
+</HEAD>
+<BODY LANG="en-US" DIR="LTR">
+<P ALIGN=CENTER STYLE="margin-bottom: 0in">README for Excel
+spreadsheet file r/w access scripts for octave (>
+3.2.0)<BR><BR>Copyright (C) 2009, Philip Nienhuis <prnienhuis at
+users.sf.net><BR><BR>This version 11 December 2009.</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=LEFT STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>EXCEL
+ .XLS SUPPORT FILES</B></U></FONT></P>
+<P ALIGN=LEFT STYLE="margin-bottom: 0in"><BR><B>doc/README-XLS.html</B></P>
+<P ALIGN=LEFT STYLE="margin-bottom: 0in">This file.</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><I>----- Functions
+intended for direct invocation by users ------</I></P>
+<P ALIGN=LEFT STYLE="margin-bottom: 0in"><BR><B>xlsread.m</B></P>
+<P STYLE="margin-bottom: 0in">All-in-one function for reading data
+from one specific worksheet in an Excel spreadsheet file. This script
+has Matlab-compatible functionality.<BR><BR><B>xlswrite.m</B></P>
+<P STYLE="margin-bottom: 0in">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></P>
+<P STYLE="margin-bottom: 0in">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></P>
+<P STYLE="margin-bottom: 0in">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; etc.), but it's choice
+can be overridden.<BR><BR><B>xls2oct.m</B></P>
+<P STYLE="margin-bottom: 0in">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></P>
+<P STYLE="margin-bottom: 0in">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></P>
+<P STYLE="margin-bottom: 0in">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></P>
+<P STYLE="margin-bottom: 0in">Function for separating the data in raw
+arrays returned by xls2oct, into numerical/logical and text (cell)
+arrays.</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR><I>------- Lower level
+function for indirect use --------<BR>(though there's no problem in
+calling them directly)</I></P>
+<P STYLE="margin-bottom: 0in"><BR><B>xls2com2oct.m, oct2com2xls.m</B></P>
+<P STYLE="margin-bottom: 0in">Functions for carrying out the actual
+reading from and writing to an Excel file using the COM interface on
+Windows systems where Excel has been installed. In fact, Excel.exe is
+invoked directly but invisibly. These low-level functions are not
+intended for direct use, they should rather be invoked through
+xls2oct() and oct2xls(). In principle all Excel file types can be
+processed providing the installed Excel version supports those
+formats. But for now only .xls is supported.<BR><BR><B>xls2jpoi2oct.m,
+oct2jpoi2xls.m</B></P>
+<P STYLE="margin-bottom: 0in">Functions for reading from and writing
+to an Excel file using the Java & Apache POI interface on any
+system where Java > 1.6.0 has been installed. These low-level
+functions are not meant to be invoked directly by users but rather
+through the xls2oct() and oct2xls() functions. Only Excel 97-2003
+files (BIFF8 format) and (to be implemented later:) OOXML spreadsheet
+files can be read from and written to.<BR><BR><B>xls2jxla2com.m,
+oct2jxla2com.m</B></P>
+<P STYLE="margin-bottom: 0in">Functions for reading from and writing
+to an Excel file using the Java & JExcelAPI interface on any
+system where Java > 1.6.0 is installed. These low-level functions
+aren't meant to be invoked directly by users but rather through the
+xls2oct() & oct2xls() functions. Excel 95 (BIFF 5; reading only)
+and Excel 97-2003 (BIFF8; reading & writing) formats are
+supported.</P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>REQUIRED
+SUPPORT SOFTWARE</B></U></FONT></P>
+<P STYLE="margin-bottom: 0in"><BR>For the Excel/COM interface:</P>
+<UL>
+ <LI><P STYLE="margin-bottom: 0in">A windows computer with Excel
+ installed</P>
+ <LI><P STYLE="margin-bottom: 0in">Octave-forge Windows-1.0.8 package
+ WITH LATEST SVN PATCHES APPLIED</P>
+</UL>
+<P STYLE="margin-left: 0.25in; text-indent: -0.26in; margin-bottom: 0in">
+<BR>
+</P>
+<P STYLE="margin-left: 0.25in; text-indent: -0.26in; margin-bottom: 0in">
+For the Java / Apache POI / JExcelAPI interfaces (general):</P>
+<UL>
+ <LI><P STYLE="margin-bottom: 0in">octave-forge java-1.2.5 package or
+ later version on Linux</P>
+ <LI><P STYLE="margin-bottom: 0in">octave-forge java-1.2.6 with
+ latest svn fixes on Windows/MingW</P>
+ <LI><P STYLE="margin-bottom: 0in">Java jre or jdk > 1.6.0 (hasn't
+ been tested with earlier versions)</P>
+ <LI><P STYLE="margin-bottom: 0in">./jre/lib/rt.jar in classpath</P>
+</UL>
+<P STYLE="margin-bottom: 0in">Apache POI specific:</P>
+<UL>
+ <LI><P STYLE="margin-bottom: 0in">class .jars:
+ <B>poi-3.5-FINAL-<date>.jar</B> &
+ <B>poi-ooxml-3.5-FINAL-<date>.jar</B> (or later versions) in
+ classpath</P>
+ <LI><P STYLE="margin-bottom: 0in">Get it here:
+ <A HREF="http://poi.apache.org/download.html">http://poi.apache.org/download.html</A></P>
+</UL>
+<P STYLE="margin-bottom: 0in">JExcelAPI specific:</P>
+<UL>
+ <LI><P STYLE="margin-bottom: 0in">class .jar: <B>jxl.jar</B> in
+ classpath</P>
+ <LI><P STYLE="margin-bottom: 0in">Get it here:
+ <A HREF="http://sourceforge.net/projects/jexcelapi/files/">http://sourceforge.net/projects/jexcelapi/files/</A></P>
+</UL>
+<P STYLE="margin-bottom: 0in">Hint: simply put the relevant
+javaaddpath statements in the .octaverc file.</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>USAGE</B></U></FONT></P>
+<P STYLE="margin-bottom: 0in"><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.<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.<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</B></P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>MATLAB
+COMPATIBILITY</B></U></FONT></P>
+<P STYLE="margin-bottom: 0in"><BR><B>xlsread</B>, <B>xlswrite</B> and
+<B>xlsfinfo</B> are for the most part matlab-compatible. Some small
+differences are mentioned below.<BR><BR><B>xlsread</B><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>When using a Java interface, octave-forge returns info
+about the actual (rather than the requested) cell range where the
+data came from; matlab can't. This is due to Excel automatically
+trimming returned arrays from empty outer columns and rows; when
+using COM, octave has the same limitation.<BR>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 :-)<BR><BR>When using the Java
+interface, reading and writing xls-files by octave-forge is
+platform-independent. On systems w/o Excel Matlab can only read Excel
+95 formatted .xls files.<BR><BR>Matlab's <B>xlsread</B> invokes
+<B>csvread</B> if no Excel interface is present. Octave-forge's
+<B>xlsread</B> doesn't.<BR><BR><B>xlswrite</B><BR>Octave-forge's
+<B>xlswrite</B> works on systems w/o Excel support, matlab's
+doesn't.<BR><BR><B>xlsfinfo</B><BR>When invoking Excel/COM interface,
+octave-forge'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).</P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>COMPARISON
+OF INTERFACES & USAGE</B></U></FONT></P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P STYLE="margin-bottom: 0in">Using Excel itself (through COM /
+ActiveX on Windows systems) is probably the most robust and versatile
+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>As Excel/COM trims returned
+arrays from empty outer row and columns it is not easy to find out
+from what cell range the returned data originate.<BR><BR>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
+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. 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 confidence is needed.</P>
+<P STYLE="margin-bottom: 0in">JExcelAPI supports BIFF5 and BIFF8
+(Excel 95 and Excel 97-2003, respectively).<BR><BR>Apache POI
+(Java-based and platform-independent too) is based on the
+OpenOffice.org I/O Excel r/w routines. It is a little more versatile
+than JExcelAPI, however it while it doesn't support BIFF5 it does
+support BIFF8 and OOXML (Excel 2007). It is almost as fast as native
+Excel & COM, but at the moment (3.5-FINAL) evaluation of complex
+Excel formulas while reading .xls files seems broken and OOXML
+support is not working yet through octave.<BR><BR>All in all, of the
+two Java options I'd lightly prefer JExcelAPI but I hope that Apache
+POI gets better soon.</P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P STYLE="margin-bottom: 0in"><BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><FONT SIZE=4 STYLE="font-size: 15pt"><U><B>DEVELOPMENT</B></U></FONT></P>
+<P ALIGN=CENTER STYLE="margin-bottom: 0in"><BR>
+</P>
+<P STYLE="margin-bottom: 0in"><B>xlsopen</B>/<B>xlsclos</B>e and
+friends have been written so that adding other interfaces (Perl?
+...?) should be very easily accomplished. <B>xlsopen</B> merely needs
+two stanzas, <B>xls2oct</B>, <B>oct2xls</B> and <B>xlsfinfo</B> each
+need an additional elseif clause, and <B>xlsclose</B> needs a small
+stanza for closing the pointer struct and writing to disk.
+</P>
+<P STYLE="margin-bottom: 0in">The real work lies in creating the
+relevant <B>xls2<...>2oct</B> & <B>oct2<...>2xls</B>
+scripts, but that shouldn't be really hard. 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?):</P>
+<UL>
+ <LI><P STYLE="margin-bottom: 0in">Speeding up, especially Java
+ worksheet/cell access. For cracks, not me.</P>
+ <LI><P STYLE="margin-bottom: 0in">Automatic conversion of Excel
+ date/time values into octave ones and vice versa. 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?</P>
+ <LI><P STYLE="margin-bottom: 0in">Creating Excel graphs (a
+ significant enterprise to write from scratch).</P>
+ <LI><P STYLE="margin-bottom: 0in">ODS (OpenOffice.org Open Document)
+ support. Probably to be called odsread.m / odswrite.m. This is on my
+ own wish list.</P>
+ <LI><P STYLE="margin-bottom: 0in">Support for "passing function
+ handle" in xlsread.</P>
+</UL>
+<P STYLE="margin-left: 0.26in; text-indent: -0.26in; margin-bottom: 0in">
+<BR>
+</P>
+<P STYLE="margin-left: 0.26in; text-indent: -0.26in; margin-bottom: 0in">
+Enjoy!</P>
+<P STYLE="margin-left: 0.26in; text-indent: -0.26in; margin-bottom: 0in">
+<BR>
+</P>
+<P ALIGN=CENTER STYLE="margin-left: 0.26in; text-indent: -0.26in; margin-bottom: 0in">
+Philip Nienhuis, Dec 10, 2009</P>
+</BODY>
+</HTML>
\ No newline at end of file
Added: trunk/octave-forge/main/io/inst/oct2com2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2com2xls.m (rev 0)
+++ trunk/octave-forge/main/io/inst/oct2com2xls.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,202 @@
+## Copyright (C) 2007 by Michael Goffioul <michael.goffioul at swing.be>
+##
+## Adapted by P.R. Nienhuis <prnienhuis at users.sf.net> (2009)
+## for more flexible writing into Excel worksheets through COM.
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; If not, see <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi})
+## @deftypefnx {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi}, @var{wsh})
+## @deftypefnx {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi}, @var{wsh}, @var{top_left_cell})
+## Save matrix @var{obj} into worksheet @var{wsh} in Excel file pointed
+## to in struct @var{xlsi}. All elements of @var{obj} are converted into
+## Excel cells, starting at cell @var{top_left_cell}. Return argument
+## @var{xlso} is @var{xlsi} with updated fields.
+##
+## oct2com2xls should not be invoked directly but rather through oct2xls.
+##
+## Excel invocations may be left running invisibly in case of COM errors.
+##
+## Example:
+##
+## @example
+## xls = oct2com2xls (rand (10, 15), xls, 'Third_sheet', 'BF24');
+## @end example
+##
+## @seealso oct2xls, xls2oct, xlsopen, xlsclose, xlswrite, xlsread, xls2com2oct
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Rewritten: 2009-09-26
+## Last updated 2009-12-11
+
+function [ xls, status ] = oct2com2xls (obj, xls, wsh, top_left_cell='A1')
+
+ # define some constants not yet in __COM__.cc
+ xlWorksheet = -4167; # xlChart= 4;
+
+ # scratch vars
+ status = 0;
+
+ # Preliminary sanity checks
+ if (nargin < 2) error ("oct2com2xls needs a minimum of 2 arguments."); endif
+ if (nargin == 2) wsh = 1; endif
+ if (~iscell (obj)) error ("Cell array expected as input argument"); endif
+ if (~strcmp (tolower (xls.filename(end-3:end)), '.xls'))
+ error ("oct2com2xls can only write to Excel .xls files")
+ endif
+ if (isnumeric (wsh))
+ if (wsh < 1) error ("Illegal worksheet number: %i\n", wsh); endif
+ elseif (size (wsh, 2) > 31)
+ error ("Illegal worksheet name - too long")
+ endif
+ if (isempty (obj))
+ warning ("Request to write empty matrix.");
+ rstatus = 1;
+ return;
+ endif
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || ~strcmp (char (xls.xtype), 'COM');
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ if test1
+ error ("Invalid file pointer struct");
+ endif
+
+ # Cleanup NaNs. Start with backing up strings, empty & boolean cells,
+ # then set text cells to 0
+ obj2 = cell (size (obj));
+ txtptr = cellfun ('isclass', obj, 'char');
+ if (any(txtptr)) obj2{txtptr} = obj{txtptr}; obj{txtptr} = 0; endif
+ eptr = cellfun ('isempty', obj);
+ if (any (eptr)) obj{eptr} = 0; endif
+ lptr = cellfun ("islogical" , obj);
+ if (any (lptr)) obj2{lptr} = obj{lptr}; obj{lptr} = 0; endif
+
+ ptr = cellfun ("isnan", obj);
+ if (any (ptr)) obj{ptr} = []; endif
+
+ # Restore text & booleans
+ if (any (txtptr)) obj{txtptr} = obj2{txtptr}; endif
+ if (any (lptr)) obj{lptr} = obj2{lptr}; endif
+ clear obj2 txtptr eptr lptr ptr;
+
+ if (xls.changed < 2)
+ # Existing file. Some involved investigation is needed to preserve
+ # existing data that shouldn't be touched.
+ #
+ # See if desired *sheet* name exists.
+ old_sh = 0;
+ ws_cnt = xls.workbook.Sheets.count;
+ if (isnumeric (wsh))
+ if (wsh <= ws_cnt)
+ # Here we check for sheet *position* in the sheet stack
+ # rather than a name like "Sheet<Number>"
+ old_sh = wsh;
+ else
+ # wsh > nr of sheets; proposed new sheet name.
+ # This sheet name can already exist to the left in the sheet stack!
+ shnm = sprintf ("Sheet%d", wsh); shnm1 = shnm;
+ endif
+ endif
+ if (~old_sh)
+ # Check if the requested (or proposed) sheet already exists
+ # COM objects are not OO (yet?), so we need a WHILE loop
+ ii = 1; jj = 1;
+ while ((ii <= ws_cnt) && ~old_sh)
+ # Get existing sheet names one by one
+ sh_name = xls.workbook.Sheets(ii).name;
+ if (~isnumeric (wsh) && strcmp (sh_name, wsh))
+ # ...and check with requested sheet *name*...
+ old_sh = ii;
+ elseif (isnumeric (wsh) && strcmp (sh_name, shnm))
+ # ... or proposed new sheet name (corresp. to requested sheet *number*)
+ shnm = [shnm "_"];
+ ii = 0; # Also check if this new augmented sheet name exists...
+ if (strmatch (shnm1, sh_name)), jj++; endif
+ if (jj > 5) # ... but not unlimited times...
+ error (sprintf (" > 5 sheets named [_]Sheet%d already present!", wsh));
+ endif
+ endif
+ ++ii;
+ endwhile
+ endif
+
+ if (old_sh)
+ # Requested sheet exists. Check if it is a *work*sheet
+ if ~(xls.workbook.Sheets(old_sh).Type == xlWorksheet)
+ # Error as you can't write data to this
+ error (sprintf ("Existing sheet '%s' is not type worksheet.", wsh));
+ else
+ # Simply point to the relevant sheet
+ sh = xls.workbook.Worksheets (old_sh);
+ endif
+ else
+ # Add a new worksheet. Earlier it was checked whether this is safe
+ sh = xls.workbook.Worksheets.Add ();
+ if (~isnumeric (wsh))
+ sh.Name = wsh;
+ else
+ sh.Name = shnm;
+ printf ("Writing to worksheet %s\n", shnm);
+ endif
+ # Prepare to move new sheet to right of the worksheet stack anyway
+ ws_cnt = xls.workbook.Worksheets.count; # New count needed
+ # Find where Excel has left it. We have to, depends on Excel version :-(
+ ii = 1;
+ while ((ii < ws_cnt+1) && ~strcmp (sh.Name, xls.workbook.Worksheets(ii).Name) == 1)
+ ++ii;
+ endwhile
+ # Excel can't move it beyond the current last one, so we need a trick.
+ # First move it to just before the last one....
+ xls.workbook.Worksheets(ii).Move (before = xls.workbook.Worksheets(ws_cnt));
+ # ....then move the last one before the new sheet.
+ xls.workbook.Worksheets (ws_cnt).Move (before = xls.workbook.Worksheets(ws_cnt - 1));
+ endif
+ xls.changed = 1;
+
+ else
+ # The easy case: a new Excel file.
+ # Workbook was created in xlsopen. Write to first worksheet:
+ sh = xls.workbook.Worksheets (1);
+ # Delete empty non-used sheets, last one first
+ xls.app.Application.DisplayAlerts = 0;
+ xls.workbook.Worksheets(3).Delete(); xls.workbook.Worksheets(2).Delete();
+ xls.app.Application.DisplayAlerts = 1;
+
+ # Rename the sheet
+ if (isnumeric(wsh))
+ sh.Name = sprintf("Sheet%i", wsh);
+ else
+ sh.Name = wsh;
+ endif
+ xls.changed = 2;
+ endif
+
+ # MG's original part.
+ # Save object in Excel sheet, starting at cell top_left_cell
+ if (~isempty(obj))
+ r = sh.Range (top_left_cell);
+ r = r.Resize (size (obj, 1), size (obj, 2));
+ r.Value = obj;
+ delete (r);
+ endif
+
+ # If we get here, all went OK
+ status = 1;
+
+endfunction
\ No newline at end of file
Added: trunk/octave-forge/main/io/inst/oct2jpoi2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2jpoi2xls.m (rev 0)
+++ trunk/octave-forge/main/io/inst/oct2jpoi2xls.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,159 @@
+## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls ( @var{arr}, @var{xlsi})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{topleft})
+##
+## Add data in 1D/2D CELL array @var{arr} into a range with upper left
+## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
+## spreadsheet file pointed to in structure @var{range}.
+## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
+## updated by oct2java2xls.
+##
+## oct2jpoi2xls should not be invoked directly but rather through oct2xls.
+##
+## Example:
+##
+## @example
+## [xlso, status] = xls2jpoi2oct ('arr', xlsi, 'Third_sheet', 'AA31');
+## @end example
+##
+## @seealso oct2xls, xls2oct, xlsopen, xlsclose, xlsread, xlswrite
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-11-26
+## Last updated 2009-12-11
+
+function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, topleftcell="A1")
+
+ persistent ctype;
+ if (isempty (ctype))
+ # Get cell types. Beware as they start at 0 not 1
+ ctype(1) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_NUMERIC');
+ ctype(2) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_STRING');
+ ctype(3) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_FORMULA');
+ ctype(4) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
+ ctype(5) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BOOLEAN');
+ ctype(6) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_ERROR');
+ endif
+
+ # scratch vars
+ rstatus = 0; changed = 1;
+
+ # Preliminary sanity checks
+ if (isempty (obj))
+ warning ("Request to write empty matrix.");
+ rstatus = 1;
+ return;
+ elseif (~iscell(obj))
+ error ("First argument is not a cell array");
+ endif
+ if (nargin < 2) error ("oct2jpoi2xls needs a minimum of 2 arguments."); endif
+ if (nargin == 2) wsh = 1; endif
+ if (~strmatch(tolower(xls.filename(end-4:end)), '.xls'))
+ error ("oct2jpoi2xls can only write to Excel .xls or .xlsx files")
+ endif
+ # Check if xls struct pointer seems valid
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || ~strcmp (char (xls.xtype), 'POI');
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ if test1 error ("Invalid xls file struct"); endif
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = xls.workbook.getNumberOfSheets();
+ if (isnumeric (wsh))
+ if (wsh > nr_of_sheets)
+ # Watch out as a sheet called Sheet%d can exist with a lower index...
+ strng = sprintf ("Sheet%d", wsh);
+ ii = 1;
+ while (~isempty (xls.workbook.getSheet (strng)) && (ii < 5))
+ strng = ['_' strng];
+ ++ii;
+ endwhile
+ if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
+ sh = xls.workbook.createSheet (strng);
+ else
+ sh = xls.workbook.getSheetAt (wsh - 1); # POI sheet count 0-based
+ endif
+ printf ("(Writing to worksheet %s)\n", sh.getSheetName());
+ else
+ sh = xls.workbook.getSheet (wsh);
+ if (isempty(sh))
+ # Sheet not found, just create it
+ sh = xls.workbook.createSheet (wsh);
+ xls.changed = 2;
+ endif
+ endif
+
+ # Beware of strings variables interpreted as char arrays; change them to cell.
+ if (ischar (obj)) obj = {obj}; endif
+
+ [topleft, nrows, ncols, trow, lcol] = parse_sp_range (topleftcell);
+ [nrows, ncols] = size (obj);
+
+ # Prepare type array
+ typearr = ctype(4) * ones (nrows, ncols); # type "BLANK", provisionally
+ obj2 = cell (size (obj)); # Temporary storage for strings
+
+ txtptr = cellfun ('isclass', obj, 'char'); # type "STRING" replaced by "NUMERIC"
+ obj2(txtptr) = obj(txtptr); obj(txtptr) = ctype(1); # Save strings in a safe place
+
+ emptr = cellfun ("isempty", obj);
+ obj(emptr) = ctype(1); # Set empty cells to NUMERIC
+
+ lptr = cellfun ("islogical" , obj); # Find logicals...
+ obj2(lptr) = obj(lptr); # .. and set them to BOOLEAN
+
+ ptr = cellfun ("isnan", obj); # Find NaNs & set to BLANK
+ typearr(ptr) = ctype(4); typearr(~ptr) = ctype(1); # All other cells are now numeric
+
+ obj(txtptr) = obj2(txtptr); # Copy strings back into place
+ obj(lptr) = obj2(lptr);
+ typearr(txtptr) = ctype(2); # ...and clean up
+ typearr(emptr) = ctype(4);
+ typearr(lptr) = ctype(5); # BOOLEAN
+
+ # Create formula evaluator (needed to be able to write boolean values!)
+ frm_eval = xls.workbook.getCreationHelper().createFormulaEvaluator();
+
+ for ii=1:nrows
+ ll = ii + trow - 2; # Java POI's row count = 0-based
+ row = sh.getRow (ll);
+ if (isempty (row)) row = sh.createRow (ll); endif
+ for jj=1:ncols
+ kk = jj + lcol - 2; # POI's column count is also 0-based
+ cell = row.createCell (kk, typearr(ii,jj));
+ if (typearr(ii, jj) == ctype(5))
+ cell = row.createCell (kk, ctype(3));
+ # Provisionally we make do with formulas evaluated immediately 8-Z
+ if obj{ii, jj} bool = '(1=1)'; else bool = '(1=0)'; endif
+ cell.setCellFormula (bool); frm_eval.evaluateInCell (cell);
+ elseif ~(typearr(ii, jj) == 3)
+ # Just put text or number in cell
+ cell.setCellValue (obj{ii, jj});
+ endif
+ endfor
+ endfor
+
+ rstatus = 1;
+
+endfunction
Added: trunk/octave-forge/main/io/inst/oct2jxla2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2jxla2xls.m (rev 0)
+++ trunk/octave-forge/main/io/inst/oct2jxla2xls.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,159 @@
+## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls ( @var{arr}, @var{xlsi})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{topleft})
+##
+## Add data in 1D/2D CELL array @var{arr} into a range with upper left
+## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
+## spreadsheet file pointed to in structure @var{range}.
+## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
+## updated by oct2jxla2xls.
+##
+## oct2jxla2xls should not be invoked directly but rather through oct2xls.
+##
+## Example:
+##
+## @example
+## [xlso, status] = oct2jxla2oct ('arr', xlsi, 'Third_sheet', 'AA31');
+## @end example
+##
+## @seealso oct2xls, xls2oct, xlsopen, xlsclose, xlsread, xlswrite, xls2jxla2oct
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-12-04
+## Last updated 2009-12-11
+
+function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, topleftcell="A1")
+
+ persistent ctype;
+ if (isempty (ctype))
+ ctype = [1, 2, 3, 4, 5];
+ # Boolean, Number, String, NaN, Empty
+ endif
+
+ # scratch vars
+ rstatus = 0; changed = 1;
+
+ # Preliminary sanity checks
+ if (isempty (obj))
+ warning ("Request to write empty matrix.");
+ rstatus = 1;
+ return;
+ elseif (~iscell(obj))
+ error ("First argument is not a cell array");
+ endif
+ if (nargin < 2) error ("oct2java2xls needs a minimum of 2 arguments."); endif
+ if (nargin == 2) wsh = 1; endif
+ if (~strmatch(tolower(xls.filename(end-4:end)), '.xls')) # FIXME for OOXML
+ error ("oct2java2xls can only write to Excel .xls files")
+ endif
+
+ # Prepare workbook pointer if needed
+ if (xls.changed < 2)
+ # Create writable copy of workbook. If 2 a writable wb was made in xlsopen
+ xlsout = java_new ('java.io.File', xls.filename);
+ wb = java_invoke ('jxl.Workbook', 'createWorkbook', xlsout, xls.workbook);
+ xls.changed = 1; # For in case we come from reading the file
+ xls.workbook = wb;
+ else
+ wb = xls.workbook;
+ endif
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = xls.workbook.getNumberOfSheets(); # 1 based !!
+ if (isnumeric (wsh))
+ if (wsh > nr_of_sheets)
+ # Watch out as a sheet called Sheet%d can exist with a lower index...
+ strng = sprintf ("Sheet%d", wsh);
+ ii = 1;
+ while (~isempty (wb.getSheet (strng)) && (ii < 5))
+ strng = ['_' strng];
+ ++ii;
+ endwhile
+ if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
+ sh = wb.createSheet (strng, nr_of_sheets); ++nr_of_sheets;
+ else
+ sh = wb.getSheet (wsh - 1); # POI sheet count 0-based
+ endif
+ shnames = char(wb.getSheetNames ());
+ printf ("(Writing to worksheet %s)\n", shnames {nr_of_sheets, 1});
+ else
+ sh = wb.getSheet (wsh);
+ if (isempty(sh))
+ # Sheet not found, just create it
+ sh = wb.createSheet (wsh, nr_of_sheets);
+ ++nr_of_sheets;
+ xls.changed = 2;
+ endif
+ endif
+
+ # Beware of strings variables interpreted as char arrays; change them to cell.
+ if (ischar (obj)) obj = {obj}; endif
+
+ [topleft, nrows, ncols, trow, lcol] = parse_sp_range (topleftcell);
+ [nrows, ncols] = size (obj);
+
+ # Prepare type array to speed up writing
+ typearr = 5 * ones (nrows, ncols); # type "EMPTY", provisionally
+ obj2 = cell (size (obj)); # Temporary storage for strings
+
+ txtptr = cellfun ('isclass', obj, 'char'); # type "STRING" replaced by "NUMERIC"
+ obj2(txtptr) = obj(txtptr); obj(txtptr) = 3; # Save strings in a safe place
+
+ emptr = cellfun ("isempty", obj);
+ obj(emptr) = 5; # Set empty cells to NUMERIC
+
+ lptr = cellfun ("islogical" , obj); # Find logicals...
+ obj2(lptr) = obj(lptr); # .. and set them to BOOLEAN
+
+ ptr = cellfun ("isnan", obj); # Find NaNs & set to BLANK
+ typearr(ptr) = 4; typearr(~ptr) = 2; # All other cells are now numeric
+
+ obj(txtptr) = obj2(txtptr); # Copy strings back into place
+ obj(lptr) = obj2(lptr);
+ typearr(txtptr) = 3; # ...and clean up
+ typearr(emptr) = 5;
+ typearr(lptr) = 1; # BOOLEAN
+
+ # Write date to worksheet
+ for ii=1:nrows
+ ll = ii + trow - 2; # Java JExcelAPI's row count = 0-based
+ for jj=1:ncols
+ kk = jj + lcol - 2; # JExcelAPI's column count is also 0-based
+ switch typearr(ii, jj)
+ case 1 # Boolean
+ tmp = java_new ('jxl.write.Boolean', kk, ll, obj{ii, jj});
+ sh.addCell (tmp);
+ case 2 # Numerical
+ tmp = java_new ('jxl.write.Number', kk, ll, obj{ii, jj});
+ sh.addCell (tmp);
+ case 3 # String
+ tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
+ sh.addCell (tmp);
+ otherwise
+ # Just skip
+ endswitch
+ endfor
+ endfor
+
+ rstatus = 1;
+
+endfunction
Added: trunk/octave-forge/main/io/inst/oct2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2xls.m (rev 0)
+++ trunk/octave-forge/main/io/inst/oct2xls.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,105 @@
+## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls})
+## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh})
+## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{topleft})
+##
+## Add data in 1D/2D CELL array @var{arr} into a range with upper left
+## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
+## spreadsheet file pointed to in structure @var{range}.
+## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
+## updated by oct2xls.
+##
+## oct2xls is a mere wrapper for interface-dependent scripts (e.g.,
+## oct2com2xls, oct2jpoi2xls, oct2jxla2xls) that do the actual writing to
+## spreadsheet files.
+##
+## A subsequent call to xlsclose is needed to write the updated spreadsheet
+## to disk (and -if needed- close the Excel or Java invocation).
+##
+## @var{arr} can be any array type save complex. Mixed numeric/text arrays
+## can only be cell arrays.
+##
+## @var{xls} must be a valid pointer struct created earlier by xlsopen.
+##
+## @var{wsh} can be a number or string (max. 31 chars).
+## In case of a yet non-existing Excel file, the first worksheet will be
+## used & named according to @var{wsh} - the extra worksheets that Excel
+## creates by default are deleted.
+## In case of existing files, some checks are made for existing worksheet
+## names or numbers, or whether @var{wsh} refers to an existing sheet with
+## a type other than worksheet (e.g., chart).
+## When new worksheets are to be added to the Excel file, they are
+## inserted to the right of all existing worksheets. The pointer to the
+## "active" sheet (shown when Excel opens the file) remains untouched.
+##
+## If omitted, @var{topleft} is supposed to be 'A1'. The actual range to
+## be used is determined by the size of @var{arr}.
+##
+## Data are added to the worksheet, ignoring other data already present;
+## existing data in the range to be used will be overwritten.
+##
+## If @var{range} contains merged cells, only the elements of @var{arr}
+## corresponding to the top or left Excel cells of those merged cells
+## will be written, other array cells corresponding to that cell will be
+## ignored.
+##
+## Beware that -if invoked- Excel invocations may be left running silently
+## in case of COM errors. Invoke xlsclose with proper pointer struct to
+## close them.
+##
+## Examples:
+##
+## @example
+## [xlso, status] = xls2oct ('arr', xlsi, 'Third_sheet', 'AA31');
+## @end example
+##
+## @seealso xls2oct, xlsopen, xlsclose, xlsread, xlswrite, oct2com2xls, oct2jpoi2xls, oct2jxla2xls
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-12-01
+## Latest update: 2009-12-11
+
+function [ xls, rstatus ] = oct2xls (obj, xls, wsh, topleft='A1')
+
+ if (isnumeric (obj))
+ obj = num2cell (obj);
+ elseif (ischar (obj))
+ obj = {obj};
+ endif
+
+ if (strcmp (xls.xtype, 'COM'))
+ # Call oct2com2xls to do the work
+ [xls, rstatus] = oct2com2xls (obj, xls, wsh, topleft);
+
+ elseif (strcmp (xls.xtype, 'POI'))
+ # Invoke Java and Apache POI
+ [xls, rstatus] = oct2jpoi2xls (obj, xls, wsh, topleft);
+
+ elseif (strcmp (xls.xtype, 'JXL'))
+ # Invoke Java and JExcelAPI
+ [xls, rstatus] = oct2jxla2xls (obj, xls, wsh, topleft);
+
+# elseif (strcmp'xls.xtype, '<whatever>'))
+# <Other Excel interfaces>
+
+ endif
+
+endfunction
Added: trunk/octave-forge/main/io/inst/parse_sp_range.m
===================================================================
--- trunk/octave-forge/main/io/inst/parse_sp_range.m (rev 0)
+++ trunk/octave-forge/main/io/inst/parse_sp_range.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,100 @@
+## Copyright (C) 2009 Philip Nienhuis <pr.nienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## parse_sp_range
+## Parse a string representing a range of cells for a spreadsheet
+## into nr of rows and nr of columns and also extract top left
+## cell address + top row+ left column. Some error checks are implemented.
+
+## Author: Philip Nienhuis
+## Created: 2009-06-20
+## Latest update 2009-12-11
+
+function [topleft, nrows, ncols, toprow, lcol] = parse_sp_range (range_org)
+
+ # This entire function needs fixing or replacement for OOXML (larger ranges)
+
+ range = deblank (toupper (range_org));
+ range_error = 0; nrows = 0; ncols = 0;
+
+ if (index (range, ':') == 0)
+ if (isempty (range))
+ range_error = 0;
+ leftcol = 'A';
+ rightcol = 'A';
+ else
+ # Only upperleft cell given, just complete range to 1x1
+ range = [range ":" range];
+ endif
+ endif
+ [topleft, lowerright] = strtok (range, ':');
+ [st, en] = regexp (topleft, '\d+');
+ toprow = str2num (topleft(st:en));
+ leftcol = deblank (topleft(1:st-1));
+ [st, en1] = regexp( leftcol,'\s+');
+ if (isempty (en1))
+ en1=0;
+ endif
+ [st, en2] = regexp (leftcol,'\D+');
+ leftcol = leftcol(en1+1:en2);
+
+ [st, en] = regexp (lowerright,'\d+');
+ bottomrow = str2num (lowerright(st:en));
+ rightcol = deblank (lowerright(2:st-1));
+ [st, en1] = regexp (rightcol,'\s+');
+ if (isempty (en1))
+ en1=0;
+ endif
+ [st, en2] = regexp (rightcol,'\D+');
+ rightcol = rightcol(en1+1:en2);
+ nrows = bottomrow - toprow + 1;
+ if (nrows < 1)
+ range_error = 1;
+ endif
+
+ if (range_error == 0)
+ # Get columns.
+ # FIXME for OOXML! We provisonally assume Excel 97 format, max 256 columns
+ ncols = 0;
+ [st, en1] = regexp (leftcol, '\D+');
+ [st, en2] = regexp (rightcol, '\D+');
+ i1= 0; i2 = 0;
+ if (en2 > en1)
+ i1 = rightcol(2:2) - leftcol(1:1) + 1;
+ i2 = rightcol(1:1) - 'A'+ 1;
+ lcol = leftcol(1:1) - 'A' + 1;
+ elseif (en2 == en1)
+ i1 = rightcol(en2) - leftcol(en2) + 1;
+ lcol = leftcol(1:1) - 'A' + 1;
+ if (en2 == 2)
+ i2 = rightcol(1) - leftcol(1);
+ lcol = lcol * 26 + (leftcol(2:2) - 'A' + 1);
+ endif
+ else
+ range_error = 1;
+ endif
+ ncols = i1 + 26 * i2;
+ if (ncols < 1)
+ range_error = 1;
+ endif
+ endif
+
+ if (range_error > 0)
+ ncols = 0; nrows = 0;
+ error ("Spreadsheet range error! ");
+ endif
+
+endfunction
Added: trunk/octave-forge/main/io/inst/parsecell.m
===================================================================
--- trunk/octave-forge/main/io/inst/parsecell.m (rev 0)
+++ trunk/octave-forge/main/io/inst/parsecell.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,125 @@
+## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [ @var{numarr}, @var{txtarr}, @var{lim} ] = parsecell (@var{rawarr})
+##
+## Divide a heterogeneous 2D cell array into a 2D numeric array and a
+## 2D cell array containing only strings. Both returned arrays are
+## trimmed from empty outer rows and columns.
+##
+## Optional return argument @var{lim} contains two field with the outer
+## column and row numbers of @var{numarr} and @var{txtarr} in the
+## original @var{rawarr}.
+##
+## Example:
+##
+## @example
+## [An, Tn] = parsecell (Rn);
+## (which returns the numeric contents of Rn into array An and the
+## text data into array Tn)
+## @end example
+##
+## @seealso xlsread, xls2oct
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-12-11
+
+function [ numarr, txtarr, lim ] = parsecell (rawarr)
+
+ lim = struct ( "numlimits", [], "txtlimits", [] );
+
+ numarr = [];
+ txtarr = {};
+
+ if (~isempty (rawarr))
+ # Valid data returned. Divide into numeric & text arrays
+ no_txt = 0; no_num = 0;
+ if (isnumeric ([rawarr{:}]))
+ numarr = num2cell (rawarr);
+ no_txt = 1;
+ elseif (iscellstr (rawarr))
+ txtarr = cellstr (rawarr);
+ no_num = 1;
+ endif
+ # Prepare parsing
+ [nrows, ncols] = size (rawarr);
+
+ # Find text entries in raw data cell array
+ txtptr = cellfun ('isclass', rawarr, 'char');
+ if (~no_txt)
+ # Prepare text array. Create placeholder for text cells
+ txtarr = cell (size (rawarr));
+ txtarr(:) = {''};
+ if (any (any (txtptr)))
+ # Copy any text cells found into place holder
+ txtarr(txtptr) = rawarr(txtptr);
+ # Clean up text array (find leading / trailing empty
+ # rows & columns)
+ irowt = 1;
+ while (~any (txtptr(irowt, :))) irowt++; endwhile
+ irowb = nrows;
+ while (~any (txtptr(irowb, :))) irowb--; endwhile
+ icoll = 1;
+ while (~any (txtptr(:, icoll))) icoll++; endwhile
+ icolr = ncols;
+ while (~any (txtptr(:, icolr))) icolr--; endwhile
+ # Crop textarray
+ txtarr = txtarr(irowt:irowb, icoll:icolr);
+ lim.txtlimits = [icoll, icolr; irowt, irowb];
+ else
+ # If no text cells found return empty text array
+ txtarr = {};
+ endif
+ endif
+
+ if (~no_num)
+ # Prepare numeric array. Set all text & empty cells to
+ # NaN. First get their locations
+ emptr = cellfun ('isempty', rawarr);
+ emptr(find (txtptr)) = 1;
+ if (all (all (emptr)))
+ numarr= [];
+ else
+ # Find leading & trailing empty rows
+ irowt = 1;
+ while (all(emptr(irowt, :))) irowt++; endwhile
+ irowb = nrows;
+ while (all(emptr(irowb, :))) irowb--; endwhile
+ icoll = 1;
+ while (all(emptr(:, icoll))) icoll++; endwhile
+ icolr = ncols;
+ while (all(emptr(:, icolr))) icolr--; endwhile
+ # Put numvalues column by column into temporary storage while
+ # skipping leading & trailing NaN columns & -rows.
+ # As columns are usually bigger chunks than rows & octave
+ # stores arrays by column, building by columns is supposedly
+ # faster than bulding by rows
+ tmparr = cell (1, icolr-icoll+1);
+ for ii = icoll:icolr
+ tmpcol = rawarr(irowt:irowb, ii);
+ tmpcol(find (emptr(irowt:irowb, ii))) = NaN;
+ tmparr(ii) = cat (1, tmpcol{:});
+ endfor
+ numarr = cat (2, tmparr{:});
+ lim.numlimits = [icoll, icolr; irowt, irowb];
+ endif
+ endif
+ endif
+
+endfunction
Added: trunk/octave-forge/main/io/inst/xls2com2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2com2oct.m (rev 0)
+++ trunk/octave-forge/main/io/inst/xls2com2oct.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,124 @@
+## Copyright (C)2009 P.R. Nienhuis, <pr.nienhuis at hccnet.nl>
+##
+## based on mat2xls by Michael Goffioul (2007) <mic...@sw...>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; If not, see <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2com2oct (@var{xls})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2com2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2com2oct (@var{xls}, @var{wsh}, @var{range})
+## Get cell contents in @var{range} in worksheet @var{wsh} in an Excel
+## file pointed to in struct @var{xls} into the cell array @var{obj}.
+##
+## xls2com2oct should not be invoked directly but rather through xls2oct.
+##
+## Examples:
+##
+## @example
+## [Arr, status, xls] = xls2com2oct (xls, 'Second_sheet', 'B3:AY41');
+## Arr = xls2com2oct (xls, 'Second_sheet');
+## @end example
+##
+## @seealso xls2oct, oct2xls, xlsopen, xlsclose, xlsread, xlswrite
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-09-23
+## Last updated 2009-12-11
+
+function [obj, xls, rstatus ] = xls2com2oct (xls, wsh, range)
+
+ rstatus = 0; obj = {};
+
+ # Basic checks
+ if (nargin < 2) error ("xls2com2oct needs a minimum of 2 arguments."); endif
+ if (size (wsh, 2) > 31)
+ warning ("Worksheet name too long - truncated")
+ wsh = wsh(1:31);
+ endif
+
+ nrows = 0;
+ emptyrange = 0;
+ if ((nargin == 2) || (isempty (range)))
+ emptyrange = 1;
+ else
+ # Extract top_left_cell from range
+ [topleft, nrows, ncols] = parse_sp_range (range);
+ endif;
+
+ if (nrows >= 1 || emptyrange)
+ # Check the file handle struct
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || ~strcmp (char (xls.xtype), 'COM');
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ if test1
+ error ("Invalid file pointer struct");
+ endif
+ app = xls.app;
+ wb = xls.workbook;
+ wb_cnt = wb.Worksheets.count;
+ old_sh = 0;
+ if (isnumeric (wsh))
+ if (wsh < 1 || wsh > wb_cnt)
+ errstr = sprintf ("Worksheet number: %d out of range 1-%d", wsh, wb_cnt);
+ error (errstr)
+ return
+ else
+ old_sh = wsh;
+ endif
+ else
+ # Find worksheet number corresponding to name in wsh
+ wb_cnt = wb.Worksheets.count;
+ for ii =1:wb_cnt
+ sh_name = wb.Worksheets(ii).name;
+ if (strcmp (sh_name, wsh)) old_sh = ii; endif
+ endfor
+ if (~old_sh)
+ errstr = sprintf ("Worksheet name \"%s\" not present", wsh);
+ error (errstr)
+ else
+ wsh = old_sh;
+ endif
+ endif
+
+ sh = wb.Worksheets (wsh);
+
+ if (emptyrange)
+ allcells = sh.UsedRange;
+ obj = allcells.Value;
+ else
+ # Get object from Excel sheet, starting at cell top_left_cell
+ r = sh.Range (topleft);
+ r = r.Resize (nrows, ncols);
+ obj = r.Value;
+ delete (r);
+ endif;
+ # Take care of actual singe cell range
+ if (isnumeric (obj) || ischar (obj))
+ obj = {obj};
+ endif
+ # If we get here, all seems to have gone OK
+ rstatus = 1;
+
+ else
+ error ("No data read from Excel file");
+ rstatus = 0;
+
+ endif
+
+endfunction
Added: trunk/octave-forge/main/io/inst/xls2jpoi2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2jpoi2oct.m (rev 0)
+++ trunk/octave-forge/main/io/inst/xls2jpoi2oct.m 2009-12-11 22:05:43 UTC (rev 6637)
@@ -0,0 +1,202 @@
+## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+##
+## This program is free software; you can redistribute it and/or modify
+## it under the terms of the GNU General Public License as published by
+## the Free Software Foundation; either version 2 of the License, or
+## (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with Octave; see the file COPYING. If not, see
+## <http://www.gnu.org/licenses/>.
+
+## -*- texinfo -*-
+## @deftypefn {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2jpoi2oct (@var{xls})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2jpoi2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2jpoi2oct (@var{xls}, @var{wsh}, @var{range})
+## Get cell contents in @var{range} in worksheet @var{wsh} in an Excel
+## file pointed to in struct @var{xls} into the cell array @var{obj}.
+## @var{range} can be a range or just the top left cell of the range.
+##
+## xls2jpoi2oct should not be invoked directly but rather through xls2oct.
+##
+## Examples:
+##
+## @example
+## [Arr, status, xls] = xls2jpoi2oct (xls, 'Second_sheet', 'B3:AY41');
+## B = xls2jpoi2oct (xls, 'Second_sheet', 'B3');
+## @end example
+##
+## @seealso xls2oct, oct2xls, xlsopen, xlsclose, xlsread, xlswrite, oct2jpoi2xls
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-11-23
+## Last updated 2009-12-11
+
+function [ rawarr, xls, status ] = xls2jpoi2oct (xls, wsh, cellrange=[])
+
+ persistent ctype;
+ if (isempty (ctype))
+ # Get enumrated cell types. Beware as they start at 0 not 1
+ ctype(1) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_NUMERIC');
+ ctype(2) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_STRING');
+ ctype(3) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_FORMULA');
+ ctype(4) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
+ ctype(5) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BOOLEAN');
+ ctype(6) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_ERROR');
+ endif
+
+ status = 0; jerror = 0;
+
+ # Check if xls struct pointer seems valid
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || ~strcmp (char (xls.xtype), 'POI');
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ if test1
+ error ("Invalid xls file struct");
+ else
+ wb = xls.workbook;
+ endif
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = wb.getNumberOfSheets ();
+ if (isnumeric (wsh))
+ if (wsh > nr_of_sheets), error (sprintf ("Worksheet # %d bigger than nr. of sheets (%d) in file %s", wsh, nr_of_sheets, xls.filename)); endif
+ sh = wb.getSheetAt (wsh - 1); # POI sheet count 0-based
+ printf ("(Reading from worksheet %s)\n", sh.getSheetName ());
+ else
+ sh = wb.getSheet (wsh);
+ if (isempty (sh)), error (sprintf("Worksheet %s not found in file %s", wsh, xls.filename)); endif
+ end
+
+ # Check ranges
+ firstrow = sh.getFirstRowNum ();
+ lastrow = sh.getLastRowNum ();
+ if (isempty (cellrange))
+ # Get used range by searching (slow...). Beware, it can be bit unreliable
+ lcol = 65535; # FIXME for OOXML
+ rcol = 0;
+ for ii=firstrow:lastrow
+ irow = sh.getRow (ii);
+ if (~isempty (irow))
+ scol = (irow.getFirstCellNum).intValue ();
+ lcol = min (lcol, scol);
+ ecol = (irow.getLastCellNum).intValue () - 1;
+ rcol = max (rcol, ecol);
+ # Keep track of lowermost non-empty row as getLastRowNum() is unreliable
+ if ~(irow.getCell(scol).getCellType () == ctype(4) && irow.getCell(ecol).getCellType () == ctype(4))
+ botrow = ii;
+ endif
+ endif
+ endfor
+ lastrow = min (lastrow, botrow);
+ nrows = lastrow - firstrow + 1;
+ ncols = rcol - lcol + 1;
+ else
+ # Translate range to HSSF POI row & column numbers
+ [topleft, nrows, ncols, trow, lcol] = parse_sp_range (cellrange);
+ firstrow = max (trow-1, firstrow);
+ lastrow = firstrow + nrows - 1;
+ lcol = lcol -1; # POI rows & column # 0-based
+ endif
+
+ # Create formula evaluator (needed to infer proper cell type into rawarr)
+ # NB formula evaluation is not very reliable in POI
+ frm_eval = wb.getCreationHelper().createFormulaEvaluator ();
+
+ #wb.clearAllCachedResultsValues(); # does not work
+
+ # Read contents into rawarr
+ rawarr = cell (nrows, ncols); # create placeholder
+ for ii = firstrow:lastrow
+ irow = sh.getRow (ii);
+ if ~isempty (irow)
+ scol = (irow.getFirstCellNum).intValue ();
+ ecol = (irow.getLastCellNum).intValue () - 1;
+ for jj = max (scol, lcol) : min (lcol+ncols-1, ecol)
+ cell = irow.getCell (jj);
+ if ~isempty (cell)
+ # Process cell contents
+ type_of_cell = cell.getCellType ();
+ if (type_of_cell == ctype(3)) # Formula
+ try
+ cell = frm_eval.evaluate (cell);
+ type_of_cell = cell.getCellType();
+ switch type_of_cell
+ case ctype (1) # Numeric
+ rawarr (ii+1-firstrow, jj+1-lcol) = cell.getNumberValue ();
+ case ctype(2) # String
+ rawarr (ii+1-firstrow, jj+1-lcol) = char (cell.getStringValue ());
+ case ctype (5) # Boolean
+ rawarr (ii+1-firstrow, jj+1-lcol) = cell.BooleanValue ();
+...
[truncated message content] |