时间戳

首页 > 解决方案 > CPLEX SheetWrite 错误：此平台不支持工作表数据

excel - CPLEX SheetWrite 错误：此平台不支持工作表数据

问题描述

我有这个 CPLEX 模型，决策变量是区间。我能够从 excel 文件中读取数据，并且将浮点/整数结果写入 excel 没有问题。但是，当我尝试将区间决策变量写入同一个 excel 时，会显示错误消息“此平台不支持工作表数据”。

我喜欢保存的变量看起来像这个 要保存的变量

由于我需要使用结果（使用 vba 代码）制作甘特图，因此我不希望将结果保存为其他格式，我需要保存此表中的所有信息。任何人都可以帮我解决这个问题吗？或者，如果对从结果中制作甘特图的其他方法有任何建议，那也有帮助。谢谢！

标签： excelcplex

解决方案

对于“此平台不支持工作表数据”，您可以在OPL 和 Excel中看到Read without SheetRead（适用于 MacO 和 Linux）

// Read from an Excel spreadsheet without SheetRead
// which means you can use this on non Windows platform

execute
    {

    function read_excel(filename,sheetname,skiprows,nrows,cols,datfilename,resname)
    {
        var quote="\"";
        
        var python=new IloOplOutputFile("c:\\temp\\readexcel.py");
        
        python.writeln("import pandas as pd");
        python.writeln("import xlrd");
        python.writeln("df=pd.read_excel('"+filename+"'"+",sheet_name = '"+sheetname+"'"+
        ",skiprows = "+skiprows+  ",nrows= "+nrows+ ","
        +"header=None,usecols = '"+cols+"')");
        python.writeln("print(df)");
        
        
        
        python.writeln("res = open(",quote,datfilename,quote,",",quote,"w",quote,")");
        python.writeln("res.write(",quote,resname,"=[",quote,")");
        python.writeln("res.write(",quote,"\\","n",quote,")");
        python.writeln("for i, row in enumerate(df.values):");
       
        python.writeln("   res.write(",quote,"[",quote,")");
        
        python.writeln("   for j in row:");
       
        python.writeln("      if (j==j):");
        python.writeln("         res.write(str(j))");
        python.writeln("         res.write(\",\")");
       
        python.writeln("   res.write(\"],\")    ");
        python.writeln("   res.write(",quote,"\\","n",quote,")");
        python.writeln("res.write(\"];\")");
        python.writeln("res.close()");
        python.close();
       
        python.close();
        
        IloOplExec("C:\\Python36\\python.exe c:\\temp\\readexcel.py",true);
        
    }
    read_excel("c:\\\\temp\\\\read2Darray.xls","Sheet1",0,2,"B:D","c:\\\\temp\\\\resexcel","res");
}

对于导出区间，您可以先将区间转换为元组集，然后导出元组集。

例如动物园调度示例：

/*

Model written by Philippe Laborie

https://www.linkedin.com/pulse/zoo-buses-kids-optimization-resource-allocation-philippe-laborie/

*/
using CP;

tuple Bus { string Name; int Seats; int Cost; int Avail; int Outward; int Return; }

{Bus} Buses = {
  < "A40", 40,  500,  480, 30, 25 >,
  < "B40", 40,  500,  480, 30, 25 >,
  < "C40", 40,  500,  480, 30, 25 >,
  < "D40", 40,  500,  480, 30, 25 >,
  < "E40", 40,  500,  510, 30, 25 >,
  < "F30", 30,  400,  480, 25, 20 >,
  < "G30", 30,  400,  480, 25, 20 >,
  < "H30", 30,  400,  480, 25, 20 >,
  < "I30", 30,  400,  480, 25, 20 >,
  < "J30", 30,  400,  510, 25, 20 >
};

int Opening    = 600; // 10:00
int NbKids     = 300;
int NbTeachers = 3;
int NbMaxTrips = 2;

dvar interval outwardTrip[b in Buses][i in 1..NbMaxTrips] optional in b.Avail..Opening size b.Outward;
dvar interval roundTrip  [b in Buses][i in 1..NbMaxTrips] optional size b.Outward+b.Return;

minimize sum(b in Buses, i in 1..NbMaxTrips) (presenceOf(outwardTrip[b][i]) * b.Cost);
subject to {
  sum(b in Buses, i in 1..NbMaxTrips) (presenceOf(outwardTrip[b][i]) * b.Seats) >= NbKids;
  sum(b in Buses, i in 1..NbMaxTrips) pulse(roundTrip[b][i],1) <= NbTeachers; 
  forall(b in Buses) {
    forall(i in 1..NbMaxTrips) {
      presenceOf(outwardTrip[b][i]) == presenceOf(roundTrip[b][i]);startAtStart(outwardTrip[b][i], roundTrip[b][i]);
      if (i>1) {
        presenceOf(roundTrip[b][i]) => presenceOf(roundTrip[b][i-1]);
        endBeforeStart(roundTrip[b][i-1], roundTrip[b][i]);
      }
    }
  }
}

tuple result
{
  string name;
  int seats;
  int s;
  int e;
}

{result} results=
{<b.Name,b.Seats,startOf(outwardTrip[b][i]),endOf(outwardTrip[b][i])> | b in Buses, i in 1..NbMaxTrips : presenceOf(outwardTrip[b][i])==1};
  
execute
{
  writeln(results);
}

推荐阅读