gdxxls: An Automation Tool for Generating
Formatted Excel Tables from GAMS GDX Data Files

Thomas F. Rutherford

May 2006

* This web page documents a computational tool which has been developed within the project "Indicators and Quantitative Tools for Improving the Impact Assessment Process for Sustainability" (I.Q. Tools), 6th Framework Programme of the European Commission, Contract SSP1-CT-2003-502078, Thematic Priority 8: Policy Oriented Research.

$title	ex1.gms: Elementary Use of GDXXLS from a GAMS Program

set	r	Regions	/
	a	Country A
	b	Country B
	c	Country C /

	g	Goods /
	x	Good x -- labor intensive
	y	Good y -- capital intensive /;

parameter	
	t(r,g)	Trade flows
	z(r,g)	Production levels;

t(r,g) = uniform(0,1);
z(r,g) = uniform(0,1);

*	Save the sets and parameters to be written:

execute_unload "ex1.gdx",t,z,r,g;

*	Write out a command file for GDXXRW.

*	In this example two tables are generated.

*	The table for parameter t is written with set
*	elements as row and column labels.

*	The table for parameter z substitute descriptive
*	text in place of set elements.  We also change the default
*	font from Arial to Garamond:

$onecho >ex1.rsp
item = t

item = z
RowDomain = r
ColDomain = g
DataWidth = 25
xlMarkup = .cells.Font.Name = "Garamond" 
$offecho

*	Generate the worksheet:

execute "gdxxls ex1";

$title	ex2.gms: Advanced Use of GDXXLS from a GAMS Program

*	Define sets for both the column and row domains.

*	Note that we use integers to impose a specific order
*	on rows and columns in the output table.

set flbl(*,*)/
	1.Land		Land
	2.NatlRes	Resources
	3.Capital	Capital
	4.UnskLab	Unskilled
	5.SkLab		Skilled Labor /;

set	rlabels(*,*)	Regions/

	1.header1	Andean Pact
	2.per		Peru
	3.col		Colombia
	4.ven		Venezuela
	5.xap		Rest of Andean Pact
	7.header2	Other South America
	8.bra		Brazil
	9.arg		Argentina
	10.chl		Chile
	11.ury		Uruguay
	13.mex		Mexico
	12.cen		Rest of South America
	15.header3	Rest of world
	16.eur		European Union
	17.usa		United States of America
	18.chn		China
	19.jpn		Japan /;


*	Define a subset of factors for vfmshr.  Note that this
*	omits two columns (land and resources) from the output.

set kl /Capital	Capital
	UnskLab	Unskilled Labor
	SkLab	Skilled Labor /;

*	Define some row labels, including some subheadings and
*	assiging row numbers.  Note that row 17 is omitted and
*	therefore is left blank:

set	irlabels(*,*,*) /

	1.tex.h1	Textiles production
	2.tex.per	Peru
	3.tex.col	Colombia
	4.tex.ven	Venezuela
	5.tex.xap	Rest of Andean Pact
	6.tex.bra	Brazil
	7.tex.arg	Argentina
	8.tex.chl	Chile
	9.tex.ury	Uruguay
	10.tex.cen	Other South-Central America
	11.tex.mex	Mexico
	12.tex.eur	European Union
	13.tex.usa	United States of America
	14.tex.chn	China
	15.tex.jpn	Japan
	16.tex.row	Rest of World

	18.wap.h2	Wearing Apparel
	19.wap.per	Peru
	20.wap.col	Colombia
	21.wap.ven	Venezuela
	22.wap.xap	Rest of Andean Pact
	23.wap.bra	Brazil
	24.wap.arg	Argentina
	25.wap.chl	Chile
	26.wap.ury	Uruguay
	27.wap.cen	Other South-Central America
	28.wap.mex	Mexico
	29.wap.eur	European Union
	30.wap.usa	United States of America
	31.wap.chn	China
	32.wap.jpn	Japan
	33.wap.row	Rest of world/;

*	Read the GTAP data tables:

$include gtapdata

*	Save the data and labels to a GDX file:

execute_unload 'ex2.gdx',flbl,rlabels,evomshr,kl,irlabels,vfmshr;

*	Write the GDXXLS command file:

$onecho >ex2.rsp
item = evomshr
coldomain = flbl
rowdomain = rlabels
LabelWidth = 25
DataWidth = 15
TitleMarkup = .Font.Bold=True: .Font.Size=18
xlmarkup = .Rows(5).Font.Bold=True : .Rows(11).Font.Bold=True : .Rows(19).Font.Bold=True

item = vfmshr
coldomain = kl
rowdomain = irlabels
LabelWidth = 25
DataWidth = 20
TitleMarkup = .Font.Bold=True: .Font.Size=18
xlmarkup = .Rows(5).Font.Bold=True : .Rows(22).Font.Bold=True : .Rows(5).Font.Size=12 : .Rows(22).Font.Size=12
$offecho

execute 'gdxxls ex2';

GDXXLS Command File Options

Distribution Materials

1. gdxxls.wsf Here is a web-viewable version of the VBScript which moves data between GDX and XLS files.
2. gdxxls.zip Here are both the script and Erwin Kalvelagen's COM server object which generates a COM server wrapper around GDXIO.DLL. Unzip these files in your GAMS system directory or somewhere else on the PATH. The automation interface makes it possible to invoke GDXIO.DLL routines from Windows scripting languages.
3. test.zip Here are a couple of illustrative applications.
4. gdxapi.chm Contains documentation for Paul van der Eijk's DLL for reading and writing GDX files.
5. script56.chm Contains documentation for VBScript.
6. script_center.chm Contains more documentation for VBScript.