1## Copyright (C) 2009-2021 Philip Nienhuis 2## 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 6## version. 7## 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 11## details. 12## 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/>. 15 16## __JOD_spsh2oct__ - get data out of an ODS spreadsheet into octave using jOpenDocument. 17## Watch out, no error checks, and spreadsheet formula error results 18## are conveyed as 0 (zero). 19## 20## Author: Philip Nienhuis <prnienhuis@users.sf.net> 21## Created: 2009-12-13 22 23function [ rawarr, ods, rstatus] = __JOD_spsh2oct__ (ods, wsh, crange, spsh_opts) 24 25 persistent months; 26 months = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", ... 27 "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"}; 28 persistent octv = compare_versions (version, "4.1.0", ">="); 29 30 rstatus = 0; 31 ## Check jOpenDocument version 32 sh = ods.workbook.getSheet (0); 33 cl = sh.getCellAt (0, 0); 34 if (ods.odfvsn >= 3) 35 ## 1.2b3+ has public getValueType () 36 persistent ctype; 37 if (isempty (ctype)) 38 BOOLEAN = char (__java_get__ ("org.jopendocument.dom.ODValueType", "BOOLEAN")); 39 CURRENCY = char (__java_get__ ("org.jopendocument.dom.ODValueType", "CURRENCY")); 40 DATE = char (__java_get__ ("org.jopendocument.dom.ODValueType", "DATE")); 41 FLOAT = char (__java_get__ ("org.jopendocument.dom.ODValueType", "FLOAT")); 42 PERCENTAGE = char (__java_get__ ("org.jopendocument.dom.ODValueType", "PERCENTAGE")); 43 STRING = char (__java_get__ ("org.jopendocument.dom.ODValueType", "STRING")); 44 TIME = char (__java_get__ ("org.jopendocument.dom.ODValueType", "TIME")); 45 endif 46 endif 47 48 ## Sheet INDEX starts at 0 49 if (isnumeric (wsh)); 50 --wsh; 51 endif 52 ## Check if sheet exists. If wsh = numeric, nonexistent sheets throw errors. 53 try 54 sh = ods.workbook.getSheet (wsh); 55 catch 56 error ("xls/ods2oct: illegal sheet number (%d) requested for file %s\n", ... 57 wsh+1, ods.filename); 58 end_try_catch 59 ## If wsh = string, nonexistent sheets yield empty results 60 if (isempty (sh)) 61 error ("xls/ods2oct: no sheet called '%s' present in file %s\n", wsh, ... 62 ods.filename); 63 endif 64 65 ## Either parse (given cell range) or prepare (unknown range) help variables 66 if (isempty (crange)) 67 if (ods.odfvsn < 3) 68 error ("xls/ods2oct: no empty read range allowed in jOpenDocument version 1.2b2") 69 else 70 if (isnumeric (wsh)); wsh = wsh + 1; endif 71 [ trow, brow, lcol, rcol ] = getusedrange (ods, wsh); 72 nrows = brow - trow + 1; ## Number of rows to be read 73 ncols = rcol - lcol + 1; ## Number of columns to be read 74 endif 75 else 76 [dummy, nrows, ncols, trow, lcol] = parse_sp_range (crange); 77 ## Check ODS column limits 78 if (lcol > 1024 || trow > 65536) 79 error ("xls/ods2oct: invalid range; max 1024 columns & 65536 rows."); 80 endif 81 ## Truncate range silently if needed 82 rcol = min (lcol + ncols - 1, 1024); 83 ncols = min (ncols, 1024 - lcol + 1); 84 nrows = min (nrows, 65536 - trow + 1); 85 brow = trow + nrows - 1; 86 endif 87 ## Create storage for data content 88 rawarr = cell (nrows, ncols); 89 90 if (ods.odfvsn >= 3) 91 ## Version 1.2b3+ 92 for ii=1:nrows 93 for jj = 1:ncols 94 try 95 scell = sh.getCellAt (lcol+jj-2, trow+ii-2); 96 if (spsh_opts.formulas_as_text) 97 ## Check if it is a formula =[.C5]+[.C7] 98 frml = scell.getFormula (); 99 if (! isempty (frml)) 100 ## For older jOpenDocument than 1.4.x 101 frml = strrep (frml, "of:", ""); 102 rawarr{ii, jj} = regexprep (frml, '\[\.(\$?[A-Z]+\$?[0-9]+)\]', '$1'); 103 sctype = "FORMULA"; 104 else 105 sctype = char (scell.getValueType ()); 106 endif 107 else 108 sctype = char (scell.getValueType ()); 109 endif 110 switch sctype 111 ## try both char value (Octave) and ODValuetype (jOpenDocument) for 112 ## backward compatibility with older jOpenDocument versions 113 case { FLOAT, " FLOAT", CURRENCY, "CURRENCY", PERCENTAGE, "PERCENTAGE" } 114 ## Next IF reqd. as temporary workaround for bugs #48013 and #48591 115 if (octv) 116 rawarr{ii, jj} = scell.getValue ().doubleValue (); 117 else 118 rawarr{ii, jj} = scell.getValue (); 119 endif 120 case { BOOLEAN, "BOOLEAN" } 121 rawarr {ii, jj} = scell.getValue () == 1; 122 case { STRING, "STRING" } 123 rawarr{ii, jj} = scell.getValue(); 124 case { DATE, "DATE" } 125 tmp = strsplit (char (scell.getValue ()), " "); 126 yy = str2num (tmp{6}); 127 mo = find (ismember (months, toupper (tmp{2})) == 1); 128 dd = str2num (tmp{3}); 129 hh = str2num (tmp{4}(1:2)); 130 mi = str2num (tmp{4}(4:5)); 131 ss = str2num (tmp{4}(7:8)); 132 rawarr{ii, jj} = datenum (yy, mo, dd, hh, mi, ss); 133 case { TIME, "TIME" } 134 tmp = strsplit (char (scell.getValue ().getTime ()), " "); 135 hh = str2num (tmp{4}(1:2)) / 24.0; 136 mi = str2num (tmp{4}(4:5)) / 1440.0; 137 ss = str2num (tmp{4}(7:8)) / 86600.0; 138 rawarr {ii, jj} = hh + mi + ss; 139 case "FORMULA" 140 ## Do nothing, was catched above the switch stmt 141 otherwise 142 ## Workaround for sheets written by jOpenDocument (no value-type attrb): 143 if (! isempty (scell.getValue) ) 144 ## FIXME Assume cell contains string if there's a text attr. 145 ## But it could be BOOLEAN too... 146 rawarr{ii, jj} = scell.getValue(); 147 if (strfind (char (scell), "<text:")) 148 sctype = STRING; 149 else 150 ## Numeric 151 ## Next IF reqd. as temporary workaround for bugs #48013 and #48591 152 if (octv) 153 rawarr{ii, jj} = scell.getValue ().doubleValue (); 154 else 155 rawarr{ii, jj} = scell.getValue (); 156 endif 157 endif 158 endif 159 ## Nothing 160 endswitch 161 catch 162 ## Probably a merged cell, just skip 163 end_try_catch 164 endfor 165 endfor 166 else ## ods.odfvsn == 3 167 ## 1.2b2 168 for ii=1:nrows 169 for jj = 1:ncols 170 celladdress = calccelladdress (trow+ii-1, lcol+jj-1); 171 try 172 val = sh.getCellAt (celladdress).getValue (); 173 catch 174 ## No panic, probably a merged cell 175 val = {}; 176 end_try_catch 177 if (! isempty (val)) 178 if (ischar (val)) 179 ## Text string 180 rawarr(ii, jj) = val; 181 elseif (isnumeric (val)) 182 ## Boolean 183 if (val) 184 rawarr(ii, jj) = true; 185 else; 186 rawarr(ii, jj) = false; 187 endif 188 else 189 try 190 val = sh.getCellAt (celladdress).getValue ().doubleValue (); 191 rawarr(ii, jj) = val; 192 catch 193 val = char (val); 194 if (isempty (val)) 195 ## Probably empty Cell 196 else 197 ## Maybe date / time value. Dirty hack to get values: 198 mo = find (strcmp (toupper (val(5:7)), months)); 199 dd = str2num (val(9:10)); 200 yy = str2num (val(25:end)); 201 hh = str2num (val(12:13)); 202 mm = str2num (val(15:16)); 203 ss = str2num (val(18:19)); 204 rawarr(ii, jj) = datenum (yy, mo, dd, hh, mm,ss); 205 endif 206 end_try_catch 207 endif 208 endif 209 endfor 210 endfor 211 212 endif 213 214 ## Keep track of data rectangle limits 215 ods.limits = [lcol, rcol; trow, brow]; 216 rstatus = 1; 217 218endfunction 219