Function File: [ rawarr, xls, rstatus ] = xls2oct (xls)
Function File: [ rawarr, xls, rstatus ] = xls2oct (xls, wsh)
Function File: [ rawarr, xls, rstatus ] = xls2oct (xls, wsh, range)
Function File: [ rawarr, xls, rstatus ] = xls2oct (xls, wsh, range, options)

Read data contained within cell range range from worksheet wsh in a spreadsheet file pointed to in struct xls.

Spreadsheet file pointer xls is supposed to have been created earlier by xlsopen in the same octave session.

wsh is either numerical or text, in the latter case it is case-sensitive; for .xlsx and .xls formats it may be max. 31 characters long. Note that in case of a numerical wsh this number refers to the position in the total sheet stack, counted from the left in a spreadsheet program window. The default is numerical 1, i.e. corresponding to the leftmost sheet tab in the spreadsheet file.

range is expected to be either a regular spreadsheet range format, "" (empty string, indicating all data in a worksheet), or a "Named range" defined in the spreadsheet file. In case of a Named range, the worksheet associated with that Named range will be used instead of the one specified in wsh. If no range is specified or is specified as an empty string the occupied cell range will have to be determined behind the scenes first; this can take some time for the Java-based interfaces. Be aware that in COM/ActiveX interface the inferred range can be outdated. The Java-based interfaces are more reliable in this respect albeit much slower.

Optional argument options, a structure, can be used to specify various read modes by setting option fields in the struct to true (1) or false (0). Currently recognized option fields are:

"formulas_as_text"

If set to TRUE or 1, spreadsheet formulas (if at all present) are read as formula strings rather than the evaluated formula result values (the latter if at all present in th spreadsheet). The default value is 0 (FALSE).

"strip_array"

Set the value of this field set to TRUE or 1 to strip the returned output array rawarr from empty outer columns and rows. The spreadsheet cell rectangle limits from where the data actually came will be updated. The default value is FALSE or 0 (no cropping). When using the COM interface, the output array is always cropped.

"convert_utf"

If set to 1 or TRUE, xls2oct tries to do a best job of converting UTF-8 characters to one-byte characters so that they display properly in Octave if that uses a terminal that does not support UTF-8 encoding (e.g., Windows 7 and below). For the COM interface this conversion is done by libraries outside Octave so for COM this option has no effect.

If only the first argument xls is specified, xls2oct will try to read all contents from the first = leftmost (or the only) worksheet (as if a range of "" (empty string) was specified).

If only two arguments are specified, xls2oct assumes the second argument to be wsh. In that case xls2oct will try to read all data contained in that worksheet.

Return argument rawarr contains the raw spreadsheet cell data. Use utility function parsecell() to separate numeric and text values from rawarr.

Optional return argument xls contains the pointer struct. If any data were read, field xls.limits contains the outermost column and row numbers of the actually returned cell range.

Optional return argument rstatus will be set to 1 if the requested data have been read successfully, 0 otherwise.

Erroneous data and empty cells turn up empty in rawarr. Date/time values in xlsx/.xls files are returned as numerical values. Note that Excel and Octave have different date base values (epoch; 1/1/1900 and 1/1/0000, respectively). The epoch of returned date values depend on interface and version of the support SW. Be aware that the COM interface trims rawarr from empty outer rows & columns, so a returned cell array may turn out to be smaller than requested in range, independent of field ’formulas_as_text’ in options. When using COM, POI, or UNO interface, formulas in cells are evaluated; if that fails cached values are retrieved. These may be outdated depending on "Automatic calculation" settings when the spreadsheet was saved.

When reading from merged cells, all array elements NOT corresponding to the leftmost or upper spreadsheet cell will be treated as if the "corresponding" spreadsheet cells are empty.

Examples:

  A = xls2oct (xls1, '2nd_sheet', 'C3:AB40');
  (which returns the numeric contents in range C3:AB40 in worksheet
  '2nd_sheet' from a spreadsheet file pointed to in pointer struct xls1,
  into numeric array A) 
  [An, xls2, status] = xls2oct (xls2, 'Third_sheet');

See also: oct2xls, xlsopen, xlsclose, parsecell, xlsread, xlsfinfo, xlswrite.

Package: io