Excel表格合并(PyQt5)_记录_231114

1、UI文件

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

#
# 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.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(1035, 600)
        MainWindow.setMinimumSize(QtCore.QSize(1035, 600))
        MainWindow.setMaximumSize(QtCore.QSize(1035, 600))
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget)
        self.verticalLayout.setObjectName("verticalLayout")
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.pushButton = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton.setObjectName("pushButton")
        self.horizontalLayout.addWidget(self.pushButton)


        self.label = QtWidgets.QLabel(self.centralwidget)
        self.label.setObjectName("label")
        self.horizontalLayout.addWidget(self.label)
        self.horizontalLayout.setStretch(0, 1)
        self.horizontalLayout.setStretch(1, 9)
        self.verticalLayout.addLayout(self.horizontalLayout)
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        spacerItem = QtWidgets.QSpacerItem(308, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem)
        self.pushButton_2 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_2.setObjectName("pushButton_2")
        self.horizontalLayout_2.addWidget(self.pushButton_2)

        spacerItem1 = QtWidgets.QSpacerItem(498, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem1)
        self.horizontalLayout_2.setStretch(0, 1)
        self.horizontalLayout_2.setStretch(1, 1)
        self.horizontalLayout_2.setStretch(2, 1)
        self.verticalLayout.addLayout(self.horizontalLayout_2)
        self.textBrowser = QtWidgets.QTextBrowser(self.centralwidget)
        self.textBrowser.setObjectName("textBrowser")
        self.verticalLayout.addWidget(self.textBrowser)
        self.horizontalLayout_3 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_3.setObjectName("horizontalLayout_3")
        spacerItem2 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_3.addItem(spacerItem2)
        self.label_2 = QtWidgets.QLabel(self.centralwidget)
        self.label_2.setText("")
        self.label_2.setAlignment(QtCore.Qt.AlignCenter)
        self.label_2.setObjectName("label_2")
        self.horizontalLayout_3.addWidget(self.label_2)
        spacerItem3 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_3.addItem(spacerItem3)
        self.horizontalLayout_3.setStretch(0, 3)
        self.horizontalLayout_3.setStretch(1, 4)
        self.horizontalLayout_3.setStretch(2, 3)
        self.verticalLayout.addLayout(self.horizontalLayout_3)
        self.horizontalLayout_4 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_4.setObjectName("horizontalLayout_4")
        spacerItem4 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_4.addItem(spacerItem4)
        self.label_3 = QtWidgets.QLabel(self.centralwidget)
        self.label_3.setObjectName("label_3")
        self.horizontalLayout_4.addWidget(self.label_3)
        spacerItem5 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_4.addItem(spacerItem5)
        self.horizontalLayout_4.setStretch(0, 1)
        self.horizontalLayout_4.setStretch(1, 1)
        self.horizontalLayout_4.setStretch(2, 1)
        self.verticalLayout.addLayout(self.horizontalLayout_4)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 1035, 26))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

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

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "Excel表格合并Test_2023_V1.0"))
        self.pushButton.setText(_translate("MainWindow", "请选择文件目录"))
        self.pushButton.clicked.connect(self.select_directory)
        self.label.setText(_translate("MainWindow", "|"))
        self.pushButton_2.setText(_translate("MainWindow", "开始合并表格"))
        self.pushButton_2.clicked.connect(self.merged)
        self.label_3.setText(_translate("MainWindow", "Excel2019及以后的版本最大支持1048576行和16384列"))

2、主文件

'''
Author       : grace_echo2022@163.com
Date         : 2023-11-15 13:46:23
LastEditTime : 2023-11-15 16:29:09
Copyright (c) 2023 by Echo, All Rights Reserved. 
'''
import math
import os
import time
from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QFileDialog,QMessageBox
import sys
import pandas as pd
from Ui_excel import Ui_MainWindow
from time import localtime, strftime
from PyQt5.QtCore import QTimer, QDateTime, QThread, pyqtSignal
from numpy import dtype

