## Copyright (C) 2013,2014 Markus Bergholz
## Parts Copyright (C) 2013-2017 Philip Nienhuis
##
## This program is free software; you can redistribute it and/or modify
## it under the terms of the GNU General Public License as published by
## the Free Software Foundation; either version 3 of the License, or
## (at your option) any later version.
##
## This program is distributed in the hope that it will be useful,
## but WITHOUT ANY WARRANTY; without even the implied warranty of
## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
## GNU General Public License for more details.
##
## You should have received a copy of the GNU General Public License
## along with Octave; see the file COPYING. If not, see
## .
## -*- texinfo -*-
## @deftypefn {Function File} [ @var{raw}, @var{xls}, @var rstatus} ] = __OCT_xlsx2oct__ (@var{xlsx}, @var{wsh}, @var{range}, @spsh_opts)
## Internal function for reading data from an xlsx worksheet
##
## @seealso{}
## @end deftypefn
## Author: Markus Bergholz
## Created: 2013-10-04
function [ raw, xls, rstatus ] = __OCT_xlsx2oct__ (xls, wsh, crange="", spsh_opts)
printf ("separate regexps\n");
## spsh_opts is guaranteed to be filled by caller
## If a worksheet if given, check if it's given by a name (string) or a number
if (ischar (wsh))
## Search for requested sheet name
id = find (strcmp (xls.sheets.sh_names, wsh));
if (isempty (id))
error ("xls2oct: cannot find sheet '%s' in file %s", wsh, xls.filename);
else
wsh = id;
endif
elseif (wsh > numel (xls.sheets.sh_names))
error ("xls2oct: worksheet number %d > number of worksheets in file (%d)", wsh, numel (xls.sheets.sh_names));
elseif (wsh < 1)
warning ("xls2oct: illegal worksheet number (%d) - worksheet #1 assumed\n", wsh);
endif
## Prepare to open requested worksheet file in subdir xl/ .
## Note: Windows accepts forward slashes
rawsheet = fopen (sprintf ('%s/xl/worksheets/sheet%d.xml', xls.workbook, wsh));
if (rawsheet <= 0)
# Try to open sheet from r:id in worksheets.rels.xml
wsh = xls.sheets.rels( xls.sheets.rels(:,1) == id ,2);
rawsheet = fopen (sprintf ('%s/xl/worksheets/sheet%d.xml', xls.workbook, wsh));
if (rawsheet <= 0)
error ("Couldn't open worksheet xml file sheet%d.xml\n", wsh);
endif
else
## Get data
rawdata = fread (rawsheet, "char=>char").';
fclose (rawsheet);
## Strings
try
fid = fopen (sprintf ("%s/xl/sharedStrings.xml", xls.workbook));
strings = fread (fid, "char=>char").';
fclose (fid);
catch
## No sharedStrings.xml; implies no "fixed" strings (computed strings can
## still be present)
strings = "";
end_try_catch
endif
rstatus = 0;
## General note for tuning: '"([^"]*)"' (w/o single quotes) could be faster
## than '"(.*?)"'
## (http://stackoverflow.com/questions/2503413/regular-expression-to-stop-at-first-match comment #7)
## As to requested subranges: it's complicated to extract just part of a sheet;
## either way the entire sheet would need to be scanned for cell addresses
## before one can know what part of the sheet XML the requested range lives.
## In addition the endpoint cells of that range may not exist in the sheet XML
## (e.g., if they're empty).
## So we read *all* data and in the end just return the requested rectangle.
## In below regexps, we ignore "cm" and "ph" tags immediately after tag. As soon as we hit them in the wild
## these can be added (at the cost of speed performance).
%keyboard
## 1. Get all numbers, including double formula results. Below regexp also
## catches missing t="n" tags.
% Original combined regexp
% valn = cell2mat(regexp (rawdata, ']*?(?: t="n".*?>|>| s="\d+?">)(?:|/>))?(.*?)', "tokens"));
% Separate regexps for each XML syntax
valn = cell2mat (regexp (rawdata, ']*? t="n".*?>(?:)?(.*?)', "tokens"));
valn = [valn cell2mat(regexp (rawdata, ']*? t="n".*?>(?:)?(.*?)', "tokens"))];
valn = [valn cell2mat(regexp (rawdata, ']*?>(?:)?(.*?)', "tokens"))];
valn = [valn cell2mat(regexp (rawdata, ']*?>(?:)?(.*?)', "tokens"))];
valn = [valn cell2mat(regexp (rawdata, ']*? s="\d+?">(?:)?(.*?)', "tokens"))];
valn = [valn cell2mat(regexp (rawdata, ']*? s="\d+?">(?:)?(.*?)', "tokens"))];
## Check if we have a reshapable array
if (! rem (numel (valn), 2) == 0)
warning ("Error reading numeric data from sheet %d", wsh);
val = cell (0, 2);;
elseif (! isempty (valn))
val = reshape (valn, 2, [])';
val(:, 2) = num2cell (str2double (val(:, 2)));
endif
clear valn;
valb = cell2mat (regexp (rawdata, ']*?(?: t="b")>(?:|/>))?(.*?)', "tokens"));
if (! rem (numel (val), 2) == 0)
warning ("Error reading boolean data from sheet %d", wsh);
elseif (! isempty (valb))
valb = reshape(valb, 2, [])';
id = find (str2double (valb(:, 2)));
valb(:, 2) = false;
valb(id, 2) = true;
val = [val; valb];
endif
clear valb;
vald = cell2mat (regexp (rawdata, ']*?(?: t="d")>(?:|/>))?(.*?)', "tokens"));
if (! rem (numel (val), 2) == 0)
warning ("Error reading date/time from sheet %d", wsh);
elseif (! isempty (vald))
vald = reshape(valb, 2, [])';
## Process date nodes
vald = [vald(:, 1) datenum(vald(:, 2))];
val = [val; vald];
endif
clear vald;
## 2. String / text formulas (cached results are in this sheet; fixed strings
## in )
## 2.A Formulas
if (spsh_opts.formulas_as_text)
## Get formulas themselves as text strings. Formulas can have a
## 't="str"' attribute. Keep starting '>' for next line
valf1 = cell2mat (regexp (rawdata, ']*?(?: t="str").*?>.*?)(?:.*?)?', "tokens"));
if (! isempty (valf1) && rem (numel (valf1), 2) == 0)
valf1 = reshape (valf1, 2, [])';
valf1(:, 2) = regexprep (valf1(:, 2), "^>", "=");
## Formulas start with '=' so:
val = [val; valf1];
endif
clear valf1;
else
%% valf2 = cell2mat (regexp (rawdata, ']*(?: t="str")[^>]*>(?:(?:]*>[^<]*)|(?:]*/>))(?:]*>([^<]*))?', "tokens"));
valf2 = cell2mat (regexp (rawdata, ']*?(?: t="str").*?>(?:.*?)(?:(.*?))?', "tokens"));
if (! isempty (valf2) && rem (numel (valf2), 2) == 0)
valf2 = reshape (valf2, 2, [])';
val = [val; valf2];
endif
clear valf2;
endif
## 2.B. Strings
if (! isempty (strings))
## Extract string values. May be much more than present in current sheet
strings = regexp (strings, ']*>.*?', "match");
ctext = cell (numel (strings));
if (! isempty (strings))
for n = 1:numel (strings)
ctext{n, 1} = cell2mat (cell2mat (regexp (strings{1, n}, ']*>(.*?)', "tokens")));
end
## Pointers into sharedStrings.xml. "Hard" (fixed) strings have 't="s"' attribute
## Get addresses & pointers. Those pointers are zero-based, so:
vals = cell2mat (regexp (rawdata, ']*?(?: t="s").*?>(\d+)', "tokens"));
if (! isempty (vals) && rem (numel (vals), 2) == 0)
vals = reshape (vals, 2, [])';
## Get actual values. Watch out for empty strings
vals = [vals(:, 1) ctext(str2double (vals(:, 2)) + 1)];
ids = cellfun (@isempty, vals);
if (any (ids))
vals(ids) = {""};
endif
val = [val; vals];
endif
endif
clear vals;
endif
## 2.C. Inline strings
vali = cell2mat (regexp (rawdata, ']*?(?: t="inlineStr").*?>(.*?)', "tokens"));
if (! isempty (vali) && rem (numel (vali), 2) == 0)
vali = reshape (vali, 2, [])';
val = [val; vali];
endif
clear vali;
## If val is empty, sheet is empty
if (isempty (val))
xls.limits = [];
raw = {};
return
endif
## 3. Prepare for assigning extracted values to output cell array
## Get the row numbers (currently supported from 1 to 999999)
vi.row = str2double (cell2mat (regexp (val(:, 1), '(\d+|\d+\d+|\d+\d+\d+|\d+\d+\d+\d+|\d+\d+\d+\d+\+d|\d+\d+\d+\d+\d+\d+)?', "match"))')';
## Get the column characters (A to ZZZ) (that are more than 18k supported cols)
vi.alph = cell2mat (regexp (val(:, 1), '([A-Za-z]+|[A-Za-z]+[A-Za-z]+|[A-Za-z]+[A-Za-z]+[A-Za-z]+)?', "match"));
idx.all = val(:, 1);
if (0 < numel (idx.all))
idx.num = str2double (cell2mat (regexp (idx.all, '(\d+|\d+\d+|\d+\d+\d+|\d+\d+\d+\d+|\d+\d+\d+\d+\+d|\d+\d+\d+\d+\d+\d+)?', "match"))')';
idx.alph = cell2mat (regexp (idx.all, '([A-Za-z]+|[A-Za-z]+[A-Za-z]+|[A-Za-z]+[A-Za-z]+[A-Za-z]+)?', "match"));
idx.alph = double (cell2mat (cellfun (@col2num, vi.alph, "UniformOutput", 0)));
else
## To prevent warnings or errors while calculating corresponding NaN matrix
idx.num = [];
idx.alph = [];
end
## Transform column character to column number
## A -> 1; C -> 3, AB -> 28 ...
vi.col = double (cell2mat (cellfun (@col2num, vi.alph, "UniformOutput", 0)));
## Find data rectangle limits
idx.mincol = min ([idx.alph; vi.col]);
idx.minrow = min ([idx.num; vi.row]);
idx.maxrow = max ([idx.num; vi.row]);
idx.maxcol = max ([idx.alph; vi.col]);
## Convey limits of data rectangle to xls2oct. Must be done here as first start
xls.limits = [idx.mincol, idx.maxcol; idx.minrow, idx.maxrow];
## column adjustment when first number or formula don't begin in first column
if (1 < idx.mincol)
vi.col = vi.col - (idx.mincol - 1);
endif
## row adjustment when first number or formular don't begin in first row
if (1 < idx.minrow)
vi.row = vi.row - (idx.minrow - 1);
endif
## Initialize output cell array
raw = cell (idx.maxrow - idx.minrow + 1, idx.maxcol - idx.mincol + 1);
## get logical indices for 'val' from 'valraw' positions in NaN matrix
vi.idx = sub2ind (size (raw), (vi.row), (vi.col));
## set values to the corresponding indizes in final cell matrix
raw(vi.idx) = val(:, 2);
## Process requested cell range argument
if (! isempty (crange))
## Extract only the requested cell rectangle (see comments higher up)
[~, nr, nc, tr, lc] = parse_sp_range (crange);
xls.limits = [max(idx.mincol, lc), min(idx.maxcol, lc+nc-1); ...
max(idx.minrow, tr), min(idx.maxrow, tr+nr-1)];
## Correct spreadsheet locations for lower right shift of raw
rc = idx.minrow - 1;
cc = idx.mincol - 1;
raw = raw(xls.limits(2, 1)-rc : xls.limits(2, 2)-rc, ...
xls.limits(1, 1)-cc : xls.limits(1, 2)-cc);
endif
if (! isempty (val))
rstatus = 1;
endif
endfunction