Table of Contents
This tutorial gives an overview on how to exchange data between GAMS and Microsoft Excel.
GAMS can communicate with Microsoft Excel via GDX (GAMS Data Exchange) files. In order to write data from GAMS and to Excel, selected GAMS data can be written into a GDX file and then to an Excel file: GAMS -> GDX -> Excel. Similarly selected Excel data can be written to a GDX file and then read into GAMS: Excel -> GDX -> GAMS.
Some of GAMS/Excel data exchange tools that provide functionality to exchange data between GAMS and Excel are also discussed in the section Data Exchange Tools. The data exchange between GAMS and a CSV (Comma-separated values) file format and GAMS is covered in Data Exchange with Text Files.
From GAMS to Excel
Consider the following modication 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 ;
Variables
x(i,j) 'shipment quantities in cases'
z 'total transportation costs in thousands of dollars' ;
Positive Variable x ;
Equations
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 ;
*=== Export to Excel using GDX utilities
*=== First unload to GDX file (occurs during execution phase)
execute_unload "results.gdx" x.L x.M
*=== Now write to variable levels to Excel file from GDX
*=== Since we do not specify a sheet, data is placed in first sheet
execute 'gdxxrw.exe results.gdx o=results.xlsx var=x.L'
*=== Write marginals to a different sheet with a specific range
execute 'gdxxrw.exe results.gdx o=results.xlsx var=x.M rng=NewSheet!f1:i4'
After the solve statement, the data (x.L and x.M) from variable x can be written into a GDX file during the execution time using the command execute_unload:
execute_unload "results.gdx" x.L x.M
The execute_unload command above is executed during the actual execution phase to create a GDX file called results.gdx. The solution x and the marginals of x in the GDX file can be written to the Excel file results.xlsx using GDXXRW tool:
execute 'gdxxrw.exe results.gdx var=x.L'
execute 'gdxxrw.exe results.gdx var=x.M rng=NewSheet!f1:i4'
For the first call for x.L, there is no range specified and the data is written in cell A1 and beyond in the first available sheet. For the second call for marginals x.M, data will be written to cells F1:I4 in the sheet named NewSheet.
Note that GAMS can also write data into a GDX file during compile time. It is also possible to convert data stored in a GDX file into an Excel file spreadsheets using GDX2XLS tool and to write GAMS data to standard output formatted as a GAMS program with data statements using GDXDUMP tool.
From Excel to GAMS
Consider the following modifciation of the [TRNSPORT] model from the gams model library and the file results.xlsx file created from the previous example.
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 ;
Variables
x(i,j) 'shipment quantities in cases'
z 'total transportation costs in thousands of dollars' ;
Positive Variable x ;
Equations
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/ ;
*=== Import from Excel using GDX utilities
*=== First unload to GDX file (occurs during compilation phase)
$call gdxxrw.exe results.xlsx par=Level rng=sheet1!A1:D3
*=== Now import data from GDX
Parameter Level(i,j);
$gdxin results.gdx
$load Level
$gdxin
*=== Fix variables to values from Excel file
x.FX(i,j) = Level(i,j);
display Level, x.L;
Solve transport using lp minimizing z ;
Display x.l, x.m ;
The data in the Excel file can be loaded into a GDX file using the $call command and GDXXRW tool:
$call gdxxrw.exe results.xlsx par=Level rng=A1:D3
The command $call above executes a program called GDXXRW during compile time. The GDXXRW reads data from the range A1:D3 in results.xlsx into a GAMS parameter called Level in the GDX file results.gdx. As an output GDX file is not specified when calling GDXXRW, the output file will be derived from the input file by changing the file extension of the input file and removing any path information.
To import data from a GDX file into a parameter, the parameter must be defined over appropriate sets before read. The data from a GDX file can be read during the compile time using the commands $gdxin and $load:
Parameter Level(i,j);
$gdxin results.gdx
$load Level
$gdxin
The first command $gdxin specifies the name of the GDX file results.gdx to be read. The command $load reads parameter Level from the GDX file. The second command $gdxin closed the GDX file.
GAMS can read from a GDX file either during compile time or during execution time. See Example 4 - Reading a GDX File when reading data with domain information and Example 5 - Reading a GDX File when reading from a GDX file during execution time.
Note that it is also possible to write all worksheets of an Excel workbook into a GDX file using XLSDUMP tool.
Data Exchange Tools
There are a number of tools that provide functionality to exchange data between GAMS and an Excel file. This section discusses some of the data exchange tools with some examples. The complete list of the tools can be found at GAMS/Excel Data Exchange tools.
GDXXRW
GDXXRW is a tool to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet.
How to use GDXXRW to exchange data between GAMS and Excel is covered in the section From GAMS to Excel and the section From Excel to GAMS. More details on usage and examples of GDXXRW tool is covered in GDXXRW.
XLS2GMS
XLS2GMS is a simple utility that allows you to extract data from an Excel spreadsheet and convert it into a GAMS include file. XLS2GMS can be run interactively or in batch mode.
Consider the Excel data from the following spreadsheet:
The data can be imported from the Excel file into a GAMS include file by calling XLS2GMS tool and inserted an include file as parameter data elements using the command $include:
set ssi /
'new york', 'washington dc', 'los angeles', 'san francisco'
/;
parameter ssdata(ssi) /
$call =d:\util\xls2gms I="c:\my documents\test2.xlsx" B O=d:\tmp\x.inc
$include d:\tmp\x.inc
/;
display ssdata;
Notice the B parameter, which is needed as there are embedded blanks in the labels.
Sometimes a translation between the labels used in the model and the ones used in the is needed. One way to do this is to use a mapping set in GAMS. Suppose the rest of the model is defined in terms of the set I which is defined as:
set i / ny, dc, la, sf/;
To map a parameter data defined over this set, the following simple GAMS fragment can be used:
set map(i,ssi) mapping set /
ny.'new york'
dc.'washington dc'
la.'los angeles'
sf.'san francisco'
/;
display map;
parameter data(i);
data(i) = sum(map(i,ssi), ssdata(ssi));
display data;
SQL2GMS
In some cases it is convenient to consider tabular data in an Excel spreadsheet as a database table and to import it via GDX file using the SQL2GMS tool.
Consider the following spreadsheet:
This table can be read using an SQL query:
SELECT year,loc,prod,'sales',sales FROM [profitdata$] \ UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]
The table name is equal to the sheet name(profitdata). We can pass the query to the Excel ODBC driver using the tool SQL2GMS tool as follows:
set y 'years' /1997,1998/;
set c 'city' /la,nyc,sfo,was/;
set p 'product' /hardware,software/;
set k 'key' /sales,profit/;
$onecho > excelcmd.txt
c=DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);dbq=%system.fp%profit.xlsx;
q=SELECT year,loc,prod,'sales',sales FROM [profitdata$] \
UNION SELECT year,loc,prod,'profit',profit FROM [profitdata$]
x=fromexcel.gdx
$offecho
$call =sql2gms @excelcmd.txt
parameter d(y,c,p,k) ;
$gdxin excel.gdx
$load d=p
display d;
and the DISPLAY results will be:
--- 21 PARAMETER d FROM SQL2GMS
INDEX 1 = 1997
sales profit
la .hardware 80.000 5.000
la .software 60.000 10.000
nyc.hardware 100.000 15.000
nyc.software 130.000 25.000
sfo.hardware 50.000 9.000
sfo.software 60.000 6.000
was.hardware 80.000 7.000
was.software 90.000 8.000
INDEX 1 = 1998
sales profit
la .hardware 88.000 5.250
la .software 66.000 10.500
nyc.hardware 110.000 15.750
nyc.software 143.000 26.250
sfo.hardware 55.000 9.450
sfo.software 66.000 6.300
was.hardware 88.000 7.350
was.software 99.000 8.400
GDXVIEWER
GDXVIEWER is a tool to view and convert data contained in GDX files. It can also export to csv, xlsx, xml-files and pivot tables. The usage and examples are covered in GDXVIEWER.
GDX2XLS
GDX2XLS tool to convert the contents of a GDX file into an Excel file or an xml-file. The usage and examples are covered in GDX2XLS.