Function Reference: xlsopen

Function File: xls = xlsopen (filename) ¶

Function File: xls = xlsopen (filename, readwrite) ¶

Function File: xls = xlsopen (filename, readwrite, reqintf) ¶

Function File: xls = xlsopen (filename, readwrite, reqintf, verb) ¶

Get a pointer to a spreadsheet in memory in the form of return argument (file pointer struct) 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.

filename should be a valid spreadsheet file name (including extension); see "help xlsread" for an overview of supported spreadsheet file formats.

If readwrite is set to 0 (default value) or omitted, the spreadsheet file is opened for reading. If readwrite is set to true or 1, a spreadsheet file is opened (or created) for reading & writing.

Optional input argument 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 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).

Spreadsheet I/O Interfaces ========================== 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/LibreOffice or OpenOffice.org - EXPERIMENTAL!), ’OTK’ (ODF Toolkit), ’JOD’ (jOpendocument); see below:

xls and .xlsx:

One or more of (1) a Java JRE plus Apache POI >= 3.5, and/or JExcelAPI and/or OpenXLS, and/or LibreOffice or OpenOffice.org 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. Note that the OXS interface does not work on all systems due to incampitibility with newer Java version.
Excel’95 spreadsheets (BIFF5) can only be read using the JXL (JExcelAPI), UNO (LibreOffice/OpenOffice.org), and COM (Excel-ActiveX) interfaces.

.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 LibreOffice/OpenOffice.org 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.

Other formats:

By invoking the UNO interface one can read any format that the installed LibreOffice/OpenOffice.org version supports; see below. The same goes (on Windows systems) for the COM interface (invoking MS-Excel). However, writing to other file formats than .xlsx, .ods and .xls is not implemented for COM.

Depending on the installed LibreOffice/OpenOffice.org release, in addition to .xls, .xlsx, .ods and .sxc, the following file formats may be read/written (untested!) when using the UNO interface. The pertinent import/export filters are inferred from the filename extension.

  File format                           filename extension(s)
  ====================================  =====================
  "Gnumeric"                            .gnumeric, .gnm
  "Text CSV"                            .csv
  "UOF spreadsheet"                     .uos
  "OpenDocument Spreadsheet Flat XML"   .fods
  "dBase"                               .dbf
  "Digital Interchange Format"          .dif
  "Lotus 1-2-3"                         .wk1 .wk2 .123
  "WPS Lotus Calc"                      .wk3 .wk4
  "MS Works Calc"                       .wks, .wdb
  "ClarisWorks Calc"                    .cwk
  "Mac Works Calc"                      .wps
  "Quattro Pro 6.0"                     .wb2
  "WPS QPro Calc"                       .wb1 .wq1 .wq2
  "Rich Text Format (StarCalc)"         .rtf
  "SYLK"                                .slk .sylk
  "Apple Numbers"                       .numbers
  "Microsoft Multiplan"                 .mp

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. For UNO, the default socket transport starts or reuses a local LibreOffice listener. A listener started by Octave is terminated when the io package is unloaded or Octave exits, unless OCTAVE_IO_UNO_KEEPALIVE is set to a true value. Existing LibreOffice desktop sessions are left running. Automatic pipe names and socket ports contain the Octave process ID, so kept listeners are not rediscovered by later sessions unless connection details are explicit.

UNO socket transport listens on unauthenticated localhost TCP. That blocks remote network access but not other local users or processes on shared machines. Use OCTAVE_IO_UNO_TRANSPORT=pipe (or pipes) for an Octave-started named-pipe listener without local TCP exposure. Use bootstrap only for the older Java Bootstrap launcher. Managed socket and pipe transports target LibreOffice only. Apache OpenOffice is not supported with these transports; use its legacy Bootstrap launcher by selecting OCTAVE_IO_UNO_TRANSPORT=bootstrap. Some LibreOffice installations can omit the Java/UNO components required by this interface; install those components if listener startup repeatedly fails.

OCTAVE_IO_UNO_TRANSPORT: UNO transport. "socket" is the default. "pipe" and "pipes" select an Octave-started named-pipe listener. "bootstrap" selects the older Java Bootstrap launcher.
OCTAVE_IO_UNO_URL: Explicit UNO URL to connect to. If set, Octave does not start an office process.
OCTAVE_IO_UNO_PORT: TCP port for an Octave-started socket listener. Ignored for pipe transport. If omitted, io chooses a user- and process-specific high port to reduce collisions.
OCTAVE_IO_UNO_PROFILE: LibreOffice user profile for an Octave-started listener. If omitted, io uses a user- and process-specific profile below tempdir and removes it on package unload unless OCTAVE_IO_UNO_KEEPALIVE is true.
OCTAVE_IO_UNO_KEEPALIVE: If set to "1", "true", "yes", or "on", keep an Octave-started listener and its automatic profile after package unload or Octave exit. Later sessions choose different process-specific defaults unless connection details are set explicitly.
OCTAVE_IO_SOFFICE: Full path to the LibreOffice soffice executable used by managed socket and pipe transports.

Examples:

  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)

See also: xlsclose, xlsread, xlswrite, xls2oct, oct2xls, xlsfinfo, chk_spreadsheet_support.

Package: io