1 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <prnienhuis at users.sf.net>
3 ## This program is free software; you can redistribute it and/or modify it under
4 ## the terms of the GNU General Public License as published by the Free Software
5 ## Foundation; either version 3 of the License, or (at your option) any later
8 ## This program is distributed in the hope that it will be useful, but WITHOUT
9 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
10 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
13 ## You should have received a copy of the GNU General Public License along with
14 ## this program; if not, see <http://www.gnu.org/licenses/>.
17 ## @deftypefn {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls})
18 ## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh})
19 ## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{range})
20 ## @deftypefnx {Function File} [ @var{xls}, @var{rstatus} ] = oct2xls (@var{arr}, @var{xls}, @var{wsh}, @var{range}, @var{options})
22 ## Add data in 1D/2D CELL array @var{arr} into a cell range specified in
23 ## @var{range} in worksheet @var{wsh} in an Excel spreadsheet file
24 ## pointed to in structure @var{xls}.
25 ## Return argument @var{xls} equals supplied argument @var{xls} and is
26 ## updated by oct2xls.
28 ## A subsequent call to xlsclose is needed to write the updated spreadsheet
29 ## to disk (and -if needed- close the Excel or Java invocation).
31 ## @var{arr} can be any 1D or 2D array containing numerical or character
32 ## data (cellstr) except complex. Mixed numeric/text arrays can only be
35 ## @var{xls} must be a valid pointer struct created earlier by xlsopen.
37 ## @var{wsh} can be a number or string (max. 31 chars).
38 ## In case of a yet non-existing Excel file, the first worksheet will be
39 ## used & named according to @var{wsh} - extra empty worksheets that Excel
40 ## creates by default are deleted.
41 ## In case of existing files, some checks are made for existing worksheet
42 ## names or numbers, or whether @var{wsh} refers to an existing sheet with
43 ## a type other than worksheet (e.g., chart).
44 ## When new worksheets are to be added to the Excel file, they are
45 ## inserted to the right of all existing worksheets. The pointer to the
46 ## "active" sheet (shown when Excel opens the file) remains untouched.
48 ## If @var{range} is omitted or just the top left cell of the range is
49 ## specified, the actual range to be used is determined by the size of
50 ## @var{arr}. If nothing is specified for @var{range} the top left cell
51 ## is assumed to be 'A1'.
53 ## Data are added to the worksheet, ignoring other data already present;
54 ## existing data in the range to be used will be overwritten.
56 ## If @var{range} contains merged cells, only the elements of @var{arr}
57 ## corresponding to the top or left Excel cells of those merged cells
58 ## will be written, other array cells corresponding to that cell will be
61 ## Optional argument @var{options}, a structure, can be used to specify
62 ## various write modes.
63 ## Currently the only option field is "formulas_as_text", which -if set
64 ## to 1 or TRUE- specifies that formula strings (i.e., text strings
65 ## starting with "=" and ending in a ")" ) should be entered as litteral
66 ## text strings rather than as spreadsheet formulas (the latter is the
69 ## Beware that -if invoked- Excel invocations may be left running silently
70 ## in case of COM errors. Invoke xlsclose with proper pointer struct to
72 ## When using Java, note that large data array sizes elements may exhaust
73 ## the Java shared memory space for the default java memory settings.
74 ## For larger arrays, appropriate memory settings are needed in the file
75 ## java.opts; then the maximum array size for the Java-based spreadsheet
76 ## options may be in the order of 10^6 elements. In caso of UNO this
77 ## limit is not applicable and spreadsheets may be much larger.
82 ## [xlso, status] = xls2oct ('arr', xlsi, 'Third_sheet', 'AA31:AB278');
85 ## @seealso {xls2oct, xlsopen, xlsclose, xlsread, xlswrite, xlsfinfo}
89 ## Author: Philip Nienhuis
90 ## Created: 2009-12-01
92 ## 2010-01-03 (OOXML support)
93 ## 2010-03-14 Updated help text section on java memory usage
94 ## 2010-07-27 Added formula writing support (based on patch by Benjamin Lindner)
95 ## 2010-08-01 Added check on input array size vs. spreadsheet capacity
96 ## '' Changed argument topleft into range (now compatible with ML); the
97 ## '' old argument version (just topleft cell) is still recognized, though
98 ## 2010-08014 Added char array conversion to 1x1 cell for character input arrays
99 ## 2010-08-16 Added check on presence of output argument. Made wsh = 1 default
100 ## 2010-08-17 Corrected texinfo ("topleft" => "range")
101 ## 2010-08-25 Improved help text (section on java memory usage)
102 ## 2010-11-12 Moved ptr struct check into main func. More input validity checks
103 ## 2010-11-13 Added check for 2-D input array
104 ## 2010-12-01 Better check on file pointer struct (ischar (xls.xtype))
105 ## 2011-03-29 OpenXLS support added. Works but saving to file (xlsclose) doesn't work yet
106 ## '' Bug fixes (stray variable c_arr, and wrong test for valid xls struct)
107 ## 2011-05-18 Experimental UNO support
108 ## 2011-09-08 Bug fix in range arg check; code cleanup
109 ## 2011-11-18 Fixed another bug in test for range parameter being character string
110 ## 2012-01-26 Fixed "seealso" help string
111 ## 2012-02-20 Fixed range parameter to be default empty string rather than empty numeral
112 ## 2012-02-27 More range param fixes
113 ## 2012-03-07 Updated texinfo help text
114 ## 2012-05-22 Cast all numeric data in input array to double
116 ## Last script file update (incl. subfunctions): 2012-05-21
118 function [ xls, rstatus ] = oct2xls (obj, xls, wsh=1, crange='', spsh_opts=[])
120 if (nargin < 2) error ("oct2xls needs a minimum of 2 arguments."); endif
122 # Validate input array, make sure it is a cell array
124 warning ("Request to write empty matrix - ignored.");
127 elseif (isnumeric (obj))
128 obj = num2cell (obj);
129 elseif (ischar (obj))
131 printf ("(oct2xls: input character array converted to 1x1 cell)\n");
132 elseif (~iscell (obj))
133 error ("oct2xls: input array neither cell nor numeric array");
135 if (ndims (obj) > 2), error ("Only 2-dimensional arrays can be written to spreadsheet"); endif
136 # Cast all numerical values to double as spreadsheets only have double/boolean/text type
137 idx = cellfun (@isnumeric, obj, "UniformOutput", true);
138 obj(idx) = cellfun (@double, obj(idx), "UniformOutput", false);
140 # Check xls file pointer struct
141 test1 = ~isfield (xls, "xtype");
142 test1 = test1 || ~isfield (xls, "workbook");
143 test1 = test1 || isempty (xls.workbook);
144 test1 = test1 || isempty (xls.app);
145 test1 = test1 || ~ischar (xls.xtype);
147 error ("Invalid xls file pointer struct");
150 # Check worksheet ptr
151 if (~(ischar (wsh) || isnumeric (wsh))), error ("Integer (index) or text (wsh name) expected for arg # 3"); endif
154 if (~isempty (crange) && ~ischar (crange))
155 error ("Character string (range) expected for arg # 4");
156 elseif (isempty (crange))
161 if (isempty (spsh_opts))
162 spsh_opts.formulas_as_text = 0;
163 # other options to be implemented here
164 elseif (isstruct (spsh_opts))
165 if (~isfield (spsh_opts, 'formulas_as_text')), spsh_opts.formulas_as_text = 0; endif
166 # other options to be implemented here
168 error ("Structure expected for arg # 5");
171 if (nargout < 1) printf ("Warning: no output spreadsheet file pointer specified.\n"); endif
173 # Select interface to be used
174 if (strcmpi (xls.xtype, 'COM'))
175 # Call oct2com2xls to do the work
176 [xls, rstatus] = oct2com2xls (obj, xls, wsh, crange, spsh_opts);
177 elseif (strcmpi (xls.xtype, 'POI'))
178 # Invoke Java and Apache POI
179 [xls, rstatus] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts);
180 elseif (strcmpi (xls.xtype, 'JXL'))
181 # Invoke Java and JExcelAPI
182 [xls, rstatus] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts);
183 elseif (strcmpi (xls.xtype, 'OXS'))
184 # Invoke Java and OpenXLS ##### Not complete, saving file doesn't work yet!
185 printf ('Sorry, writing with OpenXLS not reliable => not supported yet\n');
186 # [xls, rstatus] = oct2oxs2xls (obj, xls, wsh, crange, spsh_opts);
187 elseif (strcmpi (xls.xtype, 'UNO'))
188 # Invoke Java and UNO bridge (OpenOffice.org)
189 [xls, rstatus] = oct2uno2xls (obj, xls, wsh, crange, spsh_opts);
190 # elseif (strcmpi (xls.xtype, '<whatever>'))
191 # <Other Excel interfaces>
193 error (sprintf ("oct2xls: unknown Excel .xls interface - %s.", xls.xtype));
199 #===================================================================================
200 ## Copyright (C) 2009,2010,2011,2012 by Philip Nienhuis <prnienhuis@users.sf.net>
202 ## This program is free software; you can redistribute it and/or modify it under
203 ## the terms of the GNU General Public License as published by the Free Software
204 ## Foundation; either version 3 of the License, or (at your option) any later
207 ## This program is distributed in the hope that it will be useful, but WITHOUT
208 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
209 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
212 ## You should have received a copy of the GNU General Public License along with
213 ## this program; if not, see <http://www.gnu.org/licenses/>.
216 ## @deftypefn {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi})
217 ## @deftypefnx {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi}, @var{wsh})
218 ## @deftypefnx {Function File} [@var{xlso}, @var{status}] = oct2com2xls (@var{obj}, @var{xlsi}, @var{wsh}, @var{top_left_cell})
219 ## Save matrix @var{obj} into worksheet @var{wsh} in Excel file pointed
220 ## to in struct @var{xlsi}. All elements of @var{obj} are converted into
221 ## Excel cells, starting at cell @var{top_left_cell}. Return argument
222 ## @var{xlso} is @var{xlsi} with updated fields.
224 ## oct2com2xls should not be invoked directly but rather through oct2xls.
226 ## Excel invocations may be left running invisibly in case of COM errors.
231 ## xls = oct2com2xls (rand (10, 15), xls, 'Third_sheet', 'BF24');
234 ## @seealso {oct2xls, xls2oct, xlsopen, xlsclose, xlswrite, xlsread, xls2com2oct}
238 ## Author: Philip Nienhuis (originally based on mat2xls by Michael Goffioul)
239 ## Rewritten: 2009-09-26
242 ## 2010-01-12 Fixed typearr sorting out (was only 1-dim & braces rather than parens))
243 ## Set cells corresponding to empty array cells empty (cf. Matlab)
244 ## 2010-01-13 Removed an extraneous statement used for debugging
245 ## I plan look at it when octave v.3.4 is about to arrive.
246 ## 2010-08-01 Added checks for input array size vs check on capacity
247 ## '' Changed topleft arg into range arg (just topleft still recognized)
248 ## '' Some code cleanup
249 ## '' Added option for formula input as text string
250 ## 2010-08-01 Added range vs. array size vs. capacity checks
251 ## 2010-08-03 Moved range checks and type array parsing to separate functions
252 ## 2010-10-20 Bug fix removing new empty sheets in new workbook that haven't been
253 ## created in the first place due to Excel setting (thanks Ian Journeaux)
254 ## '' Changed range use in COM transfer call
255 ## 2010-10-21 Improved file change tracking (var xls.changed)
256 ## 2010-10-24 Fixed bug introduced in above fix: for loops have no stride param,
257 ## '' replaced by while loop
258 ## '' Added check for "live" ActiveX server
259 ## 2010-11-12 Moved ptr struct check into main func
260 ## 2012-01-26 Fixed "seealso" help string
261 ## 2012-02-27 Copyright strings updated
263 function [ xls, status ] = oct2com2xls (obj, xls, wsh, crange, spsh_opts)
265 # Preliminary sanity checks
266 if (~strmatch (lower (xls.filename(end-4:end)), '.xls'))
267 error ("oct2com2xls can only write to Excel .xls or .xlsx files")
270 if (wsh < 1) error ("Illegal worksheet number: %i\n", wsh); endif
271 elseif (size (wsh, 2) > 31)
272 error ("Illegal worksheet name - too long")
274 # Check to see if ActiveX is still alive
276 wb_cnt = xls.workbook.Worksheets.count;
278 error ("ActiveX invocation in file ptr struct seems non-functional");
281 # define some constants not yet in __COM__.cc
282 xlWorksheet = -4167; # xlChart= 4;
287 [nr, nc] = size (obj);
288 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
289 lowerright = calccelladdress (trow + nrows - 1, lcol + ncols - 1);
290 crange = [topleft ':' lowerright];
291 if (nrows < nr || ncols < nc)
292 warning ("Array truncated to fit in range");
293 obj = obj(1:nrows, 1:ncols);
296 # Cleanup NaNs. Find where they are and mark as empty
297 ctype = [0 1 2 3 4]; # Numeric Boolean Text Formula Empty
298 typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
299 # Make cells now indicated to be empty, empty
300 fptr = ~(4 * (ones (size (typearr))) .- typearr);
301 obj(fptr) = cellfun (@(x) [], obj(fptr), "Uniformoutput", false);
303 if (spsh_opts.formulas_as_text)
304 # find formulas (designated by a string starting with "=" and ending in ")")
305 fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1) && strncmp (x(end:end), ")", 1), obj);
306 # ... and add leading "'" character
307 obj(fptr) = cellfun (@(x) ["'" x], obj(fptr), "Uniformoutput", false);
312 # Existing file OR a new file with data added in a previous oct2xls call.
313 # Some involved investigation is needed to preserve
314 # existing data that shouldn't be touched.
316 # See if desired *sheet* name exists.
318 ws_cnt = xls.workbook.Sheets.count;
321 # Here we check for sheet *position* in the sheet stack
322 # rather than a name like "Sheet<Number>"
325 # wsh > nr of sheets; proposed new sheet name.
326 # This sheet name can already exist to the left in the sheet stack!
327 shnm = sprintf ("Sheet%d", wsh); shnm1 = shnm;
331 # Check if the requested (or proposed) sheet already exists
332 # COM objects are not OO (yet?), so we need a WHILE loop
334 while ((ii <= ws_cnt) && ~old_sh)
335 # Get existing sheet names one by one
336 sh_name = xls.workbook.Sheets(ii).name;
337 if (~isnumeric (wsh) && strcmp (sh_name, wsh))
338 # ...and check with requested sheet *name*...
340 elseif (isnumeric (wsh) && strcmp (sh_name, shnm))
341 # ... or proposed new sheet name (corresp. to requested sheet *number*)
343 ii = 0; # Also check if this new augmented sheet name exists...
344 if (strmatch (shnm1, sh_name)), jj++; endif
345 if (jj > 5) # ... but not unlimited times...
346 error (sprintf (" > 5 sheets named [_]Sheet%d already present!", wsh));
354 # Requested sheet exists. Check if it is a *work*sheet
355 if ~(xls.workbook.Sheets(old_sh).Type == xlWorksheet)
356 # Error as you can't write data to Chart sheet
357 error (sprintf ("Existing sheet '%s' is not type worksheet.", wsh));
359 # Simply point to the relevant sheet
360 sh = xls.workbook.Worksheets (old_sh);
363 # Add a new worksheet. Earlier it was checked whether this is safe
365 sh = xls.workbook.Worksheets.Add ();
367 error (sprintf ("Cannot add new worksheet to file %s\n", xls.filename));
369 if (~isnumeric (wsh))
373 printf ("Writing to worksheet %s\n", shnm);
375 # Prepare to move new sheet to right of the worksheet stack anyway
376 ws_cnt = xls.workbook.Worksheets.count; # New count needed
377 # Find where Excel has left it. We have to, depends on Excel version :-(
380 while ((ii < ws_cnt+1) && ~strcmp (sh.Name, xls.workbook.Worksheets(ii).Name) == 1)
383 # Excel can't move it beyond the current last one, so we need a trick.
384 # First move it to just before the last one....
385 xls.workbook.Worksheets(ii).Move (before = xls.workbook.Worksheets(ws_cnt));
386 # ....then move the last one before the new sheet.
387 xls.workbook.Worksheets (ws_cnt).Move (before = xls.workbook.Worksheets(ws_cnt - 1));
391 # The easy case: a new Excel file. Workbook was created in xlsopen.
393 # Delete empty non-used sheets, last one first
394 xls.app.Application.DisplayAlerts = 0;
395 ii = xls.workbook.Sheets.count;
397 xls.workbook.Worksheets(ii).Delete();
400 xls.app.Application.DisplayAlerts = 1;
402 # Write to first worksheet:
403 sh = xls.workbook.Worksheets (1);
406 sh.Name = sprintf ("Sheet%i", wsh);
410 xls.changed = 2; # 3 => 2
413 # MG's original part.
414 # Save object in Excel sheet, starting at cell top_left_cell
416 r = sh.Range (crange);
420 error (sprintf ("Cannot add data to worksheet %s in file %s\n", sh.Name, xls.filename));
425 # If we get here, all went OK
427 xls.changed = max (xls.changed, 1); # If it was 2, preserve it.
432 #====================================================================================
434 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <prnienhuis at users.sf.net>
436 ## This program is free software; you can redistribute it and/or modify it under
437 ## the terms of the GNU General Public License as published by the Free Software
438 ## Foundation; either version 3 of the License, or (at your option) any later
441 ## This program is distributed in the hope that it will be useful, but WITHOUT
442 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
443 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
446 ## You should have received a copy of the GNU General Public License along with
447 ## this program; if not, see <http://www.gnu.org/licenses/>.
450 ## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls ( @var{arr}, @var{xlsi})
451 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh})
452 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range})
453 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jpoi2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range}, @var{options})
455 ## Add data in 1D/2D CELL array @var{arr} into a range with upper left
456 ## cell equal to @var{topleft} in worksheet @var{wsh} in an Excel
457 ## spreadsheet file pointed to in structure @var{range}.
458 ## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
459 ## updated by oct2java2xls.
461 ## oct2jpoi2xls should not be invoked directly but rather through oct2xls.
466 ## [xlso, status] = xls2jpoi2oct ('arr', xlsi, 'Third_sheet', 'AA31');
469 ## @seealso {oct2xls, xls2oct, xlsopen, xlsclose, xlsread, xlswrite}
473 ## Author: Philip Nienhuis
474 ## Created: 2009-11-26
476 ## 2010-01-03 Bugfixes
477 ## 2010-01-12 Added xls.changed = 1 statement to signal successful write
478 ## 2010-03-08 Dumped formula evaluator for booleans. Not being able to
479 ## write booleans was due to a __java__.oct deficiency (see
480 ## http://sourceforge.net/mailarchive/forum.php?thread_name=4B59A333.5060302%40net.in.tum.de&forum_name=octave-dev )
481 ## 2010-07-27 Added formula writing support (based on patch by Benjamin Lindner)
482 ## 2010-08-01 Improved try-catch for formulas to enter wrong formulas as text strings
483 ## 2010-08-01 Added range vs. array size vs. capacity checks
484 ## 2010-08-03 Moved range checks and type array parsingto separate functions
485 ## 2010-10-21 Improved logic for tracking file changes
486 ## 2010-10-27 File change tracking again refined, internal var 'changed' dropped
487 ## 2010-11-12 Moved ptr struct check into main func
488 ## 2011-11-19 Try-catch added to allow for changed method name for nr of worksheets
489 ## 2012-01-26 Fixed "seealso" help string
490 ## 2012-02-27 Copyright strings updated
491 ## 2012-05-21 "Double" cast added when writing numeric values
492 ## 2012-05-21 "Double" cast moved into main func oct2xls
494 function [ xls, rstatus ] = oct2jpoi2xls (obj, xls, wsh, crange, spsh_opts)
496 # Preliminary sanity checks
497 if (~strmatch (tolower (xls.filename(end-4:end)), '.xls'))
498 error ("oct2jpoi2xls can only write to Excel .xls or .xlsx files")
503 # Get cell types. Beware as they start at 0 not 1
504 ctype(1) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_NUMERIC'); # 0
505 ctype(2) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BOOLEAN'); # 4
506 ctype(3) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_STRING'); # 1
507 ctype(4) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_FORMULA'); # 2
508 ctype(5) = java_get ('org.apache.poi.ss.usermodel.Cell', 'CELL_TYPE_BLANK'); # 3
511 rstatus = 0; f_errs = 0;
513 # Check if requested worksheet exists in the file & if so, get pointer
515 nr_of_sheets = xls.workbook.getNumWorkSheets ();
517 nr_of_sheets = xls.workbook.getNumberOfSheets ();
520 if (wsh > nr_of_sheets)
521 # Watch out as a sheet called Sheet%d can exist with a lower index...
522 strng = sprintf ("Sheet%d", wsh);
524 while (~isempty (xls.workbook.getSheet (strng)) && (ii < 5))
528 if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
529 sh = xls.workbook.createSheet (strng);
530 xls.changed = min (xls.changed, 2); # Keep 2 for new files
532 sh = xls.workbook.getSheetAt (wsh - 1); # POI sheet count 0-based
534 printf ("(Writing to worksheet %s)\n", sh.getSheetName ());
536 sh = xls.workbook.getSheet (wsh);
538 # Sheet not found, just create it
539 sh = xls.workbook.createSheet (wsh);
540 xls.changed = min (xls.changed, 2); # Keep 2 or 3 f. new files
545 [nr, nc] = size (obj);
546 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
547 if (nrows < nr || ncols < nc)
548 warning ("Array truncated to fit in range");
549 obj = obj(1:nrows, 1:ncols);
553 typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
554 if ~(spsh_opts.formulas_as_text)
555 # Remove leading '=' from formula strings
556 # FIXME should be easier using typearr<4> info
557 fptr = ~(2 * (ones (size (typearr))) .- typearr);
558 obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
561 # Create formula evaluator
562 frm_eval = xls.workbook.getCreationHelper ().createFormulaEvaluator ();
565 ll = ii + trow - 2; # Java POI's row count = 0-based
566 row = sh.getRow (ll);
567 if (isempty (row)) row = sh.createRow (ll); endif
569 kk = jj + lcol - 2; # POI's column count is also 0-based
570 if (typearr(ii, jj) == ctype(5)) # Empty cells
571 cell = row.createCell (kk, ctype(5));
572 elseif (typearr(ii, jj) == ctype(4)) # Formulas
573 # Try-catch needed as there's no guarantee for formula correctness
575 cell = row.createCell (kk, ctype(4));
576 cell.setCellFormula (obj{ii,jj});
579 cell.setCellType (ctype (3)); # Enter formula as text
580 cell.setCellValue (obj{ii, jj});
583 cell = row.createCell (kk, typearr(ii,jj));
584 if (isnumeric (obj{ii, jj}))
585 cell.setCellValue (obj{ii, jj});
587 cell.setCellValue (obj{ii, jj});
594 printf ("%d formula errors encountered - please check input array\n", f_errs);
596 xls.changed = max (xls.changed, 1); # Preserve a "2"
602 #====================================================================================
603 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <prnienhuis at users.sf.net>
605 ## This program is free software; you can redistribute it and/or modify it under
606 ## the terms of the GNU General Public License as published by the Free Software
607 ## Foundation; either version 3 of the License, or (at your option) any later
610 ## This program is distributed in the hope that it will be useful, but WITHOUT
611 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
612 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
615 ## You should have received a copy of the GNU General Public License along with
616 ## this program; if not, see <http://www.gnu.org/licenses/>.
619 ## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls ( @var{arr}, @var{xlsi})
620 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh})
621 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range})
622 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2jxla2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range}, @var{options})
624 ## Add data in 1D/2D CELL array @var{arr} into spreadsheet cell range @var{range}
625 ## in worksheet @var{wsh} in an Excel spreadsheet file pointed to in structure
627 ## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
628 ## updated by oct2jxla2xls.
630 ## oct2jxla2xls should not be invoked directly but rather through oct2xls.
635 ## [xlso, status] = oct2jxla2oct ('arr', xlsi, 'Third_sheet', 'AA31');
638 ## @seealso {oct2xls, xls2oct, xlsopen, xlsclose, xlsread, xlswrite, xls2jxla2oct}
642 ## Author: Philip Nienhuis
643 ## Created: 2009-12-04
646 ## 2010-01-12 Fixed skipping empty array values (now Excel-conformant => cell cleared)
647 ## Added xls.changed = 1 statement to signal successful write
648 ## 2010-07-27 Added formula writing support (based on POI patch by Benjamin Lindner)
649 ## Added check for valid file pointer struct
650 ## 2010-08-01 Improved try-catch for formulas to enter wrong formulas as text strings
651 ## 2010-08-01 Added range vs. array size vs. capacity checks
653 ## '' Changed topleft arg into range arg (topleft version still recognized)
654 ## 2010-08-03 Moved range checks and cell type parsing to separate routines
655 ## 2010-08-11 Moved addcell() into try-catch as it is addCell which throws fatal errors
656 ## 2010-10-20 Improved logic for tracking file changes (xls.changed 2 or 3); dropped
657 ## '' internal variable 'changed'
658 ## 2010-10-27 File change tracking again refined
659 ## 2010-11-12 Moved ptr struct check into main func
660 ## 2012-01-26 Fixed "seealso" help string
661 ## 2012-02-27 Copyright strings updated
662 ## 2012-05-21 "Double" cast added when writing numeric values
663 ## 2012-05-21 "Double" cast moved into main func oct2xls
665 function [ xls, rstatus ] = oct2jxla2xls (obj, xls, wsh, crange, spsh_opts)
667 # Preliminary sanity checks
668 if (~strmatch (tolower (xls.filename(end-4:end-1)), '.xls')) # No OOXML in JXL
669 error ("JExcelAPI can only write to Excel .xls files")
674 ctype = [1, 2, 3, 4, 5];
675 # Number, Boolean, String, Formula, Empty
678 rstatus = 0; f_errs = 0;
680 # Prepare workbook pointer if needed
681 if (xls.changed == 0) # Only for 1st call of octxls after xlsopen
682 # Create writable copy of workbook. If >2 a writable wb was made in xlsopen
683 xlsout = java_new ('java.io.File', xls.filename);
684 wb = java_invoke ('jxl.Workbook', 'createWorkbook', xlsout, xls.workbook);
685 # Catch JExcelAPI bug/"feature": when switching to write mode, the file on disk
686 # is affected and the memory file MUST be written to disk to save earlier data
692 # Check if requested worksheet exists in the file & if so, get pointer
693 nr_of_sheets = xls.workbook.getNumberOfSheets (); # 1 based !!
695 if (wsh > nr_of_sheets)
696 # Watch out as a sheet called Sheet%d can exist with a lower index...
697 strng = sprintf ("Sheet%d", wsh);
699 while (~isempty (wb.getSheet (strng)) && (ii < 5))
703 if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
704 sh = wb.createSheet (strng, nr_of_sheets); ++nr_of_sheets;
705 xls.changed = min (xls.changed, 2); # Keep a 2 in case of new file
707 sh = wb.getSheet (wsh - 1); # JXL sheet count 0-based
709 shnames = char (wb.getSheetNames ());
710 printf ("(Writing to worksheet %s)\n", shnames {nr_of_sheets, 1});
712 sh = wb.getSheet (wsh);
714 # Sheet not found, just create it
715 sh = wb.createSheet (wsh, nr_of_sheets);
717 xls.changed = min (xls.changed, 2); # Keep a 2 for new file
722 [nr, nc] = size (obj);
723 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
724 if (nrows < nr || ncols < nc)
725 warning ("Array truncated to fit in range");
726 obj = obj(1:nrows, 1:ncols);
730 typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
731 if ~(spsh_opts.formulas_as_text)
732 # Remove leading '=' from formula strings
733 fptr = ~(4 * (ones (size (typearr))) .- typearr);
734 obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
738 # Write date to worksheet
740 ll = ii + trow - 2; # Java JExcelAPI's row count = 0-based
742 kk = jj + lcol - 2; # JExcelAPI's column count is also 0-based
743 switch typearr(ii, jj)
745 tmp = java_new ('jxl.write.Number', kk, ll, obj{ii, jj});
748 tmp = java_new ('jxl.write.Boolean', kk, ll, obj{ii, jj});
751 tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
754 # First make sure formula functions are all uppercase
755 obj{ii, jj} = toupper (obj{ii, jj});
756 # There's no guarantee for formula correctness, so....
757 try # Actually JExcelAPI flags formula errors as mere warnings :-(
758 tmp = java_new ('jxl.write.Formula', kk, ll, obj{ii, jj});
759 # ... while errors are actually detected in addCell(), so
760 # that should be within the try-catch
764 # Formula error. Enter formula as text string instead
765 tmp = java_new ('jxl.write.Label', kk, ll, obj{ii, jj});
768 case 5 # Empty or NaN
769 tmp = java_new ('jxl.write.Blank', kk, ll);
778 printf ("%d formula errors encountered - please check input array\n", f_errs);
780 xls.changed = max (xls.changed, 1); # Preserve 2 for new files
786 ## Copyright (C) 2011 Philip Nienhuis <prnienhuis@users.sf.net>
788 ## This program is free software; you can redistribute it and/or modify it under
789 ## the terms of the GNU General Public License as published by the Free Software
790 ## Foundation; either version 3 of the License, or (at your option) any later
793 ## This program is distributed in the hope that it will be useful, but WITHOUT
794 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
795 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
798 ## You should have received a copy of the GNU General Public License along with
799 ## this program; if not, see <http://www.gnu.org/licenses/>.
802 ## @deftypefn {Function File} [ @var{xlso}, @var{rstatus} ] = oct2oxs2xls ( @var{arr}, @var{xlsi})
803 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2oxs2xls (@var{arr}, @var{xlsi}, @var{wsh})
804 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2oxs2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range})
805 ## @deftypefnx {Function File} [ @var{xlso}, @var{rstatus} ] = oct2oxs2xls (@var{arr}, @var{xlsi}, @var{wsh}, @var{range}, @var{options})
807 ## Add data in 1D/2D CELL array @var{arr} into spreadsheet cell range @var{range}
808 ## in worksheet @var{wsh} in an Excel spreadsheet file pointed to in structure
810 ## Return argument @var{xlso} equals supplied argument @var{xlsi} and is
811 ## updated by oct2oxs2xls.
813 ## oct2oxs2xls should not be invoked directly but rather through oct2xls.
817 ## Author: Philip Nienhuis <prnienhuis@users.sf.net>
818 ## Created: 2011-03-29
822 function [ xls, rstatus ] = oct2oxs2xls (obj, xls, wsh, crange, spsh_opts)
824 # Preliminary sanity checks
825 if (~strmatch (tolower (xls.filename(end-4:end-1)), '.xls')) # No OOXML in OXS
826 error ("OXS can only write to Excel .xls files")
833 ctype = [1, 2, 3, 4, 5];
834 # Number, Boolean, String, Formula, Empty
837 rstatus = 0; f_errs = 0;
839 # Prepare workbook pointer if needed
842 # Check if requested worksheet exists in the file & if so, get pointer
843 nr_of_sheets = wb.getNumWorkSheets (); # 1 based !!
845 if (wsh > nr_of_sheets)
846 # Watch out as a sheet called Sheet%d can exist with a lower index...
847 strng = sprintf ("Sheet%d", wsh);
850 # While loop should be inside try-catch
852 sh = wb.getWorkSheet (strng)
857 # No worksheet named <strng> found => we can proceed
859 if (ii >= 5) error (sprintf( " > 5 sheets named [_]Sheet%d already present!", wsh)); endif
860 sh = wb.createWorkSheet (strng); ++nr_of_sheets;
861 xls.changed = min (xls.changed, 2); # Keep a 2 in case of new file
863 sh = wb.getWorkSheet (wsh - 1); # OXS sheet count 0-based
865 printf ("(Writing to worksheet %s)\n", sh.getSheetName ());
868 sh = wb.getWorkSheet (wsh);
870 # Sheet not found, just create it
871 sh = wb.createWorkSheet (wsh); ++nr_of_sheets;
872 xls.changed = min (xls.changed, 2); # Keep a 2 for new file
877 [nr, nc] = size (obj);
878 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
879 if (nrows < nr || ncols < nc)
880 warning ("Array truncated to fit in range");
881 obj = obj(1:nrows, 1:ncols);
885 typearr = spsh_prstype (obj, nrows, ncols, ctype, spsh_opts);
886 if ~(spsh_opts.formulas_as_text)
887 # Remove leading '=' from formula strings //FIXME needs updating
888 fptr = ~(4 * (ones (size (typearr))) .- typearr);
889 obj(fptr) = cellfun (@(x) x(2:end), obj(fptr), "Uniformoutput", false);
897 sh.getCell(jj+trow-2, ii+lcol-2).setVal (obj{jj, ii}); # Addr.cnt = 0-based
900 # Cell not existent. Add cell
901 if ~(typearr(jj, ii) == 5)
902 sh.add (obj{jj, ii}, jj+trow-2, ii+lcol-2);
909 if (changed), xls.changed = max (xls.changed, 1); endif # Preserve 2 for new files
915 ## Copyright (C) 2011,2012 Philip Nienhuis <prnienhuis@users.sf.net>
917 ## This program is free software; you can redistribute it and/or modify it under
918 ## the terms of the GNU General Public License as published by the Free Software
919 ## Foundation; either version 3 of the License, or (at your option) any later
922 ## This program is distributed in the hope that it will be useful, but WITHOUT
923 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
924 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
927 ## You should have received a copy of the GNU General Public License along with
928 ## this program; if not, see <http://www.gnu.org/licenses/>.
932 ## Author: Philip Nienhuis <prnienhuis@users.sf.net>
933 ## Created: 2011-05-18
934 ## 2011-09-18 Adapted sh_names type to LO 3.4.1
935 ## 2011-09-23 Removed stray debug statements
936 ## 2012-02-25 Fixed wrong var name in L.933
937 ## 2012-02-25 Catch stray Java RuntimeException when deleting sheets
938 ## 2012-02-26 Bug fix when adding sheets near L.994 (wrong if-else-end construct).
939 ## 2012-02-27 Copyright strings updated
940 ## 2012-05-21 "Double" cast added when writing numeric values
941 ## 2012-05-21 "Double" cast moved into main func oct2xls
943 function [ xls, rstatus ] = oct2uno2xls (c_arr, xls, wsh, crange, spsh_opts)
947 ctype = [1, 2, 3, 4, 5]; # Float, Logical, String, Formula, Empty
949 # Get handle to sheet, create a new one if needed
950 sheets = xls.workbook.getSheets ();
951 sh_names = sheets.getElementNames ();
952 if (! iscell (sh_names))
953 # Java array (LibreOffice 3.4.+); convert to cellstr
954 sh_names = char (sh_names);
956 sh_names = {sh_names};
959 # Clear default 2 last sheets in case of a new spreadsheet file
961 ii = numel (sh_names);
965 # Catch harmless Java RuntimeException "out of range" in LibreOffice 3.5rc1
966 sheets.removeByName (shnm);
970 # Give remaining sheet a name
971 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XSpreadsheet');
972 sh = sheets.getByName (sh_names{1}).getObject.queryInterface (unotmp);
973 if (isnumeric (wsh)); wsh = sprintf ("Sheet%d", wsh); endif
974 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.container.XNamed');
975 sh.queryInterface (unotmp).setName (wsh);
978 # Check sheet pointer
979 # FIXME sheet capacity check needed
982 error ("Illegal sheet index: %d", wsh);
983 elseif (wsh > numel (sh_names))
984 # New sheet to be added. First create sheet name but check if it already exists
985 shname = sprintf ("Sheet%d", numel (sh_names) + 1);
986 jj = strmatch (wsh, sh_names);
988 # New sheet name already in file, try to create a unique & reasonable one
989 ii = 1; filler = ''; maxtry = 5;
991 shname = sprintf ("Sheet%s%d", [filler "_"], numel (sh_names + 1));
992 if (isempty (strmatch (wsh, sh_names)))
999 error ("Could not add sheet with a unique name to file %s");
1005 # turn wsh index into the associated sheet name
1006 wsh = sh_names (wsh);
1009 # wsh is a sheet name. See if it exists already
1010 if (isempty (strmatch (wsh, sh_names)))
1011 # Not found. New sheet to be added
1016 # Add a new sheet. Sheet index MUST be a Java Short object
1017 shptr = java_new ("java.lang.Short", sprintf ("%d", numel (sh_names) + 1));
1018 sh = sheets.insertNewByName (wsh, shptr);
1020 # At this point we have a valid sheet name. Use it to get a sheet handle
1021 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XSpreadsheet');
1022 sh = sheets.getByName (wsh).getObject.queryInterface (unotmp);
1025 # Check size of data array & range / capacity of worksheet & prepare vars
1026 [nr, nc] = size (c_arr);
1027 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, xls.xtype, xls.filename);
1028 --trow; --lcol; # Zero-based row # & col #
1029 if (nrows < nr || ncols < nc)
1030 warning ("Array truncated to fit in range");
1031 c_arr = c_arr(1:nrows, 1:ncols);
1034 # Parse data array, setup typarr and throw out NaNs to speed up writing;
1035 typearr = spsh_prstype (c_arr, nrows, ncols, ctype, spsh_opts, 0);
1036 if ~(spsh_opts.formulas_as_text)
1037 # Find formulas (designated by a string starting with "=" and ending in ")")
1038 fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1), c_arr);
1039 typearr(fptr) = ctype(4); # FORMULA
1042 # Transfer data to sheet
1046 XCell = sh.getCellByPosition (lcol+jj-1, trow+ii-1);
1047 switch typearr(ii, jj)
1049 XCell.setValue (c_arr{ii, jj});
1050 case 2 # Logical. Convert to float as OOo has no Boolean type
1051 XCell.setValue (double (c_arr{ii, jj}));
1053 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
1054 XCell.queryInterface (unotmp).setString (c_arr{ii, jj});
1056 if (spsh_opts.formulas_as_text)
1057 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
1058 XCell.queryInterface (unotmp).setString (c_arr{ii, jj});
1060 XCell.setFormula (c_arr{ii, jj});
1067 printf ("Error writing cell %s (typearr() = %d)\n", calccelladdress(trow+ii, lcol+jj), typearr(ii, jj));
1073 xls.changed = max (min (xls.changed, 2), changed); # Preserve 2 (new file), 1 (existing)