GDX2XLS

A Tool to convert GDX data to MS Excel spreadsheets

Author
Erwin Kalvelagen
Date
June 10, 2005; December 6, 2006 This document describes the GDX2XLS utility which allows to convert data stored in a GDX file into Excel spreadsheets.

Overview

GDX2XLS is a tool to dump the complete contents of a GDX file to an MS Excel spreadsheet file (.xlsx or .xls file). Every identifier gets its own sheet in the .XLSX file. Excel 2007 or more recent versions will default to an xlsx file; versions before that default to the .xls file format. For instance when we save the results of the trnsport model from the model library:

C:\tmp>gamslib trnsport
Model trnsport.gms retrieved

C:\tmp>gams trnsport gdx=trnsport lo=2

C:\tmp>gdxdump trnsport.gdx symbols
   Symbol Dim Type  Explanatory text
 1 a        1  Par  capacity of plant i in cases
 2 b        1  Par  demand at market j in cases
 3 c        2  Par  transport cost in thousands of dollars per case
 4 cost     0  Equ  define objective function
 5 d        2  Par  distance in thousands of miles
 6 demand   1  Equ  satisfy demand at market j
 7 f        0  Par  freight in dollars per case per thousand miles
 8 i        1  Set  canning plants
 9 j        1  Set  markets
10 supply   1  Equ  observe supply limit at plant i
11 x        2  Var  shipment quantities in cases
12 z        0  Var  total transportation costs in thousands of dollars

C:\tmp>

The example shows how we copy the trnsport.gms model from the model library, and then solve it. The option gdx=filename will save the complete symbol table to a GDX file. The option lo=2 tells GAMS to save the log to a file (in this case trnsport.log) instead of writing it to the screen. The gdxdump will display the contents of the GDX file (the option symbols will only display the table of contents, rather than all data)

Once we have a GDX file we can use GDX2XLS to create an .XLSX file:

C:\tmp>gdx2xls trnsport.gdx
GDX2XLS          24.4.1 r50296 Released Dec 20, 2014 VS8 x86 32bit/MS Windows
Output file: C:\tmp\trnsport.xlsx
(Created backup file)

C:\tmp>

gdx2xls1.png
Figure 1. Table of Contents sheet

The resulting XLSX file, opened with MS Excel is shown in Figure 1. The first page is the Table of Contents page with all identifiers sorted alphabetically. When clicking on variable x, the sheet shown in Figure 2 is displayed.

gdx2xls2.png
Figure 2. A variable from the trnsport.gms model

The table of contents can be reached again by clicking on the TOC link in the top left corner. The complete process shown here can be automated as is shown in section Model gdx2xls1: import trnsport.gdx. As can be seen, every identifier is stored in its own sheet. Index positions get a column with labels showing their domains, or dim1, dim2, etc. if domain information is not available. By default scalar quantities are collected in a single sheet called scalar.

AutoFilter

By default the exported tables are organized in AutoFilter tables. This will allow you to easily make selections and sort the results.

gdx2xls4.png
Figure 4. Drop down list in AutoFilter

It is possible to set filters for different columns. Only the rows that meet the criteria will be shown. The columns used in the filter can be recognized by having a blue arrow instead of a black one in the drop down menu header.

gdx2xls5.png
Figure 5. Top 10 list configuration in AutoFilter

gdx2xls6.png
Figure 6. Custom filter in AutoFilter

Sorting can also be performed on multiple columns: e.g. first sort on one column, then sort on a second column.

The autofilter generation can be turned off using an option in the .ini file.

Options

Default ini file

Options are specified in an .INI file. By default, the file gdx2xls.ini located in the same directory as gdx2xls.exe is consulted. If this file is not available, the program will continue using default settings.

Custom ini file

It is also possible to tell the program to use a different .ini file. This is done by using an extra argument of the form @inifile. An example would be:

C:\TMP> gdx2xls myfile.gdx @myinifile.ini

In this case the program will not read gdx2xls.ini located in the same directory as gdx2xls.exe but rather myinifile.ini in the current directory.

The ini file can contain two sections: [settings] and [colors]. A complete ini file with all possible settings looks like:

