Monday, March 12, 2012

Exporting Excel Tables

GDXXRW is a flexible GAMS tool to read and write Excel files. Here is an example output sheet:

Slide1 

A few things I would like to be done better such that the resulting tables look a little bit less basic. GDXXRW has some facilities to deal with formatting (such as Merge), but these really only work well with static tables so that a predefined format can be used. In my case the size of the table is not known in advance. With a JavaScript based script I clean up all the tables (approx. 60 of them), so the results look like:

Slide2 

As these tables will be generated by a Web based front-end, the whole thing should run automatically and without user intervention.

Note: the warning about numbers being formatted as strings is related to set elements (years in this case) that look like numbers. This warning can be disabled with an Excel option.