Python处理XLS文件，GUI界面处理

先说下需求：
将A.xlsx文件里的内容，按照B.txt的内容,分割成不同的文件

test.xlsx文件分配一个内容；
test1.xlsx文件分配两个内容；
test2.xlsx文件分配三个内容；

接下来上代码，因为这里用gui形式，所以用到了PyQt5。
贴上全部代码：

import sys
import os
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QFileDialog
import pandas as pd
import configparser

class GiftPackage(object):

    splitFile = ''  # 打开的文件
    folderName = ''  # 保存的文件夹
    customFile = []  # 自定义分配文件
    isCfg = 1  # 1为基于文件-数量分配；2为基于配置分配
    baseOn = 0  # 0为基于文件数量平均分配；1为基于礼包码数量平均分配
    number = 0

    def setupUi(self, mainWindow):
        mainWindow.setObjectName("mainWindow")
        mainWindow.resize(800, 592)
        self.centralwidget = QtWidgets.QWidget(mainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setGeometry(QtCore.QRect(10, 10, 81, 23))
        self.pushButton.setObjectName("pushButton")
        self.pushButton.clicked.connect(self.openFile)

        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setGeometry(QtCore.QRect(100, 10, 691, 23))
        self.label.setObjectName("label")

        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setGeometry(QtCore.QRect(10, 50, 83, 23))
        self.pushButton_2.setObjectName("pushButton_2")
        self.pushButton_2.clicked.connect(self.openFolder)

        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setGeometry(QtCore.QRect(98, 50, 691, 23))
        self.label_2.setObjectName("label_2")

        self.tableWidget = QtWidgets.QTableWidget(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(0, 200, 801, 311))
        self.tableWidget.setColumnCount(2)
        self.tableWidget.setObjectName("tableWidget")
        self.tableWidget.setRowCount(0)

        item = QtWidgets.QTableWidgetItem()
        font = QtGui.QFont()
        font.setKerning(True)
        item.setFont(font)
        self.tableWidget.setHorizontalHeaderItem(0, item)
        item = QtWidgets.QTableWidgetItem()

        self.tableWidget.setHorizontalHeaderItem(1, item)
        self.tableWidget.horizontalHeader().setCascadingSectionResizes(False)
        self.tableWidget.horizontalHeader().setDefaultSectionSize(300)
        self.tableWidget.horizontalHeader().setSectionResizeMode(QtWidgets.QHeaderView.Stretch)

        self.pushButton_3 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_3.setGeometry(QtCore.QRect(10, 530, 88, 23))
        self.pushButton_3.setObjectName("pushButton_3")
        self.pushButton_3.clicked.connect(self.start)

        self.progressBar = QtWidgets.QProgressBar(self.centralwidget)
        self.progressBar.setGeometry(QtCore.QRect(120, 530, 671, 23))
        self.progressBar.setProperty("value", 0)
        self.progressBar.setMinimum(0)
        self.progressBar.setMaximum(100)
        self.progressBar.setObjectName("progressBar")

        self.splitter_2 = QtWidgets.QSplitter(self.centralwidget)
        self.splitter_2.setGeometry(QtCore.QRect(10, 90, 249, 19))
        self.splitter_2.setOrientation(QtCore.Qt.Horizontal)
        self.splitter_2.setObjectName("splitter_2")

        # self.radioButton = QtWidgets.QRadioButton(self.splitter_2)
        self.radioButton = QtWidgets.QRadioButton(self.centralwidget)
        self.radioButton.setGeometry(QtCore.QRect(11, 90, 185, 19))
        self.radioButton.setObjectName("radioButton")

        # self.radioButton_2 = QtWidgets.QRadioButton(self.splitter_2)
        self.radioButton_2 = QtWidgets.QRadioButton(self.centralwidget)
        self.radioButton_2.setGeometry(QtCore.QRect(190, 90, 102, 19))
        self.radioButton_2.setObjectName("radioButton_2")

        self.lineEdit = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit.setEnabled(True)
        self.lineEdit.setGeometry(QtCore.QRect(180, 120, 157, 21))
        self.lineEdit.setObjectName("lineEdit")

        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setEnabled(True)
        self.label_3.setGeometry(QtCore.QRect(140, 120, 30, 21))
        self.label_3.setObjectName("label_3")

        self.comboBox = QtWidgets.QComboBox(self.centralwidget)
        self.comboBox.setEnabled(True)
        self.comboBox.setGeometry(QtCore.QRect(10, 120, 111, 21))
        self.comboBox.setObjectName("comboBox")
        self.comboBox.addItem("")
        self.comboBox.addItem("")

        self.pushButton_4 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_4.setGeometry(QtCore.QRect(10, 170, 141, 23))
        self.pushButton_4.setObjectName("pushButton_4")
        self.pushButton_4.clicked.connect(self.openSetFile)

        self.pushButton_5 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_5.setGeometry(QtCore.QRect(600, 119, 75, 23))
        self.pushButton_5.setObjectName("pushButton_5")
        self.pushButton_5.clicked.connect(self.saveConfig)

        self.label_4 = QtWidgets.QLabel(self.centralwidget)
        self.label_4.setGeometry(QtCore.QRect(350, 119, 75, 23))
        self.label_4.setEnabled(True)
        self.label_4.setObjectName("label_4")
        # self.horizontalLayout.addWidget(self.label_4)
        self.lineEdit_2 = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit_2.setGeometry(QtCore.QRect(430, 119, 157, 21))
        self.lineEdit_2.setEnabled(True)
        self.lineEdit_2.setObjectName("lineEdit_2")
        # self.horizontalLayout.addWidget(self.lineEdit_2)

        mainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(mainWindow)
        self.statusbar.setObjectName("statusbar")
        mainWindow.setStatusBar(self.statusbar)

        #禁止调整大小，禁用最大化按钮
        mainWindow.setFixedSize(mainWindow.width(), mainWindow.height());

        self.retranslateUi(mainWindow)
        QtCore.QMetaObject.connectSlotsByName(mainWindow)

    def retranslateUi(self, mainWindow):
        _translate = QtCore.QCoreApplication.translate
        mainWindow.setWindowTitle(_translate("mainWindow", "礼包工具"))

        self.pushButton.setText(_translate("mainWindow", "源文件"))

        self.label.setText(_translate("mainWindow", "未选择源文件"))

        self.pushButton_2.setText(_translate("mainWindow", "目标文件夹"))

        self.label_2.setText(_translate("mainWindow", "未选择目标文件夹"))

        item = self.tableWidget.horizontalHeaderItem(0)

        item.setText(_translate("mainWindow", "文件名"))

        item = self.tableWidget.horizontalHeaderItem(1)

        item.setText(_translate("mainWindow", "礼包数量"))

        self.pushButton_3.setText(_translate("mainWindow", "开始"))

        self.radioButton.setText(_translate("mainWindow", "按文件名-数量分配"))

        self.radioButton_2.setText(_translate("mainWindow", "按配置分配"))

        self.label_3.setText(_translate("mainWindow", "数量"))

        self.comboBox.setItemText(0, _translate("mainWindow", "文件数量"))

        self.comboBox.setItemText(1, _translate("mainWindow", "礼包数量"))

        self.pushButton_4.setText(_translate("mainWindow", "导入分配配置文件"))

        self.pushButton_5.setText(_translate("mainWindow", "保存配置"))

        self.label_4.setText(_translate("mainWindow", "排除sheet"))

    def openFile(self):
        _translate = QtCore.QCoreApplication.translate
        fileName, _ = QFileDialog.getOpenFileName(None, '打开excel文件', 'D:\wwwroot', 'Excel files (*.xls *.xlsx *.csv)')
        if fileName == "":
            print("\n取消选择")
            return
        self.label.setText(_translate("mainWindow", fileName))
        GiftPackage.splitFile = pd.io.excel.ExcelFile(fileName)

    def openSetFile(self):
        _translate = QtCore.QCoreApplication.translate
        fileName, _ = QFileDialog.getOpenFileName(None, '打开txt文件', 'D:\wwwroot', 'TXT files (*.txt)')
        if fileName == "":
            print("\n取消选择")
            return
        with open(fileName, "r", encoding='utf-8') as f:
            lines = f.readlines()
            for i in range(len(lines)):
                fileName, num = lines[i].split('-')
                num = num.strip()
                fileName = fileName.strip()
                self.tableWidget.insertRow(i)
                self.tableWidget.setItem(i, 0, QtWidgets.QTableWidgetItem(fileName))
                self.tableWidget.setItem(i, 1, QtWidgets.QTableWidgetItem(num))
                GiftPackage.customFile.append([fileName, num])

    def openFolder(self):
        _translate = QtCore.QCoreApplication.translate
        GiftPackage.folderName = QFileDialog.getExistingDirectory(None, '选择文件夹', 'D:\wwwroot')
        if GiftPackage.folderName == "":
            print("\n取消选择")
            return
        self.label_2.setText(_translate("mainWindow", GiftPackage.folderName))

    def writeConfig(self, mainWindow):
        path = os.path.abspath(os.curdir) + '\config.ini'
        isConfig = os.path.exists(path)
        if not isConfig:
            cf = configparser.ConfigParser()
            cf.add_section('config')
            cf.set('config', 'is_cfg', '1')
            cf.set('config', 'base_on', '0')
            cf.set('config', 'number', '0')
            cf.set('config', 'exclusive_sheet', '1')
            try:
                with open(path, "a") as f:
                    cf.write(f)
            except ImportError:
                pass

    def get_digits(self, n):
        list1 = []
        n = int(n)
        if n:
            list1 = self.get_digits(int(n) // 10)
            return list1 + list(map(int, str(n % 10)))  # 采用map函数将单个数字变成列表
        else:
            return list1

    def initConfigInfo(self, mainWindow):
        cf = configparser.ConfigParser()
        path = os.path.abspath(os.curdir) + '\config.ini'
        cf.read(path)
        a_sections = cf.sections()
        for i in a_sections:
            for j in cf.options(i):
                currentItem = int(cf.get(i, j))
                if j == 'is_cfg':
                    if currentItem == 1:
                        self.radioButton.setChecked(True)
                    else:
                        self.radioButton_2.setChecked(True)
                if j == 'base_on':
                    self.comboBox.setCurrentIndex(currentItem)
                if j == 'number':
                    self.lineEdit.setText(str(currentItem))
                if j == 'exclusive_sheet':
                    self.lineEdit_2.setText(str(currentItem))

    def saveConfig(self):
        path = os.path.abspath(os.curdir) + '\config.ini'
        #获取配置
        if self.radioButton.isChecked():
            is_cfg = 1
        else:
            is_cfg = 2
        base_on = self.comboBox.currentIndex()
        number = self.lineEdit.text()
        exclusive_sheet = self.lineEdit_2.text()
        cf = configparser.ConfigParser()
        cf.read(path)
        cf.set('config', 'is_cfg', str(is_cfg))
        cf.set('config', 'base_on', str(base_on))
        cf.set('config', 'number', number)
        cf.set('config', 'exclusive_sheet', exclusive_sheet)
        try:
            with open(path, "w+") as f:
                cf.write(f)
        except ImportError:
            print('保存错误')
            self.statusbar.showMessage('保存错误')
            pass
        self.statusbar.showMessage('保存成功')

    def start(self):
        data_xls = GiftPackage.splitFile
        exclusive_sheet = self.lineEdit_2.text()
        exclusive_sheet = self.get_digits(exclusive_sheet)
        df_obj = []
        df_name = []
        if type(data_xls) == str:
            self.statusbar.showMessage('请选择待分配文件')
            return
        for index in range(len(data_xls.sheet_names)):
            name = data_xls.sheet_names[index]
            if index + 1 in exclusive_sheet:
                print('索引：' + str(index) + '在其中，跳过')
                continue
            df = pd.read_excel(data_xls, sheet_name=name, header=None)
            df_obj.append(df)
            df_name.append(name)
        self.splitXlsFile(df_obj, df_name)


    def splitXlsFile(self, df, df_name):
        _translate = QtCore.QCoreApplication.translate
        currentPath = os.path.abspath(os.curdir)
        if GiftPackage.folderName:
            currentPath = GiftPackage.folderName
        if len(df) == 0:
            self.statusbar.showMessage('请选择待分配文件')
            return
        for obj_index in range(len(df)):
            rows, cols = df[obj_index].shape
            if self.radioButton.isChecked():
                is_cfg = 1  # 基于文件名-数量分配
            else:
                is_cfg = 2  # 基于配置分配

            base_on = self.comboBox.currentIndex()
            number = self.lineEdit.text()
            doneProgress = 0  # 完成进度
            if is_cfg == 1:
                fileArr = GiftPackage.customFile
                k = 0
                new_list = []
                rows_format = 0
                if not fileArr:
                    self.statusbar.showMessage('请选择分配配置文件')
                    return
                for fileItem in fileArr:
                    fileName, num = fileItem
                    num = int(num)
                    if (k + num) >= rows:
                        break
                    new_list.append([fileName, k, k + num])
                    k = k + num
                    rows_format = k
                allCount = len(new_list)
                k = 0
                for f_i_j in new_list:
                    self.pushButton_3.setEnabled(False)
                    self.pushButton_3.setText(_translate("mainWindow", "停止"))
                    k = k + 1
                    doneProgress = (k / allCount) * 100
                    f, i, j = f_i_j
                    excel_small = df[obj_index][i:j]
                    if os.path.isfile('{0}/{1}_{2}_{3}.xlsx'.format(currentPath, f, i, j)):
                        with pd.ExcelWriter('{0}/{1}_{2}_{3}.xlsx'.format(currentPath, f, i, j), mode="a") as writer:
                            excel_small.to_excel(writer, sheet_name=df_name[obj_index], index=False, header=False)
                            if obj_index == 0:
                                df[obj_index][2][0:1].to_excel(writer, sheet_name=df_name[obj_index], startrow=0, startcol=2, index=False, header=False)
                    else:
                        with pd.ExcelWriter('{0}/{1}_{2}_{3}.xlsx'.format(currentPath, f, i, j)) as writer:
                            excel_small.to_excel(writer, sheet_name=df_name[obj_index], index=False, header=False)
                            if obj_index == 0:
                                df[obj_index][2][0:1].to_excel(writer, sheet_name=df_name[obj_index], startrow=0, startcol=2, index=False, header=False)
                if rows > rows_format:
                    if os.path.isfile('{0}/礼包文件_last.xlsx'.format(currentPath)):
                        with pd.ExcelWriter('{0}/礼包文件_last.xlsx'.format(currentPath), mode="a") as writer:
                            df[obj_index][rows_format:].to_excel(writer, sheet_name=df_name[obj_index], index=False, header=False)
                            if obj_index == 0:
                                df[obj_index][2][0:1].to_excel(writer, sheet_name=df_name[obj_index], startrow=0, startcol=2, index=False, header=False)
                    else:
                        with pd.ExcelWriter('{0}/礼包文件_last.xlsx'.format(currentPath)) as writer:
                            df[obj_index][rows_format:].to_excel(writer, sheet_name=df_name[obj_index], index=False, header=False)
                            if obj_index == 0:
                                df[obj_index][2][0:1].to_excel(writer, sheet_name=df_name[obj_index], startrow=0, startcol=2, index=False, header=False)
                    self.progressBar.setValue(int(doneProgress))
            else:
                self.statusbar.showMessage('该功能暂未开放')
                return False
            if doneProgress >= 100:
                self.statusbar.showMessage('完成')
                self.pushButton_3.setEnabled(True)
                self.pushButton_3.setText(_translate("mainWindow", "开始"))


    def mkdir(self, path):
        path = path.strip()
        path = path.rstrip("\\")
        isExists = os.path.exists(path)
        if not isExists:
            os.makedirs(path)
            print(path + ' 创建成功')
            return True
        else:
            print(path + ' 目录已存在')
            return False


def main():
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = GiftPackage()
    ui.setupUi(MainWindow)
    ui.writeConfig(MainWindow)
    ui.initConfigInfo(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

if __name__ == '__main__':
    main()

有什么不懂得可以提问，或者关注WX 七月之上7，后台留言也可以。