[Python知识库]Pyqt5+爬虫实现函证地址核对

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

# Form implementation generated from reading ui file 'demo.ui'
#
# 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
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
import traceback
from functools import partial
import xlwt
import requests
from bs4 import BeautifulSoup
import difflib
import time


class EmptyDelegate(QItemDelegate):
    def __init__(self, parent):
        super(EmptyDelegate, self).__init__(parent)

    def createEditor(self, QWidget, QStyleOptionViewItem, QModelIndex):
        return None


# 自定义的QTableWidget,使用ToolTip提示用户当前单元格内的详细内容
class MyTableWidget(QTableWidget):
    update_table_tooltip_signal = pyqtSignal(object)

    def __init__(self):
        super(MyTableWidget, self).__init__()
        self.ini_table()

    def ini_table(self):
        """---------初始化表格的常用选项(按需修改)------------"""
        # QTableWidget.resizeColumnsToContents(self)
        # QTableWidget.resizeRowsToContents(self)
        # self.setSelectionMode(QAbstractItemView.NoSelection)
        # self.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 列宽自动分配
        # self.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 行高自动分配
        # # self.verticalHeader().stretchLastSection()                         #自动拓展最后一行适应表格高度
        # self.horizontalHeader().setVisible(False)
        # self.verticalHeader().setVisible(False)
        # self.setEditTriggers(QAbstractItemView.NoEditTriggers)
        """------------关键代码--------------"""
        self.vertical_scrollbar = QScrollBar()
        self.horizon_scrollbar = QScrollBar()
        self.vertical_scrollbar.valueChanged.connect(partial(self.scollbar_change_slot, "vertical"))
        self.horizon_scrollbar.valueChanged.connect(partial(self.scollbar_change_slot, "horizon"))
        self.setVerticalScrollBar(self.vertical_scrollbar)
        self.setHorizontalScrollBar(self.horizon_scrollbar)
        self.init_row = 0
        self.init_col = 0
        self.tool_tip = ""
        self.update_table_tooltip_signal.connect(self.update_table_tooltip_slot)
        self.title_row_height = 0

    # 设置表格列标题
    def set_horizon_title(self, title_list):
        self.horizontalHeader().setDefaultAlignment(Qt.AlignLeft)
        self.horizontalHeader().setVisible(True)
        self.title_row_height = 25  # (关键值)这里的值设置为列标题高
        for col, item in enumerate(title_list):
            item = QTableWidgetItem(str(item))
            item.setSizeHint(QSize(200, self.title_row_height))  # 这里默认设置了列标题的宽和高分别为200、45,可根据需要修改
            self.setHorizontalHeaderItem(col, item)

    # 为TableWidget安装事件过滤器
    def install_eventFilter(self):
        self.installEventFilter(self)
        self.setMouseTracking(True)

    # 改变滚动条时重置当前页面的初始行和列
    def scollbar_change_slot(self, type):
        if type == "vertical":
            value = self.verticalScrollBar().value()
            self.init_row = value
            # print("垂直滚动条当前的值为:",value)
            # print("当前页面的起始行为:",self.init_row)
        else:
            value = self.horizontalScrollBar().value()
            self.init_col = value
            # print("水平滚动条当前的值为:",value)
            # print("当前页面的起始列为:",self.init_col)

    # 通过计算坐标确定当前位置所属单元格
    def update_table_tooltip_slot(self, posit):
        self.tool_tip = ""
        self.mouse_x = posit.x()
        self.mouse_y = posit.y()
        self.row_height = self.title_row_height  # 累计行高,初始值为列标题行高
        for r in range(self.rowCount()):
            current_row_height = self.rowHeight(r)
            self.col_width = 0  # 累计列宽
            if self.row_height <= self.mouse_y <= self.row_height + current_row_height:
                for c in range(self.columnCount()):
                    current_col_width = self.columnWidth(c)
                    if self.col_width <= self.mouse_x <= self.col_width + current_col_width:
                        r = self.init_row + r
                        c = self.init_col + c
                        # print("鼠标当前所在的行和列为:({},{})".format(r, c))
                        item = self.item(r, c)
                        if item != None:
                            self.tool_tip = item.text()
                        else:
                            self.tool_tip = ""
                        return self.tool_tip
                    else:
                        self.col_width = self.col_width + current_col_width
            else:
                if self.mouse_y < self.row_height:
                    break
                else:
                    self.row_height = self.row_height + current_row_height

    # 事件过滤器
    def eventFilter(self, object, event):
        try:
            if event.type() == QEvent.ToolTip:
                self.setCursor(Qt.ArrowCursor)
                # print("当前鼠标位置为:", event.pos())
                self.update_table_tooltip_signal.emit(event.pos())
                # 设置提示气泡显示范围矩形框,当鼠标离开该区域则ToolTip消失
                rect = QRect(self.mouse_x, self.mouse_y, 30, 10)  # QRect(x,y,width,height)
                # 设置QSS样式
                self.setStyleSheet(
                    """QToolTip{border:10px;
                       border-top-left-radius:5px;
                       border-top-right-radius:5px;
                       border-bottom-left-radius:5px;
                       border-bottom-right-radius:5px;
                       background:#4F4F4F;
                       color:#00BFFF;
                       font-size:18px;
                       font-family:"微软雅黑";
                    }""")
                QApplication.processEvents()
                # 在指定位置展示ToolTip
                QToolTip.showText(QCursor.pos(), self.tool_tip, self, rect, 1500)

                """
                showText(QPoint, str, QWidget, QRect, int)
                #############参数详解###########
                #QPoint指定tooptip显示的绝对坐标,QCursor.pos()返回当前鼠标所在位置
                #str为设定的tooptip
                #QWidget为要展示tooltip的控件
                #QRect指定tooltip显示的矩形框范围,当鼠标移出该范围,tooltip隐藏,使用该参数必须指定Qwidget!
                #int用于指定tooltip显示的时长(毫秒)
                """
            return QWidget.eventFilter(self, object, event)
        except Exception as e:
            traceback.print_exc()


