PYQT5项目实践|实现Python+cplex 求解模型后，写入GUI小程序中，具体实践包括： CPLEX 的求解整数规划模型和求解结果以表的形式输出在GUI界面中_from cplex.exceptions import cplexerror-CSDN博客

本文链接：https://blog.csdn.net/crazy_girl_me/article/details/118490071

1 Cplex求解模型代码

#coding:utf-8
import cplex
from cplex.exceptions import CplexError
from pandas import DataFrame
import pandas as pd
import numpy as np


class ILP:
    def __init__(self, CMI, margins, beds):
        self.cmi = CMI
        self.margins = margins
        self.beds = beds

        df_x_pw = pd.read_excel('x_pw上下界.xlsx')

        drg_list = list(set(df_x_pw['drg_group_code']))
        drg_size = len(drg_list)
        xpw_los = df_x_pw['los_pw'].tolist()
        xp_rw = df_x_pw['rw_pw'].tolist()
        xpw_profit = df_x_pw['profit_pw'].tolist()

        # print(sorted(list(set(df_x_pw['DRG_group_code']) - set(df_results['drg_group_code']))))

        df_yw = pd.read_excel('科室病房数据附件.xlsx')
        df_x_p = pd.read_excel('x_p.xlsx')
        xp_list = df_x_p['x_p'].tolist()
        xp_list = [int(i*1.2) for i in xp_list]

        dept_list = df_yw['出院科室'].tolist()
        dept_size = len(dept_list)
        yw_lb = df_yw['y_lb'].tolist()
        yw_ub = df_yw['y_ub'].tolist()
        my_rws = xp_rw + [0]*dept_size



        x_pw_lb = df_x_pw['x_pw_lb'].tolist()
        x_pw_ub = df_x_pw['x_pw_ub'].tolist()
        decision_lb = x_pw_lb + yw_lb
        decision_ub = x_pw_ub + yw_ub

        # print(x_pw_ub, '\n', x_pw_lb)

        my_cmi = [self.cmi]*(dept_size*drg_size)
        my_types = 'I'*(dept_size*(drg_size+1))

        my_colname = ['x'+str(i) for i in range(dept_size*(1+drg_size))]

        my_rowname = ['r1', 'r2']
        for i in range(3, drg_size+4+dept_size):
            my_rowname.append('r'+str(i))
        my_sense = 'LLL' + 'L'*drg_size + 'L' * dept_size

        # 不等式约束
        my_rhs = [-self.margins, 0]
        my_rhs = my_rhs + xp_list
        unequal_rows = [[my_colname[:dept_size*drg_size], -np.array(xpw_profit)],
                    [my_colname[:dept_size*drg_size], np.array(my_cmi) - np.array(xp_rw)]]
        for i in range(drg_size):

            unequal_rows.append([my_colname[i*dept_size:(i+1)*dept_size], list(np.ones(dept_size))])
        yw = df_yw['核定床位数'].tolist()
        for i in range(dept_size):
            my_rhs.append(0)

        for i in range(dept_size):
            unequal_rows.append([my_colname[i::dept_size], xpw_los[i::dept_size]+[-650]])
        # 添加总床位约束
        unequal_rows.append([my_colname[dept_size*drg_size:], [1]*dept_size])
        my_rhs.append(self.beds)
        self.my_rws = my_rws
        self.decision_lb = decision_lb
        self.decision_ub = decision_ub
        self.my_types = my_types
        self.unequal_rows = unequal_rows
        self.my_sense = my_sense
        self.my_rowname = my_rowname
        self.my_rhs = my_rhs
        self.drg_size = drg_size
        self.dept_size = dept_size
        self.xp_rw = xp_rw
        self.my_colname = my_colname
        self.drg_group_code = df_x_pw['drg_group_code'].tolist()
        self.discharge_dept = df_x_pw['discharge_dept'].tolist()
        self.dept = df_yw["出院科室"].tolist()

