Table of Contents
This tutorial shows how to use the GAMS Connect agents ExcelReader and ExcelWriter to exchange data between GAMS and Microsoft Excel.
Import from Excel
Consider the following modification of the trnsport model from the GAMS model library:
Set i 'canning plants', j 'markets';
Parameter d(i<,j<) 'distance in thousands of miles'
a(i) 'capacity of plant i in cases'
b(j) 'demand at market j in cases';
$onEmbeddedCode Connect:
- ExcelReader:
file: input.xlsx
symbols:
- name: d
range: distance!A1
- name: a
range: capacity!A1
columnDimension: 0
- name: b
range: demand!A1
rowDimension: 0
- GAMSWriter:
symbols: all
$offEmbeddedCode
Scalar f 'freight in dollars per case per thousand miles' / 90 /;
Parameter c(i,j) 'transport cost in thousands of dollars per case';
c(i,j) = f*d(i,j)/1000;
Variable
x(i,j) 'shipment quantities in cases'
z 'total transportation costs in thousands of dollars';
Positive Variable x;
Equation
cost 'define objective function'
supply(i) 'observe supply limit at plant i'
demand(j) 'satisfy demand at market j';
cost.. z =e= sum((i,j), c(i,j)*x(i,j));
supply(i).. sum(j, x(i,j)) =l= a(i);
demand(j).. sum(i, x(i,j)) =g= b(j);
Model transport / all /;
solve transport using lp minimizing z;
display x.l, x.m;
The data for parameters d, a and b is stored in the Excel file input.xlsx with the following content:
The example uses compile-time embedded Connect code to read the data with the ExcelReader agent. The ExcelReader agent creates the parameters in the Connect database. All parameters can be directly written to the GAMS database using the GAMSWriter agent. The GAMSWriter makes the parameters available outside of the embedded Connect code. Note that the sets i and j are implicitly defined through the parameter d. Embedded Connect code is also available at execution time, see the example in the following section Export to Excel.
The ExcelReader provides many options that allow to conveniently read data from an Excel file into GAMS sets and parameters. For example, it can handle open ranges, which together with skipEmpty provide a flexible way to read data even when a spreadsheet contains multiple data tables. The options autoMerge and mergedCells can be used to control the handling of merged cells. The agent also allows to manipulate data already while reading using indexSubstitutions and valueSubstitutions. Refer to the ExcelReader documentation to learn more.
Export to Excel
Consider the following modification of the trnsport model from the GAMS model library:
Sets
i 'canning plants' / seattle, san-diego /
j 'markets' / new-york, chicago, topeka / ;
Parameters
a(i) 'capacity of plant i in cases'
/ seattle 350
san-diego 600 /
b(j) 'demand at market j in cases'
/ new-york 325
chicago 300
topeka 275 / ;
Table d(i,j) 'distance in thousands of miles'
new-york chicago topeka
seattle 2.5 1.7 1.8
san-diego 2.5 1.8 1.4 ;
Scalar f 'freight in dollars per case per thousand miles' / 90 /;
Parameter c(i,j) 'transport cost in thousands of dollars per case';
c(i,j) = f*d(i,j)/1000;
Variable
x(i,j) 'shipment quantities in cases'
z 'total transportation costs in thousands of dollars';
Positive Variable x;
Equation
cost 'define objective function'
supply(i) 'observe supply limit at plant i'
demand(j) 'satisfy demand at market j';
cost.. z =e= sum((i,j), c(i,j)*x(i,j));
supply(i).. sum(j, x(i,j)) =l= a(i);
demand(j).. sum(i, x(i,j)) =g= b(j);
Model transport / all /;
solve transport using lp minimizing z;
display x.l, x.m;
EmbeddedCode Connect:
- GAMSReader:
symbols:
- name: x
- Projection:
name: x.l(i,j)
newName: x_level(i,j)
- ExcelWriter:
file: output.xlsx
symbols:
- name: x_level
endEmbeddedCode
The example uses execution-time embedded Connect code to export the results to an Excel file. The GAMSReader agents imports variable x from the GAMS database into the Connect database. The Projection agent is used to create a new parameter that contains only the level values of variable x. The final parameter x_level is then written to output.xlsx using the ExcelWriter agent. Since no range is specified the parameter will be written to range x_level!A1. The content of the file output.xlsx looks as follows:
The ExcelWriter provides many options that allow to write tailored Excel files. For example, the option mergedCells can be used to write the data with merged cells into an Excel sheet. The option tableOfContents allows to write a table of contents into an additional sheet. Refer to the ExcelWriter documentation to learn more.