SheetRead

OPL keyword to read spreadsheet ranges into multidimensional arrays or sets.

context
Data files (.dat)

Syntax

Read: Id "from" "DBRead" '(' Id String ')' PlaceHolders_opt
    | Id "from" "SheetRead" '(' Id String ')'

Description

Use the SheetRead keyword to read spreadsheet ranges into OPL 1-D or 2-D arrays and sets, as shown in the following example. If you do not specify a sheet, the active sheet is selected (in Microsoft Excel, the one in bold).

Note:

  • OPL does not support the R1C1 reference style to specify the range when reading data from an Excel spreadsheet.

Example

From the oilSheet example:

In the model file

{string} Gasolines = ...;
{string} Oils = ...;

tuple gasType {
  float demand;
  float price;
  float octane;
  float lead;
}

tuple oilType {
  float capacity;
  float price;
  float octane;
  float lead;
}

gasType Gas[Gasolines] = ...;
oilType Oil[Oils] = ...;

In the data file

SheetConnection sheet("oilSheet.xls");

Gasolines from SheetRead(sheet,"'gas data'!A2:A4");
Oils from SheetRead(sheet,"'oil data'!A2:A4");

Gas from SheetRead(sheet,"'gas data'!B2:E4");
Oil from SheetRead(sheet,"'oil data'!B2:E4");

OPL opens a spreadsheet in read-only mode to read data from it.

The types of data elements supported are the same as for databases, plus arrays with one or two dimensions:

  • sets with integers, floats, strings, or tuples;

  • arrays with integers, floats, strings, or tuples with up to two dimensions;

  • Only tuples with integer, float, and string components are supported.

If the sheet name contains a space, the name should be surrounded by single quotes in the SheetRead instruction. For example:

Oil from SheetRead(sheet,"'oil data'!B2:E4");

See What data can be read from an Excel spreadsheet? in the Language Reference Manual.