[开发工具]pandas合并excel文件 V3.0（使用pyqt5制作Windows应用程序，合并多个excel中的多个sheet表）

为了更方便的合并文件，使用pyqt5制作Windows应用程序。

参考文档：
PyCharm安装PyQt5及其工具（Qt Designer、PyUIC、PyRcc）详细教程
（备注，有人说不需要设置环境变量，不太清楚对不对。）
（备注：5.15.4版本的designer.exe文件在qt5_applications\Qt\bin目录下）

使用Qtdesigner 设计界面，使用Pyuic将界面文件，转换成py代码。

单词combine写错了，请大家忽略这个问题

conbine_files_UI.py 文件

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

# Form implementation generated from reading ui file 'conbine_files_UI.ui'
#
# Created by: PyQt5 UI code generator 5.15.6
#
# 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.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_mainWindow(object):
    def setupUi(self, mainWindow):
        mainWindow.setObjectName("mainWindow")
        mainWindow.resize(800, 1200)
        mainWindow.setMinimumSize(QtCore.QSize(800, 1200))
        mainWindow.setMaximumSize(QtCore.QSize(1200, 1200))
        mainWindow.setBaseSize(QtCore.QSize(800, 800))
        self.centralwidget = QtWidgets.QWidget(mainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.setObjectName("gridLayout")
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.verticalLayout_4 = QtWidgets.QVBoxLayout()
        self.verticalLayout_4.setContentsMargins(0, 5, 0, 5)
        self.verticalLayout_4.setSpacing(5)
        self.verticalLayout_4.setObjectName("verticalLayout_4")
        self.TextEdit_filefolder_path = QtWidgets.QTextEdit(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Expanding)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.TextEdit_filefolder_path.sizePolicy().hasHeightForWidth())
        self.TextEdit_filefolder_path.setSizePolicy(sizePolicy)
        self.TextEdit_filefolder_path.setBaseSize(QtCore.QSize(300, 20))
        self.TextEdit_filefolder_path.setObjectName("TextEdit_filefolder_path")
        self.verticalLayout_4.addWidget(self.TextEdit_filefolder_path)
        self.TextEdit_savefolder_path = QtWidgets.QTextEdit(self.centralwidget)
        self.TextEdit_savefolder_path.setBaseSize(QtCore.QSize(300, 0))
        self.TextEdit_savefolder_path.setObjectName("TextEdit_savefolder_path")
        self.verticalLayout_4.addWidget(self.TextEdit_savefolder_path)
        self.horizontalLayout_2.addLayout(self.verticalLayout_4)
        self.verticalLayout_3 = QtWidgets.QVBoxLayout()
        self.verticalLayout_3.setContentsMargins(-1, 5, -1, 5)
        self.verticalLayout_3.setSpacing(5)
        self.verticalLayout_3.setObjectName("verticalLayout_3")
        self.btn_select_filefolder = QtWidgets.QPushButton(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Minimum)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.btn_select_filefolder.sizePolicy().hasHeightForWidth())
        self.btn_select_filefolder.setSizePolicy(sizePolicy)
        self.btn_select_filefolder.setBaseSize(QtCore.QSize(100, 0))
        self.btn_select_filefolder.setObjectName("btn_select_filefolder")
        self.verticalLayout_3.addWidget(self.btn_select_filefolder)
        self.btn_select_savefolder = QtWidgets.QPushButton(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Minimum)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.btn_select_savefolder.sizePolicy().hasHeightForWidth())
        self.btn_select_savefolder.setSizePolicy(sizePolicy)
        self.btn_select_savefolder.setObjectName("btn_select_savefolder")
        self.verticalLayout_3.addWidget(self.btn_select_savefolder)
        self.horizontalLayout_2.addLayout(self.verticalLayout_3)
        self.gridLayout.addLayout(self.horizontalLayout_2, 0, 0, 1, 1)
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.verticalLayout = QtWidgets.QVBoxLayout()
        self.verticalLayout.setObjectName("verticalLayout")
        self.label = QtWidgets.QLabel(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Preferred, QtWidgets.QSizePolicy.Preferred)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.label.sizePolicy().hasHeightForWidth())
        self.label.setSizePolicy(sizePolicy)
        self.label.setMinimumSize(QtCore.QSize(20, 0))
        self.label.setBaseSize(QtCore.QSize(100, 0))
        font = QtGui.QFont()
        font.setPointSize(9)
        self.label.setFont(font)
        self.label.setObjectName("label")
        self.verticalLayout.addWidget(self.label)
        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setBaseSize(QtCore.QSize(100, 0))
        font = QtGui.QFont()
        font.setPointSize(9)
        self.label_2.setFont(font)
        self.label_2.setObjectName("label_2")
        self.verticalLayout.addWidget(self.label_2)
        self.horizontalLayout.addLayout(self.verticalLayout)
        self.verticalLayout_2 = QtWidgets.QVBoxLayout()
        self.verticalLayout_2.setObjectName("verticalLayout_2")
        self.textEdit_top_drop_num = QtWidgets.QTextEdit(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Fixed, QtWidgets.QSizePolicy.Fixed)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.textEdit_top_drop_num.sizePolicy().hasHeightForWidth())
        self.textEdit_top_drop_num.setSizePolicy(sizePolicy)
        self.textEdit_top_drop_num.setMinimumSize(QtCore.QSize(20, 20))
        self.textEdit_top_drop_num.setBaseSize(QtCore.QSize(300, 0))
        font = QtGui.QFont()
        font.setPointSize(9)
        self.textEdit_top_drop_num.setFont(font)
        self.textEdit_top_drop_num.setObjectName("textEdit_top_drop_num")
        self.verticalLayout_2.addWidget(self.textEdit_top_drop_num)
        self.textEdit_buttom_drop_num = QtWidgets.QTextEdit(self.centralwidget)
        self.textEdit_buttom_drop_num.setMinimumSize(QtCore.QSize(20, 20))
        self.textEdit_buttom_drop_num.setBaseSize(QtCore.QSize(300, 0))
        font = QtGui.QFont()
        font.setPointSize(9)
        self.textEdit_buttom_drop_num.setFont(font)
        self.textEdit_buttom_drop_num.setObjectName("textEdit_buttom_drop_num")
        self.verticalLayout_2.addWidget(self.textEdit_buttom_drop_num)
        self.horizontalLayout.addLayout(self.verticalLayout_2)
        self.Btn_combine = QtWidgets.QPushButton(self.centralwidget)
        sizePolicy = QtWidgets.QSizePolicy(QtWidgets.QSizePolicy.Minimum, QtWidgets.QSizePolicy.Minimum)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.Btn_combine.sizePolicy().hasHeightForWidth())
        self.Btn_combine.setSizePolicy(sizePolicy)
        font = QtGui.QFont()
        font.setPointSize(9)
        self.Btn_combine.setFont(font)
        self.Btn_combine.setObjectName("Btn_combine")
        self.horizontalLayout.addWidget(self.Btn_combine)
        self.horizontalLayout.setStretch(0, 2)
        self.horizontalLayout.setStretch(1, 1)
        self.horizontalLayout.setStretch(2, 2)
        self.gridLayout.addLayout(self.horizontalLayout, 1, 0, 1, 1)
        self.verticalLayout_5 = QtWidgets.QVBoxLayout()
        self.verticalLayout_5.setObjectName("verticalLayout_5")
        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setObjectName("label_3")
        self.verticalLayout_5.addWidget(self.label_3)
        self.textBrowser_msg = QtWidgets.QTextBrowser(self.centralwidget)
        self.textBrowser_msg.setMinimumSize(QtCore.QSize(0, 0))
        self.textBrowser_msg.setObjectName("textBrowser_msg")
        self.verticalLayout_5.addWidget(self.textBrowser_msg)
        self.label_4 = QtWidgets.QLabel(self.centralwidget)
        self.label_4.setObjectName("label_4")
        self.verticalLayout_5.addWidget(self.label_4)
        self.gridLayout.addLayout(self.verticalLayout_5, 2, 0, 1, 1)
        self.gridLayout.setRowMinimumHeight(0, 1)
        self.gridLayout.setRowMinimumHeight(1, 1)
        self.gridLayout.setRowMinimumHeight(2, 5)
        self.gridLayout.setRowStretch(0, 1)
        self.gridLayout.setRowStretch(1, 1)
        self.gridLayout.setRowStretch(2, 5)
        mainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(mainWindow)
        self.statusbar.setObjectName("statusbar")
        mainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(mainWindow)
        self.btn_select_filefolder.clicked.connect(mainWindow.select_filefolderpath_click) # type: ignore
        self.Btn_combine.clicked.connect(mainWindow.combine_click) # type: ignore
        self.btn_select_savefolder.clicked.connect(mainWindow.select_savepath_click) # type: ignore
        QtCore.QMetaObject.connectSlotsByName(mainWindow)

    def retranslateUi(self, mainWindow):
        _translate = QtCore.QCoreApplication.translate
        mainWindow.setWindowTitle(_translate("mainWindow", "文件合并助手 V1.0"))
        self.btn_select_filefolder.setText(_translate("mainWindow", "选择待合并文件夹"))
        self.btn_select_savefolder.setText(_translate("mainWindow", "选择保存文件夹"))
        self.label.setText(_translate("mainWindow", "列名所在行"))
        self.label_2.setText(_translate("mainWindow", "尾行需要去掉的行数"))
        self.textEdit_top_drop_num.setHtml(_translate("mainWindow", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">1</p></body></html>"))
        self.textEdit_buttom_drop_num.setHtml(_translate("mainWindow", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">0</p></body></html>"))
        self.Btn_combine.setText(_translate("mainWindow", "开始合并"))
        self.label_3.setText(_translate("mainWindow", "合并进度"))
        self.label_4.setText(_translate("mainWindow", "powered  by  陆百万  13027923626"))

