Excel文件定位工具创建

背景：女朋友说我excel文件太乱了，找不到，只模糊记得单元格里有啥内容，文件名字是啥忘了，我遇到好多次了，太难找了，我说，那我写个程序帮你找吧，忠诚的程序是不嫌累的。

说干就干，于是第一个版本的出现了：

嗯，它成功的解决了问题找到了文件，但是女朋友说，界面简直太丑了，能不能敢好看点，我不敢也得敢啊。
程序是用Python写的，于是思路是pyside2加pyinstaller搞一下，界面好久没用了，百度去查一下怎么使吧，发现现在都是pyside6了，我想也没多久啊，版本竟然直接从2到6了，真的好久没有关注他了（后来发现这是直接跳过去的）。
思路都有了，中途好多信号和槽，还有多线程都是从这个小项目中学过来的，最终的样子变成这样了。

嗯，废话不多说了，贴代码
UI界面文件

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

################################################################################
## Form generated from reading UI file 'ESelector.ui'
##
## Created by: Qt User Interface Compiler version 6.2.1
##
## WARNING! All changes made in this file will be lost when recompiling UI file!
################################################################################

from PySide6.QtCore import (QCoreApplication, QDate, QDateTime, QLocale,
    QMetaObject, QObject, QPoint, QRect,
    QSize, QTime, QUrl, Qt)
from PySide6.QtGui import (QBrush, QColor, QConicalGradient, QCursor,
    QFont, QFontDatabase, QGradient, QIcon,
    QImage, QKeySequence, QLinearGradient, QPainter,
    QPalette, QPixmap, QRadialGradient, QTransform)
from PySide6.QtWidgets import (QApplication, QGridLayout, QLabel, QLineEdit,
    QListWidget, QListWidgetItem, QMainWindow, QMenuBar,
    QProgressBar, QPushButton, QSizePolicy, QStatusBar,
    QTextEdit, QWidget)

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        if not MainWindow.objectName():
            MainWindow.setObjectName(u"MainWindow")
        MainWindow.resize(800, 593)
        self.centralwidget = QWidget(MainWindow)
        self.centralwidget.setObjectName(u"centralwidget")
        self.gridLayout_2 = QGridLayout(self.centralwidget)
        self.gridLayout_2.setObjectName(u"gridLayout_2")
        self.gridLayout = QGridLayout()
        self.gridLayout.setObjectName(u"gridLayout")
        self.btn_start = QPushButton(self.centralwidget)
        self.btn_start.setObjectName(u"btn_start")

        self.gridLayout.addWidget(self.btn_start, 1, 2, 1, 1)

        self.lab_address = QLabel(self.centralwidget)
        self.lab_address.setObjectName(u"lab_address")

        self.gridLayout.addWidget(self.lab_address, 0, 2, 1, 3)

        self.textEdit = QTextEdit(self.centralwidget)
        self.textEdit.setObjectName(u"textEdit")

        self.gridLayout.addWidget(self.textEdit, 2, 1, 1, 2)

        self.lineEdit = QLineEdit(self.centralwidget)
        self.lineEdit.setObjectName(u"lineEdit")

        self.gridLayout.addWidget(self.lineEdit, 1, 1, 1, 1)

        self.btn_select = QPushButton(self.centralwidget)
        self.btn_select.setObjectName(u"btn_select")

        self.gridLayout.addWidget(self.btn_select, 0, 1, 1, 1)

        self.listWidget = QListWidget(self.centralwidget)
        self.listWidget.setObjectName(u"listWidget")

        self.gridLayout.addWidget(self.listWidget, 2, 4, 2, 1)

        self.btn_clog = QPushButton(self.centralwidget)
        self.btn_clog.setObjectName(u"btn_clog")
        sizePolicy = QSizePolicy(QSizePolicy.Minimum, QSizePolicy.Preferred)
        sizePolicy.setHorizontalStretch(0)
        sizePolicy.setVerticalStretch(0)
        sizePolicy.setHeightForWidth(self.btn_clog.sizePolicy().hasHeightForWidth())
        self.btn_clog.setSizePolicy(sizePolicy)

        self.gridLayout.addWidget(self.btn_clog, 3, 1, 1, 2)

        self.progressBar = QProgressBar(self.centralwidget)
        self.progressBar.setObjectName(u"progressBar")
        self.progressBar.setEnabled(True)
        self.progressBar.setValue(0)
        self.progressBar.setTextVisible(True)

        self.gridLayout.addWidget(self.progressBar, 1, 4, 1, 1)


        self.gridLayout_2.addLayout(self.gridLayout, 0, 0, 1, 1)

        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QStatusBar(MainWindow)
        self.statusbar.setObjectName(u"statusbar")
        MainWindow.setStatusBar(self.statusbar)
        self.menubar = QMenuBar(MainWindow)
        self.menubar.setObjectName(u"menubar")
        self.menubar.setGeometry(QRect(0, 0, 800, 22))
        MainWindow.setMenuBar(self.menubar)

        self.retranslateUi(MainWindow)
        self.btn_clog.clicked.connect(self.textEdit.clear)

        QMetaObject.connectSlotsByName(MainWindow)
    # setupUi

    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(QCoreApplication.translate("MainWindow", u"MainWindow", None))
        self.btn_start.setText(QCoreApplication.translate("MainWindow", u"\u5f00\u59cb", None))
        self.lab_address.setText("")
        self.lineEdit.setText("")
        self.lineEdit.setPlaceholderText(QCoreApplication.translate("MainWindow", u"\u8bf7\u8f93\u5165\u5173\u952e\u8bcd", None))
        self.btn_select.setText(QCoreApplication.translate("MainWindow", u"\u9009\u62e9\u6587\u4ef6\u5939", None))
        self.btn_clog.setText(QCoreApplication.translate("MainWindow", u"\u6e05\u7a7a\u65e5\u5fd7", None))
    # retranslateUi

