SheetWrite

OPL keyword to write results to a spreadsheet.

context
Data files (.dat)

Syntax

Publish: "DBExecute" '(' Id String ')'
       | Id "to" "DBUpdate" '(' Id String ')'
       | Id "to" "SheetWrite" '('Id String')'

Description

In the context of spreadsheet initialization, you use the SheetWrite instruction to publish the results of a solved model in a spreadsheet. This instruction causes OPL to open the spreadsheet in read-write mode. This action may fail if another process is already using the .xls file. The types of data supported for writing are the same as for reading (see SheetRead).

If you do not specify a spreadsheet, the active spreadsheet is selected (in Microsoft Excel, the one in bold).

Note:

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

Example

See the oilSheet example in the current distribution.

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 with the SheetWrite command, using the following syntax:

SheetConnection sheetData("C:\\ILOG_Files\\myExcelFile.xls");
prods to SheetWrite(sheetData,"Product");
Note the double separator \\ in the SheetConnection command.

The SheetWrite command is normal, and in this example the Excel name Product replaces the normal syntax of, for example, 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, you must be careful when adding rows at the end of a range, as the range is not automatically updated. It has to be updated manually.

  • OPL allows blank rows in a named range. If you are reading a set of numbers, 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 a named range but instead use explicit ranges such as 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 in Excel, error messages are sent if the size of the data does not match the size of the range.

    • If the data set is smaller than the range, you receive the error message:

      Exception from IBM ILOG Concert: Excel: range does not fit the dimensions of the array.

    • If you try to write more data than the range can accommodate, you receive the error message:

      Exception from 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.