Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[6759] trunk/octave-forge/main/io/inst
|
From: <prn...@us...> - 2010-01-12 20:47:03
|
Revision: 6759
http://octave.svn.sourceforge.net/octave/?rev=6759&view=rev
Author: prnienhuis
Date: 2010-01-12 20:46:49 +0000 (Tue, 12 Jan 2010)
Log Message:
-----------
Bugfixes (notably parsing mixed cell arrays before writing); added unwind_protect in xlsread/xlswrite to better get rid of hidden Excel invocations in case of COM errors (and bugs 8-() ); improved Matlab compatibility: spreadsheet cells corresponding to empty array cells are cleared now rather than skipped when writing.
Modified Paths:
--------------
trunk/octave-forge/main/io/inst/oct2xls.m
trunk/octave-forge/main/io/inst/xls2oct.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-01-12 16:39:43 UTC (rev 6758)
+++ trunk/octave-forge/main/io/inst/oct2xls.m 2010-01-12 20:46:49 UTC (rev 6759)
@@ -148,7 +148,10 @@
## Author: Philip Nienhuis
## Rewritten: 2009-09-26
-## Last updated 2009-12-11
+## Updates:
+## 2009-12-11
+## 2010-01-12 Fixed typearr sorting out (was only 1-dim & braces rather than parens))
+## Set cells corresponding to empty array cells empty (cf. Matlab)
function [ xls, status ] = oct2com2xls (obj, xls, wsh, top_left_cell='A1')
@@ -186,20 +189,22 @@
# Cleanup NaNs. Start with backing up strings, empty & boolean cells,
# then set text cells to 0
+ obj
obj2 = cell (size (obj));
txtptr = cellfun ('isclass', obj, 'char');
- if (any(txtptr)) obj2{txtptr} = obj{txtptr}; obj{txtptr} = 0; endif
+ if (any (any (txtptr))) obj2(txtptr) = obj(txtptr); obj(txtptr) = 0; endif
eptr = cellfun ('isempty', obj);
- if (any (eptr)) obj{eptr} = 0; endif
+ if (any (any (eptr))) obj(eptr) = 0; endif
lptr = cellfun ("islogical" , obj);
- if (any (lptr)) obj2{lptr} = obj{lptr}; obj{lptr} = 0; endif
+ if (any (any (lptr))) obj2(lptr) = obj(lptr); obj(lptr) = 0; endif
ptr = cellfun ("isnan", obj);
- if (any (ptr)) obj{ptr} = []; endif
+ if (any (any (ptr))) obj{ptr} = []; endif
- # Restore text & booleans
- if (any (txtptr)) obj{txtptr} = obj2{txtptr}; endif
- if (any (lptr)) obj{lptr} = obj2{lptr}; endif
+ # Restore text, empty cells & booleans
+ if (any (any (txtptr))) obj(txtptr) = obj2(txtptr); endif
+ if (any (any (lptr))) obj(lptr) = obj2(lptr); endif
+ if (any (any (eptr))) obj(eptr) = {[]}; endif
clear obj2 txtptr eptr lptr ptr;
if (xls.changed < 2)
@@ -310,6 +315,7 @@
#====================================================================================
+
## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
##
## This program is free software; you can redistribute it and/or modify
@@ -351,7 +357,9 @@
## Author: Philip Nienhuis
## Created: 2009-11-26
-## Last updated 2010-01-03
+## Updates:
+## 2010-01-03 Bugfixes
+## 2010-01-12 Added xls.changed = 1 statement to signal successful write
function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, topleftcell="A1")
@@ -462,7 +470,8 @@
endif
endfor
endfor
-
+
+ xls.changed = 1;
rstatus = 1;
endfunction
@@ -510,7 +519,10 @@
## Author: Philip Nienhuis
## Created: 2009-12-04
-## Last updated 2009-12-11
+## Updates:
+## 2009-12-11
+## 2010-01-12 Fixed skipping empty array values (now Excel-conformant => cell cleared)
+## Added xls.changed = 1 statement to signal successful write
function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, topleftcell="A1")
@@ -619,12 +631,16 @@
case 3 # String
tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
sh.addCell (tmp);
+ case {4, 5}
+ tmp = java_new ('jxl.write.Blank', kk, ll);
+ sh.addCell (tmp);
otherwise
# Just skip
endswitch
endfor
endfor
+ xls.changed = 1;
rstatus = 1;
endfunction
Modified: trunk/octave-forge/main/io/inst/xls2oct.m
===================================================================
--- trunk/octave-forge/main/io/inst/xls2oct.m 2010-01-12 16:39:43 UTC (rev 6758)
+++ trunk/octave-forge/main/io/inst/xls2oct.m 2010-01-12 20:46:49 UTC (rev 6759)
@@ -60,6 +60,10 @@
## 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}.
+## When using COM or POI interface, formulas in cells are evaluated; if
+## that fails cached values are retrieved. Those 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
@@ -240,6 +244,7 @@
#==================================================================================
+
## Copyright (C) 2009 Philip Nienhuis <prnienhuis at users.sf.net>
##
## This program is free software; you can redistribute it and/or modify
@@ -279,7 +284,7 @@
## Author: Philip Nienhuis
## Created: 2009-11-23
-## Last updated 2009-12-11
+## Last updated 2010-01-11 - fall back to cached values when formula evaluator fails
function [ rawarr, xls, status ] = xls2jpoi2oct (xls, wsh, cellrange=[])
@@ -324,7 +329,7 @@
lastrow = sh.getLastRowNum ();
if (isempty (cellrange))
# Get used range by searching (slow...). Beware, it can be bit unreliable
- lcol = 65535; # FIXME for OOXML
+ lcol = 65535;
rcol = 0;
for ii=firstrow:lastrow
irow = sh.getRow (ii);
@@ -351,11 +356,8 @@
endif
# Create formula evaluator (needed to infer proper cell type into rawarr)
- # NB formula evaluation is not very reliable in POI
frm_eval = wb.getCreationHelper().createFormulaEvaluator ();
- #wb.clearAllCachedResultsValues(); # does not work
-
# Read contents into rawarr
rawarr = cell (nrows, ncols); # create placeholder
for ii = firstrow:lastrow
@@ -366,12 +368,13 @@
for jj = max (scol, lcol) : min (lcol+ncols-1, ecol)
cell = irow.getCell (jj);
if ~isempty (cell)
- # Process cell contents
+ # Explore cell contents
type_of_cell = cell.getCellType ();
if (type_of_cell == ctype(3)) # Formula
- try
+ try # Because not al Excel formulas have been implemented
cell = frm_eval.evaluate (cell);
type_of_cell = cell.getCellType();
+ # Separate switch because form.eval. yields different type
switch type_of_cell
case ctype (1) # Numeric
rawarr (ii+1-firstrow, jj+1-lcol) = cell.getNumberValue ();
@@ -382,18 +385,16 @@
otherwise
# Nothing to do here
endswitch
+ # Set cell type to blank to skip switch below
+ type_of_cell = ctype(4);
catch
- # In case of errors we copy the formula as text into rawarr
- rawarr (ii+1-firstrow, jj+1-lcol) = ["=" cell.getCellFormula];
- type_of_cell = ctype (4);
- if (~jerror)
- warning ("Java errors in worksheet formulas (converted to string)");
- endif
- ++jerror; # We only need one warning
+ # In case of formula errors we take the cached results
+ type_of_cell = cell.getCachedFormulaResultType ();
+ ++jerror; # We only need one warning even for multiple errors
end_try_catch
- else
- if (~isnumeric (type_of_cell)) type_of_cell = 4; endif
- switch type_of_cell
+ 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) = cell.getNumericCellValue ();
case ctype(2) # 1 String
@@ -403,15 +404,14 @@
case ctype(5) # 4 Boolean
rawarr (ii+1-firstrow, jj+1-lcol) = cell.getBooleanCellValue ();
otherwise # 5 Error
- # Formula (treated above) or error; ignore
- endswitch
- endif
+ # Ignore
+ endswitch
endif
endfor
endif
endfor
- if (jerror > 0) printf ("%d Java formula evalation errors\n", jerror); endif
+ if (jerror > 0) warning (sprintf ("oct2xls: %d cached values instead of formula evaluations.\n", jerror)); endif
# Crop rawarr from empty outer rows & columns like Excel does
emptr = cellfun('isempty', rawarr);
@@ -595,4 +595,3 @@
xls.limits = [lcol+icoll, lcol+icolr; firstrow+irowt, firstrow+irowb];
endfunction
-
Modified: trunk/octave-forge/main/io/inst/xlsread.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlsread.m 2010-01-12 16:39:43 UTC (rev 6758)
+++ trunk/octave-forge/main/io/inst/xlsread.m 2010-01-12 20:46:49 UTC (rev 6759)
@@ -115,67 +115,73 @@
## Author: Philip Nienhuis
## Created: 2009-10-16
-## Latest update: 2009-12-29
+## Updates:
+## 2009-12-29 bug fixes
+## 2010-01-12 added unwind_protect to get rid of stray Excel invocations i.c.o. COM errors
function [ numarr, txtarr, rawarr, lims ] = xlsread (fn, wsh, datrange, reqintf=[])
-rstatus = 0;
+ rstatus = 0;
-if (nargin < 1)
- error ("xlsread: no arguments specified")
- numarr = []; txtarr={}; rawarr = {};
- return
-elseif (nargin == 1)
- wsh = 1;
- datrange = '';
-elseif (nargin == 2)
- # Find out whether 2nd argument = worksheet or range
- if (isnumeric (wsh) || (isempty (findstr(wsh,':')) && ~isempty (wsh)))
- # Apparently a worksheet specified
- datrange = '';
- else
- # Range specified
- datrange = wsh;
+ if (nargin < 1)
+ error ("xlsread: no arguments specified")
+ numarr = []; txtarr={}; rawarr = {};
+ return
+ elseif (nargin == 1)
wsh = 1;
+ datrange = '';
+ elseif (nargin == 2)
+ # Find out whether 2nd argument = worksheet or range
+ if (isnumeric (wsh) || (isempty (findstr(wsh,':')) && ~isempty (wsh)))
+ # Apparently a worksheet specified
+ datrange = '';
+ else
+ # Range specified
+ datrange = wsh;
+ wsh = 1;
+ endif
endif
-endif
-# A small gesture for Matlab compatibility. JExcelAPI supports BIFF5.
-if (~isempty (reqintf) && strcmp (toupper(reqintf), 'BASIC'))
- reqintf= "JXL";
- printf ("BASIC (BIFF5) support request translated to JXL. \n");
-endif
+ # A small gesture for Matlab compatibility. JExcelAPI supports BIFF5.
+ if (~isempty (reqintf) && strcmp (toupper(reqintf), 'BASIC'))
+ reqintf= "JXL";
+ printf ("BASIC (BIFF5) support request translated to JXL. \n");
+ endif
-# Checks done. Get raw data into cell array "rawarr". xlsopen finds out
-# what interface to use. If none found, suggest csv
+ # Checks done. Get raw data into cell array "rawarr". xlsopen finds out
+ # what interface to use. If none found, suggest csv
-# Get pointer array to Excel file
-xls = xlsopen (fn, 0, reqintf);
+ unwind_protect # Needed to catch COM errors & able to close stray Excel invocations
+ # Get pointer array to Excel file
+ xls = xlsopen (fn, 0, reqintf);
-if (strcmp (xls.xtype, 'COM') || strcmp (xls.xtype, 'POI') || strcmp (xls.xtype, 'JXL'))
+ if (strcmp (xls.xtype, 'COM') || strcmp (xls.xtype, 'POI') || strcmp (xls.xtype, 'JXL'))
- # Get data from Excel file & return handle
- [rawarr, xls, rstatus] = xls2oct (xls, wsh, datrange);
+ # Get data from Excel file & return handle
+ [rawarr, xls, rstatus] = xls2oct (xls, wsh, datrange);
- # Save some results before xls is wiped
- rawlimits = xls.limits;
- xtype = xls.xtype;
-
- # Close Excel file
- xls = xlsclose (xls);
+ # Save some results before xls is wiped
+ rawlimits = xls.limits;
+ xtype = xls.xtype;
- if (rstatus)
- [numarr, txtarr, lims] = parsecell (rawarr, rawlimits);
- # Wipe lims if using Excel (that doesn't return reliable rawlimits).
- # The user can get the limits relative to the rawarr by parsecell (rawarr).
- if (strcmp (xtype, 'COM')) lims = []; endif
+ if (rstatus)
+ [numarr, txtarr, lims] = parsecell (rawarr, rawlimits);
+ # Wipe lims if using Excel (that doesn't return reliable rawlimits).
+ # The user can get the limits relative to the rawarr by parsecell (rawarr).
+ if (strcmp (xtype, 'COM')) lims = []; endif
+ else
+ rawarr = {}; numarr = []; txtarr = {};
+ endif
+
else
- rawarr = {}; numarr = []; txtarr = {};
+ printf ("\n Error XLSREAD: reading EXCEL .xls file (BIFF-Format) isn\'t supported on this system.\n You need to convert the file into a tab- or comma delimited text file or .csv file\n and then invoke dlmread()\n\n");
+
endif
-else
- printf ("\n Error XLSREAD: reading EXCEL .xls file (BIFF-Format) isn\'t supported on this system.\n You need to convert the file into a tab- or comma delimited text file or .csv file\n and then invoke dlmread()\n\n");
+ unwind_protect_cleanup
+ # Close Excel file
+ xls = xlsclose (xls);
-endif
+ end_unwind_protect
endfunction
Modified: trunk/octave-forge/main/io/inst/xlswrite.m
===================================================================
--- trunk/octave-forge/main/io/inst/xlswrite.m 2010-01-12 16:39:43 UTC (rev 6758)
+++ trunk/octave-forge/main/io/inst/xlswrite.m 2010-01-12 20:46:49 UTC (rev 6759)
@@ -89,7 +89,9 @@
## Author: Philip Nienhuis
## Created: 2009-10-16
-## Latest update: 2010-01-04 (Adapted range capacity checks to OOXML)
+## Updates:
+## 2010-01-04 (Adapted range capacity checks to OOXML)
+## 2010-01-12 (Bug fix; added unwind_protect to xlsopen...xlsclose calls)
function [ rstatus ] = xlswrite (filename, arr, arg3, arg4, arg5)
@@ -105,7 +107,7 @@
elseif (nargin == 2)
# Assume first worksheet and full worksheet starting at A1
wsh = 1;
- if (strcmp (tolower (filename(end-4:end-1)), 'xls')
+ if (strcmp (tolower (filename(end-4:end-1)), 'xls'))
range = "A1:XFD1048576"; # OOXML has ridiculously large limits
else
range = "A1:IV65536"; # Regular xls limits
@@ -144,10 +146,14 @@
nrows, ncols, range);
endif
+ unwind_protect # Needed to besure Excel can be closed i.c.o. errors
xls = xlsopen (filename, 1, reqintf);
[xls, rstatus] = oct2xls (arr(1:nr, 1:nc), xls, wsh, topleft);
+ unwind_protect_cleanup
xls = xlsclose (xls);
+ end_unwind_protect
+
endfunction
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