main.py 文件

import sys
import os
import pandas as pd
import warnings
from conbine_files_UI import Ui_mainWindow
from PyQt5 import QtWidgets, QtCore
import time

warnings.filterwarnings("ignore")  # 忽略warning消息

class combine_files(QtWidgets.QMainWindow):

    #初始化
    def __init__(self):
        super(combine_files, self).__init__()
        QtWidgets.QMainWindow.__init__(self)
        self.ui = Ui_mainWindow()
        self.ui.setupUi(self)

    #获取待合并文件夹地址
    def select_filefolderpath_click(self):
        #选择待合并的文件夹
        file_name = QtWidgets.QFileDialog.getExistingDirectory(None, "Select File Directory to Save File", "")
        self.filename = file_name
        self.ui.TextEdit_filefolder_path.setText(file_name)

        #默认保存在待合并文件的上一级文件夹中
        self.savePath = os.path.dirname(file_name)
        self.ui.TextEdit_savefolder_path.setText(self.savePath)

    #选择新的文件夹保存文件
    def select_savepath_click(self):
        savePath = QtWidgets.QFileDialog.getExistingDirectory(None, "Select File Directory to Save File", "")
        self.ui.TextEdit_savefolder_path.setText(savePath)
        self.savePath=savePath

    def combine_click(self):
        headerline_num = int(self.ui.textEdit_top_drop_num.toPlainText())
        skipfooter_num = int(self.ui.textEdit_buttom_drop_num.toPlainText())
        #清空进度框
        self.ui.textBrowser_msg.setText("")
        self.headerline_num = headerline_num
        self.skipfooter_num = skipfooter_num

        self.conbine_meituanyouxuan(self.filename, self.savePath, self.headerline_num - 1, self.skipfooter_num)

        # 合并指定文件夹内xlsx文件。（默认方式）

    def conbine_meituanyouxuan(self, folderpath, savePath, headerline_num, skip_footer_num):
        # 记录开始时间
        start_time = time.time()
        dir = folderpath
        DFs = []  # 新建列表，存放每个文件数据框（每一个excel读取后存放在数据框,依次读取多个相同结构的Excel文件并创建DataFrame）

        # 将第一个excel的sheet名称保存下来，便于汇总后命名
        for root, dirs, files in os.walk(dir):
            for file in files:
                file_path = os.path.join(root, file)  # 测试的文件夹中，没有子文件夹，所以用当前文件夹和文件名组合成一个完整路径
                # 将第一个excel的sheet名称保存下来，便于汇总后命名
                if file == files[0]:
                    sheetnames = pd.read_excel(file_path, sheet_name=None).keys()

        for this_sheetname in sheetnames:
            self.ui.textBrowser_msg.append('开始合并sheet “' + this_sheetname + '”sheet \n')
            self.ui.textBrowser_msg.moveCursor(self.ui.textBrowser_msg.textCursor().atEnd())

            num = 0
            for root2, dirs2, file2s in os.walk(dir):
                for file2 in file2s:
                    file_path2 = os.path.join(root2, file2)  # 测试的文件夹中，没有子文件夹，所以用当前文件夹和文件名组合成一个完整路径
                    df = pd.read_excel(file_path2, sheet_name=this_sheetname, header=headerline_num,
                                       skipfooter=skip_footer_num, dtype=str)  # 防止订单号太长，默认都是string格式

                    if df.empty:
                        self.ui.textBrowser_msg.append('“' + file2 + '”中的“' + this_sheetname + '”sheet，是空表' + '\n')
                        if num == 0:
                            DFs.append(df)
                        else:
                            pass
                    else:
                        self.ui.textBrowser_msg.append('正在合并 “' + file2 + '”中的“' + this_sheetname + '”sheet\n')
                        DFs.append(df)
                    num = num + 1
            alldata = pd.concat(DFs)
            pathssss = os.path.join(savePath, this_sheetname + '.xlsx')
            alldata.to_excel(pathssss, sheet_name=this_sheetname, index=False,
                             engine='openpyxl')  # index：表示是否写行索引，默认为True
            DFs = []

        # 记录结束时间
        end_time = time.time()
        times = round(end_time - start_time, 2)
        self.ui.textBrowser_msg.append('合并完成，耗时' + format(times) + '秒' + '\n')


if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    window = combine_files()
    window.show()
    sys.exit(app.exec_())

例如：
电商订单数据：列名所在行为第一行，最后没有需要删除的行，因此填1，0.

社区团购-美团优选订单：所有sheet的列名所在行为第2行，最后没有需要删除的行，因此填2，0
合并后文件

问题：运行后容易卡主假死。解决办法，用多线程解决。