## Copyright (C) 2013,2014 Markus Bergholz ## Parts Copyright (C) 2014-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 ## this program; if not, see . ## -*- texinfo -*- ## xlsxwrite function pure written in octave - no dependencies are needed. ## tested with MS Excel 2010, Gnumeric, LibreOffice 4.x ## ## usage: ## __OCT_oct2xlsx__(filename, matrix, wsh, range, spsh_opts) ## @end deftypefn ## Author: Markus Bergholz ## Amended by Philip Nienhuis ## 2013/11/08 - Initial Release function [xls, rstatus] = __OCT_oct2xlsx__ (arrdat, xls, wsh=1, crange="", spsh_opts, obj_dims) ## Analyze worksheet parameter & determine if new sheet is required new_sh = 0; if (xls.changed == 3) ## Pristine spreadsheet file new_sh = 1; ## Replace default sheet name if (ischar (wsh)) wsh_string = xls.sheets.sh_names(1) = wsh; elseif (isnumeric (wsh)) wsh_string = xls.sheets.sh_names(1) = sprintf ("Sheet%d", wsh); endif wsh_number = 1; elseif (ischar (wsh)) if (length (wsh) > 31) error ("Worksheet name longer than 31 characters is not supported by Excel\n"); endif wsh_number = find (strcmp (wsh, xls.sheets.sh_names)); if (isempty (wsh_number)) ## Worksheet not in stack; create a new sheet new_sh = 1; wsh_number = numel (xls.sheets.sh_names) + 1; xls.sheets.sh_names(end+1) = wsh; endif wsh_string = wsh; elseif (isnumeric (wsh)) if (wsh > numel (xls.sheets.sh_names)) ## New worksheet new_sh = 1; ## Default sheet name wsh_string = sprintf ("Sheet%d", wsh); ## Name may already be in use... append underscores while (! isempty (find (strcmp (wsh_string, xls.sheets.sh_names))) && ... length (wsh_string <= 31)) wsh_string = strrep (wsh_string, "Sheet", "Sheet_"); endwhile if (length (wsh_string) > 31) error ("oct2xls: cannot add worksheet with a unique name\n"); endif ## The sheet index number can't leave a gap in the stack, so: wsh_number = numel (xls.sheets.sh_names) + 1; xls.sheets.sh_names(end+1) = wsh_string; else wsh_number = wsh; endif endif if (new_sh) rawarr = {}; lims = []; else ## Get all data in sheet and row/column limits [rawarr, xls] = __OCT_xlsx2oct__ (xls, wsh, "", spsh_opts); lims = xls.limits; endif ## Merge old and new data. Provisionally allow empty new data to wipe old data [rawarr, lims, onr, onc] = __OCT_merge_data__ (rawarr, lims, arrdat, obj_dims); ## What needs to be done: ## - Find out worksheet number (easy, wsh_number). Relates 1:1 to rId ## - Write data to /xl/worksheets/sheet.xml ## * For each string, check (persistent var) if sharedStrings.xml exists ## > If not, create it. ## * For each string check if it is in /sharedStrings.xml ## > if YES, put pointer in new worksheet ## > if NO, add node in sharedStrings.xml and pointer in new worksheet ## - If needed (new file) update /workbook.xml w. sheet name & a sheetId ## higher than any existing sheetId ## - Update workbook.xml.rels ## Write data to worksheet file. If status > 1, new sharedStrings file entries are required [xls, status] = __OCT_oct2xlsx_sh__ (xls, wsh_number, rawarr, lims, onc, onr, spsh_opts); ## Update worksheet bookkeeping for new sheets only if (new_sh) ## Add worksheet entry in several xml files ## === Read xl/_rels/workbook.xml.rels === rid = fopen ([xls.workbook filesep "xl" filesep "_rels" filesep "workbook.xml.rels"], "r+"); rxml = fread (rid, Inf, "char=>char").'; fclose (rid); ## Get rId's ((worksheet number in stack, needed in [Content_Types].xml) rId = str2double (cell2mat (regexp (rxml, 'Id="rId(\d+)"', "tokens"))); ## === Read xl/workbook.xml === wid = fopen ([xls.workbook filesep "xl" filesep "workbook.xml"], "r+"); wxml = fread (wid, Inf, "char=>char").'; fclose (wid); ## Get sheetId's [sheets, is, ie] = getxmlnode (wxml, "sheets"); sheetids = str2double (cell2mat (regexp (sheets, ' sheetId="(\d+?)"', "tokens"))); ## new rId must be nr of sheets + 1. rId's (>nwrId) are bumped +1 below nwrId = numel (sheetids) + 1; xls.sheets.rid(nwrId) = nwrId; if (xls.changed == 3) ## New file from template. No new sheet, just update Sheet1 name shnum = 1; sheets = strrep (sheets, 'name="Sheet1"', ['name="' wsh_string '"']); else ## Add a new sheet entry shnum = max (sheetids) + 1; wshtag = sprintf ('', ... wsh_string, shnum, nwrId); sheets = regexprep (sheets, '/>\s*', ["/>" wshtag ""]); xls.sheets.sheetid(end+1) = shnum; endif ## Re(/over-)write workbook.xml; start at sheets node wid = fopen ([xls.workbook filesep "xl" filesep "workbook.xml"], "w+"); fprintf (wid, "%s", wxml(1:is-1)); fprintf (wid, "%s", sheets); fprintf (wid, "%s", wxml(ie+1:end)); fclose (wid); ## === End of xl/workbook.xml & wxml === ## Write xl/_rels/workbook.xml.rels & [Content_Types].xml ## Only needed for new sheets in existing files if (xls.changed != 3) ## Find highest worksheet nr in nodes, add one and shift all the rest up is = iel = 0; for ii=1:numel (rId) ## Get next Relationship node. Worksheets usually come first [relshp, is, ie] = getxmlnode (rxml, "Relationship", is+1); if (rId(ii) >= shnum) ## rId needs to be bumped relshp = regexprep (relshp, 'Id="rId(\d+)"', ... ['Id="rId' sprintf("%d", rId(ii)+1) '"']); ## Insert new node back into rxml rxml = [rxml(1:is-1) relshp rxml(ie+1:end)]; else ## Remember end pos of last worksheet node iel = ie; endif if (rId(ii) == shnum) ## Insert new node entry = sprintf ('', nwrId, shnum); endif endfor rxml = [rxml(1:iel) entry rxml(iel+1:end)]; ## Rewrite xl/_rels/workbook.xml.rels rid = fopen ([xls.workbook filesep "xl" filesep "_rels" filesep "workbook.xml.rels"], "w"); fprintf (rid, "%s", rxml); fclose (rid); ## === End of xl/_rels/worksbook.xml.rels & rxml === ## === [Content_Types].xml. === ## Merely insert a worksheet #n entry (#n = nwrId) tid = fopen ([xls.workbook filesep "[Content_Types].xml"], "r+"); txml = fread (tid, Inf, "char=>char").'; fclose (tid); ## Prepare new partname node. Its stack pos = nwrId partname = ['' ]; partname = sprintf (partname, nwrId); ## Find last worksheet node & insert new node srchstr = 'worksheet+xml"/>'; ipos = strfind (txml, srchstr)(end) + length (srchstr); tid = fopen ([xls.workbook filesep "[Content_Types].xml"], "w"); fprintf (tid, "%s", txml(1:ipos-1)); fprintf (tid, partname); fprintf (tid, txml(ipos:end)); fclose (tid); endif ## === End of [Content_Types].xml & txml === ## === === aid = fopen ([xls.workbook filesep "docProps" filesep "app.xml"], "r+"); axml = fread (aid, Inf, "char=>char").'; fclose (aid); wshnode = sprintf ('%s', wsh_string); if (xls.changed == 3) [vt, is, ie] = getxmlnode (axml, "TitlesOfParts"); ## Just replace Sheet1 entry by new name vt = strrep (vt, '>Sheet1<', ['>' wsh_string '<']); else ## 1. Update HeadingPairs node [vt1, is, ie] = getxmlnode (axml, "HeadingPairs"); ## Bump number of entries nshts = str2double (getxmlnode (vt1, "vt:i4", [], 1)) + 1; vt1 = regexprep (vt1, '(\d+)', ["" sprintf("%d", nshts) ""]); ## 2. Update TitlesOfParts node [vt2, ~, ie] = getxmlnode (axml, "TitlesOfParts", ie); ## Bump number of entries nshts = str2double (getxmlattv (vt2, "size")) + 1; vt2 = regexprep (vt2, 'size="(\d+)"', ['size="' sprintf("%d", nshts) '"']); ## Add new worksheet entry vt2 = regexprep (vt2, "\s*", ["" wshnode ""]); vt = [vt1 vt2]; endif ## Re(/over-)write apps.xml aid = fopen ([xls.workbook filesep "docProps" filesep "app.xml"], "w+"); fprintf (aid, "%s", axml(1:is-1)); fprintf (aid, "%s", vt); fprintf (aid, "%s", axml(ie+1:end)); fclose (aid); endif ## === End of docProps/app.xml & axml === ## If needed update sharedStrings entries xml descriptor files if (status > 1) ## workbook_rels.xml rid = fopen ([xls.workbook filesep "xl" filesep "_rels" filesep "workbook.xml.rels"], "r+"); rxml = fread (rid, Inf, "char=>char").'; fclose (rid); if (isempty (strfind (rxml, "sharedStrings"))) ## Add sharedStrings.xml entry. First find unique rId rId = str2double (cell2mat (regexp (rxml, 'Id="rId(\d+)"', "tokens"))); nwrId = sort (rId)(end) + 1; entry = sprintf ('', nwrId); rxml = regexprep (rxml, '/>\s*', ["/>" entry ""]); rid = fopen ([xls.workbook filesep "xl" filesep "_rels" filesep "workbook.xml.rels"], "w"); fprintf (rid, "%s", rxml); fclose (rid); endif endif if (status > 1) ## Check [Content_Types].xml for SharedStrings entry tid = fopen ([xls.workbook filesep "[Content_Types].xml"], "r+"); txml = fread (tid, Inf, "char=>char").'; fclose (tid); if (isempty (strfind (txml, "sharedStrings"))) ## Add sharedStrings.xml entry after styles.xml node. First find that one [~, ~, ipos] = regexp (txml, '(?:styles\+xml)(?:.+)(>' ... txml(ipos+1:end)]; tid = fopen ([xls.workbook filesep "[Content_Types].xml"], "w"); fprintf (tid, "%s", txml); fclose (tid); endif endif ## === /docProps/core.xml (user/modifier info & date/time) === cid = fopen ([xls.workbook filesep "docProps" filesep "core.xml"], "r+"); cxml = fread (cid, Inf, "char=>char").'; fclose (cid); cxml = regexprep (cxml, 'dBy>(\w+)GNU Octave"); cxml = [ cxml(1:ia-1) '2015-09-10T19:53:40Z<' cxml(ib+1:end) ]; [modf, ia, ib] = getxmlnode (cxml, "dcterms:modified", [], 1); endif cxml = [cxml(1:ia-1) strrep(cxml(ia:ib), modf, modtime) cxml(ib+1:end)]; cid = fopen ([xls.workbook filesep "docProps" filesep "core.xml"], "w+"); fprintf (cid, "%s", cxml); fclose (cid); ## === End of docProps/core.xml & cxml === ## Update status xls.changed = max (xls.changed-1, 1); rstatus = 1; endfunction function [ xls, rstatus ] = __OCT_oct2xlsx_sh__ (xls, wsh_number, arrdat, lims, onc, onr, spsh_opts) ## Open sheet file (new or old), will be overwritten fid = fopen ([xls.workbook filesep "xl" filesep "worksheets" filesep ... sprintf("sheet%d.xml", wsh_number)], "r+"); if (fid < 0) ## Apparently a new sheet. Fill in default xml contents xml = ''; xml = [ xml "\n" ]; xml = [ xml '' ]; xml = [ xml '' ]; xml = [ xml '' ]; xml = [ xml '']; xml = [ xml '']; xml = [ xml '' ]; else ## Read complete contents xml = fread (fid, Inf, "char=>char").'; fclose (fid); endif ## Update "dimension" (=range) node [dimnode, is1, ie1] = getxmlnode (xml, "dimension"); ## Compute new limits rng = sprintf ("%s:%s", calccelladdress (lims(2, 1), lims(1, 1)), ... calccelladdress (lims(2, 2), lims(1, 2))); ## Open sheet file (new or old) in reset mode, write first part of worksheet fid = fopen ([xls.workbook filesep "xl" filesep "worksheets" filesep ... sprintf("sheet%d.xml", wsh_number)], "w+"); fprintf (fid, "%s", xml(1:is1-1)); ## Write updated dimension node fprintf (fid, '', rng); ## Get Sheetdata node [shtdata, is2, ie2] = getxmlnode (xml, "sheetData"); ## Write second block of xml until start of sheetData fprintf (fid, "%s", [xml(ie1+1:is2-1) ""]); ## Explore data types in arrdat typearr = spsh_prstype (arrdat, onr, onc, [1:5], spsh_opts); if (all (typearr(:) == 1)) ## Numeric for r=1:rows (arrdat) fprintf (fid, '', ... r+lims(2, 1)-1, ... lims(1, 1), lims(1, 2)); for c = 1:columns (arrdat) if (0 == isnan (arrdat{r, c})) fprintf (fid, sprintf ('%0.15g', ... num2col (c+lims(1, 1)-1), r+lims(2, 1)-1, arrdat{r, c})); endif endfor fprintf (fid, ""); endfor else ## Heterogeneous array. Write cell nodes depending on content i_tatt = []; strings = {}; str_cnt = uniq_str_cnt = 0; ## Check if there are any strings if (any (typearr(:) == 3)) ## Yep. Read sharedStrings.xml try sid = fopen (sprintf ("%s/xl/sharedStrings.xml", xls.workbook), "r+"); if (sid > 0) ## File exists => there are already some strings in the sheet shstr = fread (sid, "char=>char").'; fclose (sid); ## Extract string values. May be much more than present in current sheet. ## A two-step procedure is required to preserve empty strings ("") strings = cell2mat (regexp (shstr, ']*>(.*?)', "tokens")); strings = regexprep (strings, '(^|^|$)', ""); ## Take care of strings with ', "tokens"))); ## Watch out for a rare corner case: just one empty string... (avoid []) if (isempty (strings)) strings = {""}; endif sst = getxmlnode (shstr, "sst", 1); uniq_str_cnt = str2double (getxmlattv (sst, "uniqueCount")); clear sst; ## Make shstr a numeric value shstr = 1; else ## File didn't exist yet shstr = 0; endif catch ## No sharedStrings.xml; implies no "fixed" strings (computed strings can still be there) strings = {}; str_cnt = uniq_str_cnt = 0; end_try_catch endif ## Process data row by row for ii=1:rows (arrdat) ## Row node opening tag fprintf (fid, '', ii+lims(2, 1)-1, lims(1, 1), lims(1, 2)); for jj=1:columns (arrdat) ## Ignore empty values if (! isempty (arrdat{ii, jj})) ## Init required attributes. Note leading space addr = sprintf (' r="%s"', calccelladdress (ii+lims(2, 1)-1, jj+lims(1, 1)-1)); ## Init optional atttributes ttag = form = ""; ## t: e = error, b = boolean, s = string ## n = numeric, str = formula switch typearr(ii, jj) case 1 ## Numeric ## t tag ("type") is omitted for numeric data ??? ttag = ' t="n"'; val = ["" (sprintf ("%0.15g", arrdat{ii, jj})) ""]; case 2 ## Boolean ttag = ' t="b"'; if (arrdat{ii, jj}) val = "1"; else val = "0"; endif case 3 ## String ttag = ' t="s"'; ## FIXME s value provisionally set to 0 sptr = find (strcmp (arrdat{ii, jj}, strings)); if (isempty (sptr)) ## Add new string strings = [strings arrdat{ii, jj}]; ++uniq_str_cnt; ## New pointer into sharedStrings (0-based) sptr = uniq_str_cnt; endif ## Val must be pointer (0-based) into sharedStrings.xml val = sprintf ("%d", sptr - 1); ++str_cnt; case 4 ## Formula ttag = ' t="str"'; form = sprintf ("%s", arrdat{ii, jj}(2:end)); #val = "?"; val = "#####"; otherwise ## (includes "case 5" ## Empty value. Clear address addr = ""; endswitch ## Append node to file, include tags if (! isempty (addr)) fprintf (fid, "", addr, ttag); if (! isempty (val)) fprintf (fid, "%s%s", form, val); endif fprintf (fid, ""); endif endif endfor fprintf (fid, ""); endfor endif ## Closing tag fprintf (fid, ""); ## Append rest of original xml to file and close it fprintf (fid, "%s", xml(ie2+1:end)); fclose (fid); rstatus = 1; ## Rewrite sharedStrings.xml, if required if (any (typearr(:) == 3) && ! isempty (strings)) ## Apparently something to write if (uniq_str_cnt == 1 && isempty (strings{1})) ## Avoid writing sharedStrings file for just one empty string entry return endif ## (Re-)write xl/sharedStrings.xml sid = fopen (sprintf ("%s/xl/sharedStrings.xml", xls.workbook), "w+"); fprintf (sid, '\n'); fprintf (sid, '', ... str_cnt, uniq_str_cnt); for ii=1:uniq_str_cnt if (ismember (ii, i_tatt)) ## Invoke original %s", strings{ii}); else fprintf (sid, "%s", strings{ii}); endif endfor fprintf (sid, ""); fclose (sid); ## Check if new sharedStrings file entries are required if (isnumeric (shstr) && (! shstr)) rstatus = 2; return; endif endif ## Return endfunction