Writing multiple variables to Excel, which share the same set

Problems with modeling
Post Reply
alli
User
User
Posts: 3
Joined: 2 years ago

Writing multiple variables to Excel, which share the same set

Post by alli »

Hi all,

I am trying to write output data from my GAMS model to Excel using GDXXRW.

There are several parameters that I want to write to Excel in a single collated table:
Power_1(t, i)
Power_2(t)
Power_3(t)
Power_4(t)
Power_5(t)
Power_6(t)
Power_7(t)
Power_8(t)

They all share the same set t, which is defined by t1*t24.
Plus Power_1 has the additional set i, which is defined by s1*s2.

The issue is that Power_1(t,i) automatically writes to excel with t1*t24 as the first column, whereas all others automatically write to excel with t1*t24 as the first row. I also am not sure how to merge the set t.
I have been playing with the following code:
execute_unload "results.gdx" Power_1.L, Power_2.L, Power_3.L, Power_4.L, Power_5.L, Power_6.L, Power_7.L,
execute 'gdxxrw.exe results.gdx o=results.xlsx SQ=N var=Power_1.L rng=results!A1'
execute 'gdxxrw.exe results.gdx o=results.xlsx SQ=N var=Power_2.L rng=results!D1'

I would like to create the following table, where t1*t24 is the first column and all variables are
image.png
Any ideas on how to code this?

I would really appreciate your help!
Attachments
image.png
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Writing multiple variables to Excel, which share the same set

Post by bussieck »

Hi, create a 2-dimensional reporting parameter and write this to Excel:

Code: Select all

Parameter rep;
rep(t,i) = Power_1(t, i);
rep(t,'Power2') = Power_2(t);
rep(t,'Power3') = Power_3(t);
...
The first i colums will read s1, s2, ... not Power_1(s1), Power_1(s2), but if you know the cardinality of i you can use gdxxrw's hText option to alter the heading:

Code: Select all

set t /t1*t24/, i /s1*s3/;
Parameter Power_1(t,i), Power_2(t), Power_3(t);
Power_1(t,i) = uniform(0,1);
Power_2(t) = uniform(0,1);
Power_3(t) = uniform(0,1);

Parameter rep;
rep(t,i) = Power_1(t, i);
rep(t,'Power_2') = Power_2(t);
rep(t,'Power_3') = Power_3(t);

execute_unload 'rep.gdx', rep;
execute.checkErrorLevel  'gdxxrw rep.gdx o=rep.xlsx par=rep rng=rep!a1 hText="Power_1(s1),Power_1(s2),Power_1(s3)" rng=rep!B1'
-Michael
alli
User
User
Posts: 3
Joined: 2 years ago

Re: Writing multiple variables to Excel, which share the same set

Post by alli »

Hi Michael,

Thanks for your help.

So sorry but I should have written that Power_1(t), Power_2(t) etc were variables, not parameters.

How does this affect the code?

Cheers,
Alli
User avatar
bussieck
Moderator
Moderator
Posts: 1033
Joined: 7 years ago

Re: Writing multiple variables to Excel, which share the same set

Post by bussieck »

That's trivial:

Code: Select all

Parameter rep;
rep(t,i) = Power_1.l(t, i);
rep(t,'Power_2') = Power_2.l(t);
rep(t,'Power_3') = Power_3.l(t);
-Michael
alli
User
User
Posts: 3
Joined: 2 years ago

Re: Writing multiple variables to Excel, which share the same set

Post by alli »

Cheers Michael!
Post Reply