The oil sheet data file

Describes the data file in the project.

Double-click the oilSheet.dat file in the project tree to see the contents of the data file.

Data file for the Oil Sheet example (`oilSheet.dat`)

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");
MaxProduction = 14000;
ProdCost = 4;


a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");

The data file starts with the SheetConnection statement used to connect to the spreadsheet.

Note:

You can have multiple data files and, within any of them, multiple connections to spreadsheets.

Connecting to the spreadsheet from OPL

The connection is established by the following statement:

Connecting to a spreadsheet (MS Excel)

SheetConnection sheet("oilSheet.xls");

The name of the spreadsheet file in quotation marks is passed as an argument.

Note:

You do not need to specify the full path name. Relative paths are resolved using the directory of the current .dat files.

Reading from the spreadsheet

Reading spreadsheet columns

You can read data from a column in any sheet into an OPL array using the SheetRead statement, as in the data file oilSheet.dat.

Reading spreadsheet columns (oilSheet.dat)

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

Note that the cells are read from 2 upward as the name of the column is not stored.

Reading spreadsheet cell ranges

You can read blocks of cells from a spreadsheet into an OPL array using the SheetRead statement, as in oilSheet.dat.

Reading spreadsheet cells (oilSheet.dat)

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

Note that the columns read are B to E (not A) and the cells read are from 2 upward as the name column is not used and the names of the other columns are not stored in the data arrays.

Note also that the data file initializes some variables directly; it does not take data only from the spreadsheet.

Writing the results to the spreadsheet

At the end of the optimization process, you need to store the optimal blends and advertising expenditures in the RESULTS sheet.

You can insert the oil, gas, blend, and a arrays as columns in the RESULT sheet using a SheetWrite statement.

Writing results to a spreadsheet (oilSheet.dat)

a to SheetWrite(sheet,"RESULT!A2:A4");
Blend to SheetWrite(sheet,"RESULT!B2:D4");