1 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <pr.nienhuis 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{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods})
18 ## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh})
19 ## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh}, @var{range})
20 ## @deftypefnx {Function File} [ @var{ods}, @var{rstatus} ] = oct2ods (@var{arr}, @var{ods}, @var{wsh}, @var{range}, @var{options})
22 ## Transfer data to an OpenOffice_org Calc spreadsheet previously opened
25 ## Data in 1D/2D array @var{arr} are transferred into a cell range
26 ## @var{range} in sheet @var{wsh}. @var{ods} must have been made earlier
27 ## by odsopen(). Return argument @var{ods} should be the same as supplied
28 ## argument @var{ods} and is updated by oct2ods. A subsequent call to
29 ## odsclose is needed to write the updated spreadsheet to disk (and
30 ## -if needed- close the Java invocation holding the file pointer).
32 ## @var{arr} can be any 1D or 2D array containing numerical or character
33 ## data (cellstr) except complex. Mixed numeric/text arrays can only be
36 ## @var{ods} must be a valid pointer struct created earlier by odsopen.
38 ## @var{wsh} can be a number (sheet name) or string (sheet number).
39 ## In case of a yet non-existing Calc file, the first sheet will be
40 ## used & named according to @var{wsh}.
41 ## In case of existing files, some checks are made for existing sheet
43 ## When new sheets are to be added to the Calc file, they are
44 ## inserted to the right of all existing sheets. The pointer to the
45 ## "active" sheet (shown when Calc opens the file) remains untouched.
47 ## If @var{range} omitted, the top left cell where the data will be put
48 ## is supposed to be 'A1'; only a top left cell address can be specified
49 ## as well. In these cases the actual range to be used is determined by
50 ## the size of @var{arr}.
51 ## Be aware that large data array sizes may exhaust the java shared
52 ## memory space. For larger arrays, appropriate memory settings are
53 ## needed in the file java.opts; then the maximum array size for the
54 ## java-based spreadsheet options can be in the order of perhaps 10^6
57 ## Optional argument @var{options}, a structure, can be used to specify
58 ## various write modes.
59 ## Currently the only option field is "formulas_as_text", which -if set
60 ## to 1 or TRUE- specifies that formula strings (i.e., text strings
61 ## starting with "=" and ending in a ")" ) should be entered as litteral
62 ## text strings rather than as spreadsheet formulas (the latter is the
63 ## default). As jOpenDocument doesn't support formula I/O at all yet,
64 ## this option is ignored for the JOD interface.
66 ## Data are added to the sheet, ignoring other data already present;
67 ## existing data in the range to be used will be overwritten.
69 ## If @var{range} contains merged cells, also the elements of @var{arr}
70 ## not corresponding to the top or left Calc cells of those merged cells
71 ## will be written, however they won't be shown until in Calc the merge is
77 ## [ods, status] = ods2oct (arr, ods, 'Newsheet1', 'AA31:GH165');
78 ## Write array arr into sheet Newsheet1 with upperleft cell at AA31
82 ## [ods, status] = ods2oct (@{'String'@}, ods, 'Oldsheet3', 'B15:B15');
83 ## Put a character string into cell B15 in sheet Oldsheet3
86 ## @seealso {ods2oct, odsopen, odsclose, odsread, odswrite, odsfinfo}
90 ## Author: Philip Nienhuis
91 ## Created: 2009-12-13
93 ## 2010-01-15 Updated texinfo header
94 ## 2010-03-14 Updated help text (a.o. on java memory usage)
95 ## 2010-03-25 see oct2jotk2ods
96 ## 2010-03-28 Added basic support for ofdom v.0.8. Everything works except adding cols/rows
97 ## 2010-03-29 Removed odfdom-0.8 support, it's simply too buggy :-( Added a warning instead
98 ## 2010-06-01 Almost complete support for upcoming jOpenDocument 1.2b4. 1.2b3 still lacks a bit
99 ## 2010-07-05 Added example for writng character strings
100 ## 2010-07-29 Added option for entering / reading back spreadsheet formulas
101 ## 2010-08-14 Moved check on input cell array to main function
102 ## 2010-08-15 Texinfo header edits
103 ## 2010-08-16 Added check on presence of output argument
104 ## 2010-08-23 Added check on validity of ods file ptr
105 ## '' Experimental support for odfdom 0.8.6 (in separate subfunc, to be integrated later)
106 ## 2010-08-25 Improved help text (java memory, ranges)
107 ## 2010-10-27 Improved file change tracking tru ods.changed
108 ## 2010-11-12 Better input argument checks
109 ## 2010-11-13 Reset ods.limits when read was successful
110 ## 2010-11-13 Added check for 2-D input array
111 ## 2011-03-23 First try of odfdom 0.8.7
112 ## 2011-05-15 Experimental UNO support added
113 ## 2011-11-18 Fixed bug in test for range parameter being character string
114 ## 2012-01-26 Fixed "seealso" help string
115 ## 2012-02-20 Fixed range parameter to be default empty string rather than empty numeral
116 ## 2012-02-27 More range arg fixes
117 ## 2012-03-07 Updated texinfo help text
118 ## 2012-06-08 Support for odfdom-incubator-0.8.8
119 ## '' Tabs replaced by double space
121 ## Last update of subfunctions below: 2012-06-08
123 function [ ods, rstatus ] = oct2ods (c_arr, ods, wsh=1, crange='', spsh_opts=[])
125 if (nargin < 2) error ("oct2xls needs a minimum of 2 arguments."); endif
127 # Check if input array is cell
129 warning ("Request to write empty matrix - ignored.");
132 elseif (isnumeric (c_arr))
133 c_arr = num2cell (c_arr);
134 elseif (ischar(c_arr))
136 printf ("(oct2ods: input character array converted to 1x1 cell)\n");
137 elseif (~iscell (c_arr))
138 error ("oct2ods: input array neither cell nor numeric array");
140 if (ndims (c_arr) > 2), error ("Only 2-dimensional arrays can be written to spreadsheet"); endif
142 # Check ods file pointer struct
143 test1 = ~isfield (ods, "xtype");
144 test1 = test1 || ~isfield (ods, "workbook");
145 test1 = test1 || isempty (ods.workbook);
146 test1 = test1 || isempty (ods.app);
148 error ("Arg #2: Invalid ods file pointer struct");
151 # Check worksheet ptr
152 if (~(ischar (wsh) || isnumeric (wsh))), error ("Integer (index) or text (wsh name) expected for arg # 3"); endif
155 if (~isempty (crange) && ~ischar (crange))
156 error ("Character string (range) expected for arg # 4");
157 elseif (isempty (crange))
162 if (isempty (spsh_opts))
163 spsh_opts.formulas_as_text = 0;
164 # other options to be implemented here
165 elseif (isstruct (spsh_opts))
166 if (~isfield (spsh_opts, 'formulas_as_text')), spsh_opts.formulas_as_text = 0; endif
167 # other options to be implemented here
169 error ("Structure expected for arg # 5");
172 if (nargout < 1) printf ("Warning: no output spreadsheet file pointer specified.\n"); endif
174 if (strcmp (ods.xtype, 'OTK'))
175 # Write ods file tru Java & ODF toolkit.
178 [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh, crange, spsh_opts);
179 case {"0.8.6", "0.8.7", "0.8.8"}
180 [ ods, rstatus ] = oct3jotk2ods (c_arr, ods, wsh, crange, spsh_opts);
182 error ("Unsupported odfdom version");
185 elseif (strcmp (ods.xtype, 'JOD'))
186 # Write ods file tru Java & jOpenDocument. API still leaves lots to be wished...
187 [ ods, rstatus ] = oct2jod2ods (c_arr, ods, wsh, crange);
189 elseif (strcmp (ods.xtype, 'UNO'))
190 # Write ods file tru Java & UNO bridge (OpenOffice.org & clones)
191 [ ods, rstatus ] = oct2uno2ods (c_arr, ods, wsh, crange, spsh_opts);
194 # ---- < Other interfaces here >
197 error (sprintf ("ods2oct: unknown OpenOffice.org .ods interface - %s.", ods.xtype));
200 if (rstatus), ods.limits = []; endif
205 #=============================================================================
207 ## Copyright (C) 2010,2011,2012 Philip Nienhuis <prnienhuis@users.sf.net>
209 ## This program is free software; you can redistribute it and/or modify it under
210 ## the terms of the GNU General Public License as published by the Free Software
211 ## Foundation; either version 3 of the License, or (at your option) any later
214 ## This program is distributed in the hope that it will be useful, but WITHOUT
215 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
216 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
219 ## You should have received a copy of the GNU General Public License along with
220 ## this program; if not, see <http://www.gnu.org/licenses/>.
223 ## write data array to an ODS spreadsheet using Java & ODFtoolkit 0.7.5
225 ## I'm truly sorry that oct2jotk2ods is so ridiculously complex,
226 ## and therefore so slow; but there's a good reason for that:
227 ## Writing to ODS is already fairly complicated when just making a
228 ## new sheet ("table"); but it really becomes a headache when
229 ## writing to an existing sheet. In that case one should beware of
230 ## table-number-columns-repeated, table-number-rows-repeated,
231 ## covered (merged) cells, incomplete tables and rows, etc.
232 ## ODF toolkit v. 0.7.5 does nothing to hide this from the user;
233 ## you may sort it out all by yourself.
235 ## Author: Philip Nienhuis <prnienhuis@users.sf.net>
236 ## Created: 2010-01-07
238 ## 2010-01-14 (finally seems to work OK)
239 ## 2010-03-08 Some comment lines adapted
240 ## 2010-03-25 Try-catch added f. unpatched-for-booleans java-1.2.6 / 1.2.7 package
241 ## 2010-04-11 Changed all references to "cell" to "scell" to avoid reserved keyword
242 ## '' Small bugfix for cases with empty left columns (wrong cell reference)
243 ## 2010-04-13 Fixed bug with stray cell copies beyond added data rectangle
244 ## 2010-07-29 Added formula input support (based on xls patch by Benjamin Lindner)
245 ## 2010-08-01 Added try-catch around formula input
246 ## '' Changed range arg to also allow just topleft cell
247 ## 2010-08-03 Moved range checks and type array parsing to separate functions
248 ## 2010-08-13 Fixed empty Sheet1 in case of new spreadsheets, fix input text sheet name
249 ## 2010-10-27 Improved file change tracking tru ods.changed
250 ## 2010-11-12 Improved file change tracking tru ods.changed
252 function [ ods, rstatus ] = oct2jotk2ods (c_arr, ods, wsh, crange, spsh_opts)
256 # Number, Boolean, String, Formula, Empty, Date, Time (last 2 are ignored)
257 ctype = [1, 2, 3, 4, 5, 6, 7];
260 rstatus = 0; f_errs = 0;
262 # Get some basic spreadsheet data from the pointer using ODFtoolkit
263 odfcont = ods.workbook;
264 xpath = ods.app.getXPath ();
265 offsprdsh = ods.app.getContentRoot();
266 autostyles = odfcont.getOrCreateAutomaticStyles();
267 officestyles = ods.app.getOrCreateDocumentStyles();
269 # Create an instance of type NODESET for use in subsequent statements
270 NODESET = java_get ('javax.xml.xpath.XPathConstants', 'NODESET');
272 # Parse sheets ("tables") from ODS file
273 sheets = xpath.evaluate ("//table:table", odfcont, NODESET);
274 nr_of_sheets = sheets.getLength ();
275 newsh = 0; # Assume existing sheet
276 if isempty (wsh) wsh = 1; endif
277 if (~isnumeric (wsh)) # Sheet name specified
278 # Search in sheet names, match sheet name to sheet number.
279 # Beware, 0-based index, 1-based count!
281 while (++ii <= nr_of_sheets && ischar (wsh))
282 # Look in first part of the sheet nodeset
283 sh_name = sheets.item(ii-1).getTableNameAttribute ();
284 if (strcmp (sh_name, wsh))
285 # Convert local copy of wsh into a number (pointer)
289 if (ischar (wsh) && nr_of_sheets < 256) newsh = 1; endif
290 else # Sheet index specified
291 if ((ods.changed > 2) || (wsh > nr_of_sheets && wsh < 256)) # Max nr of sheets = 256
294 elseif (wsh <=nr_of_sheets && wsh > 0)
295 # Existing sheet. Count = 1-based, index = 0-based
296 --wsh; sh = sheets.item(wsh);
297 printf ("Writing to sheet %s\n", sh.getTableNameAttribute());
299 error ("oct2ods: illegal sheet number.");
303 # Check size of data array & range / capacity of worksheet & prepare vars
304 [nr, nc] = size (c_arr);
305 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, ods.xtype, ods.filename);
306 --trow; --lcol; # Zero-based row # & col #
307 if (nrows < nr || ncols < nc)
308 warning ("Array truncated to fit in range");
309 c_arr = c_arr(1:nrows, 1:ncols);
312 # Parse data array, setup typarr and throw out NaNs to speed up writing;
313 typearr = spsh_prstype (c_arr, nrows, ncols, ctype, spsh_opts, 0);
314 if ~(spsh_opts.formulas_as_text)
315 # Find formulas (designated by a string starting with "=" and ending in ")")
316 fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1) && strncmp (x(end:end), ")", 1), c_arr);
317 typearr(fptr) = ctype(4); # FORMULA
320 # Prepare worksheet for writing. If needed create new sheet
323 # New spreadsheet. Prepare to use the default 1x1 first sheet.
326 # Other sheets exist, create a new sheet. First the basics
327 sh = java_new ('org.odftoolkit.odfdom.doc.table.OdfTable', odfcont);
328 # Append sheet to spreadsheet ( contentRoot)
329 offsprdsh.appendChild (sh);
330 # Rebuild sheets nodes
331 sheets = xpath.evaluate ("//table:table", odfcont, NODESET);
337 str = sprintf ("Sheet%d", wsh);
338 sh.setTableNameAttribute (str);
340 # Assign name to sheet and change wsh into numeric pointer
341 sh.setTableNameAttribute (wsh);
342 wsh = sheets.getLength () - 1;
344 # Fixup wsh pointer in case of new spreadsheet
345 if (ods.changed > 2) wsh = 0; endif
347 # Add table-column entry for style etc
348 col = sh.addTableColumn ();
349 col.setTableDefaultCellStyleNameAttribute ("Default");
350 col.setTableNumberColumnsRepeatedAttribute (lcol + ncols + 1);
351 col.setTableStyleNameAttribute ("co1");
353 # Build up the complete row & cell structure to cover the data array.
354 # This will speed up processing later
356 # 1. Build empty table row template
357 row = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableRow', odfcont);
358 # Create an empty tablecell & append it to the row
359 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
360 scell = row.appendCell (scell);
361 scell.setTableNumberColumnsRepeatedAttribute (1024);
362 # 2. If needed add empty filler row above the data rows & if needed add repeat count
365 if (trow > 1) row.setTableNumberRowsRepeatedAttribute (trow); endif
367 # 3. Add data rows; first one serves as a template
368 drow = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableRow', odfcont);
370 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
371 drow.appendCell (scell);
372 if (lcol > 1) scell.setTableNumberColumnsRepeatedAttribute (lcol); endif
374 # 4. Add data cell placeholders
375 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
376 drow.appendCell (scell);
378 dcell = scell.cloneNode (1); # Deep copy
379 drow.appendCell (dcell);
381 # 5. Last cell is remaining column counter
382 rest = max (1024 - lcol - ncols);
384 dcell = scell.cloneNode (1); # Deep copy
385 drow.appendCell (dcell);
386 if (rest > 1) dcell.setTableNumberColumnsRepeatedAttribute (rest); endif
388 # Only now add drow as otherwise for each cell an empty table-column is
389 # inserted above the rows (odftoolkit bug?)
392 # In case of a completely new spreadsheet, delete the first initial 1-cell row
393 # But check if it *is* a row...
395 sh.removeChild (drow.getPreviousRow ());
397 # Nothing. Apparently there was only the just appended row.
400 # 6. Row template ready. Copy row template down to cover future array
402 nrow = drow.cloneNode (1); # Deep copy
405 ods.changed = min (ods.changed, 2); # Keep 2 for new spshsht, 1 for existing + changed
408 # Existing sheet. We must be prepared for all situations, incomplete rows,
409 # number-rows/columns-repeated, merged (spanning) cells, you name it.
410 # First explore row buildup of existing sheet using an XPath
411 sh = sheets.item(wsh); # 0 - based
412 str = sprintf ("//table:table[%d]/table:table-row", wsh + 1); # 1 - based
413 trows = xpath.evaluate (str, odfcont, NODESET);
414 nr_of_trows = trows.getLength(); # Nr. of existing table-rows, not data rows!
416 # For the first rows we do some preprocessing here. Similar stuff for cells
417 # i.e. table-cells (columns) is done in the loops below.
418 # Make sure the upper data array row doesn't end up in a nr-rows-repeated row
420 # Provisionally! set start table-row in case "while" & "if" (split) are skipped
421 drow = trows.item(0);
422 rowcnt = 0; trowcnt = 0; # Spreadsheet/ table-rows, resp;
423 while (rowcnt < trow && trowcnt < nr_of_trows)
424 # Count rows & table-rows UNTIL we reach trow
425 ++trowcnt; # Nr of table-rows
427 drow = row.getNextSibling ();
428 repcnt = row.getTableNumberRowsRepeatedAttribute();
429 rowcnt = rowcnt + repcnt; # Nr of spreadsheet rows
431 rsplit = rowcnt - trow;
433 # Apparently a nr-rows-repeated top table-row must be split, as the
434 # first data row seems to be projected in it (1st while condition above!)
435 row.removeAttribute ('table:number-rows-repeated');
436 row.getCellAt (0).removeAttribute ('table:number-columns-repeated');
437 nrow = row.cloneNode (1);
438 drow = nrow; # Future upper data array row
440 row.setTableNumberRowsRepeatedAttribute (repcnt - rsplit);
442 row.removeAttribute ('table:number-rows-repeated');
444 rrow = row.getNextSibling ();
445 sh.insertBefore (nrow, rrow);
447 nrow = nrow.cloneNode (1);
448 sh.insertBefore (nrow, rrow);
451 # New data rows to be added below existing data & table(!) rows, i.e.
452 # beyond lower end of the current sheet. Add filler row and 1st data row
453 row = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableRow', odfcont);
454 drow = row.cloneNode (1); # First data row
455 row.setTableNumberRowsRepeatedAttribute (-rsplit); # Filler row
456 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
457 dcell = scell.cloneNode (1);
458 scell.setTableNumberColumnsRepeatedAttribute (COL_CAP); # Filler cell
459 row.appendCell (scell);
461 drow.appendCell (dcell);
466 # For each row, for each cell, add the data. Expand row/column-repeated nodes
468 row = drow; # Start row; pointer still exists from above stanzas
470 if (~newsh) # Only for existing sheets the next checks should be made
471 # While processing next data rows, fix table-rows if needed
472 if (isempty (row) || (row.getLength () < 1))
473 # Append an empty row with just one empty cell
474 row = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableRow', odfcont);
475 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
476 scell.setTableNumberColumnsRepeatedAttribute (lcol + 1);
477 row.appendCell (scell);
480 # If needed expand nr-rows-repeated
481 repcnt = row.getTableNumberRowsRepeatedAttribute ();
483 row.removeAttribute ('table:number-rows-repeated');
484 # Insert new table-rows above row until our new data space is complete.
485 # Keep handle of upper new table-row as that's where data are added 1st
486 drow = row.cloneNode (1);
487 sh.insertBefore (drow, row);
488 for kk=1:min (repcnt, nrows-ii)
489 nrow = row.cloneNode (1);
490 sh.insertBefore (nrow, row);
492 if (repcnt > nrows-ii+1)
493 row.setTableNumberRowsRepeatedAttribute (repcnt - nrows +ii - 1);
499 # Check if leftmost cell ends up in nr-cols-repeated cell
500 colcnt = 0; tcellcnt = 0; rcellcnt = row.getLength();
501 dcell = row.getCellAt (0);
502 while (colcnt < lcol && tcellcnt < rcellcnt)
503 # Count columns UNTIL we hit lcol
504 ++tcellcnt; # Nr of table-cells counted
506 dcell = scell.getNextSibling ();
507 repcnt = scell.getTableNumberColumnsRepeatedAttribute ();
508 colcnt = colcnt + repcnt; # Nr of spreadsheet cell counted
510 csplit = colcnt - lcol;
512 # Apparently a nr-columns-repeated cell must be split
513 scell.removeAttribute ('table:number-columns-repeated');
514 ncell = scell.cloneNode (1);
516 scell.setTableNumberColumnsRepeatedAttribute (repcnt - csplit);
518 scell.removeAttribute ('table:number-columns-repeated');
520 rcell = scell.getNextSibling ();
521 row.insertBefore (ncell, rcell);
523 ncell = ncell.cloneNode (1);
524 row.insertBefore (ncell, rcell);
527 # New cells to be added beyond current last cell & table-cell in row
528 dcell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
529 scell = dcell.cloneNode (1);
530 dcell.setTableNumberColumnsRepeatedAttribute (-csplit);
531 row.appendCell (dcell);
532 row.appendCell (scell);
536 # Write a row of data from data array, column by column
539 scell = row.getCellAt (lcol + jj - 1);
542 # Apparently end of row encountered. Add cell
543 scell = java_new ('org.odftoolkit.odfdom.doc.table.OdfTableCell', odfcont);
544 scell = row.appendCell (scell);
546 # If needed expand nr-cols-repeated
547 repcnt = scell.getTableNumberColumnsRepeatedAttribute ();
549 scell.removeAttribute ('table:number-columns-repeated');
551 ncell = scell.cloneNode (1);
552 row.insertBefore (ncell, scell.getNextSibling ());
556 # Clear text contents
557 while (scell.hasChildNodes ())
558 tmp = scell.getFirstChild ();
559 scell.removeChild (tmp);
561 scell.removeAttribute ('table:formula');
564 # Empty cell count stuff done. At last we can add the data
565 switch (typearr (ii, jj))
567 scell.setOfficeValueTypeAttribute ('float');
568 scell.setOfficeValueAttribute (c_arr{ii, jj});
570 # Beware, for unpatched-for-booleans java-1.2.7- we must resort to floats
572 # First try the preferred java-boolean way
573 scell.setOfficeValueTypeAttribute ('boolean');
574 scell.removeAttribute ('office:value');
576 scell.setOfficeBooleanValueAttribute (1);
578 scell.setOfficeBooleanValueAttribute (0);
581 # Unpatched java package. Fall back to transferring a float
582 scell.setOfficeValueTypeAttribute ('float');
584 scell.setOfficeValueAttribute (1);
586 scell.setOfficeValueAttribute (0);
590 scell.setOfficeValueTypeAttribute ('string');
591 pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', c_arr{ii, jj});
592 scell.appendChild (pe);
594 # As we don't know the result type, simply remove previous type info.
595 # Once OOo Calc reads it, it'll add the missing attributes
596 scell.removeAttribute ('office:value');
597 scell.removeAttribute ('office:value-type');
598 # Try-catch not strictly needed, there's no formula validator yet
600 scell.setTableFormulaAttribute (c_arr{ii, jj});
601 scell.setOfficeValueTypeAttribute ('string');
602 pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', '#Recalc Formula#');
603 scell.appendChild (pe);
606 scell.setOfficeValueTypeAttribute ('string');
607 pe = java_new ('org.odftoolkit.odfdom.doc.text.OdfTextParagraph', odfcont,'', c_arr{ii, jj});
608 scell.appendChild (pe);
610 case {0 5} # Empty. Clear value attributes
612 scell.removeAttribute ('office:value-type');
613 scell.removeAttribute ('office:value');
615 case 6 # Date (implemented but Octave has no "date" data type - yet?)
616 scell.setOfficeValueTypeAttribute ('date');
617 [hh mo dd hh mi ss] = datevec (c_arr{ii,jj});
618 str = sprintf ("%4d-%2d-%2dT%2d:%2d:%2d", yy, mo, dd, hh, mi, ss);
619 scell.setOfficeDateValueAttribute (str);
620 case 7 # Time (implemented but Octave has no "time" data type)
621 scell.setOfficeValueTypeAttribute ('time');
622 [hh mo dd hh mi ss] = datevec (c_arr{ii,jj});
623 str = sprintf ("PT%2d:%2d:%2d", hh, mi, ss);
624 scell.setOfficeTimeValuettribute (str);
629 scell = scell.getNextSibling ();
633 row = row.getNextSibling ();
638 printf ("%d formula errors encountered - please check input array\n", f_errs);
640 ods.changed = max (min (ods.changed, 2), changed); # Preserve 2 (new file), 1 (existing)
646 #=============================================================================
648 ## Copyright (C) 2010,2011,2012 Philip Nienhuis <prnienhuis _at- users.sf.net>
650 ## This program is free software; you can redistribute it and/or modify it under
651 ## the terms of the GNU General Public License as published by the Free Software
652 ## Foundation; either version 3 of the License, or (at your option) any later
655 ## This program is distributed in the hope that it will be useful, but WITHOUT
656 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
657 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
660 ## You should have received a copy of the GNU General Public License along with
661 ## this program; if not, see <http://www.gnu.org/licenses/>.
663 ## odf3jotk2oct - read ODS spreadsheet data using Java & odftoolkit v 0.8.6+.
664 ## You need proper java-for-octave & odfdom.jar 0.8.6+ & xercesImpl.jar 2.9.1
665 ## in your javaclasspath. For reliable writing odfdom-0.8.6+ is still
666 ## experimental :-( v. 0.7.5 has been tested much more
668 ## Author: Philip Nenhuis <pr.nienhuis at users.sf.net>
669 ## Created: 2010-03-16, after oct2jotk2ods()
671 ## 2010-03-17 Rebuild for odfdom-0.8
672 ## 2010-03-19 Showstopper bug in odfdom-0.8 - getCellByPosition(<address>)
673 ## crashes on rows > #10 !!! Rest seems to work OK, however
674 ## 2010-03-22 First somewhat usable version for odfdom 0.8
675 ## 2010-03-29 Gave up. Writing a new empty sheet works, appending
676 ## data to an existing one can crash virtually anywhere.
677 ## The wait is for odfdom-0.8.+ ....
678 ## 2010-06-05 odfdom 0.8.5 is there, next try....
679 ## 2010-06-## odfdom 0.8.5 dropped, too buggy
680 ## 2010-08-22 odfdom 0.8.6 is there... seems to work with just one bug, easily worked around
681 ## 2010-10-27 Improved file change tracking tru ods.changed
682 ## 2010-11-12 Improved file change tracking tru ods.changed
683 ## 2010-12-08 Bugfixes (obj -> arg L.715; removed stray arg in call to spsh_prstype L.719)
684 ## 2011-03-23 First try of odfdom 0.8.7
685 ## 2012-06-08 Support for odfdom-incubator-0.8.8
687 function [ ods, rstatus ] = oct3jotk2ods (c_arr, ods, wsh, crange, spsh_opts)
691 # Number, Boolean, String, Formula, Empty; Date, Time - last two aren't used
692 ctype = [1, 2, 3, 4, 5, 6, 7];
695 rstatus = 0; changed = 0; newsh = 0;
697 # Get contents and table stuff from the workbook
698 odfcont = ods.workbook; # Use a local copy just to be sure. octave
699 # makes physical copies only when needed (?)
700 odfroot = odfcont.getRootElement ();
701 offsprdsh = ods.app.getContentRoot();
702 if (strcmp (ods.odfvsn, '0.8.7') || strfind (ods.odfvsn, "0.8.8"))
703 spsh = odfcont.getDocument ();
705 spsh = odfcont.getOdfDocument ();
708 # Get some basic spreadsheet data from the pointer using ODFtoolkit
709 autostyles = odfcont.getOrCreateAutomaticStyles();
710 officestyles = ods.app.getOrCreateDocumentStyles();
712 # Parse sheets ("tables") from ODS file
713 sheets = ods.app.getTableList();
714 nr_of_sheets = sheets.size ();
715 # Check user input & find sheet pointer
716 if (~isnumeric (wsh))
718 sh = ods.app.getTableByName (wsh);
719 # We do need a sheet index number...
721 while (ischar (wsh) && ii < nr_of_sheets)
722 sh_nm = sh.getTableName ();
723 if (strcmp (sh_nm, wsh)) wsh = ii + 1; else ++ii; endif
728 if isempty (sh) newsh = 1; endif
730 # Negative sheet number:
731 error (sprintf ("Illegal worksheet nr. %d\n", wsh));
732 elseif (wsh > nr_of_sheets)
735 sh = sheets.get (wsh - 1);
738 # Check size of data array & range / capacity of worksheet & prepare vars
739 [nr, nc] = size (c_arr);
740 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, ods.xtype, ods.filename);
741 --trow; --lcol; # Zero-based row # & col #
742 if (nrows < nr || ncols < nc)
743 warning ("Array truncated to fit in range");
744 c_arr = c_arr(1:nrows, 1:ncols);
747 # Parse data array, setup typarr and throw out NaNs to speed up writing;
748 typearr = spsh_prstype (c_arr, nrows, ncols, ctype, spsh_opts);
749 if ~(spsh_opts.formulas_as_text)
750 # Find formulas (designated by a string starting with "=" and ending in ")")
751 fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1) && strncmp (x(end:end), ")", 1), c_arr);
752 typearr(fptr) = ctype(4); # FORMULA
755 # Prepare spreadsheet for writing (size, etc.). If needed create new sheet
758 # New spreadsheet, use default first sheet
761 # Create a new sheet using DOM API. This part works OK.
762 sh = sheets.get (nr_of_sheets - 1).newTable (spsh, nrows, ncols);
767 str = sprintf ("Sheet%d", wsh);
768 sh.setTableName (str);
771 # Assign name to sheet and change wsh into numeric pointer
772 sh.setTableName (wsh);
774 printf ("Sheet %s added to spreadsheet.\n", wsh);
777 # Add "physical" rows & columns. Spreadsheet max. capacity checks have been done above
778 # Add spreadsheet data columns if needed. Compute nr of extra columns & rows.
779 curr_ncols = sh.getColumnCount ();
780 ii = max (0, lcol + ncols - curr_ncols);
782 nwcols = sh.appendColumn ();
784 nwcols = sh.appendColumns (ii);
787 # Add spreadsheet rows if needed
788 curr_nrows = sh.getRowCount ();
789 ii = max (0, trow + nrows - curr_nrows);
791 nwrows = sh.appendRow ();
793 nwrows = sh.appendRows (ii);
797 # Transfer array data to sheet
800 ocell = sh.getCellByPosition (jj+lcol-1, ii+trow-1);
801 if ~(isempty (ocell )) # Might be spanned (merged), hidden, ....
802 # Number, String, Boolean, Date, Time
804 switch typearr (ii, jj)
805 case {1, 6, 7} # Numeric, Date, Time
806 ocell.setDoubleValue (c_arr{ii, jj});
807 case 2 # Logical / Boolean
808 # ocell.setBooleanValue (c_arr{ii, jj}); # Doesn't work, bug in odfdom 0.8.6
809 # Bug workaround: 1. Remove all cell contents
810 ocell.removeContent ();
811 # 2. Switch to TableTableElement API
812 tocell = ocell.getOdfElement ();
813 tocell.setAttributeNS ('office', 'office:value-type', 'boolean');
814 # 3. Add boolean-value attribute.
815 # This is only accepted in TTE API with a NS tag (actual bug, IMO)
817 tocell.setAttributeNS ('office', 'office:boolean-value', 'true');
819 tocell.setAttributeNS ('office', 'office:boolean-value', 'false');
822 ocell.setStringValue (c_arr{ii, jj});
824 ocell.setFormula (c_arr{ii, jj});
825 otherwise # 5, empty and catch-all
826 # The above is all octave has to offer & java can accept...
837 ods.changed = max (min (ods.changed, 2), changed); # Preserve 2 (new file), 1 (existing)
844 #=============================================================================
846 ## Copyright (C) 2009,2010,2011,2012 Philip Nienhuis <pr.nienhuis at users.sf.net>
848 ## This program is free software; you can redistribute it and/or modify it under
849 ## the terms of the GNU General Public License as published by the Free Software
850 ## Foundation; either version 3 of the License, or (at your option) any later
853 ## This program is distributed in the hope that it will be useful, but WITHOUT
854 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
855 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
858 ## You should have received a copy of the GNU General Public License along with
859 ## this program; if not, see <http://www.gnu.org/licenses/>.
861 ## ods2oct - write data from octave to an ODS spreadsheet using the
862 ## jOpenDocument interface.
864 ## Author: Philip Nienhuis
865 ## Created: 2009-12-13
866 ## First usable version: 2010-01-14
868 ## 2010-03-17 Adapted to simplified calccelladdress argument list
869 ## 2010-04-24 Added ensureColumnCount & ensureRowCount
870 ## Fixed a few bugs with top row & left column indexes
871 ## Fixed a number of other stupid bugs
872 ## Added check on NaN before assigning data value to sprdsh-cell
873 ## 2010-06-01 Checked logic. AFAICS all should work with upcoming jOpenDocument 1.2b4;
874 ## in 1.2b3 adding a newsheet always leaves an incomplete upper row;
875 ## supposedly (hopefully) that will be fixed in 1.2b4.
876 ## '' Added check for jOpenDocument version. Adding sheets only works for
877 ## 1.2b3+ (barring bug above)
878 ## 2010-06-02 Fixed first sheet remaining in new spreadsheets
879 ## 2010-08-01 Added option for crange to be only topleft cell address
881 ## 2010-08-13 Fixed bug of ignoring text sheet name in case of new spreadsheet
882 ## 2010-08-15 Fixed bug with invalid first sheet in new spreadsheets
883 ## 2010-10-27 Improved file change tracking tru ods.changed
884 ## 2010-11-12 Improved file change tracking tru ods.changed
885 ## 2012-02-26 Write logicals as doubles (bug in jOpenDocument, would write as text)
887 function [ ods, rstatus ] = oct2jod2ods (c_arr, ods, wsh, crange)
889 rstatus = 0; sh = []; changed = 0;
891 # Get worksheet. Use first one if none given
892 if (isempty (wsh)) wsh = 1; endif
893 sh_cnt = ods.workbook.getSheetCount ();
896 error ("Sheet number out of range of ODS specification (>1024)");
897 elseif (wsh > sh_cnt)
898 error ("Sheet number (%d) larger than number of sheets in file (%d)\n", wsh, sh_cnt);
901 sh = ods.workbook.getSheet (wsh);
903 # Sheet number wsh didn't exist yet
904 wsh = sprintf ("Sheet%d", wsh+1);
905 elseif (ods.changed > 2)
906 sh.setName ('Sheet1');
911 # wsh is now either a 0-based sheet no. or a string. In latter case:
912 if (isempty (sh) && ischar (wsh))
913 sh = ods.workbook.getSheet (wsh);
915 # Still doesn't exist. Create sheet
918 # 1st "new" -unnamed- sheet has already been made when creating the spreadsheet
919 sh = ods.workbook.getSheet (0);
923 # For existing spreadsheets
924 printf ("Adding sheet '%s'\n", wsh);
925 sh = ods.workbook.addSheet (sh_cnt, wsh);
929 error ("jOpenDocument v. 1.2b2 does not support adding sheets - upgrade to v. 1.2b3\n");
934 [nr, nc] = size (c_arr);
935 if (isempty (crange))
940 elseif (isempty (strfind (deblank (crange), ':')))
941 [dummy1, dummy2, dummy3, trow, lcol] = parse_sp_range (crange);
944 # Row/col = 0 based in jOpenDocument
945 trow = trow - 1; lcol = lcol - 1;
947 [dummy, nrows, ncols, trow, lcol] = parse_sp_range (crange);
948 # Row/col = 0 based in jOpenDocument
949 trow = trow - 1; lcol = lcol - 1;
952 if (trow > 65535 || lcol > 1023)
953 error ("Topleft cell beyond spreadsheet limits (AMJ65536).");
955 # Check spreadsheet capacity beyond requested topleft cell
956 nrows = min (nrows, 65536 - trow); # Remember, lcol & trow are zero-based
957 ncols = min (ncols, 1024 - lcol);
958 # Check array size and requested range
959 nrows = min (nrows, nr);
960 ncols = min (ncols, nc);
961 if (nrows < nr || ncols < nc) warning ("Array truncated to fit in range"); endif
963 if (isnumeric (c_arr)) c_arr = num2cell (c_arr); endif
965 # Ensure sheet capacity is large enough to contain new data
966 try # try-catch needed to work around bug in jOpenDocument v 1.2b3 and earlier
967 sh.ensureColumnCount (lcol + ncols); # Remember, lcol & trow are zero-based
968 catch # catch is needed for new empty sheets (first ensureColCnt() hits null ptr)
969 sh.ensureColumnCount (lcol + ncols);
970 # Kludge needed because upper row is defective (NPE jOpenDocument bug). ?Fixed in 1.2b4?
972 # Shift rows one down to avoid defective upper row
974 printf ("Info: empy upper row above data added to avoid JOD bug.\n");
977 sh.ensureRowCount (trow + nrows);
979 # Write data to worksheet
982 val = c_arr {ii, jj};
983 if ((isnumeric (val) && ~isnan (val)) || ischar (val) || islogical (val))
984 # FIXME: jOpenDocument doesn't really support writing booleans (doesn't set OffValAttr)
985 if (islogical (val)); val = double (val); endif
987 sh.getCellAt (jj + lcol - 1, ii + trow - 1).clearValue();
988 jcell = sh.getCellAt (jj + lcol - 1, ii + trow - 1).setValue (val);
991 # No panic, probably a merged cell
992 # printf (sprintf ("Cell skipped at (%d, %d)\n", ii+lcol-1, jj+trow-1));
999 ods.changed = max (min (ods.changed, 2), changed); # Preserve 2 (new file), 1 (existing)
1006 ## Copyright (C) 2011,2012 Philip Nienhuis <prnienhuis@users.sf.net>
1008 ## This program is free software; you can redistribute it and/or modify it under
1009 ## the terms of the GNU General Public License as published by the Free Software
1010 ## Foundation; either version 3 of the License, or (at your option) any later
1013 ## This program is distributed in the hope that it will be useful, but WITHOUT
1014 ## ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
1015 ## FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
1018 ## You should have received a copy of the GNU General Public License along with
1019 ## this program; if not, see <http://www.gnu.org/licenses/>.
1021 ## oct2uno2ods - transfer data to ods or xls file using Java/UNO bridge
1022 ## with OpenOffice_org & clones
1024 ## Author: Philip Nienhuis <prnienhuis@users.sf.net>
1025 ## Created: 2011-05-15
1027 ## 2011-summer <many many improvements>
1028 ## 2011-09-08 Stylistic changes
1029 ## 2011-09-18 Adapted sh_names type to LO 3.4.1
1030 ## 2011-09-23 Removed stray debug statements
1031 ## 2012-02-25 Work around LO3.5rc1 Java Runtime Exception bug L.1043+
1032 ## 2012-02-26 Bug fix when adding sheets near L.1101 (wrong if-else-end construct).
1034 function [ ods, rstatus ] = oct2uno2ods (c_arr, ods, wsh, crange, spsh_opts)
1038 ctype = [1, 2, 3, 4, 5]; # Float, Logical, String, Formula, Empty
1040 # Get handle to sheet, create a new one if needed
1041 sheets = ods.workbook.getSheets ();
1042 sh_names = sheets.getElementNames ();
1043 if (! iscell (sh_names))
1044 # Java array (LibreOffice 3.4.+); convert to cellstr
1045 sh_names = char (sh_names);
1047 sh_names = {sh_names};
1050 # Clear default 2 last sheets in case of a new spreadsheet file
1051 if (ods.changed > 2)
1052 ii = numel (sh_names);
1054 shnm = sh_names{ii};
1055 # Work around LibreOffice 3.5.rc1 bug (Java Runtime Exception)
1057 sheets.removeByName (shnm);
1061 # Give remaining sheet a name
1062 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XSpreadsheet');
1063 sh = sheets.getByName (sh_names{1}).getObject.queryInterface (unotmp);
1064 if (isnumeric (wsh)); wsh = sprintf ("Sheet%d", wsh); endif
1065 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.container.XNamed');
1066 sh.queryInterface (unotmp).setName (wsh);
1069 # Check sheet pointer
1070 # FIXME sheet capacity check needed. How many can fit in an OOo sprsh.file?
1071 if (isnumeric (wsh))
1073 error ("Illegal sheet index: %d", wsh);
1074 elseif (wsh > numel (sh_names))
1075 # New sheet to be added. First create sheet name but check if it already exists
1076 shname = sprintf ("Sheet%d", numel (sh_names) + 1);
1077 jj = strmatch (wsh, sh_names);
1079 # New sheet name already in file, try to create a unique & reasonable one
1080 ii = 1; filler = ''; maxtry = 5;
1081 while (ii <= maxtry)
1082 shname = sprintf ("Sheet%s%d", [filler "_"], numel (sh_names + 1));
1083 if (isempty (strmatch (wsh, sh_names)))
1089 if (ii > maxtry + 1)
1090 error ("Could not add sheet with a unique name to file %s");
1096 # turn wsh index into the associated sheet name
1097 wsh = sh_names (wsh);
1100 # wsh is a sheet name. See if it exists already
1101 if (isempty (strmatch (wsh, sh_names)))
1102 # Not found. New sheet to be added
1107 # Add a new sheet. Sheet index MUST be a Java Short object
1108 shptr = java_new ("java.lang.Short", sprintf ("%d", numel (sh_names) + 1));
1109 sh = sheets.insertNewByName (wsh, shptr);
1111 # At this point we have a valid sheet name. Use it to get a sheet handle
1112 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.sheet.XSpreadsheet');
1113 sh = sheets.getByName (wsh).getObject.queryInterface (unotmp);
1116 # Check size of data array & range / capacity of worksheet & prepare vars
1117 [nr, nc] = size (c_arr);
1118 [topleft, nrows, ncols, trow, lcol] = spsh_chkrange (crange, nr, nc, ods.xtype, ods.filename);
1119 --trow; --lcol; # Zero-based row # & col #
1120 if (nrows < nr || ncols < nc)
1121 warning ("Array truncated to fit in range");
1122 c_arr = c_arr(1:nrows, 1:ncols);
1125 # Parse data array, setup typarr and throw out NaNs to speed up writing;
1126 typearr = spsh_prstype (c_arr, nrows, ncols, ctype, spsh_opts, 0);
1127 if ~(spsh_opts.formulas_as_text)
1128 # Find formulas (designated by a string starting with "=" and ending in ")")
1129 fptr = cellfun (@(x) ischar (x) && strncmp (x, "=", 1), c_arr);
1130 typearr(fptr) = ctype(4); # FORMULA
1133 # Transfer data to sheet
1137 XCell = sh.getCellByPosition (lcol+jj-1, trow+ii-1);
1138 switch typearr(ii, jj)
1140 XCell.setValue (c_arr{ii, jj});
1141 case 2 # Logical. Convert to float
1142 XCell.setValue (double (c_arr{ii, jj}));
1144 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
1145 XCell.queryInterface (unotmp).setString (c_arr{ii, jj});
1147 if (spsh_opts.formulas_as_text)
1148 unotmp = java_new ('com.sun.star.uno.Type', 'com.sun.star.text.XText');
1149 XCell.queryInterface (unotmp).setString (c_arr{ii, jj});
1151 XCell.setFormula (c_arr{ii, jj});
1158 printf ("Error writing cell %s (typearr() = %d)\n", calccelladdress(trow+ii, lcol+jj), typearr(ii, jj));
1164 ods.changed = max (min (ods.changed, 2), changed); # Preserve 2 (new file), 1 (existing)