主文件

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

import sys, os, time
from PySide6.QtCore import QObject, QThread, Signal, Slot
from PySide6.QtWidgets import QApplication, QMainWindow, QFileDialog
from PySide6.QtGui import QIcon
from UI.ESelector import Ui_MainWindow
from UI import ico
from pandas import read_excel, ExcelFile
import subprocess


# Signals class
class MySignals(QObject):
    log_add = Signal(str)
    result_add = Signal(str)
    result_clear = Signal()
    pbar_change = Signal(int)
    btn_start_enable = Signal(bool)


# dealt with excel thread
class DealtExcelThread(QThread):
    def __init__(self, parent):
        QThread.__init__(self, parent=None)
        # Instantiate signals and connect signals to the slots
        self.signals = MySignals()
        self.signals.log_add.connect(parent.log_add)
        self.signals.result_add.connect(parent.result_add)
        self.signals.result_clear.connect(parent.result_clear)
        self.signals.pbar_change.connect(parent.pbar_change)
        self.signals.btn_start_enable.connect(parent.btn_start_enable)
        self.p = parent
        self.now = lambda: time.time()
 
    def run(self):
        try:
            if not self.p.fileList:
                self.signals.log_add.emit('请选择适当的文件夹.')
                self.stop()
            if not self.p.ui.lineEdit.text():
                self.signals.log_add.emit('请填写关键词.')
                self.stop()
            else:
                self.p.key = self.p.ui.lineEdit.text()
                self.signals.result_clear.emit()
                self.p.result = []
                self.signals.log_add.emit(f'{"*"*20}开始查找{"*"*20}')
                self.signals.btn_start_enable.emit(False)
                st = self.now()
                i,total = 0,len(self.p.fileList)
                # filter the key
                for file in self.p.fileList:
                    sheet_names = ExcelFile(file).sheet_names
                    self.signals.log_add.emit(f'读取 {file}...')
                    i+=1
                    try:
                        for sheet_name in sheet_names:
                            df = read_excel(file, sheet_name=sheet_name)
                            file_name = file.split("\\")[-1]
                            if True in df.columns.astype(str).str.contains(str(self.p.key)):
                                self.p.result.append((file, sheet_name))
                                self.signals.result_add.emit(f'{file_name} -> {sheet_name}.')
                                break
                            for col in df.columns:
                                if str(df[col].dtypes) == 'object':
                                    if True in df[col].str.contains(str(self.p.key)).values:
                                        self.p.result.append((file, sheet_name))
                                        self.signals.result_add.emit(f'{file_name} -> {sheet_name}.')
                                        break
                            else:
                                continue
                            break
                    except Exception as e:
                        self.signals.log_add.emit(f'发生错误 {str(e)},继续...')

                    self.signals.pbar_change.emit(int(i/total*100))
                # finish
                if self.p.result:
                    self.signals.log_add.emit(f'{"*"*20}查找完成{"*"*20}')
                    self.signals.log_add.emit(f'用时 {self.now()-st}s')
                    self.signals.log_add.emit(f'双击右侧结果可以打开(需要安装wps或者office)')
                else:
                    self.signals.log_add.emit(f'{"*"*20}未查找到相应文件{"*"*20}')
                    self.signals.log_add.emit(f'总共用时 {self.now()-st}s')
                self.stop()

        except Exception as e:
            self.signals.log_add.emit(f'发生错误 {str(e)}')
            self.stop()

    def stop(self):
        self.signals.btn_start_enable.emit(True)
        self.quit()


