Table of Contents
A Tool to convert GDX data to MS Excel spreadsheets.
- 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
- Attention
GDX2XLS
is deprecated (see GAMS 42 GDX2XLS release notes). Please use Connect agent ExcelWriter instead.
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 Records Explanatory text 1 a 1 Par 2 capacity of plant i in cases 2 b 1 Par 3 demand at market j in cases 3 c 2 Par 6 transport cost in thousands of dollars per case 4 cost 0 Equ 1 define objective function 5 d 2 Par 6 distance in thousands of miles 6 demand 1 Equ 3 satisfy demand at market j 7 f 0 Par 1 freight in dollars per case per thousand miles 8 i 1 Set 2 canning plants 9 j 1 Set 3 markets 10 supply 1 Equ 2 observe supply limit at plant i 11 x 2 Var 6 shipment quantities in cases 12 z 0 Var 1 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 34.1.0 r644dbd9 Released Jan 29, 2021 WEI x86 64bit/MS Window Output file: C:\tmp\trnsport.xlsx C:\tmp>
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.
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.
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.
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+ . |
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';
executeTool 'win32.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 ‘executeTool 'win32.shellExecute trnsport.xlsx’;` 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';
executeTool 'win32.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';
executeTool 'win32.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';
executeTool 'win32.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';
executeTool 'win32.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';
executeTool 'win32.shellExecute mexls.xlsx';
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';
executeTool 'win32.shellExecute align.xlsx';