[settings]
inf=INF
mininf=-INF
eps=EPS
na=NA
undf=INDF
scalarsheet=1
tableformatting=1
toc=1
sorttoc=1
autofilter=1
freezeheader=1
indexformat=
valueformat=

[colors]
header=17
body=19
italics=48

[xmlcolors]
link=#0000FF
header=#9999FF
body=#FFFFCC
italics=#969696

Settings section

A complete description for the [settings] section is:

[settings] Description
inf Special values may need to be mapped to numeric values so the values can be used in formula's etc. This setting will specify the value for the GAMS INF quantity. The default is the string INF.
mininf This is the mapped value for -INF. The default is -INF.
eps This is the mapped value to be used for EPS. The default is EPS.
na This is the mapped value to be used for NA. The default is NA.
undf This is the mapped value to be used for UNDF. The default is UNDF.
scalarsheet When this parameter is set to 1, GDX2XLS will generate a separate sheet to collect scalar parameters, scalar equations and scalar variables. This can reduce the number of sheets created with just a single data item. The name of the sheet is fixed: scalar. By default this option is turned on.
tableformatting If this option is turned on, extra table formatting is used (adding colors, etc.) to make the tables look better. If this is not needed, this option can be turned off. Default: tableformatting=0.
toc Whether or not to add a {Table of Contents} sheet. Default is to generate such a table.
sorttoc Whether or not to sort the table of contents alphabetically. If turned off, the table will be displayed in the order in which the identifiers appear in the GDX file. Default is to sort.
autofilter Automatically generate AutoFilter enabled tables in Excel.
freezeheader Keep headers fixed so they don't scroll off the screen.
indexformat Custom format for index columns. By default this is an empty string.
valueformat Custom format for value columns. By default this is an empty string.

An example of setting special values can be found in section Model gdx2xls4: special value mapping.

Colors section

A complete description for the [colors] section is:

[colors] Description
header The colorindex to be used as background for table headers. Default is 17.
body The colorindex to be used as background for table bodies. Default is 19.
italics The colorindex to be used for the font when writing explanatory text. The default is light grey (color index 48).

The [xmlcolors] section is used to specify colors in the XML file to be generated.

Custom formats

The format strings consists of four pieces:

[format for $x>0$];[format for $x<0$];[format for $x=0$];[format for strings]

An example given in the Excel help is:

#,###.00_);[Red](#,###.00);0.00;"sales "@

The codes used here have the following meaning:

Formatting Characters Description
# (number sign) displays only significant digits and does not display insignificant zeros.
, (comma) To display a comma as a thousands separator or to scale a number by a multiple of one thousand, include a comma in the number format.
0 (zero) displays insignificant zeros if a number has fewer digits than there are zeros in the format.
_ (underscore) To create a space the width of a character in a number format, include an underscore, followed by the character. For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.
[color] One of [Black], [Blue], [Cyan], [Green], [Magenta], [Red], [White], [Yellow].
@ (at sign) Include an at sign (@) in the section where you want to display any text entered in the cell.

Additional formatting characters include:

Formatting Characters Description
? (question mark) adds spaces for insignificant zeros on either side of the decimal point so that decimal points align when formatted with a fixed-width font, such as Courier New. You can also use ? for fractions that have varying numbers of digits.
condition Conditions can be specified as follows: [Red][<=100];[Blue][>100].
exponent To display numbers in scientific format, use exponent codes in a section, for example, E-, E+, e-, or e+.

gdx2xls7.png
Figure 7. Custom format valueformat=#.????

A useful format is:

[settings]
valueformat=#.????

which aligns numbers on the decimal point and depicts zero's as dots just as the listing file is doing.

Examples

Model gdx2xls1: import trnsport.gdx

This example will solve the trnsport.gms model from the model library and generate a GDX file containing the complete symbol table. This GDX file is exported to Excel and MS Excel is launched to inspect the results. This is a small example that should run very quickly.

$ontext

 Test of GDX2XLS. Dumps all symbols of
 trnsport.gms to trnsport.xlsx.

$offtext