# dealt with excel thread
class OpenExcelThread(QThread):
    def __init__(self, parent,index):
        QThread.__init__(self, parent=None)
        # Instantiate signals and connect signals to the slots
        self.signals = MySignals()
        self.signals.log_add.connect(parent.log_add)
        self.p = parent
        self.index = index
 
    def run(self):
        try:
            if self.index != None:
                self.signals.log_add.emit(f'正在尝试打开 {self.p.result[self.index][0]},请稍后...')
                subprocess.call(args=f"explorer {self.p.result[self.index][0]}", shell=True)
                self.stop()

        except Exception as e:
            self.signals.log_add.emit(f'发生错误 {str(e)}')
            self.stop()

    def stop(self):
        self.quit()



class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        
        self.ui = Ui_MainWindow()
        self.ui.setupUi(self)
        self.directory = ''
        self.fileList = []
        self.key = ''
        self.result = []
        

    def select_floder(self):
        folder = QFileDialog.getExistingDirectory(None,"选取文件夹",self.directory)
        if folder:
            self.directory = folder
            folder = folder.replace('/', '\\')
            self.ui.lab_address.setText(folder)
            self.ui.textEdit.append(f"选定文件夹 {folder}.")
        
        self.fileList = []
        for root, _, files in os.walk(self.directory):
            for filename in files:
                if filename.endswith('.xlsx') or filename.endswith('.xls'):
                    self.fileList.append(os.path.join(root.replace('/', '\\'), filename))

        if self.fileList:
            self.ui.textEdit.append(f'请填写关键词(模糊查找对象仅支持文本).')
        else:
            self.ui.textEdit.append(f'该文件夹未包含Excel文件，请重新选择.')


    def initSignalsAndSlots(self):
        self.ui.btn_select.clicked.connect(self.select_floder)
        self.ui.btn_start.clicked.connect(self.dealt_excel)
        self.ui.listWidget.doubleClicked.connect(self.result_double_click)

    def dealt_excel(self):
        self.dealt_excel_thread = DealtExcelThread(self)
        self.dealt_excel_thread.start()

    def result_double_click(self,qModelIndex):
        index = qModelIndex.row()
        self.open_excel_thread = OpenExcelThread(self,index)
        self.open_excel_thread.start()

    
    @Slot(str)
    def log_add(self, msg):
        self.ui.textEdit.append(msg)

    @Slot(str)
    def result_add(self, msg):
        self.ui.listWidget.addItem(msg)

    @Slot(str)
    def result_clear(self):
        self.ui.listWidget.clear()

    @Slot(int)
    def pbar_change(self, num):
        self.ui.progressBar.setValue(num)

    @Slot(int)
    def btn_start_enable(self, b):
        self.ui.btn_start.setEnabled(b)


if __name__ == "__main__":
    app = QApplication(sys.argv)
    app.setQuitOnLastWindowClosed(True)
    window = MainWindow()

    window.setWindowTitle('Excel内容查找工具')
    window.setWindowIcon(QIcon(":/t.ico"))
    window.initSignalsAndSlots()
    window.show()

    sys.exit(app.exec())

希望能帮到大家，当然不想动手的金主爸爸也可以猛戳这里。