## Copyright (C) 2009-2020 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 2 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 ## .7 ## -*- texinfo -*- ## @deftypefn {Function File} @var{xls} = xlsopen (@var{filename}) ## @deftypefnx {Function File} @var{xls} = xlsopen (@var{filename}, @var{readwrite}) ## @deftypefnx {Function File} @var{xls} = xlsopen (@var{filename}, @var{readwrite}, @var{reqintf}) ## @deftypefnx {Function File} @var{xls} = xlsopen (@var{filename}, @var{readwrite}, @var{reqintf}, @var{verb}) ## Get a pointer to a spreadsheet in memory in the form of return argument ## (file pointer struct) @var{xls}. ## ## Calling xlsopen without specifying a return argument is fairly useless ## and considered an error! After processing the spreadsheet, the file ## pointer must be explicitly closed by calling xlsclose() to release possibly ## large amounts of RAM. ## ## @var{filename} should be a valid spreadsheet file name (including ## extension); see "help xlsread" for an overview of supported spreadsheet file ## formats. ## ## If @var{readwrite} is set to 0 (default value) or omitted, the spreadsheet ## file is opened for reading. If @var{readwrite} is set to true or 1, a ## spreadsheet file is opened (or created) for reading & writing. ## ## Optional input argument @var{reqintf} can be used to override the ## spreadsheet I/O interface (see below) that otherwise would automatically ## be selected by xlsopen. In most situations this parameter is unneeded as ## xlsopen automatically selects the most useful interface present, depending ## on installed external support software and requested file type. A ## user-specified interface selection can be reset to default by entering ## a numeric value of -1. ## ## If a value of 1 or true is entered for @var{verb}, xlsopen returns info about ## the spreadsheet I/O interfaces that were found and/or are requested and ## active. The default value is false (no info on interfaces is shown). ## ## xlsopen works with interfaces, which are links to support software, mostly ## external. @* ## The built-in 'OCT' interface needs no external software and allows ## I/O from/to OOXML (Excel 2007 and up), ODS 1.2 and Gnumeric. @* For all other ## spreadsheet formats, or if you want more speed and/or more flexibility, ## additional external software is required. See "help xlsread" for more info. @* ## Currently implemented interfaces to external SW are (in order of preference) ## 'COM' (Excel/COM), 'POI' (Java/Apache POI), 'JXL' (Java/JExcelAPI), 'OXS' ## (Java/OpenXLS), 'UNO' (Java/OpenOffice.org - EXPERIMENTAL!), 'OTK' ## (ODF Toolkit), 'JOD' (jOpendocument) or 'OCT' (native Octave); see below: ## ## @table @asis ## @item xls and .xlsx: ## One or more of (1) a Java JRE plus Apache POI >= 3.5, and/or JExcelAPI ## and/or OpenXLS, and/or OpenOffice.org (or clones) installed on your computer ## + proper javaclasspath set, or (2 - Windows only) OF-windows package and ## MS-Excel. These interfaces are referred to as POI, JXL, OXS, UNO and COM, ## resp., and are preferred in that order by default (depending on presence of ## the pertinent support SW). Currently the OCT interface has the lowest ## priority. @* ## Excel'95 spreadsheets (BIFF5) can only be read using the JXL (JExcelAPI), ## UNO (Open-/LibreOffice), and COM (Excel-ActiveX) interfaces. ## @end item ## ## @item .ods, .sxc: ## A Java JRE plus one or more of (ODFtoolkit (version 0.7.5 or 0.8.6 - 0.8.8) ## & xercesImpl v.2.9.1), jOpenDocument, or OpenOffice.org (or clones) ## installed on your computer + proper javaclasspath set. These interfaces ## are referred to as OTK, JOD, and UNO resp., and are preferred in that order ## by default (depending on presence of support SW). The OCT interface has ## lowest priority). @* ## The old OpenOffice.org .sxc format can be read using the UNO interface and ## older versions of the JOD interface. ## @end item ## ## @item Other formats: ## Apart from .gnumeric, by invoking the UNO interface one can read any format ## that the installed LibreOffice version supports. The same goes (on Windows ## systems) for MS-Excel. However, writing to other file formats than .xlsx, ## .ods, .gnumeric and .xls is not implemented. ## @end item ## @end table ## ## The utility function chk_spreadsheet_support.m can be useful to set the ## javaclasspath for the Java-based interfaces. ## ## Beware: 'zombie' Excel invocations may be left running invisibly in case ## of COM errors or after forgetting to close the file pointer. Similarly for ## LibreOffice, which may even prevent Octave from being closed (the reason ## the UNO interface is still experimental). ## ## Examples: ## ## @example ## xls = xlsopen ('test1.xls'); ## (get a pointer for reading from spreadsheet test1.xls) ## ## xls = xlsopen ('test2.xls', 1, 'POI'); ## (as above, indicate test2.xls will be written to; in this case using Java ## and the Apache POI interface are requested) ## @end example ## ## @seealso {xlsclose, xlsread, xlswrite, xls2oct, oct2xls, xlsfinfo, ## chk_spreadsheet_support} ## ## @end deftypefn ## Author: Philip Nienhuis ## Created: 2009-11-29 function [ xls ] = xlsopen (filename, xwrite=0, reqinterface=[], verbose=false) persistent interfaces; persistent chkintf; persistent lastintf; ## interfaces. = [] (not yet checked), ## 0 (found to be unsupported or unwanted), or ## 1 (checked and OK) if (isempty (chkintf) || (isnumeric (reqinterface) && reqinterface == -1)) ## Either not yet checked, or selection to be reset to default chkintf = 1; interfaces = struct ("COM", [], "JXL", [], "JOD", [], "OCT", 1, ... "OTK", [], "OXS", [], "POI", [], "UNO", []); if (isnumeric (reqinterface)) reqinterface = ""; endif endif if (isempty (lastintf)) lastintf = "---"; endif xlsintf_cnt = 1; ## Bit mask keeping track of detected/supported interfaces xlssupport = 0; ## Input checks if (nargout < 1) error ("xlsopen: no return argument specified!\n usage: XLS = xlsopen (Xlfile [, Rw] [, reqintf])\n"); endif if (! (islogical (xwrite) || isnumeric (xwrite))) error ("xlsopen: numerical or logical value expected for arg ## 2 (readwrite)\n") endif if (ischar (filename)) [pth, fnam, ext] = fileparts (filename); if (isempty (fnam)) error ("xlsopen: no filename or empty filename specified"); endif if (xwrite && ! isempty (pth)) apth = make_absolute_filename (pth); if (exist (apth) != 7) error ("xlsopen: cannot write into non-existent directory:\n'%s'\n", ... apth); endif endif else error ("xlsopen: filename expected for argument #1"); endif if (! isempty (reqinterface)) intfmsg = "requested"; if (! (ischar (reqinterface) || iscell (reqinterface))) error ("xlsopen: arg. # 3 (interface) not recognized - character value required\n"); endif ## Turn arg3 into cell array if needed if (! iscell (reqinterface)) reqinterface = {reqinterface}; endif reqinterface = cellfun (@upper, reqinterface, "uni", 0); ## Check if previously used interface matches a requested interface if (isempty (regexpi (reqinterface, lastintf, "once"){1}) || ... ! interfaces.(reqinterface{1})) ## New interface requested. Provisionally disable all interfaces interfaces.COM = 0; interfaces.JOD = 0; interfaces.JXL = 0; interfaces.OCT = 0; interfaces.OTK = 0; interfaces.OXS = 0; interfaces.POI = 0; interfaces.UNO = 0; for ii=1:numel (reqinterface) ## Try to invoke requested interface(s) for this call. Check if it ## is supported anyway by emptying the corresponding var. try interfaces.(reqinterface{ii}) = []; catch error (sprintf (["xlsopen: unknown interface \"%s\" requested.\n" "Only COM, JOD, JXL, OCT, OTK, OXS, POI or UNO) supported\n"], ... reqinterface{})); end_try_catch endfor if (verbose) printf ("Checking requested interface(s): "); endif interfaces = getinterfaces (interfaces, verbose); ## Well, is/are the requested interface(s) supported on the system? xlsintf_cnt = 0; for ii=1:numel (reqinterface) if (! interfaces.(toupper (reqinterface{ii}))) ## No it aint if (verbose) printf ("%s is not supported.\n", upper (reqinterface{ii})); endif else ++xlsintf_cnt; endif endfor ## Reset interface check indicator if no requested support found if (! xlsintf_cnt) chkintf = []; xls = []; return endif endif else intfmsg = "available"; endif ## Check if spreadsheet file exists. First check (supported) file name suffix: ## FIXME: invoke subfunct mtchext() rather than repeat below code several times ftype = 0; has_suffix = 1; [sfxpos, ~, ~, ext] = regexpi (filename, '(\.xls[bmx]?|\.gnumeric|\.ods|\.scx|\.csv)'); if (! isempty (sfxpos)) ext = lower (ext{end}); ## Is .xls or .xls[x,m,b] or .ods or .gnumeric at right(most) position? if (sfxpos(end) <= length (filename) - length (ext)) ## Apparently not, or it is an unrecognized extension ## If xwrite = 0, check file suffix, else add .xlsx has_suffix = 0; else switch ext case ".xls" ## Regular (binary) BIFF ftype = 1; case {".xlsx", ".xlsm", ".xlsb"} ## Zipped XML / OOXML. Catches xlsx, xlsb, xlsm ftype = 2; case ".ods" ## ODS 1.2 (Excel 2007+ & OOo/LO can read ODS) ftype = 3; case ".sxc" ## jOpenDocument (JOD) can read from ftype = 4; ## .sxc files, but only if odfvsn = 2 case ".gnumeric" ## Zipped XML / gnumeric ftype = 5; case ".csv" ## csv. Detected for xlsread afficionados ftype = 6; otherwise ## FIXME here should come the extraneous ones like .fods, .uos, .wk1, ... warning ("xlsopen: file type ('%s' extension) not supported\n", ext); endswitch endif else has_suffix = 0; endif ## Adapt file open mode for readwrite argument. ## Var readwrite is really used to avoid creating files when wanting ## to read, or not finding not-yet-existing files when wanting to write ## a new one. Adapt file open mode for readwrite argument if (xwrite) fmode = "r+b"; if (! has_suffix) ## Provisionally add .xlsx suffix to filename (most used format) filename = [filename ".xlsx"]; ext = ".xlsx"; ftype = 2; endif else fmode = "rb"; if (! has_suffix) ## Try to find find an existing file with a recognized file extension filnm = mtchext (filename); if (! isempty (filnm)) ## Simply choose the first one if (isstruct (filnm)) filename = filnm(1).name; else filename = filnm; endif endif endif endif ## Explore for filename in relevant rw mode. stat() can't see if file is locked fid = fopen (filename, fmode); if (fid < 0) ## File doesn't exist... if (! xwrite) ## ...which obviously is fatal for reading... ## FIXME process open apps (Excel, LibreOffice, etc) before hard error error ( sprintf ("xlsopen: file %s not found\n", filename)); else ## ...but for writing, we need more info: fid = fopen (filename, "rb"); ## Check if it exists at all... if (fid < 0) ## File didn't exist yet. Simply create it xwrite = 3; else ## File exists, but isn't writable => Error fclose (fid); ## Do not forget to close the handle neatly error (sprintf ("xlsopen: write mode requested but file %s is not \ writable\n", filename)) endif endif else ## Close file anyway to avoid COM or Java errors fclose (fid); endif ## Check for the various interfaces. No problem if they've already been ## checked, getinterfaces (far below) just returns immediately then. interfaces = getinterfaces (interfaces, verbose) ## If no external interface was detected and no suffix was given, use .xlsx if (! has_suffix && ! (interfaces.COM + interfaces.POI + ... interfaces.JXL + interfaces.OXS + ... interfaces.UNO)) % ## Just add 'x' - .xls was already added higher up % filename = [filename "x"]; ftype = 2; endif ftype ## Initialize file ptr struct xls = struct ("xtype", "NONE", "app", [], "filename", [], "workbook", [], "changed", 0, "limits", []); ## Keep track of which interface is selected xlssupport = 0; ## Interface preference order is defined below: ## currently COM -> POI -> JXL -> OXS -> OTK -> JOD -> UNO -> OCT ## ftype (file type) is conveyed depending on interface capabilities if ((! xlssupport) && interfaces.COM && (ftype != 5)) ## Excel functioning has been tested above & file exists, so we just invoke it. if (verbose) printf ("Invoking COM ...\n"); endif [ xls, xlssupport, lastintf ] = __COM_spsh_open__ (xls, xwrite, filename, xlssupport); elseif ((! xlssupport) && ((interfaces.POI >= 2 && ftype <= 2) || ... (interfaces.POI == 1 && ftype == 1))) if (verbose) printf ("Invoking POI ...\n"); endif [ xls, xlssupport, lastintf ] = __POI_spsh_open__ (xls, xwrite, filename, xlssupport, ftype, interfaces); elseif ((! xlssupport) && interfaces.JXL && ftype == 1) if (verbose) printf ("Invoking JXL ...\n"); endif [ xls, xlssupport, lastintf ] = __JXL_spsh_open__ (xls, xwrite, filename, xlssupport, ftype); elseif ((! xlssupport) && interfaces.OXS && ftype == 1) if (verbose) printf ("Invoking OXS ...\n"); endif [ xls, xlssupport, lastintf ] = __OXS_spsh_open__ (xls, xwrite, filename, xlssupport, ftype); elseif (interfaces.OTK && ! xlssupport && ftype == 3) if (verbose) printf ("Invoking OTK ...\n"); endif [ xls, xlssupport, lastintf ] = ... __OTK_spsh_open__ (xls, xwrite, filename, xlssupport); elseif (interfaces.JOD && ! xlssupport && (ftype == 3 || ftype == 4)) if (verbose) printf ("Invoking JOD ...\n"); endif [ xls, xlssupport, lastintf ] = ... __JOD_spsh_open__ (xls, xwrite, filename, xlssupport); elseif ((! xlssupport) && interfaces.UNO && (ftype != 5)) if (verbose) printf ("Invoking UNO ...\n"); endif ## Warn for LO / OOo stubbornness if (ftype == 0 || ftype == 5 || ftype == 6) warning ("UNO interface will write ODS format for unsupported file extensions\n") endif [ xls, xlssupport, lastintf ] = __UNO_spsh_open__ (xls, xwrite, filename, xlssupport); elseif ((! xlssupport) && interfaces.OCT && ... (ftype == 2 || ftype == 3 || ftype == 5)) if (verbose) printf ("Invoking OCT ...\n"); endif [ xls, xlssupport, lastintf ] = __OCT_spsh_open__ (xls, xwrite, filename, xlssupport, ftype); endif ## Rounding up. If none of the interfaces is supported we're out of luck. if (! xlssupport) if (isempty (reqinterface)) ## If no suitable interface was detected (COM or UNO can read .csv), handle ## .csv in xlsread (as that's where Matlab n00bs would expect .csv support) if (ftype != 6) ## This message is appended after message from getinterfaces() if (verbose) printf ("None.\n"); endif warning ("xlsopen: no'%s' spreadsheet I/O support with %s interfaces.\n", ... ext, intfmsg); endif else ## No match between file type & interface found warning ("xlsopen: file type not supported by %s %s %s %s %s %s %s %s\n", ... reqinterface{:}); endif xls = []; ## Reset found interfaces for re-testing in the next call. Add interfaces if needed. chkintf = []; else ## From here on xwrite is tracked via xls.changed in the various lower ## level r/w routines xls.changed = xwrite; ## xls.changed = 0 (existing/only read from), 1 (existing/data added), 2 (new, ## data added) or 3 (pristine, no data added). ## Until something was written to existing files we keep status "unchanged". if (xls.changed == 1) xls.changed = 0; endif endif endfunction function fname = mtchext (fname) ## Define preferred order of (default) file extensions persistent prefext = {".xls", "xlsx", ".xlsm", ".ods", ".gnumeric", ".csv"}; ## In case of multiple files with same name, pick the one with preferred ext. flist = {dir([fname ".*"]).name}; exts = cell2mat (cell2mat (regexpi (flist, '.*(\.\w+$)', "tokens"))); ## Get first matching file extension. ismember() arg order = vital! ext = find (ismember (prefext, exts))(1); fname = [fname ext]; endfunction