Instead of recreating the data that was in an external data source – such as an Excel spreadsheet or an Access database, in this tutorial, we can load data from Excel directly.
This only works in Windows (not MacOS or Linux). Details at the end of this post.
From CPLEX Studio, Engine & OPL Kickstart for Optimization blog post, we had separated the data from model into a .dat file.
.mod
/*********************************************
* OPL 12.9.0.0 Model
* Author: kafechew
* Creation Date: 25 Aug 2019 at 8:28:14 PM
*********************************************/
// Parameters
int nBoxes = ...;
int capacityTruckX = ...;
int capacityTruckY = ...;
float costTruckX = ...;
float costTruckY = ...;
// Decision Variables
dvar int+ truckX;
dvar int+ truckY;
// Objective Function
minimize costTruckX * truckX + costTruckY * truckY;
// Constraints
subject to {
MinimumCapacityforBoxes: // name of the constraint
capacityTruckX * truckX + capacityTruckY * truckY >= nBoxes;
}
.dat
/*********************************************
* OPL 12.9.0.0 Data
* Author: kafechew
* Creation Date: 25 Aug 2019 at 8:28:14 PM
*********************************************/
nBoxes = 300;
capacityTruckX = 40;
capacityTruckY = 30;
costTruckX = 500;
costTruckY = 400;
We need to copy the values of nBoxes, capacityTruckX, … etc. into a new excel spreadsheet. We renamed the spreadsheet as Prob1.xlsx and renamed the main tab as Prob1Data.
We need to move the spreadsheet into the same directory as the model. Right-click the project folder and select Refresh (F5).
We need to create a new .dat file, make sure it is in our project directory. Right-click the project folder and select New, then Data. We name the new .dat file as Prob1Excel.dat.
To read from Excel, we first need the SheetConnection command that connects to the spreadsheet. The SheetConnection command is followed by a local variable name, my_sheet in this case and then the name of the Excel file in quotes. We need to end with a semicolon.
SheetConnection my_sheet("Prob1.xlsx");
Once we have connected to the sheet, we then can define where we get the values for our variables. We do this with the from SheetRead() command. Within the parentheses, we define which Excel file to read with the variable sheet in this case, and then in double quotes, define the worksheet name (which is in single quotes if it has spaces) and the cell range.
nBoxes from SheetRead(my_sheet, "'Prob1Data'!B1:B1");
capacityTruckX from SheetRead(my_sheet, "'Prob1Data'!B2:B2");
capacityTruckY from SheetRead(my_sheet, "'Prob1Data'!B3:B3");
costTruckX from SheetRead(my_sheet, "'Prob1Data'!B4:B4");
costTruckY from SheetRead(my_sheet, "'Prob1Data'!B5:B5");
Our new .dat file:
SheetConnection my_sheet("Prob1.xlsx");
nBoxes from SheetRead(my_sheet, "'Prob1Data'!B1:B1");
capacityTruckX from SheetRead(my_sheet, "'Prob1Data'!B2:B2");
capacityTruckY from SheetRead(my_sheet, "'Prob1Data'!B3:B3");
costTruckX from SheetRead(my_sheet, "'Prob1Data'!B4:B4");
costTruckY from SheetRead(my_sheet, "'Prob1Data'!B5:B5");
Besides seeing how to read from Excel, we now understand the flexibility of the run configurations. Right-click on the project folder, click New and select Run Configurations. Name this run configuration Prob1ExcelRC. We will see this new run configuration. Now drag the .mod and the modified .dat into this new run configuration. Now, right-click on Prob1ExcelRC and select Run This. We will get the same answer as before. We can create as many as we need and reuse different data sets, leaving original models and data intact.
MacOS and Linux
If we tried to read data from Excel to CPLEX in Macbook, the error “Sheet data not supported on this platform” will be shown. There is no issue with Windows machine. Unfortunately, reading data from Excel is not supported on MacOS and Linux. Here are our options:
- Convert our data to a .dat file from Excel. This file can immediately used as input.
- Export our data to a .csv file and use an IloOplInputFile to load/parse this file.
- Use a 3rd party library (like that Apache POI framework) to load Excel data into our model.
Reference: