CPLEX转Python实现求助：参数格式处理与模型完善

一、数据导入代码修复 #

原Python代码存在语法错误、集合范围不匹配、多维参数初始化逻辑错误等问题，修复后的代码如下：

import pandas as pd
from docplex.mp.model import Model

# 读取Excel数据
df = pd.read_excel(r'D:\final\DATA.xlsx', sheet_name='Set1', header=None)

# 定义集合（对应CPLEX中的1-based索引范围）
numI = 4
numP = 3
I = range(1, numI+1)  # 1,2,3,4
P = range(1, numP+1)  # 1,2,3
B = 90000000
scale = 1000

# 修复v[I][P]的读取与格式转换
v_data = df.iloc[1:5, 2:5].values.tolist()  # 4行3列，匹配I和P的规模
v = {(i, p): v_data[i_idx][p_idx] for i_idx, i in enumerate(I) for p_idx, p in enumerate(P)}

# 修复f[I][P]的读取
f_data = df.iloc[6:10, 2:5].values.tolist()
f = {(i, p): f_data[i_idx][p_idx] for i_idx, i in enumerate(I) for p_idx, p in enumerate(P)}

# 读取u1/u2/u3[I][I]（4x4矩阵）
u1_data = df.iloc[12:16, 2:6].values.tolist()
u1 = {(i, j): u1_data[i_idx][j_idx] for i_idx, i in enumerate(I) for j_idx, j in enumerate(I)}

u2_data = df.iloc[12:16, 9:13].values.tolist()
u2 = {(i, j): u2_data[i_idx][j_idx] for i_idx, i in enumerate(I) for j_idx, j in enumerate(I)}

u3_data = df.iloc[12:16, 15:19].values.tolist()
u3 = {(i, j): u3_data[i_idx][j_idx] for i_idx, i in enumerate(I) for j_idx, j in enumerate(I)}

# 构建三维参数u[I][I][P]
u = {}
for i in I:
    for j in I:
        for p in P:
            if p == 1:
                u[(i, j, p)] = u1[(i, j)]
            elif p == 2:
                u[(i, j, p)] = u2[(i, j)]
            else:
                u[(i, j, p)] = u3[(i, j)]

# 修复C[I][P]的读取
C_data = df.iloc[20:24, 2:5].values.tolist()
C = {(i, p): C_data[i_idx][p_idx] for i_idx, i in enumerate(I) for p_idx, p in enumerate(P)}

# 修复MOQ[I][P]的读取
MOQ_data = df.iloc[26:30, 2:5].values.tolist()
MOQ = {(i, p): MOQ_data[i_idx][p_idx] for i_idx, i in enumerate(I) for p_idx, p in enumerate(P)}

# 修复D[P]的读取（对应CPLEX中的D[1][P]）
D_list = df.iloc[41, 1:4].tolist()
D = {p: D_list[p_idx] for p_idx, p in enumerate(P)}

# 修复strat[I][P]的读取
strat_data = df.iloc[32:36, 2:5].values.tolist()
strat = {(i, p): strat_data[i_idx][p_idx] for i_idx, i in enumerate(I) for p_idx, p in enumerate(P)}

# 修复Nmax和Nstr[P]的读取
Nmax_list = df.iloc[37, 1:4].tolist()
Nmax = {p: Nmax_list[p_idx] for p_idx, p in enumerate(P)}

Nstr_list = df.iloc[37, 5:8].tolist()
Nstr = {p: Nstr_list[p_idx] for p_idx, p in enumerate(P)}

二、完整建模与求解 #

使用docplex实现原CPLEX模型的全部逻辑，包括字典序优化：

# 创建模型实例
model = Model("multi_objective_supply_model")

# 定义决策变量
y = model.binary_var_dict([(i,p) for i in I for p in P], name="y")
scalex = model.integer_var_dict([(i,p) for i in I for p in P], lb=0, name="scalex")

# 定义x表达式：x[i][p] = scalex[i][p]/scale
x = {(i,p): scalex[(i,p)] / scale for i,p in y}

# 定义目标函数Z和K
Z = model.sum(D[p] * v[(i,p)] * x[(i,p)] + f[(i,p)] * y[(i,p)] for i in I for p in P)
K = model.sum(u[(i,j,p)] * x[(i,p)] * x[(j,p)] for i in I for j in I for p in P)

# 设置静态字典序优化：先最小化Z，再最小化K
model.add_static_lexicographic_goal([Z, K], directions=["min", "min"])

# 添加约束条件
# 针对每个品类p的约束
for p in P:
    # 品类p的总采购比例为1
    model.add_constraint(model.sum(x[(i,p)] for i in I) == 1, f"total_ratio_eq1_{p}")
    # 品类p的预算约束
    model.add_constraint(model.sum(D[p] * v[(i,p)] * x[(i,p)] + f[(i,p)] * y[(i,p)] for i in I) <= B, f"budget_limit_{p}")
    # 供应商数量上下限约束
    model.add_constraint(model.sum(y[(i,p)] for i in I) >= 2, f"min_suppliers_{p}")
    model.add_constraint(model.sum(y[(i,p)] for i in I) <= Nmax[p], f"max_suppliers_{p}")
    # 战略供应商数量约束
    model.add_constraint(model.sum(y[(i,p)] * strat[(i,p)] for i in I) >= Nstr[p], f"strategic_supplier_min_{p}")

# 针对每个供应商i和品类p的约束
for i in I:
    for p in P:
        # 采购比例不超过供应商产能上限
        model.add_constraint(x[(i,p)] <= C[(i,p)] * y[(i,p)], f"capacity_limit_{i}_{p}")
        # 满足最小起订量要求
        model.add_constraint(MOQ[(i,p)] * y[(i,p)] <= x[(i,p)], f"moq_requirement_{i}_{p}")
        # 采购比例范围约束
        model.add_constraint(x[(i,p)] <= 1, f"x_upper_bound_{i}_{p}")
        model.add_constraint(x[(i,p)] >= 0, f"x_lower_bound_{i}_{p}")

# 求解模型
solution = model.solve()

if solution:
    print("求解成功！")
    print(f"目标函数Z最优值：{solution.get_objective_value(Z):.2f}")
    print(f"目标函数K最优值：{solution.get_objective_value(K):.2f}")
    # 输出变量结果
    print("\ny变量（是否选择供应商i供应品类p）：")
    for (i,p) in y:
        print(f"y[{i}][{p}] = {int(solution.get_value(y[(i,p)]))}")
    print("\nx变量（供应商i供应品类p的比例）：")
    for (i,p) in x:
        print(f"x[{i}][{p}] = {solution.get_value(x[(i,p)]):.4f}")
else:
    print("模型无解，请检查约束或数据！")

关键说明 #

• 多维参数采用**字典（dict）**存储，键为元组(i,p)或(i,j,p)，完美对应CPLEX的多维索引逻辑，避免0/1-based索引混淆。
• 原CPLEX中的staticLex字典序优化通过add_static_lexicographic_goal实现，明确两个目标的优先级和优化方向。
• Excel切片范围需与实际数据位置完全匹配：若Excel中参数的行/列位置有变动，需对应修改iloc的起始、结束索引。

内容的提问来源于stack exchange，提问作者Trí Vũ