GAMS: writing spreadsheets

When doing real applications the demands on writing reports are sometime very high. Users have special requirements how the reports look like, and they often allow very little deviation from what they want. For this reason I often use GDXXRW with the CLEAR/MERGE option to update a table with solution data. This option will allow us to maintain a carefully designed layout.

The following was a little bit of a puzzle. How to update the spreadsheet below while skipping column E (units). The data is 4 dimensional: Commodity, Variable, Region, Year.

With GDXXRW we cannot use MERGE as that will keep old values in the spreadsheet. The option CLEAR is also not usable as it will wipe out column E. Eventually I found the following to work:

• Create an empty 5 dimensional parameter (rdim=4, cdim=1), so that we can CLEAR the body of the table
• Then use the original 4 dimensional parameter (rdim=3, cdim=1) with a MERGE option to fill the table body.

parameter reportx(*,*,*,*,*,t) 'dummy to be able to clear but keep column with units';
* index 1: commodity or '-'
* index 2: variable name
* index 3: region or '_'
* index 4: scenario
* index 5: unit or ''
* index 6: year

reportx('','','','','',t) = 0;

execute_unload "report.gdx",report,reportx;

execute '=gdxxrw i=report.gdx o=report2.xlsx skipempty=10 par=reportx rng=A4 rdim=5 cdim=1 clear par=report rng=A4 rdim=4 cdim=1 merge trace=2';

When we use the CLEAR/MERGE option, all first rdim columns have to be unique. This is somewhat unfortunate. I want a header now and then, as in:

but I can only use “Production Detail” only once. (I have had similar issues before with things like a “Subtotal”). This case often works as expected. Not always, as can be seen here:

C:\projects\china\dec24\output\chn10>gdxxrw i=report.gdx o=report2.xlsx skipempty=10 par=reportx rng=A4 rdim=5 cdim=1 clear par=report rng=A4 rdim=4 cdim=1 merge trace=2 GDXXRW           BETA  1May10 23.4.0 WIN 17193.17196 VS8 x86/MS Windows Excel version 12.0 Input file : C:\projects\china\dec24\output\chn10\report.gdx Output file: C:\projects\china\dec24\output\chn10\report2.xlsx Type Symbol       Dim     Sheet                Data          RowHeader     ColHeader Par  reportx        6     Sheet1               F5:XFD1000000 A5:E1000000   F4:XFD4 Reading range A5:E1000000 **** Duplicate Row/Column label(s) for symbol reportx:    Duplicate Row: A1062: (Yield, , , , ) Reading range F4:XFD4 Par  report         5     Sheet1               E5:XFD1000000 A5:D1000000   E4:XFD4 Reading range A5:D1000000    Duplicate Row: A1062: (Yield, , , ) Reading range E4:XFD4 Reading range E5:Y3767 Total time = 8065 Ms

May be it depends on whether some cells are empty or contain empty strings (the difference is not really visible in a spreadsheet).

However the same scheme for real rows would also be useful. E.g. in some cases I want data to be repeated, such as “YEAR”: in each row with such a key. just repeat the content.

Here I used the trick to introduce y2,y3,y5,… to make sure multiple year rows are being written. It would be better if GDXXRW would allow me to have multiple rows with row header “year”.

Update:

The “duplicate” errors are really warnings. The reason why some duplicate headers (“Production Detail”) don’t generate a message and others (“Yield”) do, depends on whether these descriptions are also used as UELS (set elements) in the GDX file.