GAMS [ Home | Support | Sales | Solvers | Documentation | Model Library | Search | Contact Us ]
GAMS/Excel Using GDX
Introduction
This section gives a brief overview on how to use the GDX facilities in GAMS to read data from Excel and to write data to Excel. For more detailed information see the "GDX Facilities" documentation.

System Requirements: A GAMS system Disitrbution 21.0 or later is required.

Introduction
GAMS communicates with Excel via GDX (GAMS Data Exchange) files.

A GDX file is a file that stores the values of one or more GAMS symbols such as sets, parameters variables and equations. GDX files can be used to prepare data for a GAMS model, present results of a GAMS model, store results of the same model using different parameters etc. A GDX file does not store a model formulation or executable statements.

GDX files are binary files that are portable between different platforms. They are written using the byte ordering native to the hardware platform they are created on, but can be read on a platform using a different byte ordering.

In order to write data from GAMS to Excel, the user writes a GDX file and then writes the Excel file from the GDX file. 

          GAMS ----> GDX ----> Excel
This is practically seamless for the user and requires few commands. The process to import data from an Excel file to GAMS is similar:

          Excel ----> GDX ----> GAMS

Example: GAMS to Excel
We will build on the simple transportation model from the GAMS Model library and write the solution x and the marginals of x to an Excel file.

After the solve statement, we unload the data (x.L and x.M) to a GDX file using the execute_unload command: 

          execute_unload "results.gdx" x.L x.M
Note that the execute_unload command is executed during the actual execution phase (not during compilation time as $ control options) and creates a GDX file called results.gdx.

Now let us write the data from the GDX file to an Excel file called results.xls. We do this using the GDXXRW utility

          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 marginals x.M data will be written to cells F1:I4 in the sheet NewSheet. Note that we specified var=x.L and var=x.M. If the user wishes to write parameters to the Excel file, the relevant command is par. See the GDX facilities in GAMS document for further details.

Example: Excel to GAMS
Again, we will use the transportation model and make use of the results.xls file created by the previous model. First we will create the GDX file from the Excel file. We will make use of the GDXXRW utility: 
             $CALL GDXXRW.EXE results.xls par=Level rng=A1:D3
Note that since we are using the $CALL command, this occurs during the compilation phase and not during execution time. We specify that the data in the range A1:D3 is read in as a parameter called Level. The resulting GDX file will be called results.gdx

Before we can read in the data, we must define a parameter called Level over the appropriate sets: 

            Parameter Level(i,j);
            $GDXIN results.gdx
            $LOAD Level
            $GDXIN
The GDXIN results.gdx command specifies that data will be read in from the appropriate GDX file. We then import data structures values using the $LOAD command. When we are finished, we terminate with $GDXIN.

In the example below, we then fix the level values of the variable x to the parameter Level so that solving results in a trivial fixed model.