class Ui_MainWindow(QtWidgets.QMainWindow):
    update_table_tooltip_signal = pyqtSignal(object)
    sentHeader = ['company', 'addressRaw', 'addressCrawl', 'score']
    returnHeader = ['company', 'addressRaw', 'odd', 'addressReply', 'score']
    headers = {
        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8',
        'Accept-Encoding': 'gzip, deflate, br',
        'Accept-Language': 'zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2',
        'Connection': 'keep-alive',
        'Cookie': 'qcc_did=9e88ffca-bc0d-4f91-bccc-2bb74f6f4507;'
                  ' UM_distinctid=17e0b447c4cba0-0eb730a72339d28-4c3e207f-e1000-17e0b447c4d702; '
                  'CNZZDATA1254842228=1372876487-1640862760-https%253A%252F%252Fwww.baidu.com%252F%7C1642123605; '
                  'acw_tc=78dff62916421274788598045ee6814e290282719150233388b137fbb7; '
                  'QCCSESSID=4983223548c197fd6c6610bfab;'
                  ' zg_did=%7B%22did%22%3A%20%2217e0b4479e4339-01888029b9ca0e-4c3e207f-e1000-17e0b4479e55e2%22%7D;'
                  ' zg_294c2ba1ecc244809c552f8f6fd2a440=%7B%22sid%22%3A%201642127496234%2C%22'
                  'updated%22%3A%201642128337575%2C%22info%22%3A%201641993618638%2C%22'
                  'superProperty%22%3A%20%22%7B%5C%22%E5%BA%94%E7%94%A8%E5%90%8D%E7%A7%B0%5C%22%3A%20%5C%22%E4%BC%81%E6%9F%A5%E6%9F%A5%E7%BD%91%E7%AB%99%5C%22%7D%22%2C%22'
                  'platform%22%3A%20%22%7B%7D%22%2C%22utm%22%3A%20%22%7B%5C%22%24'
                  'utm_source%5C%22%3A%20%5C%22baidu1%5C%22%2C%5C%22%24'
                  'utm_medium%5C%22%3A%20%5C%22cpc%5C%22%2C%5C%22%24'
                  'utm_term%5C%22%3A%20%5C%22pzsy%5C%22%7D%22%2C%22'
                  'referrerDomain%22%3A%20%22www.baidu.com%22%2C%22cuid%22%3A%20%22'
                  'undefined%22%2C%22zs%22%3A%200%2C%22sc%22%3A%200%2C%22firstScreen%22%3A%201642127496234%7D',
        'Host': 'www.qcc.com',
        'Referer': 'https://www.qcc.com/web/search?'
                   'key=%E6%B2%83%E5%B0%94%E6%B2%83%E5%BB%BA%E7%AD%91%E8%AE%BE%E5%A4%87%28%E4%B8%AD%E5%9B%BD%29%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:95.0) Gecko/20100101 Firefox/95.0'
    }
    checkBoxCompany = []
    checkChild = []

    def getAddress(self, companyName):
        # 英文括号=>中文括号
        companyName = self.setKuoHao(companyName)
        get_url = 'https://www.qcc.com/web/search?key={}'.format(companyName)
        response = requests.get(url=get_url, headers=self.headers)
        page_text = response.text
        soup = BeautifulSoup(page_text, 'lxml')
        # 找到搜索的列表
        table = soup.find('table', attrs={'class': 'ntable ntable-list'})
        # print(table)
        # 找到公司名
        span1 = table.find('span', attrs={'class': 'copy-title'})
        span2 = span1.find('span')
        name = span2.text
        if name != companyName:
            # print('1',name, companyName)
            return False, '爬取失败1'
        # 获取公司地址
        # xpath('//*[@class="ntable ntable-list"]/tr[1]/td[3]/div/div[4]/div[3]/span/div/span[1]').text
        tr1 = table.findAll('tr')[0]
        td3 = tr1.findAll('td')[2]
        div = td3.find('div')
        div4 = div.find('div', attrs={'class': 'relate-info'})
        div3 = div4.findAll('div', attrs={'class': 'rline'})[2]
        span = div3.find('span', attrs={'class': 'f'})
        addressFlag = span.text
        addressFlag = addressFlag.strip()
        addressFlag = addressFlag[:2]
        # print(addressFlag, '地址')
        if addressFlag != '地址':
            return False, '爬取失败2'
        span2 = span.find('span', attrs={'class': 'copy-value'})
        address = span2.text
        return True, address

    def getSimilarity(self, str1, str2):
        if not self.isNaNone(str1) and not self.isNaNone(str2):
            score = difflib.SequenceMatcher(None, str1, str2).quick_ratio()
        else:
            score = 0
        # print(str1, str2, score)
        return round(score, 2)

    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(760, 449)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.layoutWidget = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget.setGeometry(QtCore.QRect(10, 0, 741, 401))
        self.layoutWidget.setObjectName("layoutWidget")
        self.gridLayout_2 = QtWidgets.QGridLayout(self.layoutWidget)
        self.gridLayout_2.setContentsMargins(0, 0, 0, 0)
        self.gridLayout_2.setObjectName("gridLayout_2")
        self.gridLayout = QtWidgets.QGridLayout()
        self.gridLayout.setObjectName("gridLayout")
        self.pbReturnLetterSave = QtWidgets.QPushButton(self.layoutWidget)
        self.pbReturnLetterSave.setObjectName("pbReturnLetterSave")
        self.gridLayout.addWidget(self.pbReturnLetterSave, 2, 2, 1, 1)
        self.pbGetAddress = QtWidgets.QPushButton(self.layoutWidget)
        self.pbGetAddress.setObjectName("puGetAddress")
        self.gridLayout.addWidget(self.pbGetAddress, 3, 1, 1, 1)
        self.pbSentLetter = QtWidgets.QPushButton(self.layoutWidget)
        self.pbSentLetter.setObjectName("pbSentLetter")
        self.gridLayout.addWidget(self.pbSentLetter, 1, 0, 1, 1)
        self.pbSentLetterSave = QtWidgets.QPushButton(self.layoutWidget)
        self.pbSentLetterSave.setObjectName("pbSentLetterSave")
        self.gridLayout.addWidget(self.pbSentLetterSave, 1, 2, 1, 1)
        self.pbAddReturn = QtWidgets.QPushButton(self.layoutWidget)
        self.pbAddReturn.setObjectName("pbAddReturn")
        self.gridLayout.addWidget(self.pbAddReturn, 2, 1, 1, 1)
        self.pbAddSend = QtWidgets.QPushButton(self.layoutWidget)
        self.pbAddSend.setObjectName("pbAddSend")
        self.gridLayout.addWidget(self.pbAddSend, 1, 1, 1, 1)
        self.pbReturnLetter = QtWidgets.QPushButton(self.layoutWidget)
        self.pbReturnLetter.setObjectName("pbReturnLetter")
        self.gridLayout.addWidget(self.pbReturnLetter, 2, 0, 1, 1)
        self.gridLayout_2.addLayout(self.gridLayout, 1, 0, 1, 1)
        self.tabWidget = QtWidgets.QTabWidget(self.layoutWidget)
        self.tabWidget.setObjectName("tabWidget")
        self.sendLetter = QtWidgets.QWidget()
        self.sendLetter.setObjectName("sendLetter")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.sendLetter)
        self.verticalLayout.setObjectName("verticalLayout")

        self.sendTable = MyTableWidget()
        self.sendTable.setObjectName("sendTable")
        self.sendTable.setColumnCount(4)
        self.sendTable.setRowCount(0)
        self.verticalLayout.addWidget(self.sendTable)
        self.tabWidget.addTab(self.sendLetter, "")
        self.returnLetter = QtWidgets.QWidget()
        self.returnLetter.setObjectName("returnLetter")
        self.verticalLayout_2 = QtWidgets.QVBoxLayout(self.returnLetter)
        self.verticalLayout_2.setObjectName("verticalLayout_2")

        self.retrunTable = MyTableWidget()
        self.retrunTable.setObjectName("retrunTable")
        self.retrunTable.setColumnCount(5)
        self.retrunTable.setRowCount(0)
        self.verticalLayout_2.addWidget(self.retrunTable)
        self.tabWidget.addTab(self.returnLetter, "")
        self.gridLayout_2.addWidget(self.tabWidget, 0, 0, 1, 1)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 760, 22))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        self.tabWidget.setCurrentIndex(0)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
        # ===================================================================

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "函证地址核对"))
        self.pbReturnLetterSave.setText(_translate("MainWindow", "保存回函表"))
        self.pbGetAddress.setText(_translate("MainWindow", "企查查获取地址"))
        self.pbReturnLetter.setText(_translate("MainWindow", "导入回函表"))
        self.pbAddSend.setText(_translate("MainWindow", "添加到发函表"))
        self.pbAddReturn.setText(_translate("MainWindow", "添加到回函表"))
        self.pbSentLetter.setText(_translate("MainWindow", "导入发函表"))
        self.pbSentLetterSave.setText(_translate("MainWindow", "保存发函表"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.sendLetter), _translate("MainWindow", "发函表"))
        self.tabWidget.setTabText(self.tabWidget.indexOf(self.returnLetter), _translate("MainWindow", "回函表"))
        # =======================================================================
        # 禁止编辑，可以设置成模板
        # self.sendTable.setItemDelegateForColumn(0, EmptyDelegate(self))  # 设置第0列不可编辑, 从0开始
        self.sendTable.setItemDelegateForColumn(2, EmptyDelegate(self))  # 设置第2列不可编辑, 从0开始
        self.sendTable.setItemDelegateForColumn(3, EmptyDelegate(self))  # 设置第3列不可编辑, 从0开始
        title_list = ['公司名称', '手动录入地址', '注册地址', '匹配得分']
        self.sendTable.set_horizon_title(title_list)
        self.sendTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 自动伸缩

        # self.retrunTable.setItemDelegateForColumn(0, EmptyDelegate(self))  # 设置第0列不可编辑, 从0开始
        self.retrunTable.setItemDelegateForColumn(4, EmptyDelegate(self))  # 设置第3列不可编辑,
        # ['公司名称', '快递单号', '回函地址', '匹配得分']
        title_list = ['公司名称', '发函地址', '快递单号', '回函地址', '匹配得分']
        self.retrunTable.set_horizon_title(title_list)
        self.retrunTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 自动伸缩

        # 悬停显示单元格内容
        # hover
        self.pbSentLetter.clicked.connect(self.setSentLetter)
        self.pbReturnLetter.clicked.connect(self.setReturnLetter)
        self.pbSentLetterSave.clicked.connect(self.saveSent)
        self.pbReturnLetterSave.clicked.connect(self.saveReturn)
        # 修改后判断是否更新score
        self.sendTable.itemChanged.connect(partial(self.updateTableItem, self.sendTable, 'sentLetter'))
        self.retrunTable.itemChanged.connect(
            partial(self.updateTableItem, self.retrunTable, 'replyLetter'))
        # 爬取信息
        self.pbGetAddress.clicked.connect(partial(self.getCompaniesAddress, self.sendTable))
        # 添加到指定表中
        self.pbAddSend.clicked.connect(partial(self.addToTable, 'sentLetter', self.sendTable))
        self.pbAddReturn.clicked.connect(partial(self.addToTable, 'replyLetter', self.retrunTable))

    def getCompaniesAddress(self, tableWidget):
        rows = tableWidget.rowCount()
        progress = self.setProgress()
        for row in range(rows):
            name = tableWidget.item(row, 0).text()
            if not self.isNaNone(name):
                time.sleep(1)
                # ['company', 'addressRaw', 'addressCrawl', 'score']
                Flag, addressCrawl = self.getAddress(name)
                # print(name, addressCrawl)
                # 更新表中的爬取地址和得分
                if Flag:
                    addressCrawl = self.setKuoHao(addressCrawl)
                    item = QTableWidgetItem(addressCrawl)
                    tableWidget.setItem(row, 2, item)

                    addressRaw = tableWidget.item(row, 1).text()
                    score = self.getSimilarity(addressRaw, addressCrawl)
                    item = QTableWidgetItem(str(score))
                    tableWidget.setItem(row, 3, item)
            p = int(row / rows * 100)
            progress.setValue(p)
            if progress.wasCanceled():
                QMessageBox.warning(self, "提示", "操作失败")
                break
        else:
            progress.setValue(100)
            QMessageBox.information(self, "提示", "操作成功")

    def addToTable(self, tableName, tableWidget):
        """
        添加到指定表中
        :param tableName:
        :param tableWidget:
        :return:
        """
        if tableName == 'sentLetter':
            self.tabWidget.setCurrentIndex(0)  # 显示当前页面
        else:
            self.tabWidget.setCurrentIndex(1)  # 显示当前页面
        rows = tableWidget.rowCount()
        columns = tableWidget.columnCount()
        tableWidget.insertRow(rows)
        lastRow = rows
        # print(lastRow, rows)
        # tableWidget.setRowCount(0)
        # tableWidget.clearContents()
        tableWidget.blockSignals(True)  # 进入阻塞模式
        for column in range(columns):
            data = QTableWidgetItem('')
            tableWidget.setItem(lastRow, column, data)
        tableWidget.blockSignals(False)  # 退出阻塞模式

    def updateTableItem(self, tableWidget, table):
        """

        :param tableWidget:
        :param table: 数据库表名sentLetter
        :return:
        """
        # print(tableWidget, header, table)
        selectRows = tableWidget.selectedItems()
        if len(selectRows) == 0:
            # print("要了老命了", selectRows)
            return
        selectRow = selectRows[0].row()
        selectColunm = selectRows[0].column()
        if selectColunm not in [1, 3]:
            return

        # print(selectRow, selectCol)
        text = selectRows[0].text()
        # print(text)
        # title_list = ['公司名称', '发函地址', '快递单号', '回函地址', '匹配得分']
        if table == 'replyLetter':
            # 计算相似度
            address1 = tableWidget.item(selectRow, 1).text()
            # print('address1', address1)
            address3 = tableWidget.item(selectRow, 3).text()
            # print('address3', address3)
            if selectColunm == 1 and not self.isNaNone(address3):
                score = self.getSimilarity(address3, text)
                tableWidget.blockSignals(True)  # 进入阻塞模式
                data = QTableWidgetItem(str(score))
                tableWidget.setItem(selectRow, 4, data)
                tableWidget.blockSignals(False)  # 退出阻塞模式
            elif selectColunm == 3 and not self.isNaNone(address1):
                score = self.getSimilarity(address1, text)
                tableWidget.blockSignals(True)  # 进入阻塞模式
                data = QTableWidgetItem(str(score))
                tableWidget.setItem(selectRow, 4, data)
                tableWidget.blockSignals(False)  # 退出阻塞模式
        elif table == 'sentLetter':
            address = tableWidget.item(selectRow, 2).text()
            if selectColunm == 1 and not self.isNaNone(address):
                score = self.getSimilarity(address, text)
                tableWidget.blockSignals(True)  # 进入阻塞模式
                data = QTableWidgetItem(str(score))
                tableWidget.setItem(selectRow, 3, data)
                tableWidget.blockSignals(False)  # 退出阻塞模式

    def setKuoHao(self, string):
        if not isinstance(string, str):
            return string
        string = string.replace('(', '（')
        string = string.replace(')', '）')
        return string.strip()

    def isNaNone(self, obj):
        import pandas as pd
        feiFa = ['nan', 'null', '']
        if pd.isnull(obj) or pd.isna(obj) or obj is None or obj in feiFa:
            return True
        else:
            return False

    def read_excel_zc(self, datafile, header=0):
        import pandas as pd
        return pd.read_excel(datafile, header=header)

    def getOpenFile(self):
        # from PyQt5.QtWidgets import QFileDialog
        openfile_name = QFileDialog.getOpenFileName(self, '选择文件', '', 'Excel files(*.xlsx , *.xls)')
        # openfile_name = ('D:/大学/毕业设计/会计报表合并系统/1-函证地址核对/公司表.xlsx', 'Excel files(*.xlsx , *.xls)')
        path = r'{}'.format(openfile_name[0])
        return path

    def getSaveFile(self):
        # from PyQt5.QtWidgets import QFileDialog
        openfile_name = QFileDialog.getSaveFileName(self, '选择文件', '', 'Excel files(*.xlsx , *.xls)')
        # openfile_name = ('D:/大学/毕业设计/会计报表合并系统/1-函证地址核对/公司表.xlsx', 'Excel files(*.xlsx , *.xls)')
        path = r'{}'.format(openfile_name[0])
        return path

    def setSentLetter(self):
        '''
        更新SentTable的内容、更新数据库的内容
        excel：公司名称，手动录入地址，注册地址，匹配得分
        1,3,4不准编辑
        属于一个事务必须同时完成
        :return:
        '''
        self.tabWidget.setCurrentIndex(0)  # 显示当前页面
        # 清除表格
        self.sendTable.setRowCount(0)
        self.sendTable.clearContents()

        path = self.getOpenFile()
        if len(path) == 0:
            return
        dataFrame = self.read_excel_zc(path)
        rows, columns = dataFrame.shape
        if columns != 4:
            return
        self.sendTable.setRowCount(rows)  # 设置行数
        self.sendTable.setColumnCount(columns)  # 设置列数

        self.sendTable.verticalHeader().stretchLastSection()
        # title_list = ['公司名称', '手动录入地址', '注册地址', '匹配得分']
        # self.sendTable.set_horizon_title(title_list)
        """为TableWidget安装事件过滤器(关键)"""
        self.sendTable.install_eventFilter()
        # 数据插入表格
        progress = self.setProgress()
        for i in range(rows):
            temp = []
            for j in range(columns):
                temp.append(dataFrame.iloc[i, j])
                item = temp[j]
                # print(item)
                text = str(item)
                if not self.isNaNone(text):
                    text = self.setKuoHao(text)
                    data = QTableWidgetItem(text)
                    # data.setForeground(QtGui.QColor(0,125,125))
                    # data.setForeground(QtGui.QColor('rad'))  # 设置字体颜色
                    # data.setBackground(QtGui.QColor(125, 125, 125))  # 设置背景
                    self.sendTable.setItem(i, j, data)
            # temp = ['company', 'addressRaw', 'addressCrawl', 'score']
            addressRaw = temp[1]
            addressCrawl = temp[2]
            # 如果原地址和爬取的地址都不为空则更新他们的相似度
            if not self.isNaNone(addressRaw) and not self.isNaNone(addressCrawl):
                score = self.getSimilarity(addressRaw, addressCrawl)
                data = QTableWidgetItem(str(score))
                self.sendTable.setItem(i, 3, data)
            p = int(i / rows * 100)
            # print(p)
            progress.setValue(p)
            if progress.wasCanceled():
                QMessageBox.warning(self, "提示", "操作失败")
                break
        else:
            progress.setValue(100)
            QMessageBox.information(self, "提示", "操作成功")

        # self.sendTable.resizeColumnsToContents()  # 列随内容变化
        # self.sendTable.resizeRowsToContents()  # 行随内容变化
        self.sendTable.setAlternatingRowColors(True)  # 行交错显示
        # vhayout.addWidget(self.table)  # 将表格添加到水平布局中
        # self.setLayout(vhayout)  # 设置当前窗口的布局方式
        self.sendTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 自动伸缩

    def setReturnLetter(self):
        """
        更新ReturnTable的内容、更新数据库的内容
                excel：公司名称，快递单号，回函地址，匹配得分
                1,3,4不准编辑
                属于一个事务必须同时完成
                :return:
        :return:
        """
        self.tabWidget.setCurrentIndex(1)  # 设置当前页面
        # 清除表格
        self.retrunTable.setRowCount(0)
        self.retrunTable.clearContents()

        path = self.getOpenFile()
        if len(path) == 0:
            return
        dataFrame = self.read_excel_zc(path)
        rows, columns = dataFrame.shape
        if columns != 5:
            return
        self.retrunTable.setRowCount(rows)  # 设置行数
        self.retrunTable.setColumnCount(columns)  # 设置列数

        self.retrunTable.verticalHeader().stretchLastSection()

        # self.retrunTable.set_horizon_title(title_list)
        """为TableWidget安装事件过滤器(关键)"""
        self.retrunTable.install_eventFilter()
        # 数据插入表格
        progress = self.setProgress()
        for i in range(rows):
            temp = []
            for j in range(columns):
                temp.append(dataFrame.iloc[i, j])
                item = temp[j]
                text = str(item)
                if not self.isNaNone(text):
                    text = self.setKuoHao(text)
                    # print(text)
                    data = QTableWidgetItem(text)
                    self.retrunTable.setItem(i, j, data)
            # title_list = ['公司名称', '发函地址', '快递单号', '回函地址', '匹配得分']
            # temp = ['company', 'addressRaw', 'oder','addressReturn', 'score']
            addressRaw = temp[1]
            addressReturn = temp[3]
            # 如果原地址和爬取的地址都不为空则更新他们的相似度
            if not self.isNaNone(addressRaw) and not self.isNaNone(addressReturn):
                score = self.getSimilarity(addressRaw, addressReturn)
                data = QTableWidgetItem(str(score))
                self.sendTable.setItem(i, 4, data)
            p = int(i / rows * 100)
            # print(p)
            progress.setValue(p)
            if progress.wasCanceled():
                QMessageBox.warning(self, "提示", "操作失败")
                break
        else:
            progress.setValue(100)
            QMessageBox.information(self, "提示", "操作成功")

        self.retrunTable.resizeColumnsToContents()  # 列随内容变化
        self.retrunTable.resizeRowsToContents()  # 行随内容变化
        self.retrunTable.setAlternatingRowColors(True)  # 行交错显示
        self.retrunTable.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 自动伸缩

    def setProgress(self):
        progress = QProgressDialog(self)
        progress.setWindowTitle("请稍等")
        progress.setLabelText("正在操作...")
        progress.setCancelButtonText("取消")
        progress.setMinimumDuration(5)  # 多长时间打印一次
        progress.setRange(0, 100)
        progress.setWindowModality(Qt.WindowModal)
        return progress

    def showDialog(self, progress, value):
        progress.setValue(value)

    def saveTable(self, tableWidget, header, sheetName='Test'):
        path = self.getSaveFile()
        if len(path) == 0:
            return
        # print(path)
        work_book = xlwt.Workbook()  # 设置当前活动页
        work_sheet = work_book.add_sheet(sheetName)  # 设置当前表格
        rows = tableWidget.rowCount()
        columns = tableWidget.columnCount()
        # header = ['被函证单位', '发函地址', '注册地址查验（企查查）', '返回注册地址查验结果']

        maxWidth = self.writeTemplate(work_sheet, tableWidget, rows, columns, header)
        self.setColSize(work_sheet, maxWidth, columns)
        # print('开始保存')
        work_book.save(path)

    def saveSent(self):
        header = ['被函证单位', '发函地址', '注册地址查验（企查查）', '返回注册地址查验结果']
        self.saveTable(self.sendTable, header)

    def saveReturn(self):
        header = ['被函证单位', '发函地址','回函快递单号', '根据回函单号获取回函地址信息', '核对回函地址与发函地址']
        self.saveTable(self.retrunTable, header)

    def writeTemplate(self, workSheet, tableWidget, rows, columns, header=None):
        """
        用于保存TableWidget中的数据
        :param columns:
        :param rows:
        :param tableWidget:
        :param workSheet: WorkBook
        :param row:
        :param column:
        :param header: 表头列表
        :return:
        """
        # 写表头
        for i, elem in enumerate(header):
            workSheet.write(0, i, str(header[i]))
        if header is None:
            maxWidth = [0 for i in range(columns)]  # 用于记录最大单元格长度
        else:
            maxWidth = [len(elem) for elem in header]
        for currentRow in range(rows):
            for currentColumn in range(columns):
                item = tableWidget.item(currentRow, currentColumn)
                if item is not None:
                    text = item.text()
                    text = self.setKuoHao(text)
                    if not self.isNaNone(text):
                        # print(currentRow, currentColumn, text)
                        if header is None:
                            workSheet.write(currentRow, currentColumn, text)
                        else:
                            workSheet.write(currentRow + 1, currentColumn, text)
                        width = len(text)
                        # print(maxWidth)
                        if width > maxWidth[currentColumn]:
                            maxWidth[currentColumn] = width
                    # 水平冻结
        workSheet.set_panes_frozen('1')
        workSheet.set_horz_split_pos(1)
        return maxWidth

    def setColSize(self, workSheet, maxWidth, columns):
        """
        设置单元格大小
        :param maxWidth: list
        :param columns: int
        :return:
        """
        if len(maxWidth) != columns:
            return
        for i in range(columns):
            col = workSheet.col(i)
            # 256为一个单位，中文占两个字符
            col.width = 256 * maxWidth[i] * 2


if __name__ == '__main__':
    import sys

    QtCore.QCoreApplication.setAttribute(QtCore.Qt.AA_EnableHighDpiScaling)
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()

    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