Reading from a spreadsheet

Explains how to read from a spreadsheet from within OPL.

In OPL, spreadsheet ranges can be read into one- or two-dimensional arrays or sets. For instance, the instructions:


/* .mod file */
{string} Gasolines = ...;

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

GasType gas[Gasolines] = ...;

/* .dat file */
SheetConnection sheet("oilSheet.xls");

Gasolines from SheetRead(sheet,"'gas data'!A2:A4");
Gas from SheetRead(sheet,"'gas data'!B2:E4");

What data can be read from an Excel spreadsheet?

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

The types of data elements supported are:

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

  • scalar integers, floats, or strings;

  • arrays with integers, floats, one- or two-dimensional strings, or one-dimensional tuples;

  • one- or two-dimensional arrays of simple types: for such arrays, the data must be formatted, that is, it must have the same width/length as the array to be filled. OPL automatically determines whether the data must be read line by line or column by column. When facing a square zone (a two-dimensional array with [x][x] as dimensions), the engine reads the data line by line.

Note:

  • 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");

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

Accessing named ranges in Excel

IBM ILOG OPL supports the convention of names, which are a word or string of characters used to represent a cell, range of cells, formula, or constant value, and that can be used in other formulas.

Thus you can use easy-to-understand names, such as Nutrients, to refer to hard-to-understand ranges, such as B4:J15 or IncreasedProtein to refer to a constraint. You can then substitute these names in formulas for the range of cells or constraint.

Excel names, or named ranges, can be accessed using the SheetRead command, using the following syntax:

SheetConnection sheetData("C:\\ILOG_Files\\myExcelFile.xls");
prods from SheetRead(sheetData,"Product");

Note the double separator \\ in the SheetConnection command.

The SheetRead command is normal, and in this example the Excel name Product replaces the normal syntax of, say, C13:O72.

To create named ranges in Excel 2007:

  1. Highlight the range of cells you want to name, then click the Name box at the left end of the Formula Bar.

  2. Type the name you want to assign to this range and press Enter.

  3. Save the spreadsheet file.

Note: For a list of supported versions of Microsoft Excel, please see the detailed system requirements http://www-01.ibm.com/support/docview.wss?uid=swg27019100.

Additional information on named ranges

  • Excel automatically updates (expands) a named range when a row is added somewhere within the range. However, one must careful adding rows at the end of a range as the range does not get automatically updated in that case. It would have to be updated manually.

  • OPL allows blank rows in a named range. If you are reading a set of strings, it will consider the blank cells as having the value 0. If you are reading a set of strings, then it inserts an empty string "" into the set. For example:

    s2 = {"Monday" "" "Wednesday" "Thursday" "Friday"}

    This behavior is the same when you don't use named range but instead use explicit ranges like C1:C5, where C2 is empty.

  • With the Excel VBA one can name the first (top left) cell of a named range and access the whole range. OPL does not support this.

  • When using sheetWrite to write to named ranges, the size of the range does not have to match the size of the data you are writing to Excel. If the set is smaller, then only the top most cells will be filled.

    If you try to write more data than the range can accommodate, you receive the error message: "Exception from IBM ILOG Concert: excel: range is not wide enough to write the set".

    In this sense, named ranges behave in exactly the same way as "regular" ranges.

Format of the Excel data

Here we must differentiate between simple types and tuples:

  • Sets of simple types: The engine reads data from left to right and top to bottom. Data can therefore be read either horizontally, vertically, or from a rectangular zone.

  • Sets of tuples: The data has to be formatted because the tuple schema has an arity. As in databases and manual tables, the data format is “fixed width, variable length”. Therefore, tuple sets are read only line by line in Excel: this is the same representation as in pure data files.