--- /dev/null
+## Copyright (C) 2009,2010,2011,12 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 3 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{rawarr}, @var{xls}, @var{rstatus} ] = xls2oct (@var{xls})
+## @deftypefnx {Function File} [ @var{rawarr}, @var{xls}, @var{rstatus} ] = xls2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [ @var{rawarr}, @var{xls}, @var{rstatus} ] = xls2oct (@var{xls}, @var{wsh}, @var{range})
+## @deftypefnx {Function File} [ @var{rawarr}, @var{xls}, @var{rstatus} ] = xls2oct (@var{xls}, @var{wsh}, @var{range}, @var{options})
+##
+## Read data contained within cell range @var{range} from worksheet @var{wsh}
+## in an Excel spreadsheet file pointed to in struct @var{xls}.
+##
+## @var{xls} is supposed to have been created earlier by xlsopen in the
+## same octave session.
+##
+## @var{wsh} is either numerical or text, in the latter case it is
+## case-sensitive and it may be max. 31 characters long.
+## Note that in case of a numerical @var{wsh} this number refers to the
+## position in the worksheet stack, counted from the left in an Excel
+## window. The default is numerical 1, i.e. the leftmost worksheet
+## in the Excel file.
+##
+## @var{range} is expected to be a regular spreadsheet range format,
+## or "" (empty string, indicating all data in a worksheet).
+## If no range is specified the occupied cell range will have to be
+## determined behind the scenes first; this can take some time for the
+## Java-based interfaces. Be aware that in COM/ActiveX interface the
+## used range can be outdated. The Java-based interfaces are more
+## reliable in this respect albeit much slower.
+##
+## Optional argument @var{options}, a structure, can be used to
+## specify various read modes by setting option fields in the struct
+## to true (1) or false (0). Currently recognized option fields are:
+##
+## @table @asis
+## @item "formulas_as_text"
+## If set to TRUE or 1, spreadsheet formulas (if at all present)
+## are read as formula strings rather than the evaluated formula
+## result values. The default value is 0 (FALSE).
+##
+## @item 'strip_array'
+## Set the value of this field set to TRUE or 1 to strip the returned
+## output array @var{rawarr} from empty outer columns and rows. The
+## spreadsheet cell rectangle limits from where the data actually
+## came will be updated. The default value is FALSE or 0 (no cropping).
+## When using the COM interface, the output array is always cropped.
+## @end table
+##
+## If only the first argument @var{xls} is specified, xls2oct will try
+## to read all contents from the first = leftmost (or the only)
+## worksheet (as if a range of @'' (empty string) was specified).
+##
+## If only two arguments are specified, xls2oct assumes the second
+## argument to be @var{wsh}. In that case xls2oct will try to read
+## all data contained in that worksheet.
+##
+## Return argument @var{rawarr} contains the raw spreadsheet cell data.
+## Use parsecell() to separate numeric and text values from @var{rawarr}.
+##
+## Optional return argument @var{xls} contains the pointer struct,
+## If any data have been read, field @var{xls}.limits contains the
+## outermost column and row numbers of the actually returned cell range.
+##
+## Optional return argument @var{rstatus} will be set to 1 if the
+## requested data have been read successfully, 0 otherwise.
+##
+## Erroneous data and empty cells turn up empty in @var{rawarr}.
+## Date/time values in Excel are returned as numerical values.
+## Note that Excel and Octave have different date base values (1/1/1900 &
+## 1/1/0000, resp.)
+## Be aware that Excel trims @var{rawarr} from empty outer rows & columns,
+## so any returned cell array may turn out to be smaller than requested
+## in @var{range}, independent of field 'formulas_as_text' in @var{options}.
+## When using COM, POI, or UNO interface, formulas in cells are evaluated; if
+## that fails cached values are retrieved. These may be outdated depending
+## on Excel's "Automatic calculation" settings when the spreadsheet was saved.
+##
+## When reading from merged cells, all array elements NOT corresponding
+## to the leftmost or upper Excel cell will be treated as if the
+## "corresponding" Excel cells are empty.
+##
+## Beware: when the COM interface is used, hidden Excel invocations may be
+## kept running silently in case of COM errors.
+##
+## Examples:
+##
+## @example
+## A = xls2oct (xls1, '2nd_sheet', 'C3:AB40');
+## (which returns the numeric contents in range C3:AB40 in worksheet
+## '2nd_sheet' from a spreadsheet file pointed to in pointer struct xls1,
+## into numeric array A)
+## @end example
+##
+## @example
+## [An, xls2, status] = xls2oct (xls2, 'Third_sheet');
+## @end example
+##
+## @seealso {oct2xls, xlsopen, xlsclose, parsecell, xlsread, xlsfinfo, xlswrite }
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2010-10-16
+## Updates:
+## 2009-01-03 (added OOXML support & cleaned up code. Excel
+## ADDRESS function still not implemented in Apache POI)
+## 2010-03-14 Updated help text
+## 2010-05-31 Updated help text (delay i.c.o. empty range due to getusedrange call)
+## 2010-07-28 Added option to read formulas as text strings rather than evaluated value
+## 2010-08-25 Small typo in help text
+## 2010-10-20 Added option fornot stripping output arrays
+## 2010-11-07 More rigorous input checks.
+## 2010-11-12 Moved pointer check into main func
+## 2010-11-13 Catch empty sheets when no range was specified
+## 2011-03-26 OpenXLS support added
+## 2011-03-29 Test for proper input xls struct extended
+## 2011-05-18 Experimental UNO support added
+## 2011-09-08 Minor code layout
+## 2012-01-26 Fixed "seealso" help string
+## 2012-02-25 Fixed missing quotes in struct check L.149-153
+## 2012-02-26 Updated texinfo header help text
+## 2012-06-06 Implemented "formulas_as_text" option for COM
+## 2012-06-07 Replaced all tabs by double space
+##
+## Latest subfunc update: 2012-06-06
+
+function [ rawarr, xls, rstatus ] = xls2oct (xls, wsh=1, datrange='', spsh_opts=[])
+
+ # Check if xls struct pointer seems valid
+ if (~isstruct (xls)), error ("File ptr struct expected for arg @ 1"); endif
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ test1 = test1 || ~ischar (xls.xtype);
+ if test1
+ error ("Invalid xls file pointer struct");
+ endif
+
+ # Check worksheet ptr
+ if (~(ischar (wsh) || isnumeric (wsh))), error ("Integer (index) or text (wsh name) expected for arg # 2"); endif
+ # Check range
+ if (~(isempty (datrange) || ischar (datrange))), error ("Character string (range) expected for arg # 3"); endif
+
+ # Check & setup options struct
+ if (nargin < 4 || isempty (spsh_opts))
+ spsh_opts.formulas_as_text = 0;
+ spsh_opts.strip_array = 1;
+ # Future options:
+ elseif (isstruct (spsh_opts))
+ if (~isfield (spsh_opts', 'formulas_as_text')), spsh_opts.formulas_as_text = 0; endif
+ if (~isfield (spsh_opts', 'strip_array')), spsh_opts.strip_array = 1; endif
+ % Future options:
+ else
+ error ("Structure expected for arg # 4");
+ endif
+
+ # Select the proper interfaces
+ if (strcmp (xls.xtype, 'COM'))
+ # Call Excel tru COM / ActiveX server
+ [rawarr, xls, rstatus] = xls2com2oct (xls, wsh, datrange, spsh_opts);
+ elseif (strcmp (xls.xtype, 'POI'))
+ # Read xls file tru Java POI
+ [rawarr, xls, rstatus] = xls2jpoi2oct (xls, wsh, datrange, spsh_opts);
+ elseif (strcmp (xls.xtype, 'JXL'))
+ # Read xls file tru JExcelAPI
+ [rawarr, xls, rstatus] = xls2jxla2oct (xls, wsh, datrange, spsh_opts);
+ elseif (strcmp (xls.xtype, 'OXS'))
+ # Read xls file tru OpenXLS
+ [rawarr, xls, rstatus] = xls2oxs2oct (xls, wsh, datrange, spsh_opts);
+ elseif (strcmp (xls.xtype, 'UNO'))
+ # Read xls file tru OpenOffice.org UNO (Java) bridge
+ [rawarr, xls, rstatus] = xls2uno2oct (xls, wsh, datrange, spsh_opts);
+# elseif ---- <Other interfaces here>
+ # Call to next interface
+ else
+ error (sprintf ("xls2oct: unknown Excel .xls interface - %s.", xls.xtype));
+ endif
+
+ # Optionally strip empty outer rows and columns & keep track of original data location
+ if (spsh_opts.strip_array)
+ emptr = cellfun ('isempty', rawarr);
+ if (all (all (emptr)))
+ rawarr = {};
+ xls.limits = [];
+ else
+ nrows = size (rawarr, 1); ncols = size (rawarr, 2);
+ 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
+
+ # Crop output cell array and update limits
+ rawarr = rawarr(irowt:irowb, icoll:icolr);
+ xls.limits = xls.limits + [icoll-1, icolr-ncols; irowt-1, irowb-nrows];
+ endif
+ endif
+
+endfunction
+
+
+#====================================================================================
+## Copyright (C) 2009,2010,2011,2012 P.R. Nienhuis, <pr.nienhuis at hccnet.nl>
+##
+## based on mat2xls by Michael Goffioul (2007) <michael.goffioul@swing.be>
+##
+## 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 3 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})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2com2oct (@var{xls}, @var{wsh}, @var{range}, @var{spsh_opts})
+## 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 updates:
+## 2009-12-11 <forgot what it was>
+## 2010-10-07 Implemented limits (only reliable for empty input ranges)
+## 2010-10-08 Resulting data array now cropped (also in case of specified range)
+## 2010-10-10 More code cleanup (shuffled xls tests & wsh ptr code before range checks)
+## 2010-10-20 Slight change to Excel range setup
+## 2010-10-24 Added check for "live" ActiveX server
+## 2010-11-12 Moved ptr struct check into main func
+## 2010-11-13 Catch empty sheets when no range was specified
+## 2012-01-26 Fixed "seealso" help string
+## 2012-06-06 Implemented "formulas_as_text option"
+
+function [rawarr, xls, rstatus ] = xls2com2oct (xls, wsh, crange, spsh_opts)
+
+ rstatus = 0; rawarr = {};
+
+ # 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
+ app = xls.app;
+ wb = xls.workbook;
+ # Check to see if ActiveX is still alive
+ try
+ wb_cnt = wb.Worksheets.count;
+ catch
+ error ("ActiveX invocation in file ptr struct seems non-functional");
+ end_try_catch
+
+ # Check & get handle to requested worksheet
+ 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)
+ rstatus = 1;
+ 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);
+
+ nrows = 0;
+ if ((nargin == 2) || (isempty (crange)))
+ allcells = sh.UsedRange;
+ # Get actually used range indices
+ [trow, brow, lcol, rcol] = getusedrange (xls, old_sh);
+ if (trow == 0 && brow == 0)
+ # Empty sheet
+ rawarr = {};
+ printf ("Worksheet '%s' contains no data\n", sh.Name);
+ return;
+ else
+ nrows = brow - trow + 1; ncols = rcol - lcol + 1;
+ topleft = calccelladdress (trow, lcol);
+ lowerright = calccelladdress (brow, rcol);
+ crange = [topleft ':' lowerright];
+ endif
+ else
+ # Extract top_left_cell from range
+ [topleft, nrows, ncols, trow, lcol] = parse_sp_range (crange);
+ brow = trow + nrows - 1;
+ rcol = lcol + ncols - 1;
+ endif;
+
+ if (nrows >= 1)
+ # Get object from Excel sheet, starting at cell top_left_cell
+ rr = sh.Range (crange);
+ if (spsh_opts.formulas_as_text)
+ rawarr = rr.Formula;
+ else
+ rawarr = rr.Value;
+ endif
+ delete (rr);
+
+ # Take care of actual singe cell range
+ if (isnumeric (rawarr) || ischar (rawarr))
+ rawarr = {rawarr};
+ endif
+
+ # If we get here, all seems to have gone OK
+ rstatus = 1;
+ # Keep track of data rectangle limits
+ xls.limits = [lcol, rcol; trow, brow];
+ else
+ error ("No data read from Excel file");
+ rstatus = 0;
+ endif
+
+endfunction
+
+
+#==================================================================================
+
+## Copyright (C) 2009,2010,2011,2012 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 3 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} ] = 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
+## Updates:
+## 2010-01-11 Fall back to cached values when formula evaluator fails
+## 2010-03-14 Fixed max column nr for OOXML for empty given range
+## 2010-07-28 Added option to read formulas as text strings rather than evaluated value
+## 2010-08-01 Some bug fixes for formula reading (cvalue rather than scell)
+## 2010-10-10 Code cleanup: -getusedrange called; - fixed typo in formula evaluation msg;
+## '' moved cropping output array to calling function.
+## 2010-11-12 Moved ptr struct check into main func
+## 2010-11-13 Catch empty sheets when no range was specified
+## 2010-11-14 Fixed sheet # index (was offset by -1) in call to getusedrange() in case
+## of text sheet name arg
+## 2012-01-26 Fixed "seealso" help string
+
+function [ rawarr, xls, rstatus ] = xls2jpoi2oct (xls, wsh, cellrange, spsh_opts)
+
+ persistent ctype;
+ if (isempty (ctype))
+ # Get enumerated 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
+
+ rstatus = 0; jerror = 0;
+ wb = xls.workbook;
+
+ # 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 (); # 0-based
+ lastrow = sh.getLastRowNum (); # 0-based
+ if (isempty (cellrange))
+ if (ischar (wsh))
+ # get numeric sheet index
+ ii = wb.getSheetIndex (sh) + 1;
+ else
+ ii = wsh;
+ endif
+ [ firstrow, lastrow, lcol, rcol ] = getusedrange (xls, ii);
+ if (firstrow == 0 && lastrow == 0)
+ # Empty sheet
+ rawarr = {};
+ printf ("Worksheet '%s' contains no data\n", sh.getSheetName ());
+ rstatus = 1;
+ return;
+ else
+ nrows = lastrow - firstrow + 1;
+ ncols = rcol - lcol + 1;
+ endif
+ else
+ # Translate range to HSSF POI row & column numbers
+ [topleft, nrows, ncols, firstrow, lcol] = parse_sp_range (cellrange);
+ lastrow = firstrow + nrows - 1;
+ rcol = lcol + ncols - 1;
+ endif
+
+ # Create formula evaluator (needed to infer proper cell type into rawarr)
+ frm_eval = wb.getCreationHelper().createFormulaEvaluator ();
+
+ # Read contents into rawarr
+ rawarr = cell (nrows, ncols); # create placeholder
+ for ii = firstrow:lastrow
+ irow = sh.getRow (ii-1);
+ if ~isempty (irow)
+ scol = (irow.getFirstCellNum).intValue ();
+ ecol = (irow.getLastCellNum).intValue () - 1;
+ for jj = lcol:rcol
+ scell = irow.getCell (jj-1);
+ if ~isempty (scell)
+ # Explore cell contents
+ type_of_cell = scell.getCellType ();
+ if (type_of_cell == ctype(3)) # Formula
+ if ~(spsh_opts.formulas_as_text)
+ try # Because not al Excel formulas have been implemented in POI
+ cvalue = frm_eval.evaluate (scell);
+ type_of_cell = cvalue.getCellType();
+ # Separate switch because form.eval. yields different type
+ switch type_of_cell
+ case ctype (1) # Numeric
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getNumberValue ();
+ case ctype(2) # String
+ rawarr {ii+1-firstrow, jj+1-lcol} = char (scell.getStringValue ());
+ case ctype (5) # Boolean
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.BooleanValue ();
+ otherwise
+ # Nothing to do here
+ endswitch
+ # Set cell type to blank to skip switch below
+ type_of_cell = ctype(4);
+ catch
+ # In case of formula errors we take the cached results
+ type_of_cell = scell.getCachedFormulaResultType ();
+ ++jerror; # We only need one warning even for multiple errors
+ end_try_catch
+ endif
+ endif
+ # Preparations done, get data values into data array
+ switch type_of_cell
+ case ctype(1) # 0 Numeric
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getNumericCellValue ();
+ case ctype(2) # 1 String
+ rawarr {ii+1-firstrow, jj+1-lcol} = char (scell.getRichStringCellValue ());
+ case ctype(3)
+ if (spsh_opts.formulas_as_text)
+ tmp = char (scell.getCellFormula ());
+ rawarr {ii+1-firstrow, jj+1-lcol} = ['=' tmp];
+ endif
+ case ctype(4) # 3 Blank
+ # Blank; ignore until further notice
+ case ctype(5) # 4 Boolean
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getBooleanCellValue ();
+ otherwise # 5 Error
+ # Ignore
+ endswitch
+ endif
+ endfor
+ endif
+ endfor
+
+ if (jerror > 0) warning (sprintf ("xls2oct: %d cached values instead of formula evaluations.\n", jerror)); endif
+
+ rstatus = 1;
+ xls.limits = [lcol, rcol; firstrow, lastrow];
+
+endfunction
+
+
+#==================================================================================
+## Copyright (C) 2009,2010,2011,2012 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} ] = xls2jxla2oct (@var{xls})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2jxla2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2jxla2oct (@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.
+##
+## xls2jxla2oct should not be invoked directly but rather through xls2oct.
+##
+## Examples:
+##
+## @example
+## [Arr, status, xls] = xls2jxla2oct (xls, 'Second_sheet', 'B3:AY41');
+## B = xls2jxla2oct (xls, 'Second_sheet');
+## @end example
+##
+## @seealso {xls2oct, oct2xls, xlsopen, xlsclose, xlsread, xlswrite, oct2jxla2xls}
+##
+## @end deftypefn
+
+## Author: Philip Nienhuis
+## Created: 2009-12-04
+## Updates:
+## 2009-12-11 ??? some bug fix
+## 2010-07-28 Added option to read formulas as text strings rather than evaluated value
+## Added check for proper xls structure
+## 2010-07-29 Added check for too latge requested data rectangle
+## 2010-10-10 Code cleanup: -getusedrange(); moved cropping result array to
+## '' calling function
+## 2010-11-12 Moved ptr struct check into main func
+## 2010-11-13 Catch empty sheets when no range was specified
+## 2011-04-11 (Ron Goldman <ron@ocean.org.il>) Fixed missing months var, wrong arg
+## '' order in strsplit, wrong isTime condition
+## 2012-01-26 Fixed "seealso" help string
+
+function [ rawarr, xls, rstatus ] = xls2jxla2oct (xls, wsh, cellrange, spsh_opts)
+
+ persistent ctype; persistent months;
+ if (isempty (ctype))
+ ctype = cell (11, 1);
+ # Get enumerated cell types. Beware as they start at 0 not 1
+ ctype( 1) = (java_get ('jxl.CellType', 'BOOLEAN')).toString ();
+ ctype( 2) = (java_get ('jxl.CellType', 'BOOLEAN_FORMULA')).toString ();
+ ctype( 3) = (java_get ('jxl.CellType', 'DATE')).toString ();
+ ctype( 4) = (java_get ('jxl.CellType', 'DATE_FORMULA')).toString ();
+ ctype( 5) = (java_get ('jxl.CellType', 'EMPTY')).toString ();
+ ctype( 6) = (java_get ('jxl.CellType', 'ERROR')).toString ();
+ ctype( 7) = (java_get ('jxl.CellType', 'FORMULA_ERROR')).toString ();
+ ctype( 8) = (java_get ('jxl.CellType', 'NUMBER')).toString ();
+ ctype( 9) = (java_get ('jxl.CellType', 'LABEL')).toString ();
+ ctype(10) = (java_get ('jxl.CellType', 'NUMBER_FORMULA')).toString ();
+ ctype(11) = (java_get ('jxl.CellType', 'STRING_FORMULA')).toString ();
+ months = {'JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC'};
+ endif
+
+ rstatus = 0;
+ wb = xls.workbook;
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = wb.getNumberOfSheets ();
+ shnames = char (wb.getSheetNames ());
+ 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.getSheet (wsh - 1); # JXL sheet count 0-based
+ printf ("(Reading from worksheet %s)\n", shnames {wsh});
+ else
+ sh = wb.getSheet (wsh);
+ if (isempty (sh)), error (sprintf ("Worksheet %s not found in file %s", wsh, xls.filename)); endif
+ end
+
+ if (isempty (cellrange))
+ # Get numeric sheet pointer (1-based)
+ ii = 1;
+ while (ii <= nr_of_sheets)
+ if (strcmp (wsh, shnames{ii}) == 1)
+ wsh = ii;
+ ii = nr_of_sheets + 1;
+ else
+ ++ii;
+ endif
+ endwhile
+ # Get data rectangle row & column numbers (1-based)
+ [firstrow, lastrow, lcol, rcol] = getusedrange (xls, wsh);
+ if (firstrow == 0 && lastrow == 0)
+ # Empty sheet
+ rawarr = {};
+ printf ("Worksheet '%s' contains no data\n", shnames {wsh});
+ rstatus = 1;
+ return;
+ else
+ nrows = lastrow - firstrow + 1;
+ ncols = rcol - lcol + 1;
+ endif
+ else
+ # Translate range to row & column numbers (1-based)
+ [dummy, nrows, ncols, firstrow, lcol] = parse_sp_range (cellrange);
+ # Check for too large requested range against actually present range
+ lastrow = min (firstrow + nrows - 1, sh.getRows ());
+ nrows = min (nrows, sh.getRows () - firstrow + 1);
+ ncols = min (ncols, sh.getColumns () - lcol + 1);
+ rcol = lcol + ncols - 1;
+ endif
+
+ # Read contents into rawarr
+ rawarr = cell (nrows, ncols); # create placeholder
+ for jj = lcol : rcol
+ for ii = firstrow:lastrow
+ scell = sh.getCell (jj-1, ii-1);
+ switch char (scell.getType ())
+ case ctype {1} # Boolean
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
+ case ctype {2} # Boolean formula
+ if (spsh_opts.formulas_as_text)
+ tmp = scell.getFormula ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
+ else
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
+ endif
+ case ctype {3} # Date
+ try
+ % Older JXL.JAR, returns float
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
+ catch
+ % Newer JXL.JAR, returns date string w. epoch = 1-1-1900 :-(
+ tmp = strsplit (char (scell.getDate ()), ' ');
+ yy = str2num (tmp{6});
+ mo = find (ismember (months, upper (tmp{2})) == 1);
+ dd = str2num (tmp{3});
+ hh = str2num (tmp{4}(1:2));
+ mi = str2num (tmp{4}(4:5));
+ ss = str2num (tmp{4}(7:8));
+ if (scell.isTime ())
+ yy = mo = dd = 0;
+ endif
+ rawarr {ii+1-firstrow, jj+1-lcol} = datenum (yy, mo, dd, hh, mi, ss);
+ end_try_catch
+ case ctype {4} # Date formula
+ if (spsh_opts.formulas_as_text)
+ tmp = scell.getFormula ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
+ else
+ unwind_protect
+ % Older JXL.JAR, returns float
+ tmp = scell.getValue ();
+ % if we get here, we got a float (old JXL).
+ % Check if it is time
+ if (~scell.isTime ())
+ % Reset rawarr <> so it can be processed below as date string
+ rawarr {ii+1-firstrow, jj+1-lcol} = [];
+ else
+ rawarr {ii+1-firstrow, jj+1-lcol} = tmp;
+ end
+ unwind_protect_cleanup
+ if (isempty (rawarr {ii+1-firstrow, jj+1-lcol}))
+ % Newer JXL.JAR, returns date string w. epoch = 1-1-1900 :-(
+ tmp = strsplit (char (scell.getDate ()), ' ');
+ yy = str2num (tmp{6});
+ mo = find (ismember (months, upper (tmp{2})) == 1);
+ dd = str2num (tmp{3});
+ hh = str2num (tmp{4}(1:2));
+ mi = str2num (tmp{4}(4:5));
+ ss = str2num (tmp{4}(7:8));
+ if (scell.isTime ())
+ yy = 0; mo = 0; dd = 0;
+ end
+ rawarr {ii+1-firstrow, jj+1-lcol} = datenum (yy, mo, dd, hh, mi, ss);
+ endif
+ end_unwind_protect
+ endif
+ case { ctype {5}, ctype {6}, ctype {7} }
+ # Empty, Error or Formula error. Nothing to do here
+ case ctype {8} # Number
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
+ case ctype {9} # String
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getString ();
+ case ctype {10} # Numerical formula
+ if (spsh_opts.formulas_as_text)
+ tmp = scell.getFormula ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
+ else
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
+ endif
+ case ctype {11} # String formula
+ if (spsh_opts.formulas_as_text)
+ tmp = scell.getFormula ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
+ else
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getString ();
+ endif
+ otherwise
+ # Do nothing
+ endswitch
+ endfor
+ endfor
+
+ rstatus = 1;
+ xls.limits = [lcol, rcol; firstrow, lastrow];
+
+endfunction
+
+
+## Copyright (C) 2011 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 3 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} ] = xls2oxs2oct (@var{xls})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2oxs2oct (@var{xls}, @var{wsh})
+## @deftypefnx {Function File} [@var{obj}, @var{rstatus}, @var{xls} ] = xls2oxs2oct (@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.
+##
+## xls2oxs2oct should not be invoked directly but rather through xls2oct.
+##
+
+## Author: Philip Nienhuis
+## Created: 2011-03-26
+## Updates:
+## 2012-02-25 Changed ctype into num array rather than cell array
+
+function [ rawarr, xls, rstatus ] = xls2oxs2oct (xls, wsh, cellrange, spsh_opts)
+
+ persistent ctype;
+ if (isempty (ctype))
+ ctype = zeros (6, 1);
+ # Get enumerated cell types. Beware as they start at 0 not 1
+ ctype( 1) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_STRING')); # 0
+ ctype( 2) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_FP')); # 1
+ ctype( 3) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_INT')); # 2
+ ctype( 4) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_FORMULA')); # 3
+ ctype( 5) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_BOOLEAN')); # 4
+ ctype( 6) = (java_get ('com.extentech.ExtenXLS.CellHandle', 'TYPE_DOUBLE')); # 5
+ endif
+
+ rstatus = 0;
+ wb = xls.workbook;
+
+ # Check if requested worksheet exists in the file & if so, get pointer
+ nr_of_sheets = wb.getNumWorkSheets ();
+ 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.getWorkSheet (wsh - 1); # OXS sheet count 0-based
+ printf ("(Reading from worksheet %s)\n", sh.getSheetName ());
+ else
+ try
+ sh = wb.getWorkSheet (wsh);
+ catch
+ error (sprintf ("Worksheet %s not found in file %s", wsh, xls.filename));
+ end_try_catch
+ end
+
+ if (isempty (cellrange))
+ # Get numeric sheet pointer (0-based)
+ wsh = sh.getTabIndex ();
+ # Get data rectangle row & column numbers (1-based)
+ [firstrow, lastrow, lcol, rcol] = getusedrange (xls, wsh+1);
+ if (firstrow == 0 && lastrow == 0)
+ # Empty sheet
+ rawarr = {};
+ printf ("Worksheet '%s' contains no data\n", shnames {wsh});
+ rstatus = 1;
+ return;
+ else
+ nrows = lastrow - firstrow + 1;
+ ncols = rcol - lcol + 1;
+ endif
+ else
+ # Translate range to row & column numbers (1-based)
+ [dummy, nrows, ncols, firstrow, lcol] = parse_sp_range (cellrange);
+ # Check for too large requested range against actually present range
+ lastrow = min (firstrow + nrows - 1, sh.getLastRow + 1 ());
+ nrows = min (nrows, sh.getLastRow () - firstrow + 1);
+ ncols = min (ncols, sh.getLastCol () - lcol + 1);
+ rcol = lcol + ncols - 1;
+ endif
+
+ # Read contents into rawarr
+ rawarr = cell (nrows, ncols); # create placeholder
+ for jj = lcol:rcol
+ for ii = firstrow:lastrow
+ try
+ scell = sh.getCell (ii-1, jj-1);
+ sctype = scell.getCellType ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getVal ();
+ if (sctype == ctype(2) || sctype == ctype(3) || sctype == ctype(6))
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getDoubleVal ();
+ endif
+ catch
+ # Empty or non-existing cell
+ end_try_catch
+ endfor
+ endfor
+
+ rstatus = 1;
+ xls.limits = [lcol, rcol; firstrow, lastrow];
+
+endfunction
+
+
+## Copyright (C) 2011,2012 Philip Nienhuis <prnienhuis@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 3 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/>.
+
+## xls2uno2oct
+
+## Author: Philip Nienhuis <prnienhuis@users.sf.net>
+## Created: 2011-05-05
+## Updates:
+## 2011-09-18 Adapted sh_names type to LO 3.4.1
+## 2011-09-19 Try to decipher if formulas return numeric or string values
+
+function [rawarr, xls, rstatus] = xls2uno2oct (xls, wsh, datrange, spsh_opts)
+
+ sheets = xls.workbook.getSheets ();
+ sh_names = sheets.getElementNames ();
+ if (! iscell (sh_names))
+ # Java array (LibreOffice 3.4.+); convert to cellstr
+ sh_names = char (sh_names);
+ else
+ sh_names = {sh_names};
+ endif
+
+ # Check sheet pointer
+ if (isnumeric (wsh))
+ if (wsh < 1 || wsh > numel (sh_names))
+ error ("Sheet index %d out of range 1-%d", wsh, numel (sh_names));
+ endif
+ else
+ ii = strmatch (wsh, sh_names);
+ if (isempty (ii)), error ("Sheet '%s' not found", wsh); endif
+ wsh = ii;
+ endif
+ unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XSpreadsheet');
+ sh = sheets.getByName(sh_names{wsh}).getObject.queryInterface (unotmp);
+
+ unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XCellRangesQuery');
+ xRQ = sh.queryInterface (unotmp);
+ # Get cell ranges of all rectangles containing data. Type values:
+ #java_get ('com.sun.star.sheet.CellFlags', 'VALUE') ans = 1
+ #java_get ('com.sun.star.sheet.CellFlags', 'DATETIME') ans = 2
+ #java_get ('com.sun.star.sheet.CellFlags', 'STRING') ans = 4
+ #java_get ('com.sun.star.sheet.CellFlags', 'FORMULA') ans = 16
+ # Yep, boolean is lacking...
+ Cellflgs = javaObject ("java.lang.Short", "23");
+ ccells = xRQ.queryContentCells (Cellflgs);
+ addrs = ccells.getRangeAddressesAsString ();
+
+ # Strip sheet name from addresses
+ adrblks = strsplit (addrs, ',');
+ if (isempty (adrblks))
+ warning ('Sheet %s contains no data', sh_names{wsh});
+ return
+ endif
+
+ # Either parse (given cell range) or prepare (unknown range) help variables.
+ # As OpenOffice knows the occupied range, we need the limits anyway to avoid
+ # out-of-range errors
+ [ trow, brow, lcol, rcol ] = getusedrange (xls, wsh);
+ if (isempty (datrange))
+ nrows = brow - trow + 1; # Number of rows to be read
+ ncols = rcol - lcol + 1; # Number of columns to be read
+ else
+ [dummy, nrows, ncols, srow, scol] = parse_sp_range (datrange);
+ # Truncate range silently if needed
+ brow = min (srow + nrows - 1, brow);
+ rcol = min (scol + ncols - 1, rcol);
+ trow = max (trow, srow);
+ lcol = max (lcol, scol);
+ nrows = min (brow - trow + 1, nrows); # Number of rows to be read
+ ncols = min (rcol - lcol + 1, ncols); # Number of columns to be read
+ endif
+ # Create storage for data at Octave side
+ rawarr = cell (nrows, ncols);
+
+ # Get data. Apparently row & column indices are 0-based in UNO
+ for ii=trow-1:brow-1
+ for jj=lcol-1:rcol-1
+ XCell = sh.getCellByPosition (jj, ii);
+ cType = XCell.getType().getValue ();
+ switch cType
+ case 1 # Value
+ rawarr{ii-trow+2, jj-lcol+2} = XCell.getValue ();
+ case 2 # String
+ unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
+ rawarr{ii-trow+2, jj-lcol+2} = XCell.queryInterface (unotmp).getString ();
+ case 3 # Formula
+ if (spsh_opts.formulas_as_text)
+ rawarr{ii-trow+2, jj-lcol+2} = XCell.getFormula ();
+ else
+ # Unfortunately OOo gives no clue as to the type of formula result
+ unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
+ rawarr{ii-trow+2, jj-lcol+2} = XCell.queryInterface (unotmp).getString ();
+ tmp = str2double (rawarr{ii-trow+2, jj-lcol+2});
+ # If the string happens to contain just a number we'll assume it is numeric
+ if (~isnan (tmp)); rawarr{ii-trow+2, jj-lcol+2} = tmp; endif
+ endif
+ otherwise
+ # Empty cell
+ endswitch
+ endfor
+ endfor
+
+ # Keep track of data rectangle limits
+ xls.limits = [lcol, rcol; trow, brow];
+
+ rstatus = ~isempty (rawarr);
+
+endfunction
git://git.creatis.insa-lyon.fr / CreaPhase.git / blobdiff