Function File: [ xls, rstatus ] = oct2xls (arr, xls)
Function File: [ xls, rstatus ] = oct2xls (arr, xls, wsh)
Function File: [ xls, rstatus ] = oct2xls (arr, xls, wsh, range)
Function File: [ xls, rstatus ] = oct2xls (arr, xls, wsh, range, options)

Add data in 1D/2D CELL array arr into a cell range range in worksheet wsh in a spreadsheet file pointed to in structure xls.

Return argument xls equals supplied argument xls and is updated by oct2xls. A subsequent call to xlsclose is needed to write the updated spreadsheet to disk (and -if needed- close the LibreOffice (OOo) or Excel invocation).

arr can be any 1D or 2D array containing numerical, logical and/or character data (cellstr) except complex. Mixed type arrays can only be cell arrays.

xls must be a valid pointer struct created earlier by xlsopen.

wsh can be a number or string (max. 31 chars for .xlsx/.xls files). If it is numeric it refers to the position in the total sheet stack incl. e.g., chartsheets.

In case of existing files, some checks are made for existing worksheet names or numbers, or whether wsh refers to an existing sheet with a type other than worksheet (e.g., chart). When new worksheets are to be added to the spreadsheet file, they are inserted to the right of all existing worksheets. The pointer to the "active" sheet (shown when opened in a spreadsheet program) remains untouched.

If range is omitted or just the top left cell of the range is specified, the actual range to be used is determined by the size of arr. If nothing is specified for range the top left cell is assumed to be ’A1’. If defined in the spreadsheet file, a "Named range" can also be specified. In that case wsh will be ignored and the worksheet associated with the specified Named range will be used.

Data are added to the worksheet, ignoring other data already present; existing data in the range to be used will be overwritten.

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 cell will be ignored.

Optional argument options, a structure, can be used to specify various write modes.

"formulas_as_text"

If set to 1 or TRUE formula strings (i.e., text strings (assumed to start with "=" and end in a ")") are to be written as literal text strings rather than as spreadsheet formulas. (The latter is the default).

’convert_utf’

If set to 1 or TRUE, oct2xls converts one-byte characters outside the range [32:127] to UTF-8 so that they are properly entered as UTF-8 encoded text in spreadsheets. The default value is 0. This setting has no effect for the COM interface as that does the encoding automatically using libraries outside Octave.

Beware that -if invoked- LibreOffice or Excel invocations may be left running silently in case of Java or COM errors. Invoke xlsclose with a proper pointer struct to try to close them. When using Java, note that large data array sizes elements may exhaust the Java shared memory space for the default Java memory settings. For larger arrays, appropriate memory settings are needed in the file java.opts; then the maximum array size for the Java-based spreadsheet options may be in the order of 10^6 elements. In caso of UNO this limit is not applicable and spreadsheets may be much larger.

Examples:

  [xlso, status] = oct2xls ('arr', xlsi, 'Third_sheet', 'AA31:AB278');

See also: xls2oct, xlsopen, xlsclose, xlsread, xlswrite, xlsfinfo.

Package: io