# print('unequal_rows:', unequal_rows[827:])
    def populatebyrow(self, prob):
        prob.objective.set_sense(prob.objective.sense.maximize)
        prob.variables.add(obj=self.my_rws, lb=self.decision_lb, ub=self.decision_ub, types=self.my_types,
                           names=self.my_colname)

        rows = self.unequal_rows

        prob.linear_constraints.add(lin_expr=rows, senses=self.my_sense,
                                    rhs=self.my_rhs, names=self.my_rowname)

    def solve_ilp(self):

        try:
            my_prob = cplex.Cplex()
            handle = self.populatebyrow(my_prob)
            my_prob.solve()
        except CplexError as exc:
            print(exc)

        print('Solution status = ', my_prob.solution.get_status(), ":", my_prob.solution.status[my_prob.solution.get_status()])

        Solution_value = my_prob.solution.get_objective_value()

        x = my_prob.solution.get_values()
        drg_res_dict = {'drg_group_code': self.drg_group_code,
                        'discharge_dept': self.discharge_dept,
                        'x_pw': x[:self.drg_size*self.dept_size]
                        }
        dept_res_dict = {
            '出院科室': self.dept,
            'y_w': x[self.drg_size*self.dept_size:]
        }
        drg_res = DataFrame(drg_res_dict)
        dept_res = DataFrame(dept_res_dict)
        # dt3 = dt[(dt.c1 != 12) & (dt.c1 != 56)]
        drg_res = drg_res[drg_res.x_pw != 0]
        drg_res.to_excel('drg_res.xlsx')
        dept_res.to_excel('dept_res.xlsx')



        # print('x:', sum(x[:self.drg_size*self.dept_size]))

        CMI =  sum(np.array(x[:self.drg_size*self.dept_size]) * np.array(self.xp_rw))/sum(x[:self.drg_size*self.dept_size])
        return Solution_value, CMI


import matplotlib.pyplot as plt


beds_lb = 1150
beds_ub = 1200
beds = 1270
cmi = 0.94
margins = -12131096.93
def beds_solve(beds_lb, beds_ub, step=5):
    res_list = []
    for beds in range(beds_lb, beds_ub, step):
        ilp = ILP(cmi, margins, beds)
        x, rw_value = ilp.solve_ilp()
        res_list.append(rw_value)
    plt.plot(list(range(beds_lb, beds_ub, step)), res_list)
    plt.xlabel('beds')
    plt.ylabel('RW_value')
    plt.title('beds-RW')
    plt.show()
    print('beds:', sum(x[825*33:]))


def margins_solve():
    res_list = []
    for margin in range(11131096, 15131096, 500000):
        ilp = ILP(cmi, -margin, beds)
        x, rw_value = ilp.solve_ilp()
        res_list.append(rw_value)
    plt.plot(list(range(11131096, 15131096, 500000)), res_list)
    plt.xlabel('margins')
    plt.ylabel('RW_value')
    plt.title('margins-RW')
    plt.show()



def cmi_solve(cmi_lb):
    cmi_List = []
    for i in range(10):
        cmi_List.append(cmi_lb)
        cmi_lb += 0.01
    res_list = []
    for cmi in cmi_List:
        ilp = ILP(cmi, margins, beds)
        x, rw_value = ilp.solve_ilp()
        res_list.append(rw_value)
    plt.plot(cmi_List, res_list)
    plt.xlabel('cmi')
    plt.ylabel('RW_value')
    plt.title('cmi-RW')
    plt.show()


def cross_solve(beds, cmi, margins):
    ilp = ILP(cmi, margins, beds)
    rw_value, CMI = ilp.solve_ilp()
    return rw_value

2 建立GUI界面

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'Total_work.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.