# 用于将大表格分割为多个小表格
def split_dataframe(df, chunk_size):
    # 计算需要分割成多少个小表格
    chunks = math.ceil(len(df) / chunk_size)
    # 使用列表推导式生成包含所有小表格的列表
    return [df[i*chunk_size:(i+1)*chunk_size] for i in range(chunks)]


# 将大表格写入多个Excel文件中
def write_large_dataframe_to_excel(df, chunk_size, output_file):
    file_list = []
    # 将数据框按照指定的块大小进行拆分
    chunks = split_dataframe(df, chunk_size)
    
    # 遍历拆分后的数据块
    for i, chunk in enumerate(chunks):
        # 使用pandas的ExcelWriter将数据块写入Excel文件
        with pd.ExcelWriter(f"{i+1}.{output_file}_表{i+1}_{len(chunk)}.xlsx") as writer:
            chunk.to_excel(writer, index=False) 
        # 输出合并完成的信息
        msg = f"{i+1}.{output_file}_表{i+1}_{len(chunk)}.xlsx"
        file_list.append(msg)
    return file_list

# 遍历当前目录下所有Excel表格并按修改时间排序
def traverse_excel_files(folder_path):
    excel_files = []
    with os.scandir(folder_path) as entries:
        for entry in entries:
            if not entry.name.startswith('~$') and entry.is_file() and (entry.name.endswith(".xlsx") or entry.name.endswith(".xls")):
                file_path = entry.path
                excel_files.append((file_path, os.path.getmtime(file_path)))  # 获取文件的修改时间
    excel_files = sorted(excel_files, key=lambda x: x[1])  # 按修改时间排序
    return [file[0] for file in excel_files]  # 返回排序后的文件路径列表


class MergeThread(QThread):
    # 定义一个信号，用于通知主线程任务完成
    mergeFinished = pyqtSignal(str)

    # 传递参数textBrowser 
    def __init__(self, excel_files_list,textBrowser,pushButton_2):
        super().__init__()
        self.excel_files_list = excel_files_list  # Excel文件列表
        self.textBrowser = textBrowser  # 文本浏览器对象
        self.pushButton_2 = pushButton_2

    def run(self):
        try:
            start_time = time.time()  # 记录开始时间
            merged_data = pd.DataFrame()  # 初始化合并后的数据框
            total_files = len(self.excel_files_list)  # 获取文件总数
            self.pushButton_2.setText('正在处理中,请耐心等待')
            for i, file in enumerate(self.excel_files_list):
                try:
                    msg_info = f"正在读取第 {i+1}/{total_files} 个文件： {file}"  # 打印当前合并的文件信息
                    print(msg_info)
                    data = pd.read_excel(file,dtype=dtype)  # 读取Excel文件数据
                    if not data.empty:  # 判断数据是否为空
                        self.textBrowser.append(f'{msg_info}-共计{len(data)}')  # 在文本浏览器中显示合并信息
                        merged_data = pd.concat([merged_data, data], ignore_index=True)  #将读取到的数据合并到merged_data中
                        # print(merged_data)
                    else:
                        print(f'{msg_info}-共计{len(data)}')
                        self.textBrowser.append(f'{msg_info}-共计{len(data)}')
                        continue
                except Exception as e:
                    msg_info = f"正在读取第 {i+1}/{total_files} 个文件：{file}" 
                    self.textBrowser.append(f'{msg_info}无法读取文件')
                    print(f'Exception_run_pd.read_excel:{e}')
                    continue
                
            excel_len = len(merged_data)  # 获取合并后的数据行数
            print(f'excel_len:{excel_len}')
            self.textBrowser.append(f'共:{excel_len}行,正在写入文件请稍等......\n')
            tmp = strftime('%Y%m%d', localtime())  # 获取当前日期
            output_file = f"{tmp}_合并后表格_共计{excel_len}"  # 生成输出文件名
            if excel_len >= 1048576:  # 如果数据行数大于等于1048576
                chunk_size = 1000000  # 设置分块大小为1048576
                file_list = write_large_dataframe_to_excel(merged_data, chunk_size, output_file)  # 调用函数将大数据集写入Excel文件
            else:
                file_list = write_large_dataframe_to_excel(merged_data, excel_len, output_file)  # 调用函数将小数据集写入Excel文件
            print(f'file_list:{file_list}')
            self.mergeFinished.emit(f'{file_list}')  # 发送合并完成的信号，携带输出文件名
            end_time = time.time()  # 记录结束时间
            elapsed_time = end_time - start_time  # 计算耗时时长
            minutes, seconds = divmod(elapsed_time, 60)  # 将耗时时长转换为分钟和秒
            formatted_time = f"{int(minutes)}分{int(seconds)}秒"  # 格式化输出
            self.pushButton_2.setText(f'已处理完成-耗时{formatted_time}')
            self.textBrowser.append(f'已处理完成-耗时{formatted_time}\n')

        except Exception as e:
            self.mergeFinished.emit(str(e))  # 发送异常信号，携带异常信息

