如何向CPLEX导入Excel数组？解决范围与数组大小不匹配报错

Let's break down exactly what's causing this error and walk through how to fix it. The core issue here is a mismatch between the dimensions of your CPLEX array definition and the Excel range you're attempting to read.

Step 1: Diagnose the Size Mismatch #

Let's map out the dimensions from your code to see where the conflict is:

• In your .mod file, Cexcl is defined as int Cexcl[1..P1, 1..J*3]. Since P1=3 and J=3, this means Cexcl is a 3-row × 9-column array (3 providers × 9 total columns from J*3).
• But in your .dat file, you're reading from 'cost1'!B3:G5:
  • Rows: 3 (rows 3, 4, 5)
  • Columns: 6 (columns B, C, D, E, F, G) → this only provides 6 columns, not the 9 your array expects.

The exact same problem applies to Cpexcl: it's defined as a 3×9 array, but you're pulling a 3×6 range from your Excel sheet. This size mismatch is exactly what triggers the error you're seeing.

Step 2: Resolve the Mismatch #

You have two clear paths to fix this, depending on your actual Excel data structure:

Option 1: Adjust the Excel Range to Match the Array

If your cost1 and cost2 sheets actually contain 9 columns of data for Cexcl and Cpexcl, update your .dat file to read the full range. For example, if your data spans from column B to J (9 total columns), modify the lines to:

SheetConnection sheetInput("Book1.xlsx");
Cexcl from SheetRead (sheetInput,"'cost1'!B3:J5");
Cpexcl from SheetRead (sheetInput,"'cost2'!B3:J5");

Option 2: Adjust the Array Definition to Match the Excel Range

If your Excel data only has 6 columns (and B3:G5 is the correct range), update your .mod file to align the array size. Your current mapping assumes 3 values per j (for h in Period), but if your Excel only has 2 values per j, adjust the code like this:

int M1=3; int M2=3; int P1=3; int P2=3; int T=12; int D=12; int H=1; int J=3; // Update H to 1 to make Period 0..1 (2 values)
range patients_meli=0..(M1+1);//0到4
range patients_Tourist=(M1+2)..(M2+M1+2);
range providers_meli=1..P1;//1到3
range providers_Tourist=(P1+1)..(P2+P1);
range Period=0..H;
int Cexcl[1..P1, 1..J*2]=...; // J*2=6 to match 6 columns in Excel
int C[p in providers_meli][j in 1..J][h in Period]=Cexcl[p,h+2*(J-1)]; // Adjust index calculation to use 2 values per j
int Cpexcl[1..P2, 1..J*2]=...;
int Cp[p in 1..3][j in 1..J][h in Period]=Cpexcl[p,h+2*(J-1)];

Step 3: Verify the Mapping #

After fixing the size mismatch, double-check that your C and Cp index calculations align with how your data is structured in Excel. For example, if you're using h+3*(J-1) to map columns to j and h, ensure each j group in Excel actually has 3 h values.

内容的提问来源于stack exchange，提问作者Kasra Kriss