import xlrd
from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(1081, 793)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.tabWidget = QtWidgets.QTabWidget(self.centralwidget)
        self.tabWidget.setGeometry(QtCore.QRect(0, 10, 1021, 721))
        font = QtGui.QFont()
        font.setFamily("HGF8_CNKI")
        font.setPointSize(16)
        self.tabWidget.setFont(font)
        self.tabWidget.setObjectName("tabWidget")
        self.tab_4 = QtWidgets.QWidget()
        self.tab_4.setObjectName("tab_4")

        self.pushButton = QtWidgets.QPushButton(self.tab_4)
        self.pushButton.setGeometry(QtCore.QRect(890, 60, 111, 41))
        self.pushButton.setObjectName("pushButton")

        self.lineEdit = QtWidgets.QLineEdit(self.tab_4)
        self.lineEdit.setGeometry(QtCore.QRect(30, 60, 831, 41))
        self.lineEdit.setObjectName("lineEdit")
        self.tableWidget = QtWidgets.QTableWidget(self.tab_4)
        self.tableWidget.setGeometry(QtCore.QRect(30, 150, 971, 511))
        self.tableWidget.setObjectName("tableWidget")
        self.tabWidget.addTab(self.tab_4, "")
        self.tab_5 = QtWidgets.QWidget()
        self.tab_5.setObjectName("tab_5")
        self.graphicsView = QtWidgets.QGraphicsView(self.tab_5)
        self.graphicsView.setGeometry(QtCore.QRect(40, 100, 951, 481))
        self.graphicsView.setObjectName("graphicsView")
        self.splitter_4 = QtWidgets.QSplitter(self.tab_5)
        self.splitter_4.setGeometry(QtCore.QRect(40, 600, 489, 43))
        self.splitter_4.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_4.setObjectName("splitter_4")
        self.label_3 = QtWidgets.QLabel(self.splitter_4)
        self.label_3.setObjectName("label_3")
        self.lineEdit_4 = QtWidgets.QLineEdit(self.splitter_4)
        self.lineEdit_4.setObjectName("lineEdit_4")
        self.splitter = QtWidgets.QSplitter(self.tab_5)
        self.splitter.setGeometry(QtCore.QRect(40, 30, 941, 46))
        self.splitter.setOrientation(QtCore.Qt.Horizontal)
        self.splitter.setObjectName("splitter")
        self.label = QtWidgets.QLabel(self.splitter)
        self.label.setObjectName("label")
        self.lineEdit_2 = QtWidgets.QLineEdit(self.splitter)
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.label_2 = QtWidgets.QLabel(self.splitter)
        self.label_2.setObjectName("label_2")
        self.lineEdit_3 = QtWidgets.QLineEdit(self.splitter)
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.pushButton_3 = QtWidgets.QPushButton(self.splitter)
        self.pushButton_3.setObjectName("pushButton_3")
        self.tabWidget.addTab(self.tab_5, "")
        self.tab = QtWidgets.QWidget()
        self.tab.setObjectName("tab")
        self.graphicsView_2 = QtWidgets.QGraphicsView(self.tab)
        self.graphicsView_2.setGeometry(QtCore.QRect(30, 100, 971, 491))
        self.graphicsView_2.setObjectName("graphicsView_2")
        self.splitter_3 = QtWidgets.QSplitter(self.tab)
        self.splitter_3.setGeometry(QtCore.QRect(40, 610, 489, 43))
        self.splitter_3.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_3.setObjectName("splitter_3")
        self.label_6 = QtWidgets.QLabel(self.splitter_3)
        self.label_6.setObjectName("label_6")
        self.lineEdit_7 = QtWidgets.QLineEdit(self.splitter_3)
        self.lineEdit_7.setObjectName("lineEdit_7")
        self.pushButton_4 = QtWidgets.QPushButton(self.tab)
        self.pushButton_4.setGeometry(QtCore.QRect(850, 40, 93, 46))
        self.pushButton_4.setObjectName("pushButton_4")
        self.label_4 = QtWidgets.QLabel(self.tab)
        self.label_4.setGeometry(QtCore.QRect(30, 40, 126, 37))
        self.label_4.setObjectName("label_4")
        self.lineEdit_5 = QtWidgets.QLineEdit(self.tab)
        self.lineEdit_5.setGeometry(QtCore.QRect(140, 40, 272, 46))
        self.lineEdit_5.setObjectName("lineEdit_5")
        self.label_5 = QtWidgets.QLabel(self.tab)
        self.label_5.setGeometry(QtCore.QRect(460, 40, 126, 37))
        self.label_5.setObjectName("label_5")
        self.lineEdit_6 = QtWidgets.QLineEdit(self.tab)
        self.lineEdit_6.setGeometry(QtCore.QRect(572, 40, 272, 46))
        self.lineEdit_6.setObjectName("lineEdit_6")
        self.tabWidget.addTab(self.tab, "")
        self.tab_3 = QtWidgets.QWidget()
        self.tab_3.setObjectName("tab_3")
        self.graphicsView_3 = QtWidgets.QGraphicsView(self.tab_3)
        self.graphicsView_3.setGeometry(QtCore.QRect(20, 90, 971, 501))
        self.graphicsView_3.setObjectName("graphicsView_3")
        self.splitter_10 = QtWidgets.QSplitter(self.tab_3)
        self.splitter_10.setGeometry(QtCore.QRect(20, 620, 489, 43))
        self.splitter_10.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_10.setObjectName("splitter_10")
        self.label_8 = QtWidgets.QLabel(self.splitter_10)
        self.label_8.setObjectName("label_8")
        self.lineEdit_9 = QtWidgets.QLineEdit(self.splitter_10)
        self.lineEdit_9.setObjectName("lineEdit_9")
        self.splitter_5 = QtWidgets.QSplitter(self.tab_3)
        self.splitter_5.setGeometry(QtCore.QRect(20, 30, 967, 46))
        self.splitter_5.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_5.setObjectName("splitter_5")
        self.label_7 = QtWidgets.QLabel(self.splitter_5)
        self.label_7.setObjectName("label_7")
        self.lineEdit_8 = QtWidgets.QLineEdit(self.splitter_5)
        self.lineEdit_8.setObjectName("lineEdit_8")
        self.label_17 = QtWidgets.QLabel(self.splitter_5)
        self.label_17.setObjectName("label_17")
        self.lineEdit_19 = QtWidgets.QLineEdit(self.splitter_5)
        self.lineEdit_19.setObjectName("lineEdit_19")
        self.pushButton_2 = QtWidgets.QPushButton(self.splitter_5)
        self.pushButton_2.setObjectName("pushButton_2")
        self.tabWidget.addTab(self.tab_3, "")
        self.tab_2 = QtWidgets.QWidget()
        self.tab_2.setObjectName("tab_2")
        self.tableWidget_1 = QtWidgets.QTableWidget(self.tab_2)
        self.tableWidget_1.setGeometry(QtCore.QRect(50, 80, 941, 531))
        self.tableWidget_1.setObjectName("tableWidget_1")
        self.splitter_6 = QtWidgets.QSplitter(self.tab_2)
        self.splitter_6.setGeometry(QtCore.QRect(50, 20, 941, 46))
        self.splitter_6.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_6.setObjectName("splitter_6")
        self.label_18 = QtWidgets.QLabel(self.splitter_6)
        self.label_18.setObjectName("label_18")

        self.lineEdit_20 = QtWidgets.QLineEdit(self.splitter_6)
        self.lineEdit_20.setObjectName("lineEdit_20",)
        self.lineEdit_20.setText("1270")
        self.label_19 = QtWidgets.QLabel(self.splitter_6)
        self.label_19.setObjectName("label_19")
        self.lineEdit_21 = QtWidgets.QLineEdit(self.splitter_6)
        self.lineEdit_21.setObjectName("lineEdit_21")
        self.lineEdit_21.setText("0.94")
        self.label_20 = QtWidgets.QLabel(self.splitter_6)
        self.label_20.setObjectName("label_20")
        self.lineEdit_22 = QtWidgets.QLineEdit(self.splitter_6)
        self.lineEdit_22.setObjectName("lineEdit_22")
        self.lineEdit_22.setText("-12131096.93")


        self.pushButton_5 = QtWidgets.QPushButton(self.splitter_6)
        self.pushButton_5.setObjectName("pushButton_5")

        self.splitter_2 = QtWidgets.QSplitter(self.tab_2)
        self.splitter_2.setGeometry(QtCore.QRect(54, 620, 569, 46))
        self.splitter_2.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_2.setObjectName("splitter_2")

        self.label_21 = QtWidgets.QLabel(self.splitter_2)
        self.label_21.setObjectName("label_21")
        self.lineEdit_10 = QtWidgets.QLineEdit(self.splitter_2)
        self.lineEdit_10.setObjectName("lineEdit_10")
        self.tabWidget.addTab(self.tab_2, "")

        self.tab_6 = QtWidgets.QWidget()
        self.tab_6.setObjectName("tab_6")
        self.toolBox = QtWidgets.QToolBox(self.tab_6)
        self.toolBox.setGeometry(QtCore.QRect(20, 20, 1041, 651))
        self.toolBox.setObjectName("toolBox")
        self.page = QtWidgets.QWidget()
        self.page.setGeometry(QtCore.QRect(0, 0, 100, 30))
        self.page.setObjectName("page")
        self.textBrowser = QtWidgets.QTextBrowser(self.page)
        self.textBrowser.setGeometry(QtCore.QRect(0, 0, 1041, 341))
        self.textBrowser.setObjectName("textBrowser")
        self.toolBox.addItem(self.page, "")
        self.page_3 = QtWidgets.QWidget()
        self.page_3.setGeometry(QtCore.QRect(0, 0, 100, 30))
        self.page_3.setObjectName("page_3")
        self.textBrowser_2 = QtWidgets.QTextBrowser(self.page_3)
        self.textBrowser_2.setGeometry(QtCore.QRect(0, 0, 1041, 341))
        self.textBrowser_2.setObjectName("textBrowser_2")
        self.toolBox.addItem(self.page_3, "")
        self.page_2 = QtWidgets.QWidget()
        self.page_2.setGeometry(QtCore.QRect(0, 0, 100, 30))
        self.page_2.setObjectName("page_2")
        self.textBrowser_3 = QtWidgets.QTextBrowser(self.page_2)
        self.textBrowser_3.setGeometry(QtCore.QRect(0, 0, 1041, 331))
        self.textBrowser_3.setObjectName("textBrowser_3")
        self.toolBox.addItem(self.page_2, "")
        self.page_4 = QtWidgets.QWidget()
        self.page_4.setGeometry(QtCore.QRect(0, 0, 100, 30))
        self.page_4.setObjectName("page_4")
        self.textBrowser_4 = QtWidgets.QTextBrowser(self.page_4)
        self.textBrowser_4.setGeometry(QtCore.QRect(0, 0, 1041, 331))
        self.textBrowser_4.setObjectName("textBrowser_4")
        self.toolBox.addItem(self.page_4, "")
        self.page_5 = QtWidgets.QWidget()
        self.page_5.setGeometry(QtCore.QRect(0, 0, 100, 30))
        self.page_5.setObjectName("page_5")
        self.textBrowser_5 = QtWidgets.QTextBrowser(self.page_5)
        self.textBrowser_5.setGeometry(QtCore.QRect(0, 0, 1041, 331))
        self.textBrowser_5.setObjectName("textBrowser_5")
        self.toolBox.addItem(self.page_5, "")
        self.page_6 = QtWidgets.QWidget()
        self.page_6.setGeometry(QtCore.QRect(0, 0, 1041, 441))
        self.page_6.setObjectName("page_6")
        self.textBrowser_6 = QtWidgets.QTextBrowser(self.page_6)
        self.textBrowser_6.setGeometry(QtCore.QRect(0, 0, 1041, 311))
        self.textBrowser_6.setObjectName("textBrowser_6")
        self.toolBox.addItem(self.page_6, "")
        self.tabWidget.addTab(self.tab_6, "")
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 1081, 22))
        self.menubar.setObjectName("menubar")
        self.menu = QtWidgets.QMenu(self.menubar)
        self.menu.setObjectName("menu")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.menubar.addAction(self.menu.menuAction())

        self.retranslateUi(MainWindow)
        self.tabWidget.setCurrentIndex(4)
        self.toolBox.setCurrentIndex(5)
        self.lineEdit_5.inputRejected.connect(self.pushButton_4.showMenu)
        self.lineEdit_6.inputRejected.connect(self.pushButton_4.showMenu)
        self.pushButton_5.clicked.connect(self.case_mix)
        self.pushButton_4.clicked.connect(self.lineEdit_7.undo)
        self.pushButton.clicked.connect(self.openfile)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.pushButton.setText(_translate("MainWindow", "浏览..."))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_4), _translate("MainWindow", "数据导入"))
        self.label_3.setText(_translate("MainWindow", "Best Beds Number:"))
        self.label.setText(_translate("MainWindow", "床位上限:"))
        self.label_2.setText(_translate("MainWindow", "床位下限:"))
        self.pushButton_3.setText(_translate("MainWindow", "RUN"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_5), _translate("MainWindow", "BEDS"))
        self.label_6.setText(_translate("MainWindow", "Best Beds Number:"))
        self.pushButton_4.setText(_translate("MainWindow", "RUN"))
        self.label_4.setText(_translate("MainWindow", "CMI上限:"))
        self.label_5.setText(_translate("MainWindow", "CMI下限:"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab), _translate("MainWindow", "CMI"))
        self.label_8.setText(_translate("MainWindow", "Best Beds Number:"))
        self.label_7.setText(_translate("MainWindow", "Marginal上限:"))
        self.label_17.setText(_translate("MainWindow", "Marginal下限:"))
        self.pushButton_2.setText(_translate("MainWindow", "RUN"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_3), _translate("MainWindow", "Marginal"))
        self.label_18.setText(_translate("MainWindow", "BEDS Num:"))
        self.label_19.setText(_translate("MainWindow", "CMI:"))
        self.label_20.setText(_translate("MainWindow", "Marginal:"))
        self.pushButton_5.setText(_translate("MainWindow", "RUN"))

        self.label_21.setText(_translate("MainWindow", "RW_VALUE:"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_2), _translate("MainWindow", "Bed allocation"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page), _translate("MainWindow", "Page 1 数据导入说明"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page_3), _translate("MainWindow", "Page 2 BEDS 模块说明"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page_2), _translate("MainWindow", "Page 3 CMI 模块说明"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page_4), _translate("MainWindow", "Page 4 Marginal 模块说明"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page_5), _translate("MainWindow", "Page 5 Bed allocation 模块说明"))
        self.toolBox.setItemText(self.toolBox.indexOf(self.page_6), _translate("MainWindow", "Page 6 其他"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.tab_6), _translate("MainWindow", "Helps"))
        self.menu.setTitle(_translate("MainWindow", "主界面"))

    def case_mix(self):
        self.lineEdit_10.undo()
        K = []
        from Text import cross_solve
        beds1 = float(self.lineEdit_20.text())
        cmi1 = float(self.lineEdit_21.text())
        margins1 = float(self.lineEdit_22.text())
        rw_value = cross_solve(beds1, cmi1, margins1)
        self.lineEdit_10.insert(str(round(rw_value,2)))

        wb = xlrd.open_workbook('dept_res.xlsx')
        sheet1 = wb.sheet_by_index(0)
        Row = list(sheet1.row_values(0))
        r_num = sheet1.nrows
        c_num = sheet1.ncols
        self.tableWidget_1.setColumnCount(r_num)
        self.tableWidget_1.setRowCount(c_num)  # 8行4列
        cols = sheet1.row_values(0)  # 获取第三列内容 品名
        # 获取整行和整列的值（数组）
        for i in range(1, len(cols)):
            rowslist = sheet1.col_values(i)  # 获取excel每列内容
            for j in range(len(rowslist)):
                # # 在tablewidget中添加行
                # row = self.tableWidget.rowCount()
                # self.tableWidget.insertRow(row)
                # # 把数据写入tablewidget中
                newItem = QtWidgets.QTableWidgetItem(str(rowslist[j]))
                self.tableWidget_1.setItem(i, j, newItem)
        self.tableWidget_1.show()

    def openfile(self):
        self.lineEdit.undo()
        openfile_name =QtWidgets.QFileDialog.getOpenFileName()[0]
        self.lineEdit.insert(str(openfile_name))

        wb = xlrd.open_workbook(str(openfile_name))
        sheet1=wb.sheet_by_index(0)
        Row=list(sheet1.row_values(0))
        r_num=sheet1.nrows
        c_num=sheet1.ncols
        self.tableWidget.setColumnCount(  r_num)
        self.tableWidget.setRowCount( c_num)  # 8行4列
        cols = sheet1.row_values(0)  # 获取第三列内容 品名
        # 获取整行和整列的值（数组）
        for i in range(0, len(cols)):
            rowslist = sheet1.col_values(i)  # 获取excel每列内容
            for j in range(len(rowslist)):
                # # 在tablewidget中添加行
                # row = self.tableWidget.rowCount()
                # self.tableWidget.insertRow(row)
                # # 把数据写入tablewidget中
                newItem = QtWidgets.QTableWidgetItem(str(rowslist[j]))
                self.tableWidget.setItem(i , j , newItem)
        self.tableWidget.show()


import sys
from PyQt5.QtWidgets import QApplication, QMainWindow

if __name__ == '__main__':
    app = QApplication(sys.argv)
    MainWindow = QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())