Continiously writing to an excel sheet

GAMS
1

Hei community,

I was wondering if anyone had this problem before:
At the end of a loop. I wanna write one variable to an Excel sheet. In the first row of the sheet I defined the names for the set W (machines). The time values change with every loop (sliding). At the end of every loop I wanna add the new values to the end of the table. The problem is that I don’t know how I can specify, that the data should be added to the end of the sheet, but the set names should be considered from the first row. I mean something like this: rng=A97:F577 cdim=A1:F1.
If annyone has an idea, I would appreciate your help alot!

Best regards
Felix



I know it works not like this, but I thought there must be a way, that it works in this way:

Sets
I time / start*(start+480) /
W machines / AA1, AA2, AA3…/;

Variable X(I,W);

$onecho > tasks.txt
var=x rng=Astart:F(start+480) rdim=1 cdim=A1:F1
$offecho
execute_unload ‘values.gdx’;
execute ‘GDXXRW values.gdx O=values.xlsx epsout=0 @tasks.txt’;

–
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.

2

Felix,

Appending to Excel tables is not easily done. It’s much better if you build up a GAMS parameter with all the data you want to export to Excel and then export once. I do not quite understand your loop set up, but in case you use a GAMS “loop” you already have a looping index that you can add to the parameter:

parameter rep(i,w);
loop(i,
…
rep(i,w) = x.l(i,w);
);

If you control your loop with “for” or “while” you can make yourself an iteration set and use that. For that you need a max iteration:

set iter /iter1*iter1000/, actiter(iter) /iter1/;
parameter rep(iter,w);
scalar done /0/;
while(not done,
…
rep(actiter,i) = x.l(w);

  • Advance actiter
    actiter(iter) = actiter(iter-1);
    abort$(card(actiter)=0) ‘need more iterations in set iter’;
    );

In any case at the end you have a two dimensional parameter that you can export in one swoop into Excel:

execute_unload ‘values.gdx’, rep;
execute ‘GDXXRW values.gdx O=values.xlsx epsout=0 par=rep rng=A1 rdim=1 cdim=1’;

Hope this helps,
Michael Bussieck - GAMSWorld Coordinator



On Friday, March 4, 2016 at 3:33:36 AM UTC-5, Felix wrote:

Hei community,

I was wondering if anyone had this problem before:
At the end of a loop. I wanna write one variable to an Excel sheet. In the first row of the sheet I defined the names for the set W (machines). The time values change with every loop (sliding). At the end of every loop I wanna add the new values to the end of the table. The problem is that I don’t know how I can specify, that the data should be added to the end of the sheet, but the set names should be considered from the first row. I mean something like this: rng=A97:F577 cdim=A1:F1.
If annyone has an idea, I would appreciate your help alot!

Best regards
Felix



I know it works not like this, but I thought there must be a way, that it works in this way:

Sets
I time / start*(start+480) /
W machines / AA1, AA2, AA3…/;

Variable X(I,W);

$onecho > tasks.txt
var=x rng=Astart:F(start+480) rdim=1 cdim=A1:F1
$offecho
execute_unload ‘values.gdx’;
execute ‘GDXXRW values.gdx O=values.xlsx epsout=0 @tasks.txt’;

–
To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at https://groups.google.com/group/gamsworld.
For more options, visit https://groups.google.com/d/optout.