在GAMS中循环以在Excel中生成方案 [英] loop in GAMS for scenario generation in excel

查看:467
本文介绍了在GAMS中循环以在Excel中生成方案的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个优化模型,我尝试为不同的输入文件解决此模型,我确切地称其为在不同情况下解决模型".因此,我需要一个循环以从excel读取每个不同工作表的数据.让我说清楚:例如,在下面的图片中,我们有一个包含4种方案的数据,并且每种方案的工作表名称都在逐个增加

I have an optimization model and I try to solve this model for different input files which I exactly call as "solve the model under different scenarios". For that reason, I need a loop to read the data from excel for each different sheet. Let me make myself clear: For example, in the image below we have a data with 4 scenarios and the sheet names are increasing one by one for each scenario

对于每种情况,此数据都必须作为表格读取,就像在excel文件中一样.

This data has to be read as a table for each scenario, like in the excel file.

我尝试通过循环从不同的Excel工作表中读取数据.我可以在Gams中做到吗?

I try to read the data from different excel sheets with a loop. Can I do that in Gams?

在GAMS中,我可以像下面那样从excel中获取数据,但这仅适用于一种情况.我想制作一个GAMS代码,以便在循环语句

In GAMS I could take datas from excel like in the below but this is just for one scenario. I want to make a GAMS code that read data for all scenarios from excel sheets in a loop statement

Table   n(t,b)
$call =xls2gms r="nonbooked!A2:I9" i="excelveri.xlsx" o="nbooked.inc"
$include nbooked.inc
;

推荐答案

GAMS put_utility(

The GAMS put_utility (https://www.gams.com/latest/docs/UG_Put.html#UG_Put_PutUtil) together with the tool GDXXRW (https://www.gams.com/latest/docs/T_GDXXRW.html) is the key for this problem. Here is a self contained (and hopefully self explanatory) example:

set i      / i1*i3 /
    j      / j1*j4 /
    sheets / Sheet1*Sheet3 /;

parameter data(i,j);
file fx; put fx;

* To make this example self contained, first prepare some data (using same mechanics as the reading)
loop(sheets,
* Create random data
  data(i,j) = uniformInt(0,9);
* Write data to GDX
  execute_unload 'data.gdx', data;
* Write GDX data to excel
  put_utility 'exec' / 'gdxxrw.exe data.gdx par=data rng=' sheets.tl:0 '!a1';
);

* Clear data to start fresh
data(i,j) = 0;

* Load data
loop(sheets,
* Write Excel data to GDX
  put_utility 'exec' / 'gdxxrw.exe data.xlsx par=data rng=' sheets.tl:0 '!a1';
* Load data from GDX
  execute_load 'data.gdx', data;
* Work with the data, just display as an example
  display data;
);

我希望能有所帮助, 鲁兹

I hope that helps, Lutz

这篇关于在GAMS中循环以在Excel中生成方案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