execute '=gamslib trnsport';
execute '=gams trnsport lo=3 gdx=trnsport';
execute '=gdx2xls trnsport.gdx';
execute '=shellExecute trnsport.xlsx';

Notes: the equal signs in from of the external programs indicate we don't go through a shell (e.g. command.com orcmd.exe). This will improve reliability in case the external program is not found. In such a case a proper error will be triggered. Without the '=' such errors go undetected and the GAMS model will continue.

The command ShellExecute will launch Excel to view the .XLSX file.

Model gdx2xls2: import indus89.gdx

This example will solve the indus89.gms+model from the model library and generate a GDX file containing the complete symbol table. This GDX file is exported to Excel and MS Excel is launched to inspect the results. This is a fairly large GDX file, with many identifiers, resulting in many sheets in the workbook.

$ontext

 Test of GDX2XLS. Dumps all symbols of
 indus89.gms to indus89.xlsx. This takes
 longer as there is a large number of symbols.

$offtext

execute '=gamslib indus89';
execute '=gams indus89 lo=3 gdx=indus89';
execute '=gdx2xls indus89.gdx';
execute '=shellExecute indus89.xlsx';

Model gdx2xls3: a large table

This is an artificial example where we generate a large identifier in GAMS: a parameter with as many elements as the number of rows that Excel can handle.

$ontext

 Test of GDX2XLS. Single symbol with 65536-3=65533 records.
 Maximum rows that XLS can handle is 65536; an XLSX file allows for slightly more than a million rows.

$offtext

set i /i1*i65533/;
parameter p(i);
p(i) = uniform(-100,100);
execute_unload 'test.gdx',p;

execute '=gdx2xls test.gdx';
execute '=ShellExecute test.xlsx';

If you create a spreadsheet with too many rows, the XLSX file writer will return OLE error 800A03EC. When generating an XML file, an error will occur when Excel loads the file.

Model gdx2xls4: special value mapping

To store special values like INF, EPS, NA in a numeric field in the database, GDX2XLS uses a mapping. This mapping can be changed using an INI file.

$ontext

 Test of GDX2XLS.
 Check special value mapping.

$offtext

$onecho > m.ini
[settings]
inf=1.0e100
mininf=-1.0e100
eps=0.0
na=#NA!
undf=#UNDF!
$offecho

parameter p(*) /
   i1 inf
   i2 -inf
   i3 eps
   i4 na
/;

p('i5') = 1/0;
display p;

*
* save parameter p in p.xlsx
* special values are translated to default values:
*
execute_unload "p.gdx",p;
execute '=gdx2xls p.gdx';
execute '=shellExecute p.xlsx';

*
* save parameter p in q.xls using new mapping
*  INF -> 1.0e100  (numeric)
*  -INF -> -1.0e100  (numeric)
*  EPS -> 0.0  (numeric)
*  NA -> #NA! (string)
*  UNDF -> #UNDF! (string)
*
execute_unload "q.gdx",p;
execute '=gdx2xls q.gdx @m.ini';
execute '=shellExecute q.xlsx';

Numeric values are important if you want Excel being able to operate on these numbers.

Model gdx2xls8: custom format

We use a custom value format to color the different values x<0, x=0, x>0 differently. Also align on the decimal point.

$ontext

    GDX2XLS example: use of custom format

$offtext

$onecho > mexls.ini
[settings]
valueformat=[Blue]#.????;[Red]-#.????;[Green]0.????;[Magenta]
$offecho

execute '=gamslib mexls';
execute '=gams mexls lo=3 gdx=mexls';
execute '=gdx2xls mexls.gdx @mexls.ini';
execute '=shellExecute mexls.xlsx';

gdx2xls8x.png
Figure 8. Custom format

Model gdx2xls9: custom format 2

This uses the more useful custom format valueformat=#.???? (see Figure 7).

$ontext

    GDX2XLS example: use of custom format

$offtext

$onecho > align.ini
[settings]
valueformat=#.????
$offecho

execute '=gamslib mexls';
execute '=gams mexls lo=3 gdx=mexls';
execute '=gdx2xls mexls.gdx @align.ini';
execute '=shellExecute align.xlsx';