Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[7915] trunk/octave-forge/main/io/inst
|
From: <prn...@us...> - 2010-11-14 16:53:37
|
Revision: 7915
http://octave.svn.sourceforge.net/octave/?rev=7915&view=rev
Author: prnienhuis
Date: 2010-11-14 16:53:26 +0000 (Sun, 14 Nov 2010)
Log Message:
-----------
Many bug fixes; more rigid input & output arg checks; better tracking of changes to files; also tested w Apache POI 3.7
Modified Paths:
--------------
trunk/octave-forge/main/io/inst/oct2xls.m
trunk/octave-forge/main/io/inst/xls2oct.m
trunk/octave-forge/main/io/inst/xlsclose.m
trunk/octave-forge/main/io/inst/xlsopen.m
trunk/octave-forge/main/io/inst/xlsread.m
trunk/octave-forge/main/io/inst/xlswrite.m
Modified: trunk/octave-forge/main/io/inst/oct2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2xls.m 2010-11-14 16:51:22 UTC (rev 7914)
+++ trunk/octave-forge/main/io/inst/oct2xls.m 2010-11-14 16:53:26 UTC (rev 7915)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -36,7 +36,7 @@
##
## @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
+## used & named according to @var{wsh} - extra empty 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
@@ -98,11 +98,14 @@
## 2010-08-16 Added check on presence of output argument. Made wsh = 1 default
## 2010-08-17 Corrected texinfo ("topleft" => "range")
## 2010-08-25 Improved help text (section on java memory usage)
+## 2010-11-12 Moved ptr struct check into main func. More input validity checks
+## 2010-11-13 Added check for 2-D input array
-## Last script file update (incl. subfunctions): 2010-08-11
+## Last script file update (incl. subfunctions): 2011-11-12
function [ xls, rstatus ] = oct2xls (obj, xls, wsh=1, crange=[], spsh_opts=[])
+ if (nargin < 2) error ("oct2xls needs a minimum of 2 arguments."); endif
# Make sure input array is a cell array
if (isempty (obj))
warning ("Request to write empty matrix - ignored.");
@@ -116,43 +119,54 @@
elseif (~iscell (obj))
error ("oct2xls: input array neither cell nor numeric array");
endif
-
+ if (ndims (c_arr) > 2), error ("Only 2-dimensional arrays can be written to spreadsheet"); endif
+ # Check xls file pointer struct
+ test1 = ~isfield (xls, "xtype");
+ test1 = test1 || ~isfield (xls, "workbook");
+ test1 = test1 || isempty (xls.workbook);
+ test1 = test1 || isempty (xls.app);
+ 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 # 3"); endif
+ # Check range
+ if (~(isempty (crange) || ischar (crange))), error ("Character string (range) expected for arg # 4"); endif
# Various options
- if isempty (spsh_opts)
+ if (isempty (spsh_opts))
spsh_opts.formulas_as_text = 0;
# other options to be implemented here
+ elseif (isstruct (spsh_opts))
+ if (~isfield (spsh_opts, 'formulas_as_text')), spsh_opts.formulas_as_text = 0; endif
+ # other options to be implemented here
+ else
+ error ("Structure expected for arg # 5");
endif
-
+
+ if (nargout < 1) printf ("Warning: no output spreadsheet file pointer specified.\n"); endif
+
# Select interface to be used
if (strcmp (xls.xtype, 'COM'))
# Call oct2com2xls to do the work
[xls, rstatus] = oct2com2xls (obj, xls, wsh, crange, spsh_opts);
-
elseif (strcmp (xls.xtype, 'POI'))
# Invoke Java and Apache POI
[xls, rstatus] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts);
-
elseif (strcmp (xls.xtype, 'JXL'))
# Invoke Java and JExcelAPI
[xls, rstatus] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts);
-
# elseif (strcmp'xls.xtype, '<whatever>'))
# <Other Excel interfaces>
-
else
error (sprintf ("oct2xls: unknown Excel .xls interface - %s.", xls.xtype));
-
endif
endfunction
#===================================================================================
-## Copyright (C) 2007 by Michael Goffioul <michael.goffioul at swing.be>
+## Copyright (C) 2009,2010 by Philip Nienhuis <prn...@us...>
##
-## 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
@@ -189,7 +203,7 @@
##
## @end deftypefn
-## Author: Philip Nienhuis
+## Author: Philip Nienhuis (originally based on mat2xls by Michael Goffioul)
## Rewritten: 2009-09-26
## Updates:
## 2009-12-11
@@ -203,18 +217,18 @@
## " Added option for formula input as text string
## 2010-08-01 Added range vs. array size vs. capacity checks
## 2010-08-03 Moved range checks and type array parsing to separate functions
+## 2010-10-20 Bug fix removing new empty sheets in new workbook that haven't been
+## created in the first place duetoExcelsetting (thanks Ian Journeaux)
+## " Changed range use in COM transfer call
+## 2010-10-21 Improved file change tracking (var xls.changed)
+## 2010-10-24 Fixed bug introduced in above fix: for loops have no stride param,
+## " replaced by while loop
+## " Added check for "live" ActiveX server
+## 2010-11-12 Moved ptr struct check into main func
function [ xls, status ] = oct2com2xls (obj, xls, wsh, crange, spsh_opts)
- # 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 (~strmatch (tolower (xls.filename(end-4:end)), '.xls'))
error ("oct2com2xls can only write to Excel .xls or .xlsx files")
endif
@@ -223,19 +237,23 @@
elseif (size (wsh, 2) > 31)
error ("Illegal worksheet name - too long")
endif
- # Check xls file pointer 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
+ # Check to see if ActiveX is still alive
+ try
+ wb_cnt = xls.workbook.Worksheets.count;
+ catch
+ error ("ActiveX invocation in file ptr struct seems non-functional");
+ end_try_catch
+ # define some constants not yet in __COM__.cc
+ xlWorksheet = -4167; # xlChart= 4;
+ # scratch vars
+ status = 0;
+
# Parse date ranges
[nr, nc] = size (obj);
[topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
+ lowerright = calccelladdress (trow + nrows - 1, lcol + ncols - 1);
+ crange = [topleft ':' lowerright];
if (nrows < nr || ncols < nc)
warning ("Array truncated to fit in range");
obj = obj(1:nrows, 1:ncols);
@@ -246,7 +264,7 @@
typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
# Make cells now indicated to be empty, empty
fptr = ~(4 * (ones (size (typearr))) .- typearr);
- obj(fptr) = cellfun(@(x) [], obj(fptr), "Uniformoutput", false);
+ obj(fptr) = cellfun (@(x) [], obj(fptr), "Uniformoutput", false);
if (spsh_opts.formulas_as_text)
# find formulas (designated by a string starting with "=" and ending in ")")
@@ -256,8 +274,9 @@
endif
clear fptr;
- if (xls.changed < 2)
- # Existing file. Some involved investigation is needed to preserve
+ if (xls.changed < 3)
+ # Existing file OR a new file with data added in a previous oct2xls call.
+ # Some involved investigation is needed to preserve
# existing data that shouldn't be touched.
#
# See if desired *sheet* name exists.
@@ -300,7 +319,7 @@
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 as you can't write data to Chart sheet
error (sprintf ("Existing sheet '%s' is not type worksheet.", wsh));
else
# Simply point to the relevant sheet
@@ -308,7 +327,11 @@
endif
else
# Add a new worksheet. Earlier it was checked whether this is safe
- sh = xls.workbook.Worksheets.Add ();
+ try
+ sh = xls.workbook.Worksheets.Add ();
+ catch
+ error (sprintf ("Cannot add new worksheet to file %s\n", xls.filename));
+ end_try_catch
if (~isnumeric (wsh))
sh.Name = wsh;
else
@@ -328,44 +351,52 @@
# ....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);
+ # The easy case: a new Excel file. Workbook was created in xlsopen.
+
# Delete empty non-used sheets, last one first
xls.app.Application.DisplayAlerts = 0;
- xls.workbook.Worksheets(3).Delete(); xls.workbook.Worksheets(2).Delete();
+ ii = xls.workbook.Sheets.count;
+ while (ii > 1)
+ xls.workbook.Worksheets(ii).Delete();
+ --ii;
+ endwhile
xls.app.Application.DisplayAlerts = 1;
+ # Write to first worksheet:
+ sh = xls.workbook.Worksheets (1);
# Rename the sheet
- if (isnumeric(wsh))
- sh.Name = sprintf("Sheet%i", wsh);
+ if (isnumeric (wsh))
+ sh.Name = sprintf ("Sheet%i", wsh);
else
sh.Name = wsh;
endif
- xls.changed = 2;
+ xls.changed = 2; # 3 => 2
endif
-
+
# MG's original part.
# Save object in Excel sheet, starting at cell top_left_cell
if (~isempty(obj))
- r = sh.Range (topleft);
- r = r.Resize (size (obj, 1), size (obj, 2));
- r.Value = obj;
+ r = sh.Range (crange);
+ try
+ r.Value = obj;
+ catch
+ error (sprintf ("Cannot add data to worksheet %s in file %s\n", sh.Name, xls.filename));
+ end_try_catch
delete (r);
endif
# If we get here, all went OK
status = 1;
+ xls.changed = max (xls.changed, 1); # If it was 2, preserve it.
endfunction
#====================================================================================
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -417,9 +448,17 @@
## 2010-08-01 Improved try-catch for formulas to enter wrong formulas as text strings
## 2010-08-01 Added range vs. array size vs. capacity checks
## 2010-08-03 Moved range checks and type array parsingto separate functions
+## 2010-10-21 Improved logic for tracking file changes
+## 2010-10-27 File change tracking again refined, internal var 'changed' dropped
+## 2010-11-12 Moved ptr struct check into main func
function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts)
+ # Preliminary sanity checks
+ if (~strmatch (tolower (xls.filename(end-4:end)), '.xls'))
+ error ("oct2jpoi2xls can only write to Excel .xls or .xlsx files")
+ endif
+
persistent ctype;
if (isempty (ctype))
# Get cell types. Beware as they start at 0 not 1
@@ -429,25 +468,9 @@
ctype(4) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_FORMULA'); # 2
ctype(5) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK'); # 3
endif
-
# scratch vars
- rstatus = 0; changed = 1; f_errs = 0;
+ rstatus = 0; f_errs = 0;
- # Preliminary sanity checks
- 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))
@@ -461,6 +484,7 @@
endwhile
if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
sh = xls.workbook.createSheet (strng);
+ xls.changed = min (xls.changed, 2); # Keep 2 for new files
else
sh = xls.workbook.getSheetAt (wsh - 1); # POI sheet count 0-based
endif
@@ -470,7 +494,7 @@
if (isempty (sh))
# Sheet not found, just create it
sh = xls.workbook.createSheet (wsh);
- xls.changed = 2;
+ xls.changed = min (xls.changed, 2); # Keep 2 or 3 f. new files
endif
endif
@@ -486,6 +510,7 @@
typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
if ~(spsh_opts.formulas_as_text)
# Remove leading '=' from formula strings
+ # FIXME should be easier using typearr<4> info
fptr = ~(2 * (ones (size (typearr))) .- typearr);
obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
endif
@@ -521,14 +546,14 @@
if (f_errs)
printf ("%d formula errors encountered - please check input array\n", f_errs);
endif
- xls.changed = 1;
+ xls.changed = max (xls.changed, 1); # Preserve a "2"
rstatus = 1;
endfunction
#====================================================================================
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -582,44 +607,38 @@
## " Changed topleft arg into range arg (topleft version still recognized)
## 2010-08-03 Moved range checks and cell type parsing to separate routines
## 2010-08-11 Moved addcell() into try-catch as it is addCell which throws fatal errors
+## 2010-10-20 Improved logic for tracking file changes (xls.changed 2 or 3); dropped
+## " internal variable 'changed'
+## 2010-10-27 File change tracking again refined
+## 2010-11-12 Moved ptr struct check into main func
function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts)
+ # Preliminary sanity checks
+ if (~strmatch (tolower (xls.filename(end-4:end-1)), '.xls')) # No OOXML in JXL
+ error ("JExcelAPI can only write to Excel .xls files")
+ endif
+
persistent ctype;
if (isempty (ctype))
ctype = [1, 2, 3, 4, 5];
# Number, Boolean, String, Formula, Empty
endif
-
# scratch vars
- rstatus = 0; changed = 1; f_errs = 0;
-
- # Preliminary sanity checks
- 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')) # No OOXML in JXL
- error ("oct2java2xls can only write to Excel .xls files")
- endif
- test1 = ~isfield (xls, "xtype");
- test1 = test1 || ~isfield (xls, "workbook");
- test1 = test1 || ~strcmp (char (xls.xtype), 'JXL');
- test1 = test1 || isempty (xls.workbook);
- test1 = test1 || isempty (xls.app);
- if test1
- error ("Invalid file pointer struct");
- endif
+ rstatus = 0; f_errs = 0;
# Prepare workbook pointer if needed
- if (xls.changed < 2)
- # Create writable copy of workbook. If 2 a writable wb was made in xlsopen
+ if (xls.changed == 0) # Only for 1st call of octxls after xlsopen
+ # 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
+ # Catch JExcelAPI bug/"feature": when switching to write mode, the file on disk
+ # is affected and the memory file MUST be written to disk to save earlier data
+ xls.changed = 1;
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))
@@ -633,8 +652,9 @@
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;
+ xls.changed = min (xls.changed, 2); # Keep a 2 in case of new file
else
- sh = wb.getSheet (wsh - 1); # POI sheet count 0-based
+ 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});
@@ -644,13 +664,10 @@
# Sheet not found, just create it
sh = wb.createSheet (wsh, nr_of_sheets);
++nr_of_sheets;
- xls.changed = 2;
+ xls.changed = min (xls.changed, 2); # Keep a 2 for new file
endif
endif
- # Beware of strings variables interpreted as char arrays; change them to cell.
- if (ischar (obj)) obj = {obj}; endif
-
# Parse date ranges
[nr, nc] = size (obj);
[topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
@@ -689,7 +706,7 @@
# There's no guarantee for formula correctness, so....
try # Actually JExcelAPI flags formula errors as warnings :-(
tmp = java_new ('jxl.write.Formula', kk, ll, obj{ii, jj});
- # ... while errors are actualy detected in addCell(), so
+ # ... while errors are actually detected in addCell(), so
# that should be within the try-catch
sh.addCell (tmp);
catch
@@ -710,7 +727,7 @@
if (f_errs)
printf ("%d formula errors encountered - please check input array\n", f_errs);
endif
- xls.changed = 1;
+ xls.changed = max (xls.changed, 1); # Preserve 2 for new files
rstatus = 1;
endfunction
Modified: trunk/octave-forge/main/io/inst/xls2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2oct.m 2010-11-14 16:51:22 UTC (rev 7914)
+++ trunk/octave-forge/main/io/inst/xls2oct.m 2010-11-14 16:53:26 UTC (rev 7915)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -64,7 +64,6 @@
##
## If one of the Java interfaces is used, field @var{xls}.limits contains
## the outermost column and row numbers of the actually read cell range.
-## This doesn't work with native Excel / COM.
##
## Erroneous data and empty cells turn up empty in @var{rawarr}.
## Date/time values in Excel are returned as numerical values.
@@ -106,21 +105,44 @@
## Created: 2010-10-16
## Updates:
## 2009-01-03 (added OOXML support & cleaned up code. Excel
-## ADDRESS function still not working OK)
+## 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
##
-## Latest subfunc update: 2010-10-08 (xls2com2oct)
+## Latest subfunc update: 2010-11-14 (poi)
-function [ rawarr, xls, rstatus ] = xls2oct (xls, wsh, datrange='', spsh_opts=[])
+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);
+ if test1
+ error ("Invalid xls file 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
@@ -167,7 +189,7 @@
#====================================================================================
-## Copyright (C) 2009 P.R. Nienhuis, <pr.nienhuis at hccnet.nl>
+## Copyright (C) 2009,2010 P.R. Nienhuis, <pr.nienhuis at hccnet.nl>
##
## based on mat2xls by Michael Goffioul (2007) <mic...@sw...>
##
@@ -211,6 +233,10 @@
## 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
function [rawarr, xls, rstatus ] = xls2com2oct (xls, wsh, crange)
@@ -222,20 +248,14 @@
warning ("Worksheet name too long - truncated")
wsh = wsh(1:31);
endif
-
- # 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;
+ # 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;
@@ -244,6 +264,7 @@
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;
@@ -269,8 +290,17 @@
allcells = sh.UsedRange;
# Get actually used range indices
[trow, brow, lcol, rcol] = getusedrange (xls, old_sh);
- nrows = brow - trow + 1; ncols = rcol - lcol + 1;
- topleft = calccelladdress (trow, lcol);
+ 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);
@@ -280,10 +310,9 @@
if (nrows >= 1)
# Get object from Excel sheet, starting at cell top_left_cell
- r = sh.Range (topleft);
- r = r.Resize (nrows, ncols);
- rawarr = r.Value;
- delete (r);
+ rr = sh.Range (crange);
+ rawarr = rr.Value;
+ delete (rr);
# Take care of actual singe cell range
if (isnumeric (rawarr) || ischar (rawarr))
@@ -304,7 +333,7 @@
#==================================================================================
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -350,6 +379,10 @@
## 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
+#3 of text sheet name arg
function [ rawarr, xls, status ] = xls2jpoi2oct (xls, wsh, cellrange=[], spsh_opts)
@@ -365,19 +398,8 @@
endif
status = 0; jerror = 0;
+ wb = xls.workbook;
- # 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))
@@ -393,33 +415,23 @@
firstrow = sh.getFirstRowNum (); # 0-based
lastrow = sh.getLastRowNum (); # 0-based
if (isempty (cellrange))
-# # Get used range by searching (slow...). Beware, it can be bit unreliable
-# ## FIXME - can be replaced by call to getusedrange.m
-# lcol = 65535; # Old xls value
-# lcol = 1048576; # OOXML (xlsx) max.
-# 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
if (ischar (wsh))
# get numeric sheet index
- ii = wb.getSheetIndex (sh);
+ ii = wb.getSheetIndex (sh) + 1;
else
ii = wsh;
endif
[ firstrow, lastrow, lcol, rcol ] = getusedrange (xls, ii);
- nrows = lastrow - firstrow + 1;
- ncols = rcol - lcol + 1;
+ 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);
@@ -450,11 +462,11 @@
# 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 ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getNumberValue ();
case ctype(2) # String
- rawarr (ii+1-firstrow, jj+1-lcol) = char (scell.getStringValue ());
+ rawarr {ii+1-firstrow, jj+1-lcol} = char (scell.getStringValue ());
case ctype (5) # Boolean
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.BooleanValue ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.BooleanValue ();
otherwise
# Nothing to do here
endswitch
@@ -470,18 +482,18 @@
# 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 ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getNumericCellValue ();
case ctype(2) # 1 String
- rawarr (ii+1-firstrow, jj+1-lcol) = char (scell.getRichStringCellValue ());
+ 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];
+ 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 ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getBooleanCellValue ();
otherwise # 5 Error
# Ignore
endswitch
@@ -492,20 +504,7 @@
if (jerror > 0) warning (sprintf ("xls2oct: %d cached values instead of formula evaluations.\n", jerror)); endif
-# # Crop rawarr from empty outer rows & columns
-# emptr = cellfun('isempty', rawarr);
-# 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 cell array
-# rawarr = rawarr(irowt:irowb, icoll:icolr);
status = 1;
-
xls.limits = [lcol, rcol; firstrow, lastrow];
endfunction
@@ -558,6 +557,8 @@
## 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
function [ rawarr, xls, status ] = xls2jxla2oct (xls, wsh, cellrange=[], spsh_opts)
@@ -565,33 +566,22 @@
if (isempty (ctype))
ctype = cell (11, 1);
# Get enumerated cell types. Beware as they start at 0 not 1
- ctype(1,1) = (java_get ('jxl.CellType', 'BOOLEAN')).toString ();
- ctype(2,1) = (java_get ('jxl.CellType', 'BOOLEAN_FORMULA')).toString ();
- ctype(3,1) = (java_get ('jxl.CellType', 'DATE')).toString ();
- ctype(4,1) = (java_get ('jxl.CellType', 'DATE_FORMULA')).toString ();
- ctype(5,1) = (java_get ('jxl.CellType', 'EMPTY')).toString ();
- ctype(6,1) = (java_get ('jxl.CellType', 'ERROR')).toString ();
- ctype(7,1) = (java_get ('jxl.CellType', 'FORMULA_ERROR')).toString ();
- ctype(8,1) = (java_get ('jxl.CellType', 'NUMBER')).toString ();
- ctype(9,1) = (java_get ('jxl.CellType', 'LABEL')).toString ();
- ctype(10,1) = (java_get ('jxl.CellType', 'NUMBER_FORMULA')).toString ();
- ctype(11,1) = (java_get ('jxl.CellType', 'STRING_FORMULA')).toString ();
+ 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 ();
endif
- status = 0;
+ status = 0;
+ wb = xls.workbook;
- # Check if xls struct pointer seems valid
- test1 = ~isfield (xls, "xtype");
- test1 = test1 || ~isfield (xls, "workbook");
- test1 = test1 || ~strcmp (char (xls.xtype), 'JXL');
- 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 ();
shnames = char (wb.getSheetNames ());
@@ -604,10 +594,6 @@
if (isempty (sh)), error (sprintf ("Worksheet %s not found in file %s", wsh, xls.filename)); endif
end
- # Check ranges
-# firstrow = 0;
-# lcol = 0;
-
if (isempty (cellrange))
# Get numeric sheet pointer (1-based)
ii = 1;
@@ -621,8 +607,16 @@
endwhile
# Get data rectangle row & column numbers (1-based)
[firstrow, lastrow, lcol, rcol] = getusedrange (xls, wsh);
- nrows = lastrow - firstrow + 1;
- ncols = rcol - lcol + 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);
@@ -639,43 +633,85 @@
for ii = firstrow:lastrow
scell = sh.getCell (jj-1, ii-1);
switch char (scell.getType ())
- case ctype {1, 1} # Boolean
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
- case ctype {2, 1} # Boolean formula
+ 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];
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
else
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
endif
- case ctype {3, 1} # Date
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
- case ctype {4, 1} # Date formula
+ 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];
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
else
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
+ 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, 1}, ctype {6, 1}, ctype {7, 1} }
+ case { ctype {5}, ctype {6}, ctype {7} }
# Empty, Error or Formula error. Nothing to do here
- case ctype {8, 1} # Number
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
- case ctype {9, 1} # String
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getString ();
- case ctype {10, 1} # Numerical formula
+ 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];
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
else
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getValue ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getValue ();
endif
- case ctype {11, 1} # String formula
+ case ctype {11} # String formula
if (spsh_opts.formulas_as_text)
tmp = scell.getFormula ();
- rawarr (ii+1-firstrow, jj+1-lcol) = ["=" tmp];
+ rawarr {ii+1-firstrow, jj+1-lcol} = ["=" tmp];
else
- rawarr (ii+1-firstrow, jj+1-lcol) = scell.getString ();
+ rawarr {ii+1-firstrow, jj+1-lcol} = scell.getString ();
endif
otherwise
# Do nothing
@@ -683,20 +719,7 @@
endfor
endfor
-# # Crop rawarr from empty outer rows & columns
-# emptr = cellfun('isempty', rawarr);
-# 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 cell array
-# rawarr = rawarr(irowt:irowb, icoll:icolr);
status = 1;
-
xls.limits = [lcol, rcol; firstrow, lastrow];
endfunction
Modified: trunk/octave-forge/main/io/inst/xlsclose.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsclose.m 2010-11-14 16:51:22 UTC (rev 7914)
+++ trunk/octave-forge/main/io/inst/xlsclose.m 2010-11-14 16:53:26 UTC (rev 7915)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -16,15 +16,28 @@
## -*- texinfo -*-
## @deftypefn {Function File} [@var{xls}] = xlsclose (@var{xls})
+## @deftypefnx {Function File} [@var{xls}] = xlsclose (@var{xls}, @var{filename})
## Close the Excel spreadsheet pointed to in struct @var{xls}, if needed
-## write the file to disk. An empty pointer struct will be returned.
-## xlsclose will determine if the file must be written to disk based
-## on information contained in @var{xls}.
+## write the file to disk. Based on information contained in @var{xls},
+## xlsclose will determine if the file should be written to disk.
##
-## You need MS-Excel (95 - 2003), and/or the Java package > 1.2.6 plus Apache
-## POI > 3.5 installed on your computer + proper javaclasspath set, to make
-## this function work at all.
+## If no errors occured during writing, the xls file pointer struct will be
+## reset and -if COM interface was used- ActiveX/Excel will be closed.
+## However if errors occurred, the file pinter will be ontouched so you can
+## clean up before a next try with xlsclose().
+## Be warned that until xlsopen is called again with the same @var{xls} pointer
+## struct and @var{_keepxls_} omitted or set to false, hidden Excel or Java
+## applications with associated (possibly large) memory chunks are kept alive
+## taking up resources.
##
+## @var{filename} can be used to write changed spreadsheet files to
+## an other file than opened with xlsopen(); unfortunately this doesn't work
+## with JXL (JExcelAPI) interface.
+##
+## You need MS-Excel (95 - 2010), and/or the Java package > 1.2.6 plus Apache
+## POI > 3.5 and/or JExcelAPI installed on your computer + proper
+## javaclasspath set, to make this function work at all.
+##
## @var{xls} must be a valid pointer struct made by xlsopen() in the same
## octave session.
##
@@ -41,15 +54,44 @@
##
## @end deftypefn
-
## Author: Philip Nienhuis
## Created: 2009-11-29
## Updates:
## 2010-01-03 (checked OOXML support)
## 2010-08-25 See also: xlsopen (instead of xlsclose)
+## 2010-10-20 Improved tracking of file changes and need to write it to disk
+## 2010-10-27 Various changes to catch errors when writing to disk;
+## " Added input arg "keepxls" for use with xlswrite.m to save the
+## " untouched file ptr struct in case of errors rather than wipe it
+## 2010-11-12 Replaced 'keepxls' by new filename arg; catch write errors and
+## always keep file pointer in case of write errors
-function [ xls ] = xlsclose (xls)
+function [ xls ] = xlsclose (xls, filename=[])
+ if (~isempty (filename))
+ if (ischar (filename))
+ if (xls.changed == 0)
+ warning ("File %s wasn't changed, new filename ignored.", filename);
+ else
+ if (strcmp (xls.xtype, 'JXL'))
+ warning ("JXL doesn't support changing filename, new filename ignored.");
+ elseif ~(strcmp (xls.xtype, 'COM') || strmatch ('.xls', filename))
+ # Excel / ActiveX will write any filename extension
+ error ('No .xls or .xlsx filename extension specified');
+ else
+ ### For multi-user environments, uncomment below AND relevant stanza in xlsopen
+ # In case of COM, be sure to first close the open workbook
+ #if (strcmp (xls.xtype, \xC7OM'))
+ # xls.app.Application.DisplayAlerts = 0;
+ # xls.workbook.close();
+ # xls.app.Application.DisplayAlerts = 0;
+ #endif
+ xls.filename = filename;
+ endif
+ endif
+ endif
+ endif
+
if (strcmp (xls.xtype, 'COM'))
# If file has been changed, write it out to disk.
#
@@ -62,45 +104,72 @@
# 51 = .xlsx - xlOpenXMLWorkbook (without macro's in 2007)
# 52 = .xlsm - xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007)
# 56 = .xls - xlExcel8 (97-2003 format in Excel 2007)
+ # (see Excel Help, VB reference, Enumerations, xlFileType)
+
+ # xls.changed = 0: no changes: just close;
+ # 1: existing file with changes: save, close.
+ # 2: new file with data added: save, close
+ # 3: new file, no added added (empty): close & delete on disk
- unwind_protect
- xls.app.Application.DisplayAlerts = 0;
- if (xls.changed > 0)
+ xls.app.Application.DisplayAlerts = 0;
+ try
+ if (xls.changed > 0 && xls.changed < 3)
if (xls.changed == 2)
# Probably a newly created, or renamed, Excel file
printf ("Saving file %s ...\n", xls.filename);
xls.workbook.SaveAs (canonicalize_file_name (xls.filename));
elseif (xls.changed == 1)
- # Just updated exiting Excel file
+ # Just updated existing Excel file
xls.workbook.Save ();
endif
+ xls.changed = 0;
xls.workbook.Close (canonicalize_file_name (xls.filename));
endif
- unwind_protect_cleanup
xls.app.Quit ();
delete (xls.workbook); # This statement actually closes the workbook
delete (xls.app); # This statement actually closes down Excel
- end_unwind_protect
+ catch
+ xls.app.Application.DisplayAlerts = 1;
+ end_try_catch
elseif (strcmp (xls.xtype, 'POI'))
- if (xls.changed > 0)
- if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
- xlsout = java_new ("java.io.FileOutputStream", xls.filename);
- xls.workbook.write (xlsout);
- xlsout.close ();
+ if (xls.changed > 0 && xls.changed < 3)
+ try
+ xlsout = java_new ("java.io.FileOutputStream", xls.filename);
+ if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
+ xls.workbook.write (xlsout);
+ xlsout.close ();
+ xls.changed = 0;
+ catch
+# xlsout.close ();
+ end_try_catch
endif
elseif (strcmp (xls.xtype, 'JXL'))
- if (xls.changed > 0)
- if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
- (xls.workbook).write ();
+ if (xls.changed > 0 && xls.changed < 3)
+ try
+ if (xls.changed == 2) printf ("Saving file %s...\n", xls.filename); endif
+ xls.workbook.write ();
+ xls.workbook.close ();
+ if (xls.changed == 3)
+ # Upon entering write mode, JExcelAPI always makes a disk file
+ # Incomplete new files (no data added) had better be deleted.
+ xls.workbook.close ();
+ delete (xls.filename);
+ endif
+ xls.changed = 0;
+ catch
+ end_try_catch
endif
- (xls.workbook).close ();
-
+
# elseif <other interfaces here>
endif
- xls = [];
+ if (xls.changed)
+ warning (sprintf ("File %s could not be saved. Read-only or in use elsewhere?\nFile pointer preserved.", xls.filename));
+ else
+ xls = [];
+ endif
endfunction
Modified: trunk/octave-forge/main/io/inst/xlsopen.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsopen.m 2010-11-14 16:51:22 UTC (rev 7914)
+++ trunk/octave-forge/main/io/inst/xlsopen.m 2010-11-14 16:53:26 UTC (rev 7915)
@@ -1,4 +1,4 @@
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -33,10 +33,11 @@
## following jars in your javaclasspath: poi-ooxml-schemas-3.5.jar,
## xbean.jar and dom4j-1.6.1.jar (or later versions).
##
-## @var{filename} should be a valid .xls or xlsx Excel file name; but if you use the
-## COM interface you can specify any extension that your installed Excel version
-## can read AND write. If @var{filename} does not contain any directory path,
-## the file is saved in the current directory.
+## @var{filename} should be a valid .xls or xlsx Excel file name (including
+## extension); but if you use the COM interface you can specify any extension
+## that your installed Excel version can read AND write, using the Java
+## interfaces only .xls or .xlsx are allowed. If @var{filename} does not
+## contain any directory path, the file is saved in the current directory.
##
## If @var{readwrite} is set to 0 (default value) or omitted, the Excel file
## is opened for reading. If @var{readwrite} is set to True or 1, an Excel
@@ -76,154 +77,211 @@
## should be []
## 2010-08-25 Improved help text
## 2010-09-27 Improved POI help message for unrecognized .xls format to hint for BIFF5/JXL
+## 2010-10-20 Improved code for tracking changes to new/existing files
+## " Lots of code cleanup, improved error checking and catching
+## " Implemented fallback to JXL if POI can't read a file.
+## 2010-10-30 More fine-grained file existence/writable checks
+## 2010-11-01 Added <COM>.Application.DisplayAlerts=0 in COM section to avoid Excel pop-ups
+## 2010-11-05 Option for multiple requested interface types (cell array)
+## " Bug fix: JXL fallback from POI for BIFF5 is only useful for reading
+## 2010-11-05 Slight change to reporting to screen
+## 2010-11-08 Tested with POI 3.7 (OK)
+## 2010-11-10 Texinfo header updated
##
-## 2010-09-27 Latest subfunction update
+## 2010-11-05 Latest subfunction update
function [ xls ] = xlsopen (filename, xwrite=0, reqinterface=[])
persistent xlsinterfaces; persistent chkintf;
+ # xlsinterfaces.<intf> = [] (not yet checked), 0 (found to be unsupported) or 1 (OK)
if (isempty (chkintf))
xlsinterfaces = struct ( "COM", [], "POI", [], "JXL", [] );
chkintf = 1;
endif
-
- if (nargout < 1) usage ("XLS = xlsopen (Xlfile, [Rw]). But no return argument specified!"); endif
+ if (nargout < 1)
+ usage ("XLS = xlsopen (Xlfile [, Rw] [, reqintf]). But no return argument specified!");
+ endif
+ if (~(islogical (xwrite) || isnumeric (xwrite)))
+ usage ("Numerical or logical value expected for arg # 2")
+ endif
if (~isempty (reqinterface))
- # Try to invoke requested interface for this call. Check if it
- # is supported anyway by emptying the corresponding var.
- if (strcmp (tolower (reqinterface), tolower ('COM')))
- printf ("Excel/COM interface requested... ");
- xlsinterfaces.COM = []; xlsinterfaces.POI = 0; xlsinterfaces.JXL = 0;
- elseif (strcmp (tolower (reqinterface), tolower ('POI')))
- printf ("Java/Apache POI interface requested... ");
- xlsinterfaces.COM = 0; xlsinterfaces.POI = []; xlsinterfaces.JXL = 0;
- elseif (strcmp (tolower (reqinterface), tolower ('JXL')))
- printf ("Java/JExcelAPI interface requested... ");
- xlsinterfaces.COM = 0; xlsinterfaces.POI = 0; xlsinterfaces.JXL = [];
- else
- usage (sprintf ("Unknown .xls interface \"%s\" requested. Only COM, POI or JXL supported", reqinterface));
- endif
+ if ~(ischar (reqinterface) || iscell(reqinterface)), usage ("Arg # 3 not recognized"); endif
+ # Turn arg3 into cell array if needed
+ if (~iscell (reqinterface)), reqinterface = {reqinterface}; endif
+ xlsinterfaces.COM = 0; xlsinterfaces.POI = 0; xlsinterfaces.JXL = 0;
+ for ii=1:numel (reqinterface)
+ reqintf = toupper (reqinterface {ii});
+ # Try to invoke requested interface(s) for this call. Check if it
+ # is supported anyway by emptying the corresponding var.
+ if (strcmp (reqintf, 'COM'))
+ xlsinterfaces.COM = [];
+ elseif (strcmp (reqintf, 'POI'))
+ xlsinterfaces.POI = [];
+ elseif (strcmp (reqintf, 'JXL'))
+ xlsinterfaces.JXL = [];
+ else
+ usage (sprintf ("Unknown .xls interface \"%s\" requested. Only COM, POI or JXL supported", reqinterface{}));
+ endif
+ endfor
+ printf ("Checking interface(s):\n");
xlsinterfaces = getxlsinterfaces (xlsinterfaces);
-
- # Well, is the requested interface supported on the system?
- if (~xlsinterfaces.(toupper (reqinterface)))
- # No it aint
- error (" ...but that's not supported!");
- endif
+ # Well, is/are the requested interface(s) supported on the system?
+ # FIXME check for multiple interfaces
+ for ii=1:numel (reqinterface)
+ if (~xlsinterfaces.(toupper (reqinterface{ii})))
+ # No it aint
+ error ("%s is not supported!", reqinterface{ii});
+ endif
+ endfor
endif
# Var xwrite is really used to avoid creating files when wanting to read, or
# not finding not-yet-existing files when wanting to write.
- if (xwrite) xwrite = 1; endif # Be sure it's either 0 or 1 initially
-
- # Check if Excel file exists
- fid = fopen (filename, 'rb');
- if (fid < 0)
- if (~xwrite)
- err_str = sprintf ("File %s not found\n", filename);
- error (err_str)
- else
- printf ("Creating file %s\n", filename);
- xwrite = 2;
+ # Check if Excel file exists. Adapt file open mode for readwrite argument
+ if (xwrite), fmode = 'r+b'; else fmode = 'rb'; endif
+ fid = fopen (filename, fmode);
+ if (fid < 0) # File doesn't exist...
+ if (~xwrite) # ...which obviously is fatal for reading...
+ error ( sprintf ("File %s not found\n", filename));
+ else # ...but for writing, we need more info:
+ fid = fopen (filename, 'rb'); # Check if it exists at all...
+ if (fid < 0) # File didn't exist yet. Simply create it
+ printf ("Creating file %s\n", filename);
+ xwrite = 3;
+ else # File exists, but is not writable => Error
+ fclose (fid); # Do not forget to close the handle neatly
+ error (sprintf ("Write mode requested but file %s is not writable\n", filename))
+ endif
endif
else
- # close file anyway to avoid COM or Java errors
+ # Close file anyway to avoid COM or Java errors
fclose (fid);
endif
# Check for the various Excel interfaces. No problem if they've already
# been checked, getxlsinterfaces (far below) just returns immediately then.
-
xlsinterfaces = getxlsinterfaces (xlsinterfaces);
# Supported interfaces determined; Excel file type check moved to seperate interfaces.
chk1 = strcmp (tolower (filename(end-3:end)), '.xls');
chk2 = strcmp (tolower (filename(end-4:end-1)), '.xls');
+ # Initialize file ptr struct
xls = struct ("xtype", 'NONE', "app", [], "filename", [], "workbook", [], "changed", 0, "limits", []);
-
+
+ # Keep track of which interface is selected
+ xlssupport = 0;
+
# Interface preference order is defined below: currently COM -> POI -> JXL
-
- if (xlsinterfaces.COM)
+ if (xlsinterfaces.COM && ~xlssupport)
# Excel functioning has been tested above & file exists, so we just invoke it
- xls.xtype = 'COM';
app = actxserver ("Excel.Application");
- xls.app = app;
- if (xwrite < 2)
- # Open workbook
- wb = app.Workbooks.Open (canonicalize_file_name (filename));
- elseif (xwrite == 2)
- # Create a new workbook
- wb = app.Workbooks.Add ();
- endif
- xls.workbook = wb;
- xls.filename = filename;
+ try # Because Excel itself can still crash on file formats etc.
+ app.Application.DisplayAlerts = 0;
+ if (xwrite < 2)
+ # Open workbook
+ wb = app.Workbooks.Open (canonicalize_file_name (filename));
+ elseif (xwrite > 2)
+ # Create a new workbook
+ wb = app.Workbooks.Add ();
+ ### Uncommenting the below statement can be useful in multi-user environments.
+ ### Be sure to uncomment correspondig stanza in xlsclose to avoid zombie Excels
+ # wb.SaveAs (canonicalize_file_name (filename))
+ endif
+ xls.app = app;
+ xls.xtype = 'COM';
+ xls.workbook = wb;
+ xls.filename = filename;
+ xlssupport += 1;
+ catch
+ warning ( sprintf ("ActiveX error trying to open or create file %s\n", filename));
+ app.Application.DisplayAlerts = 1;
+ app.Quit ();
+ delete (app);
+ end_try_catch
+ endif
- elseif (xlsinterfaces.POI)
+ if (xlsinterfaces.POI && ~xlssupport)
if ~(chk1 || chk2)
error ("Unsupported file format for xls2oct / Apache POI.")
endif
- xls.xtype = 'POI';
# Get handle to workbook
- if (xwrite == 2)
- if (chk1)
- wb = java_new ('org.apache.poi.hssf.usermodel.HSSFWorkbook');
- elseif (chk2)
- wb = java_new ('org.apache.poi.xssf.usermodel.XSSFWorkbook');
+ try
+ if (xwrite > 2)
+ if (chk1)
+ wb = java_new ('org.apache.poi.hssf.usermodel.HSSFWorkbook');
+ elseif (chk2)
+ wb = java_new ('org.apache.poi.xssf.usermodel.XSSFWorkbook');
+ else
+ # Nothing; we let the user encounter the full java error text
+ endif
+ xls.app = 'new_POI';
else
- # Nothing; we let the user encounter the full java error text
- endif
- xls.app = 'new_POI';
- else
- try
xlsin = java_new ('java.io.FileInputStream', filename);
wb = java_invoke ('org.apache.poi.ss.usermodel.WorkbookFactory', 'create', xlsin);
xls.app = xlsin;
- catch
- error ("File format not supported. Hint: perhaps it's (Excel 95) - try JXL");
- end_try_catch
- endif
- xls.workbook = wb;
- xls.filename = filename;
+ endif
+ xls.xtype = 'POI';
+ xls.workbook = wb;
+ xls.filename = filename;
+ xlssupport += 2;
+ catch
+ clear xlsin;
+ if (xlsinterfaces.JXL)
+ printf ('Couldn''t open file %s using POI; trying Excel''95 format with JXL...\n', filename);
+ endif
+ end_try_catch
+ endif
- elseif (xlsinterfaces.JXL)
+ if (xlsinterfaces.JXL && ~xlssupport)
if (~chk1)
error ("Currently xls2oct / JXL can only read reliably from .xls files")
endif
- xls.xtype = 'JXL';
- xlsin = java_new ('java.io.File', filename);
- if (xwrite == 2)
- # Get handle to new xls-file
- wb = java_invoke ('jxl.Workbook', 'createWorkbook', xlsin);
+ try
+ xlsin = java_new ('java.io.File', filename);
+ if (xwrite > 2)
+ # Get handle to new xls-file
+ wb = java_invoke ('jxl.Workbook', 'createWorkbook', xlsin);
+ else
+ # Open existing file
+ wb = java_invoke ('jxl.Workbook', 'getWorkbook', xlsin);
+ endif
+ xls.xtype = 'JXL';
+ xls.app = xlsin;
+ xls.workbook = wb;
+ xls.filename = filename;
+ xlssupport += 4;
+ catch
+ clear xlsin;
+ if (xlsinterfaces.POI)
+ printf ('... No luck with JXL, unsupported file format.\n', filename);
+ endif
+ end_try_catch
+ endif
+
+ # if
+ # ---- other interfaces
+ # endif
+
+ if (~xlssupport)
+ if (isempty (reqinterface))
+ warning ("No support for Excel .xls I/O");
else
- # Open existing file
- wb = java_invoke ('jxl.Workbook', 'getWorkbook', xlsin);
+ warning ("File type not supported by %s %s %s %s", reqinterface{:});
endif
- xls.app = xlsin;
- xls.workbook = wb;
- xls.filename = filename;
-
- # elseif ---- other interfaces
-
+ xls = [];
else
- warning ("No support for Excel .xls I/O");
- xls = [];
- endif
-
- if (~isempty (xls))
- # From here on xwrite is tracked via xls struct in the various lower
+ # From here on xwrite is tracked via xls.changed in the various lower
# level r/w routines and it is only used to determine if an informative
# message is to be given when saving a newly created xls file.
-
xls.changed = xwrite;
# Until something was written to existing files we keep status "unchanged".
- # xls.changed = 0 (existing/only read from), 1 (existing/data added), 2 (new).
-
+ # xls.changed = 0 (existing/only read from), 1 (existing/data added), 2 (new,
+ # data added) or 3 (pristine, no data added).
if (xls.changed == 1) xls.changed = 0; endif
-
endif
# Rounding up. If none of the xlsinterfaces is supported we're out of luck.
@@ -236,7 +294,7 @@
endfunction
-## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
+## Copyright (C) 2009,2010 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
@@ -277,18 +335,20 @@
## 2009-12-27 Make sure proper dimensions are checked in parsed javaclasspath
## 2010-09-11 Rearranged code and clarified messages about missing classes
## 2010-09-27 More code cleanup
+## 2010-10-20 Added check for minimum Java version (should be >= 6 / 1.6)
+## 2010-11-05 Slight change to reporting to screen
function [xlsinterfaces] = getxlsinterfaces (xlsinterfaces)
+ persistent tmp1 = []; persistent jcp; # Java class path
+
if (isempty (xlsinterfaces.COM) && isempty (xlsinterfaces.POI) && isempty (xlsinterfaces.JXL))
- chk1 = 1;
- printf ("Supported interfaces: ");
- else
- chk1= 0;
+ printf ("Looking for supported interfaces:\n");
endif
# Check if MS-Excel COM ActiveX server runs
if (isempty (xlsinterfaces.COM))
+ printf ("Excel/COM... ");
xlsinterfaces.COM = 0;
try
app = actxserver ("Excel.application");
@@ -296,84 +356,97 @@
xlsinterfaces.COM = 1;
# Close Excel. Yep this is inefficient when we need only one r/w action,
# but it quickly pays off when we need to do more with the same file
- # (+, MS-Excel code is in OS cache after this call anyway so no big deal)
+ # (+, MS-Excel code is in OS cache anyway after this call so no big deal)
app.Quit();
delete(app);
- printf ("Excel (COM) OK. ");
- chk1 = 1;
+ printf ("OK.\n");
catch
# COM non-existent
+ printf ("not working.\n");
end_try_catch
endif
- try
- tmp1 = javaclasspath;
- # If we get here, at least Java works. Now check for proper entries
- # in class path. Under *nix the classpath must first be split up
- if (isunix) tmp1 = strsplit (char (tmp1), ":"); endif
- catch
- # No Java support found
- xlsinterfaces.POI = 0;
- xlsinterfaces.JXL = 0;
- if ~(isempty (xlsinterfaces.POI) && isempty (xlsinterfaces.JXL))
- # Some Java-based interface requested but Java support is absent
- error ('No Java support found.');
- else
- # No specific Java-based interface requested. Just return
- return;
- endif
- end_try_catch
+ if (isempty (tmp1))
+ # Check Java support. First try javaclasspath
+ try
+ jcp = javaclasspath;
+ # If we get here, at least Java works. Now check for proper version (>= 1.6)
+ jver = char (java_invoke ('java.lang.System', 'getProperty', 'java.version'));
+ cjver = strsplit (jver, '.');
+ if (sscanf (cjver{2}, '%d') < 6)
+ warning ("Java version too old - you need at least Java 6 (v. 1.6.x.x)\n");
+ return
+ endif
+ # Now check for proper entries in class path. Under *nix the classpath
+ # must first be split up
+ if (isunix) jcp = strsplit (char (jcp), ":"); endif
+ tmp1 = 1;
+ catch
+ # No Java support found
+ xlsinterfaces.POI = 0;
+ xlsinter...
[truncated message content] |