class MainWindow(QtWidgets.QMainWindow,Ui_MainWindow):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.excel_files_list = ""
        # 创建一个定时器并设置其超时信号连接到更新时间的槽函数
        self.timer = QTimer()
        self.timer.timeout.connect(self.update_time)
        self.timer.start(1000)  # 每隔1000毫秒（1秒）触发一次超时信号
        self.mergeThread = None

    def select_directory(self):
        try:
            directory = QFileDialog.getExistingDirectory(self, "选择文件夹")
            self.label.setText(f'{directory}')
            excel_files = [f for f in os.listdir(directory) if not f.startswith('~$') and (f.endswith('.xlsx') or f.endswith('.xls'))]
            self.textBrowser.append(f'表格共计：{len(excel_files)}\n')
            self.textBrowser.append(f'{excel_files}\n')
            if not excel_files:
                self.label.setStyleSheet("color: red;")
                return self.label.setText(f'请选择正确的文件目录')
            self.excel_files_list = traverse_excel_files(directory)
            # self.excel_files_list = excel_files
        except Exception as e:
            # 捕获异常并弹出消息框显示异常信息
            QMessageBox.critical(self, '错误', f'发生异常：{e}')
        self.pushButton_2.setText("开始合并表格")
        self.pushButton_2.setEnabled(True)
        # self.pushButton.setStyleSheet("")

    def merged(self):
        # 检查是否有要合并的Excel文件列表
        if self.excel_files_list != "":
            # 禁用pushButton_2按钮，防止用户在合并过程中再次点击
            self.pushButton_2.setEnabled(False)
            # 创建一个MergeThread对象，将excel_files_list和textBrowser作为参数传递
            self.mergeThread = MergeThread(self.excel_files_list, self.textBrowser, self.pushButton_2)
            # 当合并完成时，调用mergeFinished方法
            self.mergeThread.mergeFinished.connect(self.mergeFinished)
            # 启动合并线程
            self.mergeThread.start()
        else:
            # 如果excel_files_list为空，则显示错误信息
            msg_err = f"请重新选择目录"
            print(msg_err)
            # 将错误信息添加到文本浏览器中
            self.textBrowser.append(msg_err)
            self.pushButton.setEnabled(True)

    def mergeFinished(self, output_file):
        if isinstance(output_file, str):
            msg_log = f"合并完成，保存为： {output_file}"
            print(msg_log)
            self.textBrowser.append(msg_log)
            self.textBrowser.append(f'***********************END***********************\n')
        else:
            msg_err = f"任务执行出现异常: {output_file}"
            print(msg_err)
            self.textBrowser.append(msg_err)

    def update_time(self):
        # 获取当前时间并将其格式化为字符串
        current_time = QDateTime.currentDateTime().toString('yyyy-MM-dd,hh:mm:ss')
        # 更新标签的文本
        self.label_2.setText(current_time)
                

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

3、打包

pyinstaller --onefile --noconsole xx.py

pyinstaller -F xx.py -w --upx-exclude=vcruntime140.dll