pandas合并excel文件 V4.0（多线程，解决Windows应用程序卡顿）

陆百亿

已于 2022-03-01 10:59:32 修改

阅读量743

点赞数

分类专栏： python(pandas) 合并excel文件实例文章标签： python pandas pyqt5

于 2022-02-23 16:11:10 首次发布

本文链接：https://blog.csdn.net/weixin_42821041/article/details/123091956

版权

python(pandas) 合并excel文件实例专栏收录该内容

5 篇文章 2 订阅

订阅专栏

pandas合并excel文件 V1.0 （合并多个excel中的某一个sheet表）

pandas合并excel文件 V2.0 （合并多个excel中的多个sheet表）

pandas合并excel文件 V2.1（合并多个excel中的多个sheet表，踩坑记录）

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

Windows 软件功能：
1，合并文件夹内多个相同的excel文件（xls格式，或者xlsx格式文件）。可以设置合并后的文件保存地址。
2，根据sheet内数据格式不同，可以设置列名所在行。可以删除末尾汇总行。

例：合并美团优选结算单内，按sheet名称不同，分别合并。
在这里插入图片描述
单个结算单格式：

软件设置：

合并后结果：

在这里插入图片描述

全部代码：

在这里插入图片描述

main.py

import os
import pandas as pd
import warnings
from combine_files_UI import Ui_mainWindow
from PyQt5 import QtWidgets, QtCore
import sys
from PyQt5.QtCore import *
import time

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


class Runthread(QtCore.QThread):
    #  通过类成员对象定义信号对象
    _signal = pyqtSignal(str)

    def __init__(self, folderpath, savePath, headerline_num, skip_footer_num):
        super(Runthread, self).__init__()
        self.folderpath = folderpath
        self.savePath = savePath
        self.headerline_num = headerline_num
        self.skip_footer_num = skip_footer_num

    def __del__(self):
        self.wait()

    def run(self):
        start_time = time.time()
        dir = self.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._signal.emit('开始合并sheet “' + this_sheetname + '”sheet \n')

            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=self.headerline_num - 1,
                                       skipfooter=self.skip_footer_num, dtype=str)  # 防止订单号太长，默认都是string格式

                    if df.empty:
                        self._signal.emit('“' + file2 + '”中的“' + this_sheetname + '”sheet，是空表' + '\n')

                        if num == 0:
                            DFs.append(df)
                        else:
                            pass
                    else:
                        self._signal.emit('正在合并 “' + file2 + '”中的“' + this_sheetname + '”sheet\n')

                        DFs.append(df)
                    num = num + 1
            alldata = pd.concat(DFs)
            pathssss = os.path.join(self.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._signal.emit('合并完成，耗时' + format(times) + '秒' + '\n')


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)
        self.thread = None

    # 获取待合并文件夹地址
    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):
        self.ui.textBrowser_msg.moveCursor(self.ui.textBrowser_msg.textCursor().atEnd())

        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.start_combine_exe()

    def start_combine_exe(self):
        self.thread = Runthread(folderpath=self.filename, savePath=self.savePath, headerline_num=self.headerline_num,
                                skip_footer_num=self.skipfooter_num)
        self.thread._signal.connect(self.call_back)
        self.thread.start()

    def call_back(self, msg):
        self.ui.textBrowser_msg.append(msg)


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

combine_files_UI.py

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

# Form implementation generated from reading ui file 'combine_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(1200, 1200)
        mainWindow.setMinimumSize(QtCore.QSize(1200, 1200))
        mainWindow.setMaximumSize(QtCore.QSize(2400, 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"))

主要参考：
PyQt - 使用多线程避免界面卡顿
 Python开发Windows桌面应用程序（二）简单应用程序实现

PyCharm安装PyQt5及其工具（Qt Designer、PyUIC、PyRcc）详细教程
以及此文章评论。

还有其他文章。看了很多大神的博客，记不清了。

相关资源：
完成后的可执行文件exe文件，免费下载链接。

陆百亿

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
pandas合并excel文件 V4.0（多线程，解决Windows应用程序卡顿）

主要参考：PyQt - 使用多线程避免界面卡顿Python开发Windows桌面应用程序（二）简单应用程序实现PyCharm安装PyQt5及其工具（Qt Designer、PyUIC、PyRcc）详细教程以及此文章评论。还有其他文章。看了很多大神的博客，记不清了。...
复制链接

扫一扫