Add data in 1D/2D array arr to a spreadsheet file.
Out of the box, xlswrite can write data to .xlsx, .ods and .gnumeric
spreadsheet files. For .xlsx it is relatively fast, for .ods quite
slow and for .gnumeric it’s the only choice.
For writing to other file formats or for faster I/O, see the help for
xlsread (under "Spreadsheet I/O interfaces").
Required parameters:
——————–
filename must be a valid spreadsheet file name (including file name extension). If 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.
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.
Optional parameters:
——————–
wsh can be a number or string (max. 31 chars for .xls and .xlsx,
unlimited for .ods). In case of a not yet existing spreadsheet file, only
one sheet will be created, used & named according to wsh.
In case of existing files, some checks are made for existing sheet
names or numbers, or whether wsh refers to an existing sheet with
a type other than worksheet (e.g., chart).
When new sheets are to be added to the spreadsheet file, they are
inserted to the right of all existing sheets. The pointer to the
"active" sheet (shown when the file is opened in an external spreadsheet
program) remains untouched.
range is expected to be a regular spreadsheet range.
Data is added to the worksheet; existing data in the requested
range will be overwritten.
Array arr will be clipped at the right and/or bottom if its size
is bigger than can be accommodated in range.
If arr is smaller than the range allows, it is placed
in the top left rectangle of range and existing cell values
outside the rectangle will be retained.
If the third argument is a sheet name and range is specified as
just one cell, it is taken as the topleft cell and the bottomright
cell range address is determined from the data.
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:AMJ1048576 for .ods, 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 ":". To enter a range of just one cell specify e.g., "F3:F3".
If range contains merged cells, only the elements of arr corresponding to the top or left spreadsheet 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".
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 wsh doesn’t matter as the worksheet associated with the specified Named range will be used.
After range some optional arguments can be specified:
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 reqintf can be used to override
that automatic selection by xlswrite. The value of reqintf
is case-insensitive. For an overview of interfaces and external
support software, see the help for xlsread.
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.
If a value of true (logical) or 1 (numerical) is specified, xlswrite will echo the found spreadsheet I/O interfaces when it is started for the first time in an Octave session or when reqintf was specified. If verbose is omitted or a value of false or 0 (zero) is specified (the default) no interface info is shown.
rstatus returns 1 if writing succeeded, 0 otherwise.
xlswrite is a mere wrapper for various functions which find out what spreadsheet interface to use (COM, POI, JOD, etc), followed by separate functions for opening, writing to, and closing a spreadsheet file. For each call to xlswrite such an interface must be started and a spreadsheet file loaded and written. When writing to multiple ranges and/or sheets in the same spreadsheet file, or reading from and writing to the same spreadsheet file, a significant 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)
or (mixing reading and writing in any desired order)
(xlsopen / xls2oct / .... / octxls / .... / xlsclose)
Example:
status = xlswrite ...
('test4.xls', 'arr', 'Third_sheet', 'C3:AB40');
(which adds the contents of array arr (any type) to
range C3:AB40 in worksheet 'Third_sheet' in file
test4.xls and returns a logical True (= numerical 1)
in 'status' if all went well)
See also: xlsread, oct2xls, xls2oct, xlsopen, xlsclose, xlsfinfo, chk_spreadsheet_support.
Package: io