## Copyright (C) 2009-2016 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 -*- ## @deftypefn {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}) ## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}) ## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}, @var{range}) ## @deftypefnx {Function File} @var{rstatus} = xlswrite (@var{filename}, @var{arr}, @var{wsh}, @var{range}, @var{reqintf}) ## Add data in 1D/2D array @var{arr} to worksheet @var{wsh} in Excel ## spreadsheet file @var{filename} in cell range @var{range}. Gnumeric ## files can also be written. ## ## @var{rstatus} returns 1 if writing succeeded, 0 otherwise. ## ## @var{filename} must be a valid Excel file name (including file ## name extension). If @var{filename} does not contain any directory path, ## the file is saved in the current directory. Writing .xlsm and .xlsb ## is untested but may only reliably be possible with the COM and UNO ## interfaces. ## ## @var{arr} can be any 1D or 2D array containing numerical, logical and/or ## character data (cellstr) except complex. Mixed numeric/text arrays can ## only be cell arrays. ## ## If only 3 arguments are given, the 3rd is assumed to be a spreadsheet ## range if it contains a ":" or is a completely empty string (interpreted ## as A1:IV65336 for regular .xls or A1:XFD1048576 for OOXML .xlsx). The ## 3rd argument is assumed to refer to a worksheet if it is a numeric value ## or a non-empty text string not containing ":" ## ## @var{wsh} can be a number or string (max. 31 chars). ## In case of a not yet existing Excel file, the first worksheet will be ## used & named according to @var{wsh} - the extra worksheets that Excel ## usually creates by default are deleted (COM) or simply not created. ## In case of existing files, some checks are made for existing worksheet ## names or numbers, or whether @var{wsh} refers to an existing sheet with ## a type other than worksheet (e.g., chart). ## When new worksheets are to be added to the Excel file, they are ## inserted to the right of all existing worksheets. The pointer to the ## "active" sheet (shown when Excel opens the file) remains untouched. ## ## @var{range} is expected to be a regular spreadsheet range. ## Data is added to the worksheet; existing data in the requested ## range will be overwritten. Instead of a spreadsheet range a Named ## range defined in the spreadsheet file can be used as well. In that ## case the Named range should be specified as 4th argument and the value ## of 3rd argument @var{wsh} doesn't matter as the worksheet associated ## with the specified Named range will be used. ## Array @var{arr} will be clipped at the right and/or bottom if its size ## is bigger than can be accommodated in @var{range}. ## If @var{arr} is smaller than the @var{range} allows, it is placed ## in the top left rectangle of @var{range} and remaining cell values ## outside the rectangle will be retained. ## If the third argument is a sheet name and @var{range} is specified as ## just one cell, it is taken as the topleft cell and the bottomright ## cell range address is determinded form the data. ## ## If @var{range} contains merged cells, only the elements of @var{arr} ## corresponding to the top or left Excel cells of those merged cells ## will be written, other array cells corresponding to that merged cell ## will be ignored. In other words, merged spreadsheet cells won't be ## "unmerged". ## ## As to the optional last argument @var{reqintf}: ## When no external support SW for spreadsheet I/O ('interface') is ## installed (see below), xlsread can only write to .xlsx, .ods and ## .gnumeric files using the default (built-in) 'OCT' interface. If ## external support SW is installed, xlswrite will try locate it ## automatically and invoke it, allowing more file types to be written. ## Multiple spreadsheet I/O 'interfaces' can be installed side-by-side; ## xlswrite will then try to invoke the most suitable one depending ## on file type. ## The optional last argument @var{reqintf} can be used to override ## that automatic selection by xlswrite. Supported interfaces ## comprise: 'com' (ActiveX/Excel), 'poi' (Java/Apache POI), 'jxl' ## (Java/JExcelAPI), 'uno' (Java/OpenOffice.org), or 'oct' (native ## Octave w/o any external support software). 'oxs' (Java/OpenXLS) ## works reliably and fast for .xls (BIFF8) but has been disabled for ## writing OOXML as that is too buggy. For writing to OOXML files ## (.xlsx) a value of 'com', 'poi', 'uno', or 'oct' must be specified ## for @var{reqintf}. The value of @var{reqintf} is case-insensitive. ## Multiple interfaces can be selected if entered as a cell array of ## strings. Writing gnumeric files can only be done with the OCT ## interface, it is selected automatically for that file type. ## ## xlswrite is a mere wrapper for various scripts which find out what ## Excel interface to use (COM, POI, etc) plus code to mimic the other ## brand's syntax. For each call to xlswrite such an interface must be ## started and possibly an Excel file loaded. When writing to multiple ## ranges and/or worksheets in the same Excel file, a speed bonus can be ## obtained by invoking those scripts directly with multiple calls to ## oct2xls (one for each sheet or range) surrounded by one call to ## xlsopen and xlsclose: ## (xlsopen / octxls / oct2xls / .... / xlsclose) ## ## Examples: ## ## @example ## status = xlswrite ... ## ('test4.xls', 'arr', 'Third_sheet', 'C3:AB40'); ## (which adds the contents of array arr (any type) to ## rangeC3:AB40 in worksheet 'Third_sheet' in file ## test4.xls and returns a logical True (= numerical 1) ## in 'status' if all went well) ## @end example ## ## @seealso {xlsread, oct2xls, xls2oct, xlsopen, xlsclose, xlsfinfo} ## ## @end deftypefn ## Author: Philip Nienhuis ## Created: 2009-10-16 function [ rstatus ] = xlswrite (filename, arr, arg3, arg4, arg5) rstatus = 0; r_extnd = 0; ## Sanity checks if (nargin < 2) error ("Insufficient arguments - see 'help xlswrite'\n"); elseif (! ischar (filename)) error ("xlswrite: first argument must be a filename (incl. suffix)\n"); elseif (nargin == 2) ## Assume first worksheet and full worksheet starting at A1 wsh = 1; if (strcmpi (filename(end-4:end-1), ".xls")) crange = "A1:XFD1048576"; ## OOXML has ridiculously large limits else crange = "A1:IV65536"; ## Regular xls limits endif elseif (nargin == 3) ## Find out whether 3rd argument = worksheet or range if (isnumeric (arg3) || (isempty (findstr (arg3, ":")) && ~isempty (arg3))) ## Apparently a worksheet specified wsh = arg3; if (strcmpi (filename(end-4:end-1), ".xls")) crange = "A1:XFD1048576"; ## OOXML has ridiculously large limits else crange = "A1:IV65536"; ## Regular xls limits endif else ## Range specified wsh = 1; crange = arg3; endif elseif (nargin >= 4) wsh = arg3; crange = arg4; r_extnd = (isempty (strfind (crange, ":")) && nargin >= 4); endif if (nargin == 5) reqintf = arg5; else reqintf = []; endif if (isempty (wsh)) wsh = 1; endif if (isempty (crange) || r_extnd) if (r_extnd) tlcl = crange; else tlcl = "A1"; endif if (strcmpi (filename(end-4:end-1), ".xls")) crange = [tlcl ":XFD1048576"]; ## OOXML has ridiculously large limits else crange = [tlcl ":IV65536"]; ## Regular xls limits endif endif ## Parse range [topleft, nrows, ncols, trow, lcol] = parse_sp_range (crange); ## Check if arr fits in range [nr, nc] = size (arr); if ((nr > nrows) || (nc > ncols)) # Array too big; truncate nr = min (nrows, nr); nc = min (ncols, nc); warning ("xlswrite - array truncated to %d by %d to fit in range %s\n", ... nrows, ncols, crange); ## Adapt crange crange = [ calccelladdress(trow, lcol) ":" ... calccelladdress(trow+nrows-1, lcol+ncols-1) ]; endif unwind_protect ## Needed to be sure Excel can be closed i.c.o. errors xls_ok = 0; xls = xlsopen (filename, 1, reqintf); if (! isempty (xls)) xls_ok = 1; [xls, rstatus] = oct2xls (arr(1:nr, 1:nc), xls, wsh, crange); endif unwind_protect_cleanup if (xls_ok && ! isempty (xls)) xls = xlsclose (xls); endif end_unwind_protect if (! isempty (xls)) ## Apparently the file pointer couldn't be cleared, usually due to errors rstatus = 0; endif endfunction