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