Menu
▾
▴
[Octave-cvsupdate] SF.net SVN: octave:[7493] trunk/octave-forge/main/io/inst
|
From: <prn...@us...> - 2010-08-03 21:25:03
|
Revision: 7493
http://octave.svn.sourceforge.net/octave/?rev=7493&view=rev
Author: prnienhuis
Date: 2010-08-03 21:24:56 +0000 (Tue, 03 Aug 2010)
Log Message:
-----------
Added support for entering formulas, either as text or as literal text strings depending on a new option argument. Based on patch suggested by Benjamin Lindner
Modified Paths:
--------------
trunk/octave-forge/main/io/inst/oct2ods.m
trunk/octave-forge/main/io/inst/oct2xls.m
Modified: trunk/octave-forge/main/io/inst/oct2ods.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2ods.m 2010-07-30 21:20:49 UTC (rev 7492)
+++ trunk/octave-forge/main/io/inst/oct2ods.m 2010-08-03 21:24:56 UTC (rev 7493)
@@ -18,6 +18,7 @@
## @deftypefn {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods})
## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh})
## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh}, @var{range})
+## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh}, @var{range}, @var(options))
##
## Transfer data to an OpenOffice_org Calc spreadsheet previously opened
## by odsopen().
@@ -43,14 +44,24 @@
## inserted to the right of all existing sheets. The pointer to the
## "active" sheet (shown when Calc opens the file) remains untouched.
##
-## If omitted, @var{range} is initially supposed to be 'A1:AMJ65536'.
-## The actual range to be used is determined by the size of @var{arr}.
+## If omitted, @var{range} is initially supposed to be 'A1:AMJ65536';
+## only a top left cell address can be specified as well. In these cases
+## the actual range to be used is determined by the size of @var{arr}.
## Be aware that data array sizes > 2.10^5 elements may exhaust the
## java shared memory space for the default java memory settings.
## For larger arrays appropriate memory settings are needed in the file
## java.opts; then the maximum array size for the java-based spreadsheet
## options is about 5-6.10^5 elements.
##
+## Optional argument @var{options}, a structure, can be used to specify
+## various write modes.
+## Currently the only option field is "formulas_as_text", which -if set
+## to 1 or TRUE- specifies that formula strings (i.e., text strings
+## starting with "=" and ending in a ")" ) should be entered as litteral
+## text strings rather than as spreadsheet formulas (the latter is the
+## default). As jOpenDocument doesn't support formula I/O at all yet,
+## this option is ignored for the JOD interface.
+##
## Data are added to the sheet, ignoring other data already present;
## existing data in the range to be used will be overwritten.
##
@@ -62,9 +73,15 @@
## Examples:
##
## @example
-## [ods, status] = ods2oct ('arr', ods, 'Newsheet1', 'AA31:GH165');
+## [ods, status] = ods2oct (arr, ods, 'Newsheet1', 'AA31:GH165');
+## Write array arr into sheet Newsheet1 with upperleft cell at AA31
## @end example
##
+## @example
+## [ods, status] = ods2oct ({'String'}, ods, 'Oldsheet3', 'B15:B15');
+## Put a character string into cell B15 in sheet Oldsheet3
+## @end example
+##
## @seealso ods2oct, odsopen, odsclose, odsread, odswrite, odsfinfo
##
## @end deftypefn
@@ -78,18 +95,24 @@
## 2010-03-28 Added basic support for ofdom v.0.8. Everything works except adding cols/rows
## 2010-03-29 Removed odfdom-0.8 support, it's simply too buggy :-( Added a warning instead
## 2010-06-01 Almost complete support for upcoming jOpenDocument 1.2b4. 1.2b3 still lacks a bit
+## 2010-07-05 Added example for writng character strings
+## 2010-07-29 Added option for entering / reading back spreadsheet formulas
##
-## Last update of subfunctions below: 2010-06-01
+## Last update of subfunctions below: 2010-08-01
-function [ ods, rstatus ] = oct2ods (c_arr, ods, wsh=1, crange=[])
+function [ ods, rstatus ] = oct2ods (c_arr, ods, wsh=1, crange=[], spsh_opts=[])
+ if isempty (spsh_opts)
+ spsh_opts.formulas_as_text = 0;
+ # Other options here
+ endif
+
if (strcmp (ods.xtype, 'OTK'))
# Write ods file tru Java & ODF toolkit.
- [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh, crange);
+ [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh, crange, spsh_opts);
elseif (strcmp (ods.xtype, 'JOD'))
# Write ods file tru Java & jOpenDocument. API still leaves lots to be wished...
-# warning ("oct2ods: unreliable writing tru jOpenDocument interface.");
[ ods, rstatus ] = oct2jod2ods (c_arr, ods, wsh, crange);
# elseif ---- < Other interfaces here >
@@ -142,19 +165,23 @@
## 2010-04-11 Changed all references to "cell" to "scell" to avoid reserved keyword
## Small bugfix for cases with empty left columns (wrong cell reference)
## 2010-04-13 Fixed bug with stray cell copies beyond added data rectangle
+## 2010-07-29 Added formula input support (based on xls patch by Benjamin Lindner)
+## 2010-08-01 Added try-catch around formula input
+## " Changed range arg to also allow just topleft cell
+## 2010-08-03 Moved range checks and type array parsing to separate functions
-function [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh=1, crange=[])
+function [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh, crange, spsh_opts)
persistent ctype;
if (isempty (ctype))
- # Number, String, Boolean, Date, Time, Empty
- ctype = [1, 2, 3, 4, 5, 6];
+ # Number, Boolean, String, Formula, Empty, Date, Time
+ ctype = [1, 2, 3, 4, 5, 6, 7];
endif
- rstatus = 0; changed = 0;
+ rstatus = 0; changed = 0; f_errs = 0;
- # ODS' current row and column capacity
- ROW_CAP = 65536; COL_CAP = 1024;
+# # ODS' current row and column capacity
+# ROW_CAP = 65536; COL_CAP = 1024;
if (isnumeric (c_arr))
c_arr = num2cell (c_arr);
@@ -206,54 +233,22 @@
endif
# Check size of data array & range / capacity of worksheet & prepare vars
-
[nr, nc] = size (c_arr);
- if (isempty (crange))
- trow = 0;
- lcol = 0;
- nrows = nr;
- ncols = nc;
- else
- [dummy, nrows, ncols, trow, lcol] = parse_sp_range (crange);
- # Row/col = 0 based in ODFtoolkit
- trow = trow - 1; lcol = lcol - 1;
+ [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, ods.xtype, ods.filename);
+ --trow; --lcol; # Zero-based row # & col #
+ if (nrows < nr || ncols < nc)
+ warning ("Array truncated to fit in range");
+ obj = obj(1:nrows, 1:ncols);
endif
- if (trow > ROW_CAP || lcol > COL_CAP)
- celladd = calccelladdress (lcol, trow, 1, 1);
- error (sprintf ("Topleft cell (%s) beyond spreadsheet limits (AMJ65536).", celladd));
- endif
- # Check spreadsheet capacity beyond requested topleft cell
- nrows = min (nrows, ROW_CAP - trow + 1);
- ncols = min (ncols, COL_CAP - lcol + 1);
- # Check array size and requested range
- nrows = min (nrows, nr);
- ncols = min (ncols, nc);
- if (nrows < nr || ncols < nc) warning ("Array truncated to fit in range"); endif
# Parse data array, setup typarr and throw out NaNs to speed up writing;
-# 1Number, 2String, 3Boolean, 4Date, 5Time, 0Empty
+ typearr = spsh_prstype (c_arr, nrows, ncols, ctype, spsh_opts, 0);
+ if ~(spsh_opts.formulas_as_text)
+ # Find formulas (designated by a string starting with "=" and ending in ")")
+ fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1) && strncmp (x(end:end), ")", 1), c_arr);
+ typearr(fptr) = ctype(4); # FORMULA
+ endif
- typearr = ones (nrows, ncols); # type "NUMERIC", provisionally
- obj2 = cell (size (c_arr)); # Temporary storage for strings
-
- txtptr = cellfun ('isclass', c_arr, 'char'); # type "STRING" replaced by "NUMERIC"
- obj2(txtptr) = c_arr(txtptr); c_arr(txtptr) = 2;# Save strings in a safe place
-
- emptr = cellfun ("isempty", c_arr);
- c_arr(emptr) = 0; # Set empty cells to NUMERIC
-
- lptr = cellfun ("islogical" , c_arr); # Find logicals...
- obj2(lptr) = c_arr(lptr); # .. and set them to BOOLEAN
-
- ptr = cellfun ("isnan", c_arr); # Find NaNs & set to BLANK
- typearr(ptr) = 0; # All other cells are now numeric
-
- c_arr(txtptr) = obj2(txtptr); # Copy strings back into place
- c_arr(lptr) = obj2(lptr);
- typearr(txtptr) = 2; # ...and clean up
- typearr(emptr) = 0;
- typearr(lptr) = 3; # BOOLEAN
-
# Prepare worksheet for writing. If needed create new sheet
if (newsh)
@@ -486,18 +481,16 @@
scell.removeChild (tmp);
changed = 1;
endwhile
+ scell.removeAttribute ('table:formula');
endif
+
# Empty cell count stuff done. At last we can add the data
switch (typearr (ii, jj))
case 1 # float
scell.setOfficeValueTypeAttribute ('float');
scell.setOfficeValueAttribute (c_arr{ii, jj});
- case 2 # string
- scell.setOfficeValueTypeAttribute ('string');
- pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', c_arr{ii, jj});
- scell.appendChild (pe);
- case 3 # boolean
- # Beware, for unpatched-for-booleans java-1.2.7 we must resort to floats
+ case 2 # boolean
+ # Beware, for unpatched-for-booleans java-1.2.7- we must resort to floats
try
# First try the preferred java-boolean way
scell.setOfficeValueTypeAttribute ('boolean');
@@ -516,21 +509,42 @@
scell.setOfficeValueAttribute (0);
endif
end_try_catch
- case 4 # Date (implemented but Octave has no "date" data type - yet?)
+ case 3 # string
+ scell.setOfficeValueTypeAttribute ('string');
+ pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', c_arr{ii, jj});
+ scell.appendChild (pe);
+ case 4 # Formula.
+ # As we don't know the result type, simply remove previous type info.
+ # Once OOo Calc reads it, it'll add the missing attributes
+ scell.removeAttribute ('office:value');
+ scell.removeAttribute ('office:value-type');
+ # Try-catch not strictly needed, there's no formula validator yet
+ try
+ scell.setTableFormulaAttribute (c_arr{ii, jj});
+ scell.setOfficeValueTypeAttribute ('string');
+ pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', '#Recalc Formula#');
+ scell.appendChild (pe);
+ catch
+ ++f_errs;
+ scell.setOfficeValueTypeAttribute ('string');
+ pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', c_arr{ii, jj});
+ scell.appendChild (pe);
+ end_try_catch
+ case {0 5} # Empty. Clear value attributes
+ if (~newsh)
+ scell.removeAttribute ('office:value-type');
+ scell.removeAttribute ('office:value');
+ endif
+ case 6 # Date (implemented but Octave has no "date" data type - yet?)
scell.setOfficeValueTypeAttribute ('date');
[hh mo dd hh mi ss] = datevec (c_arr{ii,jj});
str = sprintf ("%4d-%2d-%2dT%2d:%2d:%2d", yy, mo, dd, hh, mi, ss);
scell.setOfficeDateValueAttribute (str);
- case 5 # Time (implemented but Octave has no "time" data type)
+ case 7 # Time (implemented but Octave has no "time" data type)
scell.setOfficeValueTypeAttribute ('time');
[hh mo dd hh mi ss] = datevec (c_arr{ii,jj});
str = sprintf ("PT%2d:%2d:%2d", hh, mi, ss);
scell.setOfficeTimeValuettribute (str);
- case 0 # Empty. Clear value attributes
- if (~newsh)
- scell.removeAttribute ('office:value-type');
- scell.removeAttribute ('office:value');
- endif
otherwise
# Nothing
endswitch
@@ -544,6 +558,9 @@
endfor
+ if (f_errs)
+ printf ("%d formula errors encountered - please check input array\n", f_errs);
+ endif
if (changed)
ods.changed = 1;
rstatus = 1;
@@ -588,8 +605,10 @@
## " Added check for jOpenDocument version. Adding sheets only works for
## 1.2b3+ (barring bug above)
## 2010-06-02 Fixed first sheet remaining in new spreadsheets
+## 2010-08-01 Added option for crange to be only topleft cell address
+## " Code cleanup
-function [ ods, rstatus ] = oct2jod2ods (c_arr, ods, wsh=1, crange=[])
+function [ ods, rstatus ] = oct2jod2ods (c_arr, ods, wsh, crange)
# Check jOpenDocument version
sh = ods.workbook.getSheet (0);
@@ -652,6 +671,12 @@
lcol = 0;
nrows = nr;
ncols = nc;
+ elseif (isempty (strfind (deblank (crange), ':')))
+ [dummy1, dummy2, dummy3, trow, lcol] = parse_sp_range (crange);
+ nrows = nr;
+ ncols = nc;
+ # Row/col = 0 based in jOpenDocument
+ trow = trow - 1; lcol = lcol - 1;
else
[dummy, nrows, ncols, trow, lcol] = parse_sp_range (crange);
# Row/col = 0 based in jOpenDocument
@@ -659,8 +684,7 @@
endif
if (trow > 65535 || lcol > 1023)
- celladd = calccelladdress (lcol + 1, trow + 1);
- error (sprintf ("Topleft cell (%s) beyond spreadsheet limits (AMJ65536).", celladd));
+ error ("Topleft cell beyond spreadsheet limits (AMJ65536).");
endif
# Check spreadsheet capacity beyond requested topleft cell
nrows = min (nrows, 65536 - trow); # Remember, lcol & trow are zero-based
Modified: trunk/octave-forge/main/io/inst/oct2xls.m
===================================================================
--- trunk/octave-forge/main/io/inst/oct2xls.m 2010-07-30 21:20:49 UTC (rev 7492)
+++ trunk/octave-forge/main/io/inst/oct2xls.m 2010-08-03 21:24:56 UTC (rev 7493)
@@ -17,10 +17,11 @@
## -*- texinfo -*-
## @deftypefn {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls})
## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh})
-## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{topleft})
+## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{range})
+## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{range}, @var{options})
##
-## Add data in 1D/2D CELL array @var{arr} into a range with upper left
-## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
+## Add data in 1D/2D CELL array @var{arr} into a range specified
+## in @var{topleft} in worksheet @var{wsh} in an Excel
## spreadsheet file pointed to in structure @var{xls}.
## Return argument @var{xls} equals supplied argument @var{xls} and is
## updated by oct2xls.
@@ -44,8 +45,9 @@
## inserted to the right of all existing worksheets. The pointer to the
## "active" sheet (shown when Excel opens the file) remains untouched.
##
-## If omitted, @var{topleft} is supposed to be 'A1'. The actual range to
-## be used is determined by the size of @var{arr}.
+## If @var{range} is omitted or if only a topleft cell address is specified,
+## the topleft cell of @var{range} is supposed to be 'A1' and the actual
+## range to be used is determined by the size of @var{arr}.
##
## Data are added to the worksheet, ignoring other data already present;
## existing data in the range to be used will be overwritten.
@@ -55,6 +57,14 @@
## will be written, other array cells corresponding to that cell will be
## ignored.
##
+## Optional argument @var{options}, a structure, can be used to specify
+## various write modes.
+## Currently the only option field is "formulas_as_text", which -if set
+## to 1 or TRUE- specifies that formula strings (i.e., text strings
+## starting with "=" and ending in a ")" ) should be entered as litteral
+## text strings rather than as spreadsheet formulas (the latter is the
+## default).
+##
## Beware that -if invoked- Excel invocations may be left running silently
## in case of COM errors. Invoke xlsclose with proper pointer struct to
## close them.
@@ -67,7 +77,7 @@
## Examples:
##
## @example
-## [xlso, status] = xls2oct ('arr', xlsi, 'Third_sheet', 'AA31');
+## [xlso, status] = xls2oct ('arr', xlsi, 'Third_sheet', 'AA31:AB278');
## @end example
##
## @seealso xls2oct, xlsopen, xlsclose, xlsread, xlswrite, xlsfinfo
@@ -79,26 +89,38 @@
## Updates:
## 2010-01-03 (OOXML support)
## 2010-03-14 Updated help text section on java memory usage
+## 2010-07-27 Added formula writing support (based on patch by Benjamin Lindner)
+## 2010-08-01 Added check on input array size vs. spreadsheet capacity
+## " Changed argument topleft into range (now compatible with ML); the
+## " old argument version (just topleft cell) is still recognized, though
-function [ xls, rstatus ] = oct2xls (obj, xls, wsh, topleft='A1')
+function [ xls, rstatus ] = oct2xls (obj, xls, wsh, crange=[], spsh_opts=[])
+ # Make sure input array is a cell array
if (isnumeric (obj))
obj = num2cell (obj);
elseif (ischar (obj))
obj = {obj};
endif
+ # Various options
+ if isempty (spsh_opts)
+ spsh_opts.formulas_as_text = 0;
+ # other options to be implemented here
+ endif
+
+ # Select interface to be used
if (strcmp (xls.xtype, 'COM'))
# Call oct2com2xls to do the work
- [xls, rstatus] = oct2com2xls (obj, xls, wsh, topleft);
+ [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, topleft);
+ [xls, rstatus] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts);
elseif (strcmp (xls.xtype, 'JXL'))
# Invoke Java and JExcelAPI
- [xls, rstatus] = oct2jxla2xls (obj, xls, wsh, topleft);
+ [xls, rstatus] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts);
# elseif (strcmp'xls.xtype, '<whatever>'))
# <Other Excel interfaces>
@@ -160,10 +182,15 @@
## 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)
## 2010-01-13 Removed an extraneous statement used for debugging
-## FIXMEs added (array size vs. spreadsheet size in java based subfunctions);
## I plan look at it when octave v.3.4 is about to arrive.
+## 2010-08-01 Added checks for input array size vs check on capacity
+## " Changed topleft arg into range arg (just topleft still recognized)
+## " Some code cleanup
+## " 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
-function [ xls, status ] = oct2com2xls (obj, xls, wsh, top_left_cell='A1')
+function [ xls, status ] = oct2com2xls (obj, xls, wsh, crange, spsh_opts)
# define some constants not yet in __COM__.cc
xlWorksheet = -4167; # xlChart= 4;
@@ -175,8 +202,8 @@
if (nargin < 2) error ("oct2com2xls needs a minimum of 2 arguments."); endif
if (nargin == 2) wsh = 1; endif
if (~iscell (obj)) error ("Cell array expected as input argument"); endif
- if (~strcmp (tolower (xls.filename(end-3:end)), '.xls'))
- error ("oct2com2xls can only write to Excel .xls files")
+ if (~strmatch (tolower (xls.filename(end-4:end)), '.xls'))
+ error ("oct2com2xls can only write to Excel .xls or .xlsx files")
endif
if (isnumeric (wsh))
if (wsh < 1) error ("Illegal worksheet number: %i\n", wsh); endif
@@ -188,6 +215,7 @@
rstatus = 1;
return;
endif
+ # Check xls file pointer struct
test1 = ~isfield (xls, "xtype");
test1 = test1 || ~isfield (xls, "workbook");
test1 = test1 || ~strcmp (char (xls.xtype), 'COM');
@@ -196,26 +224,30 @@
if test1
error ("Invalid file pointer struct");
endif
+
+ # Parse date ranges
+ [nr, nc] = size (obj);
+ [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
+ if (nrows < nr || ncols < nc)
+ warning ("Array truncated to fit in range");
+ obj = obj(1:nrows, 1:ncols);
+ endif
- # Cleanup NaNs. Start with backing up strings, empty & boolean cells,
- # then set text cells to 0
- obj2 = cell (size (obj));
- txtptr = cellfun ('isclass', obj, 'char');
- if (any (any (txtptr))) obj2(txtptr) = obj(txtptr); obj(txtptr) = 0; endif
- eptr = cellfun ('isempty', obj);
- if (any (any (eptr))) obj(eptr) = 0; endif
- lptr = cellfun ("islogical" , obj);
- if (any (any (lptr))) obj2(lptr) = obj(lptr); obj(lptr) = 0; endif
+ # Cleanup NaNs. Find where they are and mark as empty
+ ctype = [0 1 2 3 4]; # Numeric Boolean Text Formula Empty
+ 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);
- ptr = cellfun ("isnan", obj);
- if (any (any (ptr))) obj{ptr} = []; endif
+ if (spsh_opts.formulas_as_text)
+ # find formulas (designated by a string starting with "=" and ending in ")")
+ fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1) && strncmp (x(end:end), ")", 1), obj);
+ # ... and add leading "'" character
+ obj(fptr) = cellfun (@(x) ["'" x], obj(fptr), "Uniformoutput", false);
+ endif
+ clear fptr;
- # 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)
# Existing file. Some involved investigation is needed to preserve
# existing data that shouldn't be touched.
@@ -311,7 +343,7 @@
# MG's original part.
# Save object in Excel sheet, starting at cell top_left_cell
if (~isempty(obj))
- r = sh.Range (top_left_cell);
+ r = sh.Range (topleft);
r = r.Resize (size (obj, 1), size (obj, 2));
r.Value = obj;
delete (r);
@@ -344,7 +376,8 @@
## -*- texinfo -*-
## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls ( @var{arr}, @var{xlsi})
## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh})
-## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{topleft})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range}, @var{options})
##
## Add data in 1D/2D CELL array @var{arr} into a range with upper left
## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
@@ -372,22 +405,25 @@
## 2010-03-08 Dumped formula evaluator for booleans. Not being able to
## write booleans was due to a __java__.oct deficiency (see
## http://sourceforge.net/mailarchive/forum.php?thread_name=4B59A333.5060302%40net.in.tum.de&forum_name=octave-dev )
+## 2010-07-27 Added formula writing support (based on patch by Benjamin Lindner)
+## 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
-function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, topleftcell="A1")
+function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts)
persistent ctype;
if (isempty (ctype))
# Get cell types. Beware as they start at 0 not 1
- ctype(1) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_NUMERIC');
- ctype(2) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_STRING');
- ctype(3) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_FORMULA');
- ctype(4) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK');
- ctype(5) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BOOLEAN');
- ctype(6) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_ERROR');
+ ctype(1) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_NUMERIC'); # 0
+ ctype(2) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BOOLEAN'); # 4
+ ctype(3) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_STRING'); # 1
+ 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;
+ rstatus = 0; changed = 1; f_errs = 0;
# Preliminary sanity checks
if (isempty (obj))
@@ -399,9 +435,10 @@
endif
if (nargin < 2) error ("oct2jpoi2xls needs a minimum of 2 arguments."); endif
if (nargin == 2) wsh = 1; endif
- if (~strmatch(tolower(xls.filename(end-4:end)), '.xls'))
+ 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");
@@ -411,7 +448,7 @@
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();
+ nr_of_sheets = xls.workbook.getNumberOfSheets ();
if (isnumeric (wsh))
if (wsh > nr_of_sheets)
# Watch out as a sheet called Sheet%d can exist with a lower index...
@@ -426,10 +463,10 @@
else
sh = xls.workbook.getSheetAt (wsh - 1); # POI sheet count 0-based
endif
- printf ("(Writing to worksheet %s)\n", sh.getSheetName());
+ printf ("(Writing to worksheet %s)\n", sh.getSheetName ());
else
sh = xls.workbook.getSheet (wsh);
- if (isempty(sh))
+ if (isempty (sh))
# Sheet not found, just create it
sh = xls.workbook.createSheet (wsh);
xls.changed = 2;
@@ -438,53 +475,54 @@
# Beware of strings variables interpreted as char arrays; change them to cell.
if (ischar (obj)) obj = {obj}; endif
-
- [topleft, nrows, ncols, trow, lcol] = parse_sp_range (topleftcell);
- [nrows, ncols] = size (obj);
-
- ## FIXME Check on max spreadsheet size vs. max data array size needed
+ # Parse date ranges
+ [nr, nc] = size (obj);
+ [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
+ if (nrows < nr || ncols < nc)
+ warning ("Array truncated to fit in range");
+ obj = obj(1:nrows, 1:ncols);
+ endif
+
# Prepare type array
- typearr = ctype(4) * ones (nrows, ncols); # type "BLANK", provisionally
- obj2 = cell (size (obj)); # Temporary storage for strings
+ typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
+ if ~(spsh_opts.formulas_as_text)
+ # Remove leading '=' from formula strings
+ fptr = ~(2 * (ones (size (typearr))) .- typearr);
+ obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
+ endif
- txtptr = cellfun ('isclass', obj, 'char'); # type "STRING" replaced by "NUMERIC"
- obj2(txtptr) = obj(txtptr); obj(txtptr) = ctype(1); # Save strings in a safe place
+ # Create formula evaluator
+ frm_eval = xls.workbook.getCreationHelper ().createFormulaEvaluator ();
- emptr = cellfun ("isempty", obj);
- obj(emptr) = ctype(1); # Set empty cells to NUMERIC
-
- lptr = cellfun ("islogical" , obj); # Find logicals...
- obj2(lptr) = obj(lptr); # .. and set them to BOOLEAN
-
- ptr = cellfun ("isnan", obj); # Find NaNs & set to BLANK
- typearr(ptr) = ctype(4); typearr(~ptr) = ctype(1); # All other cells are now numeric
-
- obj(txtptr) = obj2(txtptr); # Copy strings back into place
- obj(lptr) = obj2(lptr);
-
- typearr(txtptr) = ctype(2); # ...and clean up
- typearr(emptr) = ctype(4);
- typearr(lptr) = ctype(5); # BOOLEAN, temp NUMERIC
-
- # Create formula evaluator (needed to be able to write boolean values!)
- # frm_eval = xls.workbook.getCreationHelper ().createFormulaEvaluator ();
-
for ii=1:nrows
ll = ii + trow - 2; # Java POI's row count = 0-based
row = sh.getRow (ll);
if (isempty (row)) row = sh.createRow (ll); endif
for jj=1:ncols
kk = jj + lcol - 2; # POI's column count is also 0-based
- if (typearr(ii, jj) == ctype(4))
- cell = row.createCell (kk, ctype(4));
+ if (typearr(ii, jj) == ctype(5)) # Empty cells
+ cell = row.createCell (kk, ctype(5));
+ elseif (typearr(ii, jj) == ctype(4)) # Formulas
+ # Try-catch needed as there's no guarantee for formula correctness
+ try
+ cell = row.createCell (kk, ctype(4));
+ cell.setCellFormula (obj{ii,jj});
+ catch
+ ++f_errs;
+ cell.setCellType (ctype (3)); # Enter formula as text
+ cell.setCellValue (obj{ii, jj});
+ end_try_catch
else
cell = row.createCell (kk, typearr(ii,jj));
cell.setCellValue (obj{ii, jj});
endif
endfor
endfor
-
+
+ if (f_errs)
+ printf ("%d formula errors encountered - please check input array\n", f_errs);
+ endif
xls.changed = 1;
rstatus = 1;
@@ -511,7 +549,8 @@
## -*- texinfo -*-
## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls ( @var{arr}, @var{xlsi})
## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh})
-## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{topleft})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range})
+## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range}, @var{options})
##
## Add data in 1D/2D CELL array @var{arr} into a range with upper left
## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
@@ -537,17 +576,24 @@
## 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
+## 2010-07-27 Added formula writing support (based on POI patch by Benjamin Lindner)
+## Added check for valid file pointer struct
+## 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
+## " Code cleanup
+## " Changed topleft arg into range arg (topleft version still recognized)
+## 2010-08-03 Moved range checks and cell type parsing to separate routines
-function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, topleftcell="A1")
+function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts)
persistent ctype;
if (isempty (ctype))
ctype = [1, 2, 3, 4, 5];
- # Boolean, Number, String, NaN, Empty
+ # Number, Boolean, String, Formula, Empty
endif
# scratch vars
- rstatus = 0; changed = 1;
+ rstatus = 0; changed = 1; f_errs = 0;
# Preliminary sanity checks
if (isempty (obj))
@@ -559,9 +605,17 @@
endif
if (nargin < 2) error ("oct2java2xls needs a minimum of 2 arguments."); endif
if (nargin == 2) wsh = 1; endif
- if (~strmatch(tolower(xls.filename(end-4:end)), '.xls')) # FIXME for OOXML
+ 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
# Prepare workbook pointer if needed
if (xls.changed < 2)
@@ -575,7 +629,7 @@
endif
# Check if requested worksheet exists in the file & if so, get pointer
- nr_of_sheets = xls.workbook.getNumberOfSheets(); # 1 based !!
+ nr_of_sheets = xls.workbook.getNumberOfSheets (); # 1 based !!
if (isnumeric (wsh))
if (wsh > nr_of_sheets)
# Watch out as a sheet called Sheet%d can exist with a lower index...
@@ -590,7 +644,7 @@
else
sh = wb.getSheet (wsh - 1); # POI sheet count 0-based
endif
- shnames = char(wb.getSheetNames ());
+ shnames = char (wb.getSheetNames ());
printf ("(Writing to worksheet %s)\n", shnames {nr_of_sheets, 1});
else
sh = wb.getSheet (wsh);
@@ -604,50 +658,52 @@
# Beware of strings variables interpreted as char arrays; change them to cell.
if (ischar (obj)) obj = {obj}; endif
-
- [topleft, nrows, ncols, trow, lcol] = parse_sp_range (topleftcell);
- [nrows, ncols] = size (obj);
- ## FIXME check on spreadsheet size vs. data array size needed
+ # Parse date ranges
+ [nr, nc] = size (obj);
+ [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
+ if (nrows < nr || ncols < nc)
+ warning ("Array truncated to fit in range");
+ obj = obj(1:nrows, 1:ncols);
+ endif
- # Prepare type array to speed up writing
- typearr = 5 * ones (nrows, ncols); # type "EMPTY", provisionally
- obj2 = cell (size (obj)); # Temporary storage for strings
+ # Prepare type array
+ typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
+ if ~(spsh_opts.formulas_as_text)
+ # Remove leading '=' from formula strings
+ fptr = ~(4 * (ones (size (typearr))) .- typearr);
+ obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
+ endif
+ clear fptr
- txtptr = cellfun ('isclass', obj, 'char'); # type "STRING" replaced by "NUMERIC"
- obj2(txtptr) = obj(txtptr); obj(txtptr) = 3; # Save strings in a safe place
-
- emptr = cellfun ("isempty", obj);
- obj(emptr) = 5; # Set empty cells to NUMERIC
-
- lptr = cellfun ("islogical" , obj); # Find logicals...
- obj2(lptr) = obj(lptr); # .. and set them to BOOLEAN
-
- ptr = cellfun ("isnan", obj); # Find NaNs & set to BLANK
- typearr(ptr) = 4; typearr(~ptr) = 2; # All other cells are now numeric
-
- obj(txtptr) = obj2(txtptr); # Copy strings back into place
- obj(lptr) = obj2(lptr);
- typearr(txtptr) = 3; # ...and clean up
- typearr(emptr) = 5;
- typearr(lptr) = 1; # BOOLEAN
-
# Write date to worksheet
for ii=1:nrows
ll = ii + trow - 2; # Java JExcelAPI's row count = 0-based
for jj=1:ncols
kk = jj + lcol - 2; # JExcelAPI's column count is also 0-based
switch typearr(ii, jj)
- case 1 # Boolean
+ case 1 # Numerical
+ tmp = java_new ('jxl.write.Number', kk, ll, obj{ii, jj});
+ sh.addCell (tmp);
+ case 2 # Boolean
tmp = java_new ('jxl.write.Boolean', kk, ll, obj{ii, jj});
sh.addCell (tmp);
- case 2 # Numerical
- tmp = java_new ('jxl.write.Number', kk, ll, obj{ii, jj});
- sh.addCell (tmp);
case 3 # String
tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
sh.addCell (tmp);
- case {4, 5}
+ case 4 # Formula
+ # First make sure formula functions are all uppercase
+ obj{ii, jj} = toupper (obj{ii, jj});
+ # 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});
+ catch
+ ++f_errs;
+ # Formula error. Enter formula as text string instead
+ tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
+ end_try_catch
+ sh.addCell (tmp);
+ case 5 # Empty or NaN
tmp = java_new ('jxl.write.Blank', kk, ll);
sh.addCell (tmp);
otherwise
@@ -656,8 +712,10 @@
endfor
endfor
+ if (f_errs)
+ printf ("%d formula errors encountered - please check input array\n", f_errs);
+ endif
xls.changed = 1;
rstatus = 1;
endfunction
-
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
