ILOG CPLEX/OPL动态Excel工作表引用 [英] ILOG CPLEX / OPL dynamic Excel sheet referencing

问题描述

我正试图动态引用.dat中的Excel工作表或表格，以解决CPLEX(OPL)中要解决的车辆路径中的混合整数问题.

设置为:.mod =模型，.dat =数据和MS Excel电子表格

我有一个二维数组，其中包含客户需求数据= Excel范围(为便于编码，我尚未将excel数据格式化为表格)

.mod中的决策变量如下:

dvar布尔值x [vertices] [vertices] [scenarios]

.dat中的

SheetRead的顶点(数据，"Table！vertices")； 和

SheetRead中的场景(数据，尚不知道")；可能不需要

没有方案索引，一切都很好.但是随着这种模型中客户需求的变化，我想通过更改数据库参考将其包括在内.现在我想做的是两件事之一:

可以:在Excel中更改电子表格，以便根据情况在.dat中获得类似的内容:

场景= 1:

SheetRead的顶点(数据，"table-scenario-1！vertices")；

场景= 2:

SheetRead的顶点(数据，"table-scenario-2！vertices")；

因此，请更改电子表格以获取新的基本数据，或者:更改同一电子表格中的范围:

场景= 1:

SheetRead的顶点(数据，"table！vertices-1")；

场景= 2:

SheetRead的顶点(数据，"table！vertices-2")；

这两种方法都可以.

知道如何使用分组了2D表的多个电子表格创建Excel中的3D表时，似乎更自然的方法是，使顶点始终在每个Excel电子表格中引用相同的范围，同时取决于切换电子表格/页面的方案，但我只是不知道该怎么做.

感谢您的建议.

解决方案

不幸的是， SheetConnection 的参数必须是字符串文字或ID(请参见用户手册此处).同样，对于 SheetRead .这意味着，您不能有用于图纸连接的动态源.

正如我们在评论中讨论的那样，一种选择是向所有数据添加一个额外的索引:场景.然后，始终读取所有方案的数据，并在.mod文件中选择要实际使用的内容.

I'm trying to dynamically reference Excel sheets or tables within the .dat for a Mixed Integer Problem in Vehicle Routing that I'm trying to solve in CPLEX (OPL).

The setup is a: .mod = model, .dat = data and a MS Excel spreadsheet

I have a 2 dimensional array with customer demand data = Excel range (for coding convenience I did not format the excel data as a table yet)

The decision variable in .mod looks like this:

dvar boolean x[vertices][vertices][scenarios]

in .dat:

vertices from SheetRead (data, "Table!vertices"); and

scenarios from SheetRead (data, "dont know how to yet"); this might not be needed

without the scenario Index everything is fine. But as the demand for the customers changes in this model I'd like to include this via changing the data base reference. Now what I'd like to do is one of 2 things:

Either: Change the spreadsheet in Excel so that depending on the scenario I get something like that in .dat:

scenario = 1:

vertices from SheetRead (data, "table-scenario-1!vertices");

scenario = 2:

vertices from SheetRead (data, "table-scenario-2!vertices");

so changing the spreadsheet for new base data, or: Change the range within the same spreadsheet:

scenario = 1:

vertices from SheetRead (data, "table!vertices-1");

scenario = 2:

vertices from SheetRead (data, "table!vertices-2");

either way would be fine.

Knowing how 3D Tables in Excel are created using multiple spreadsheets with 2D Tables grouped, the more natural approach seems to be, to have vertices always reference the same range in every Excel spreadsheet while depending on the scenario the spreadsheet/page is switched, but I just don't know how to.

Thanks for the advice.

解决方案

Unfortunately, the arguments to SheetConnection must be a string literal or an Id (see the OPL grammar in the user manual here). And similarly for SheetRead. This means, you cannot have dynamic sources for a sheet connection.

As we discussed in the comments, one option is to add an additional index to all data: the scenario. Then always read the data for all scenarios and in the .mod file select what you want to actually use.

这篇关于ILOG CPLEX/OPL动态Excel工作表引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！