PyQt5+Serial+pypyodbc+win32com实现监听扫描枪串口，从ACCESS数据库中获取数据写入Excel自动打印

最新推荐文章于 2024-02-02 10:36:30 发布

置顶爱学习的懒汉

最新推荐文章于 2024-02-02 10:36:30 发布

阅读量2.4k

点赞数 6

本文链接：https://blog.csdn.net/weixin_44124065/article/details/107121257

版权

项目简介
我公司是生产制造公司，每切换一个订单，员工需要手动填写生产记录表，填写内容包括订单号、产品管理编号、产品名称、产品尺寸、产品类别、宽度、厚度、长度、基准重量、订单数量等等大量信息。人工书写易出错，浪费时间。事实上，设想，我公司产品数据都保存在ACCESS数据库中，只要通过扫描枪获取产品管理编号便可从数据库中获取其余信息，之后将所有信息写入Excel自动打印即可。
关键代码如下
①使用PyQt5创建操作界面（为的是选取日期及核对信息用，其实没有界面也可以。Pyqt不是本次介绍重点，此处代码过于繁琐，省略。）界面如下：

②使用Serial获取扫描枪数据
扫描枪淘宝上60几块的带USB串口功能的就可以。参照扫描枪说明书将其设置为USB串口模式。
在电脑设备管理器中确认扫描枪的端口号，我的是COM3。端口号也可以在设备管理器中更改。

		# 此处需要导入的模块
		import serial
		import sound,winsound
		
		try:
        	# 创建Serial对象，参数为(端口号，波特率，超时时间) 为防止出错此处使用try except
            ser = serial.Serial("com3", 9600, timeout=0.5)
        except Exception as e:
            # 在D盘创建了一个错误日志，如果连接串口失败，则写入此日志。
            f = open('d:/err_info.txt', 'a')
            f.writelines(str(datetime.datetime.now()))
            f.writelines(repr(e))
            f.writelines('\r\n')
            f.close()
            ser = ''
            
            '''此处是我在本地存了一个语音错误播报音频，当出现错误时调用此音频文件,
            有需要的朋友可以到https://app.xunjiepdf.com/text2voice/进行在线转换。'''
            com3_not_found_sound_path = sound_path + 'COM3_not_found.wav'
            sound.play(com3_not_found_sound_path, winsound.SND_ALIAS)

		# 从串口对象中获取数据
        def receive(ser):
            data = ''
            try:
                while ser.inWaiting() > 0:
                	# 因为串口获取的数据类型为byte此处需要decode转换
                    data += ser.read().decode()
                return data
            except Exception as e:
                return data
                # 我设置的条形码格式为 订单号|产品编号|数量 （例：123456|CMF120-19|100）
                # 注意：条形码扫描枪仅支持英文，不支持中文。

③从ACCESS数据库中获取数据存入EXCEL
因为access数据库放在公司服务器中，如果没网的话就无法正常使用，所以我的做法是将所有数据存入到EXCEL中，仅在每次打开pyqt窗体时执行数据更新。这样平时可以使用EXCEL中的数据进行查询

此处需要导入的模块

import pypyodbc
import pandas as pd
import openpyxl
import datetime
import sound
import winsound
import time
import win32com.client

创建方法，用来获取access的数据。（因为我需要的数据在两个表中，要调用两次，所以写此方法方便调用）

# 获取access的数据。
def get_table(product_info_path, sql, names,PWD, sound_path=''):
    try:
        file_path = product_info_path
        # conn = pypyodbc.win_connect_mdb(u'Driver={Microsoft Access Driver (*.mdb, *.accdb)};PWD=8110;DBQ=' + file_path)
        conn_str = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};PWD=%s;DBQ=%s'%(PWD,file_path)
        conn = pypyodbc.connect(conn_str)
        cursor = conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        df = pd.DataFrame(data, columns=names)
        return df
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')

        print(e.args)
        return ""

将两个表的数据连接起来

def link_table(parent_access_path, product_info_path,PWD, sound_path=''):
    try:
        # 获取产品信息表
        parent_access_path = parent_access_path
        sql = "select 管理编号,名称,尺寸 from 产品列表"
        names = ['管理编号', '名称', '尺寸']
        df1 = get_table(parent_access_path, sql, names,PWD)

        # 获取式样书
        product_info_path = product_info_path
        sql = "select 管理编号,[50cm重量],[50cm重量下限],[50cm重量上限],[製品厚み],厚度许可范围下限,厚度许可范围上限,原反幅,原反幅基準下限," \
              "原反幅基準上限,インフレ幅,製品長さ,原反基准重量,原反基准重量上限,原反基准重量下限 from 现有防锈龙 union select 管理编号,'0'," \
              "'0','0',[製品厚み],厚度许可范围下限,厚度许可范围上限,原反幅,原反幅基準下限,原反幅基準上限,インフレ幅,製品長さ,原反基准重量," \
              "原反基准重量上限,原反基准重量下限 from 现有一般PE"
        names = ['管理编号', '50cm重量', '50cm重量下限', '50cm重量上限', '製品厚み', '厚度许可范围下限', '厚度许可范围上限', '原反幅', '原反幅基準下限', '原反幅基準上限',
                 'インフレ幅', '製品長さ', '原反基准重量', '原反基准重量上限', '原反基准重量下限']
        # 数字类型需转换为文本后才可以连接
        df2 = get_table(product_info_path, sql, names,PWD)
        # df2['50cm重量上限'] = df2['50cm重量上限'].apply(str)

        # 连接表
        rs = pd.merge(df1, df2, how='left', on=['管理编号', '管理编号'])
        # 字段为空无法使用contains函数
        # rs['类别'] = rs['类别'].apply(str)
        # rs = rs.loc[rs['类别'].str.contains('卷材', regex=True)]
        # print(rs)
        return rs
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')
        print(e.args)
        return ''

将数据保存到需要打印的Excel中的data表中

# 从access更新数据库写入excel
def from_access_update_into_excel_xlsx(parent_access_path, product_info_path, database_path, PWD,sound_path):
    try:
        time = datetime.datetime.now().replace(microsecond=0)
        rs = link_table(parent_access_path, product_info_path,PWD)

        rs.to_excel(database_path, sheet_name='data')
        sound_path += 'database_update_successfully.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        return "数据库更新成功！在: %s" % (time)
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')

        sound_path += 'database_update_failed.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        print(sound_path)
        return "数据库更新失败！在: %s" % (time)

③win32api实现自动打印

# 写入扫描信息到Excel
def scan_val_to_excel(print_file_path, jilubiao_sheet_name, jilubiao_area, rukudan_sheet_name, rukudan_area,
                      lis_jilubiao, lis_rukudan, sound_path, print_mode):
    message = ''
    print('开始传输到excel并打印')
    print('mode:'+print_mode)
    try:
        xlApp = win32com.client.Dispatch('Excel.Application')  # 打开EXCEL，这里不需改动
        xlBook = xlApp.Workbooks.Open(print_file_path)  # 将dir改为要处理的excel文件路径
        xlSht1 = xlBook.Worksheets(jilubiao_sheet_name)  # 要处理的excel页
        xlSht2 = xlBook.Worksheets(rukudan_sheet_name)  # 要处理的excel页
        for item in lis_rukudan:
            xlSht2.Cells(item[0][0], item[0][1]).value = ''
            xlSht2.Cells(item[0][0], item[0][1]).value = item[1]

        for item in lis_jilubiao:
            xlSht1.Cells(item[0][0], item[0][1]).value = ''
            xlSht1.Cells(item[0][0], item[0][1]).value = item[1]
        if print_mode == '1':
            xlSht1.PrintOut()
            xlSht2.PrintOut()
        elif print_mode == '2':
            xlSht1.PrintOut()
        else:
            xlSht2.PrintOut()
        xlBook.Close(SaveChanges=0)  # 完成 关闭保存文件
        now = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
        message = '成功写入Excel。已传送至打印机。在：%s'%now
        sound_path += 'printing.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        return message
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')
        print(e.args)
        message = '打印传输未知错误。'
        sound_path += 'printing_failed.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        if xlBook:
            xlBook.Close(SaveChanges=0)
        return message
    finally:
        print('打印传输完毕')

代码有点多，完整代码如下：

AutoPrintSystem.py模块代码：

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

# Form implementation generated from reading ui file 'AutoPrintSystem.ui'
#
# Created by: PyQt5 UI code generator 5.13.2
#
# WARNING! All changes made in this file will be lost!


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.setEnabled(True)
        MainWindow.resize(518, 563)
        MainWindow.setMinimumSize(QtCore.QSize(518, 563))
        MainWindow.setMaximumSize(QtCore.QSize(518, 563))
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap("../../favicon.ico"), QtGui.QIcon.Normal, QtGui.QIcon.Off)
        MainWindow.setWindowIcon(icon)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.frame = QtWidgets.QFrame(self.centralwidget)
        self.frame.setGeometry(QtCore.QRect(100, 110, 391, 351))
        self.frame.setFrameShape(QtWidgets.QFrame.Box)
        self.frame.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.frame.setMidLineWidth(0)
        self.frame.setObjectName("frame")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.frame)
        self.verticalLayout.setObjectName("verticalLayout")
        self.horizontalLayout_23 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_23.setObjectName("horizontalLayout_23")
        self.label_7 = QtWidgets.QLabel(self.frame)
        self.label_7.setObjectName("label_7")
        self.horizontalLayout_23.addWidget(self.label_7)
        self.lineEdit_7 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_7.setMinimumSize(QtCore.QSize(150, 0))
        self.lineEdit_7.setReadOnly(False)
        self.lineEdit_7.setObjectName("lineEdit_7")
        self.horizontalLayout_23.addWidget(self.lineEdit_7)
        self.label_43 = QtWidgets.QLabel(self.frame)
        self.label_43.setMinimumSize(QtCore.QSize(53, 0))
        self.label_43.setMaximumSize(QtCore.QSize(100, 16777215))
        self.label_43.setObjectName("label_43")
        self.horizontalLayout_23.addWidget(self.label_43)
        self.lineEdit_40 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_40.setMinimumSize(QtCore.QSize(0, 0))
        self.lineEdit_40.setMaximumSize(QtCore.QSize(1000, 16777215))
        self.lineEdit_40.setReadOnly(False)
        self.lineEdit_40.setObjectName("lineEdit_40")
        self.horizontalLayout_23.addWidget(self.lineEdit_40)
        self.verticalLayout.addLayout(self.horizontalLayout_23)
        self.horizontalLayout_6 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_6.setObjectName("horizontalLayout_6")
        self.label_2 = QtWidgets.QLabel(self.frame)
        self.label_2.setObjectName("label_2")
        self.horizontalLayout_6.addWidget(self.label_2)
        self.lineEdit_2 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_2.setMinimumSize(QtCore.QSize(140, 0))
        self.lineEdit_2.setMaximumSize(QtCore.QSize(1000, 16777215))
        self.lineEdit_2.setReadOnly(False)
        self.lineEdit_2.setObjectName("lineEdit_2")
        self.horizontalLayout_6.addWidget(self.lineEdit_2)
        self.verticalLayout.addLayout(self.horizontalLayout_6)
        self.horizontalLayout_7 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_7.setObjectName("horizontalLayout_7")
        self.label_5 = QtWidgets.QLabel(self.frame)
        self.label_5.setObjectName("label_5")
        self.horizontalLayout_7.addWidget(self.label_5)
        self.lineEdit_5 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_5.setMinimumSize(QtCore.QSize(100, 0))
        self.lineEdit_5.setReadOnly(False)
        self.lineEdit_5.setObjectName("lineEdit_5")
        self.horizontalLayout_7.addWidget(self.lineEdit_5)
        self.verticalLayout.addLayout(self.horizontalLayout_7)
        self.horizontalLayout_8 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_8.setObjectName("horizontalLayout_8")
        self.label_6 = QtWidgets.QLabel(self.frame)
        self.label_6.setObjectName("label_6")
        self.horizontalLayout_8.addWidget(self.label_6)
        self.lineEdit_6 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_6.setMinimumSize(QtCore.QSize(100, 0))
        self.lineEdit_6.setText("")
        self.lineEdit_6.setReadOnly(False)
        self.lineEdit_6.setObjectName("lineEdit_6")
        self.horizontalLayout_8.addWidget(self.lineEdit_6)
        self.verticalLayout.addLayout(self.horizontalLayout_8)
        self.line = QtWidgets.QFrame(self.frame)
        self.line.setFrameShape(QtWidgets.QFrame.HLine)
        self.line.setFrameShadow(QtWidgets.QFrame.Sunken)
        self.line.setObjectName("line")
        self.verticalLayout.addWidget(self.line)
        self.horizontalLayout_4 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_4.setObjectName("horizontalLayout_4")
        self.label_9 = QtWidgets.QLabel(self.frame)
        self.label_9.setObjectName("label_9")
        self.horizontalLayout_4.addWidget(self.label_9)
        self.lineEdit_3 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.horizontalLayout_4.addWidget(self.lineEdit_3)
        self.label_11 = QtWidgets.QLabel(self.frame)
        self.label_11.setObjectName("label_11")
        self.horizontalLayout_4.addWidget(self.label_11)
        self.lineEdit_8 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_8.setObjectName("lineEdit_8")
        self.horizontalLayout_4.addWidget(self.lineEdit_8)
        self.label_10 = QtWidgets.QLabel(self.frame)
        self.label_10.setObjectName("label_10")
        self.horizontalLayout_4.addWidget(self.label_10)
        self.lineEdit_4 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_4.setObjectName("lineEdit_4")
        self.horizontalLayout_4.addWidget(self.lineEdit_4)
        self.verticalLayout.addLayout(self.horizontalLayout_4)
        self.horizontalLayout_24 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_24.setObjectName("horizontalLayout_24")
        self.label_44 = QtWidgets.QLabel(self.frame)
        self.label_44.setObjectName("label_44")
        self.horizontalLayout_24.addWidget(self.label_44)
        self.lineEdit_41 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_41.setObjectName("lineEdit_41")
        self.horizontalLayout_24.addWidget(self.lineEdit_41)
        self.label_46 = QtWidgets.QLabel(self.frame)
        self.label_46.setObjectName("label_46")
        self.horizontalLayout_24.addWidget(self.label_46)
        self.lineEdit_43 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_43.setObjectName("lineEdit_43")
        self.horizontalLayout_24.addWidget(self.lineEdit_43)
        self.label_45 = QtWidgets.QLabel(self.frame)
        self.label_45.setObjectName("label_45")
        self.horizontalLayout_24.addWidget(self.label_45)
        self.lineEdit_42 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_42.setObjectName("lineEdit_42")
        self.horizontalLayout_24.addWidget(self.lineEdit_42)
        self.verticalLayout.addLayout(self.horizontalLayout_24)
        self.horizontalLayout_5 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_5.setObjectName("horizontalLayout_5")
        self.label_12 = QtWidgets.QLabel(self.frame)
        self.label_12.setObjectName("label_12")
        self.horizontalLayout_5.addWidget(self.label_12)
        self.lineEdit_9 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_9.setObjectName("lineEdit_9")
        self.horizontalLayout_5.addWidget(self.lineEdit_9)
        self.label_14 = QtWidgets.QLabel(self.frame)
        self.label_14.setObjectName("label_14")
        self.horizontalLayout_5.addWidget(self.label_14)
        self.lineEdit_11 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_11.setObjectName("lineEdit_11")
        self.horizontalLayout_5.addWidget(self.lineEdit_11)
        self.label_13 = QtWidgets.QLabel(self.frame)
        self.label_13.setObjectName("label_13")
        self.horizontalLayout_5.addWidget(self.label_13)
        self.lineEdit_10 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_10.setObjectName("lineEdit_10")
        self.horizontalLayout_5.addWidget(self.lineEdit_10)
        self.verticalLayout.addLayout(self.horizontalLayout_5)
        self.horizontalLayout_11 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_11.setObjectName("horizontalLayout_11")
        self.label_18 = QtWidgets.QLabel(self.frame)
        self.label_18.setObjectName("label_18")
        self.horizontalLayout_11.addWidget(self.label_18)
        self.lineEdit_15 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_15.setObjectName("lineEdit_15")
        self.horizontalLayout_11.addWidget(self.lineEdit_15)
        self.label_20 = QtWidgets.QLabel(self.frame)
        self.label_20.setObjectName("label_20")
        self.horizontalLayout_11.addWidget(self.label_20)
        self.lineEdit_17 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_17.setObjectName("lineEdit_17")
        self.horizontalLayout_11.addWidget(self.lineEdit_17)
        self.label_19 = QtWidgets.QLabel(self.frame)
        self.label_19.setObjectName("label_19")
        self.horizontalLayout_11.addWidget(self.label_19)
        self.lineEdit_16 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_16.setObjectName("lineEdit_16")
        self.horizontalLayout_11.addWidget(self.lineEdit_16)
        self.verticalLayout.addLayout(self.horizontalLayout_11)
        self.horizontalLayout_12 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_12.setObjectName("horizontalLayout_12")
        self.label_21 = QtWidgets.QLabel(self.frame)
        self.label_21.setObjectName("label_21")
        self.horizontalLayout_12.addWidget(self.label_21)
        self.lineEdit_18 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_18.setObjectName("lineEdit_18")
        self.horizontalLayout_12.addWidget(self.lineEdit_18)
        self.label_23 = QtWidgets.QLabel(self.frame)
        self.label_23.setObjectName("label_23")
        self.horizontalLayout_12.addWidget(self.label_23)
        self.lineEdit_20 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_20.setObjectName("lineEdit_20")
        self.horizontalLayout_12.addWidget(self.lineEdit_20)
        self.label_22 = QtWidgets.QLabel(self.frame)
        self.label_22.setObjectName("label_22")
        self.horizontalLayout_12.addWidget(self.label_22)
        self.lineEdit_19 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_19.setObjectName("lineEdit_19")
        self.horizontalLayout_12.addWidget(self.lineEdit_19)
        self.verticalLayout.addLayout(self.horizontalLayout_12)
        self.horizontalLayout_10 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_10.setObjectName("horizontalLayout_10")
        self.label_15 = QtWidgets.QLabel(self.frame)
        self.label_15.setObjectName("label_15")
        self.horizontalLayout_10.addWidget(self.label_15)
        self.lineEdit_12 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_12.setObjectName("lineEdit_12")
        self.horizontalLayout_10.addWidget(self.lineEdit_12)
        self.label_17 = QtWidgets.QLabel(self.frame)
        self.label_17.setObjectName("label_17")
        self.horizontalLayout_10.addWidget(self.label_17)
        self.lineEdit_14 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_14.setObjectName("lineEdit_14")
        self.horizontalLayout_10.addWidget(self.lineEdit_14)
        self.label_16 = QtWidgets.QLabel(self.frame)
        self.label_16.setObjectName("label_16")
        self.horizontalLayout_10.addWidget(self.label_16)
        self.lineEdit_13 = QtWidgets.QLineEdit(self.frame)
        self.lineEdit_13.setObjectName("lineEdit_13")
        self.horizontalLayout_10.addWidget(self.lineEdit_13)
        self.verticalLayout.addLayout(self.horizontalLayout_10)
        self.layoutWidget = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget.setGeometry(QtCore.QRect(150, 460, 341, 41))
        self.layoutWidget.setObjectName("layoutWidget")
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout(self.layoutWidget)
        self.horizontalLayout_2.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.radioButton = QtWidgets.QRadioButton(self.layoutWidget)
        self.radioButton.setObjectName("radioButton")
        self.horizontalLayout_2.addWidget(self.radioButton)
        spacerItem = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem)
        self.radioButton_2 = QtWidgets.QRadioButton(self.layoutWidget)
        self.radioButton_2.setObjectName("radioButton_2")
        self.horizontalLayout_2.addWidget(self.radioButton_2)
        spacerItem1 = QtWidgets.QSpacerItem(40, 20, QtWidgets.QSizePolicy.Expanding, QtWidgets.QSizePolicy.Minimum)
        self.horizontalLayout_2.addItem(spacerItem1)
        self.radioButton_3 = QtWidgets.QRadioButton(self.layoutWidget)
        self.radioButton_3.setObjectName("radioButton_3")
        self.horizontalLayout_2.addWidget(self.radioButton_3)
        self.layoutWidget1 = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget1.setGeometry(QtCore.QRect(30, 0, 461, 51))
        self.layoutWidget1.setObjectName("layoutWidget1")
        self.horizontalLayout = QtWidgets.QHBoxLayout(self.layoutWidget1)
        self.horizontalLayout.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.label = QtWidgets.QLabel(self.layoutWidget1)
        self.label.setMouseTracking(True)
        self.label.setObjectName("label")
        self.horizontalLayout.addWidget(self.label)
        self.lineEdit = QtWidgets.QLineEdit(self.layoutWidget1)
        self.lineEdit.setEnabled(True)
        font = QtGui.QFont()
        font.setFamily("Calibri")
        font.setPointSize(7)
        font.setItalic(True)
        self.lineEdit.setFont(font)
        self.lineEdit.setText("")
        self.lineEdit.setReadOnly(True)
        self.lineEdit.setObjectName("lineEdit")
        self.horizontalLayout.addWidget(self.lineEdit)
        self.listWidget = QtWidgets.QListWidget(self.centralwidget)
        self.listWidget.setGeometry(QtCore.QRect(30, 110, 61, 351))
        self.listWidget.setObjectName("listWidget")
        self.line_2 = QtWidgets.QFrame(self.centralwidget)
        self.line_2.setGeometry(QtCore.QRect(0, 500, 531, 16))
        self.line_2.setFrameShadow(QtWidgets.QFrame.Raised)
        self.line_2.setLineWidth(1)
        self.line_2.setMidLineWidth(0)
        self.line_2.setFrameShape(QtWidgets.QFrame.HLine)
        self.line_2.setObjectName("line_2")
        self.layoutWidget_2 = QtWidgets.QWidget(self.centralwidget)
        self.layoutWidget_2.setGeometry(QtCore.QRect(390, 60, 101, 39))
        self.layoutWidget_2.setObjectName("layoutWidget_2")
        self.horizontalLayout_14 = QtWidgets.QHBoxLayout(self.layoutWidget_2)
        self.horizontalLayout_14.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_14.setObjectName("horizontalLayout_14")
        self.radioButton_6 = QtWidgets.QRadioButton(self.layoutWidget_2)
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.radioButton_6.setFont(font)
        self.radioButton_6.setObjectName("radioButton_6")
        self.horizontalLayout_14.addWidget(self.radioButton_6)
        self.radioButton_7 = QtWidgets.QRadioButton(self.layoutWidget_2)
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.radioButton_7.setFont(font)
        self.radioButton_7.setObjectName("radioButton_7")
        self.horizontalLayout_14.addWidget(self.radioButton_7)
        self.widget = QtWidgets.QWidget(self.centralwidget)
        self.widget.setGeometry(QtCore.QRect(230, 60, 101, 39))
        self.widget.setObjectName("widget")
        self.horizontalLayout_3 = QtWidgets.QHBoxLayout(self.widget)
        self.horizontalLayout_3.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_3.setObjectName("horizontalLayout_3")
        self.radioButton_4 = QtWidgets.QRadioButton(self.widget)
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.radioButton_4.setFont(font)
        self.radioButton_4.setObjectName("radioButton_4")
        self.horizontalLayout_3.addWidget(self.radioButton_4)
        self.radioButton_5 = QtWidgets.QRadioButton(self.widget)
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.radioButton_5.setFont(font)
        self.radioButton_5.setObjectName("radioButton_5")
        self.horizontalLayout_3.addWidget(self.radioButton_5)
        self.widget1 = QtWidgets.QWidget(self.centralwidget)
        self.widget1.setGeometry(QtCore.QRect(30, 60, 211, 39))
        self.widget1.setObjectName("widget1")
        self.horizontalLayout_13 = QtWidgets.QHBoxLayout(self.widget1)
        self.horizontalLayout_13.setContentsMargins(0, 0, 0, 0)
        self.horizontalLayout_13.setObjectName("horizontalLayout_13")
        self.label_8 = QtWidgets.QLabel(self.widget1)
        self.label_8.setMinimumSize(QtCore.QSize(0, 0))
        self.label_8.setMaximumSize(QtCore.QSize(40, 16777215))
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.label_8.setFont(font)
        self.label_8.setObjectName("label_8")
        self.horizontalLayout_13.addWidget(self.label_8)
        self.dateEdit = QtWidgets.QDateEdit(self.widget1)
        self.dateEdit.setMinimumSize(QtCore.QSize(100, 0))
        self.dateEdit.setMaximumSize(QtCore.QSize(500, 16777215))
        font = QtGui.QFont()
        font.setFamily("华文楷体")
        font.setPointSize(10)
        self.dateEdit.setFont(font)
        self.dateEdit.setLayoutDirection(QtCore.Qt.LeftToRight)
        self.dateEdit.setProperty("showGroupSeparator", False)
        self.dateEdit.setCalendarPopup(True)
        self.dateEdit.setDate(QtCore.QDate(1992, 1, 1))
        self.dateEdit.setObjectName("dateEdit")
        self.horizontalLayout_13.addWidget(self.dateEdit)
        self.frame_2 = QtWidgets.QFrame(self.widget1)
        self.frame_2.setFrameShape(QtWidgets.QFrame.StyledPanel)
        self.frame_2.setFrameShadow(QtWidgets.QFrame.Raised)
        self.frame_2.setObjectName("frame_2")
        self.horizontalLayout_13.addWidget(self.frame_2)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menuBar = QtWidgets.QMenuBar(MainWindow)
        self.menuBar.setGeometry(QtCore.QRect(0, 0, 518, 29))
        font = QtGui.QFont()
        font.setFamily("Segoe Print")
        font.setPointSize(8)
        self.menuBar.setFont(font)
        self.menuBar.setContextMenuPolicy(QtCore.Qt.NoContextMenu)
        self.menuBar.setToolTip("")
        self.menuBar.setObjectName("menuBar")
        self.menu = QtWidgets.QMenu(self.menuBar)
        font = QtGui.QFont()
        font.setFamily("Segoe Print")
        font.setPointSize(8)
        self.menu.setFont(font)
        self.menu.setObjectName("menu")
        MainWindow.setMenuBar(self.menuBar)
        self.statusBar = QtWidgets.QStatusBar(MainWindow)
        font = QtGui.QFont()
        font.setFamily("仿宋")
        self.statusBar.setFont(font)
        self.statusBar.setObjectName("statusBar")
        MainWindow.setStatusBar(self.statusBar)
        self.actionUpdate_database = QtWidgets.QAction(MainWindow)
        self.actionUpdate_database.setObjectName("actionUpdate_database")
        self.menu.addAction(self.actionUpdate_database)
        self.menuBar.addAction(self.menu.menuAction())

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)
        MainWindow.setTabOrder(self.lineEdit_2, self.lineEdit_5)
        MainWindow.setTabOrder(self.lineEdit_5, self.lineEdit_6)
        MainWindow.setTabOrder(self.lineEdit_6, self.radioButton)
        MainWindow.setTabOrder(self.radioButton, self.radioButton_2)
        MainWindow.setTabOrder(self.radioButton_2, self.radioButton_3)
        MainWindow.setTabOrder(self.radioButton_3, self.lineEdit)

    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "记录表打印工具_v1.0_202005"))
        self.label_7.setText(_translate("MainWindow", "订单号  "))
        self.label_43.setText(_translate("MainWindow", " 指示数"))
        self.label_2.setText(_translate("MainWindow", "管理编号"))
        self.label_5.setText(_translate("MainWindow", "产品名称"))
        self.label_6.setText(_translate("MainWindow", "产品尺寸"))
        self.label_9.setText(_translate("MainWindow", "基重"))
        self.label_11.setText(_translate("MainWindow", "下限"))
        self.label_10.setText(_translate("MainWindow", "上限"))
        self.label_44.setText(_translate("MainWindow", "PE厚"))
        self.label_46.setText(_translate("MainWindow", "下限"))
        self.label_45.setText(_translate("MainWindow", "上限"))
        self.label_12.setText(_translate("MainWindow", "宽度"))
        self.label_14.setText(_translate("MainWindow", "下限"))
        self.label_13.setText(_translate("MainWindow", "上限"))
        self.label_18.setText(_translate("MainWindow", "筒款"))
        self.label_20.setText(_translate("MainWindow", "下限"))
        self.label_19.setText(_translate("MainWindow", "上限"))
        self.label_21.setText(_translate("MainWindow", "卷长"))
        self.label_23.setText(_translate("MainWindow", "下限"))
        self.label_22.setText(_translate("MainWindow", "上限"))
        self.label_15.setText(_translate("MainWindow", "卷重"))
        self.label_17.setText(_translate("MainWindow", "下限"))
        self.label_16.setText(_translate("MainWindow", "上限"))
        self.radioButton.setText(_translate("MainWindow", "记录表和入库单"))
        self.radioButton_2.setText(_translate("MainWindow", "仅记录表"))
        self.radioButton_3.setText(_translate("MainWindow", "仅入库单"))
        self.label.setText(_translate("MainWindow", "扫描代码："))
        self.lineEdit.setPlaceholderText(_translate("MainWindow", "请使用扫码器扫描"))
        self.radioButton_6.setText(_translate("MainWindow", "甲"))
        self.radioButton_7.setText(_translate("MainWindow", "乙"))
        self.radioButton_4.setText(_translate("MainWindow", "早"))
        self.radioButton_5.setText(_translate("MainWindow", "夜"))
        self.label_8.setText(_translate("MainWindow", "日期:"))
        self.menu.setTitle(_translate("MainWindow", "Setting"))
        self.actionUpdate_database.setText(_translate("MainWindow", "Update database"))

data_handle.py模块代码

import pypyodbc
import pandas as pd
import re
import tkinter
from tkinter import messagebox
import openpyxl
import pywin32_system32
import win32api
import win32print
import datetime
import sound
import winsound
import time
import gc
import win32com.client


# 连接access获取数据
def get_table(product_info_path, sql, names,PWD, sound_path=''):
    try:
        file_path = product_info_path
        # conn = pypyodbc.win_connect_mdb(u'Driver={Microsoft Access Driver (*.mdb, *.accdb)};PWD=8110;DBQ=' + file_path)
        conn_str = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};PWD=%s;DBQ=%s'%(PWD,file_path)
        conn = pypyodbc.connect(conn_str)
        cursor = conn.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        df = pd.DataFrame(data, columns=names)
        return df
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')

        print(e.args)
        return ""


def link_table(parent_access_path, product_info_path,PWD, sound_path=''):
    try:
        # 获取产品信息表
        parent_access_path = parent_access_path
        sql = "select 管理编号,名称,尺寸 from 产品列表"
        names = ['管理编号', '名称', '尺寸']
        df1 = get_table(parent_access_path, sql, names,PWD)

        # 获取式样书
        product_info_path = product_info_path
        sql = "select 管理编号,[50cm重量],[50cm重量下限],[50cm重量上限],[製品厚み],厚度许可范围下限,厚度许可范围上限,原反幅,原反幅基準下限," \
              "原反幅基準上限,インフレ幅,製品長さ,原反基准重量,原反基准重量上限,原反基准重量下限 from 现有防锈龙 union select 管理编号,'0'," \
              "'0','0',[製品厚み],厚度许可范围下限,厚度许可范围上限,原反幅,原反幅基準下限,原反幅基準上限,インフレ幅,製品長さ,原反基准重量," \
              "原反基准重量上限,原反基准重量下限 from 现有一般PE"
        names = ['管理编号', '50cm重量', '50cm重量下限', '50cm重量上限', '製品厚み', '厚度许可范围下限', '厚度许可范围上限', '原反幅', '原反幅基準下限', '原反幅基準上限',
                 'インフレ幅', '製品長さ', '原反基准重量', '原反基准重量上限', '原反基准重量下限']
        # 数字类型需转换为文本后才可以连接
        df2 = get_table(product_info_path, sql, names,PWD)
        # df2['50cm重量上限'] = df2['50cm重量上限'].apply(str)

        # 连接表
        rs = pd.merge(df1, df2, how='left', on=['管理编号', '管理编号'])
        # 字段为空无法使用contains函数
        # rs['类别'] = rs['类别'].apply(str)
        # rs = rs.loc[rs['类别'].str.contains('卷材', regex=True)]
        # print(rs)
        return rs
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')

        print(e.args)
        return ''


# link_table("C:/Users/john/Desktop/axl/AXL产品数据库-后端.accdb","C:/Users/john/Desktop/提示音/failed.wav")


# 从access更新数据库写入excel
def from_access_update_into_excel_xlsx(parent_access_path, product_info_path, database_path, PWD,sound_path):
    try:
        time = datetime.datetime.now().replace(microsecond=0)
        rs = link_table(parent_access_path, product_info_path,PWD)

        rs.to_excel(database_path, sheet_name='data')
        sound_path += 'database_update_successfully.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        return "数据库更新成功！在: %s" % (time)
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')

        sound_path += 'database_update_failed.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        print(sound_path)
        return "数据库更新失败！在: %s" % (time)
    #

# 写入扫描信息到Excel
def scan_val_to_excel(print_file_path, jilubiao_sheet_name, jilubiao_area, rukudan_sheet_name, rukudan_area,
                      lis_jilubiao, lis_rukudan, sound_path, print_mode):
    # messagebox.showinfo('提示', '成功')
    # win32api.ShellExecute(0, 'open', 'http://www.baidu.com', '1','',1)
    # win32api.ShellExecute(0, 'open', 'notepad.exe', '','',0)
    message = ''
    print('开始传输到excel并打印')
    print('mode:'+print_mode)
    try:
        xlApp = win32com.client.Dispatch('Excel.Application')  # 打开EXCEL，这里不需改动
        xlBook = xlApp.Workbooks.Open(print_file_path)  # 将dir改为要处理的excel文件路径
        xlSht1 = xlBook.Worksheets(jilubiao_sheet_name)  # 要处理的excel页
        xlSht2 = xlBook.Worksheets(rukudan_sheet_name)  # 要处理的excel页
        # wb = openpyxl.load_workbook(print_file_path)
        # jilubiao_sheet = wb[jilubiao_sheet_name]
        # rukudan_sheet = wb[rukudan_sheet_name]
        for item in lis_rukudan:
            xlSht2.Cells(item[0][0], item[0][1]).value = ''
            xlSht2.Cells(item[0][0], item[0][1]).value = item[1]

        for item in lis_jilubiao:
            xlSht1.Cells(item[0][0], item[0][1]).value = ''
            xlSht1.Cells(item[0][0], item[0][1]).value = item[1]

            # jilubiao_sheet.cell(item[0][0], item[0][1], '')
            # jilubiao_sheet.cell(item[0][0], item[0][1], item[1])
        # wb.save(print_file_path)
        # jilubiao_sheet.print_area = jilubiao_area
        # jilubiao_sheet.print_options.horizontalCentered = True  # 水平居中
        # jilubiao_sheet.print_options.verticalCentered = True  # 垂直居中
        # rukudan_sheet.print_area = rukudan_area
        # rukudan_sheet.print_options.horizontalCentered = True  # 水平居中
        # rukudan_sheet.print_options.verticalCentered = True  # 垂直居中
        if print_mode == '1':
            xlSht1.PrintOut()
            xlSht2.PrintOut()
            # wb.active =jilubiao_sheet
            # wb.save(print_file_path)
            # win32api.ShellExecute(0, "print", print_file_path, win32print.GetDefaultPrinter(), "", 0)
            # wb.active =rukudan_sheet
            # wb.save(print_file_path)
            # win32api.ShellExecute(0, "print", print_file_path, win32print.GetDefaultPrinter(), "", 0)
        elif print_mode == '2':
            xlSht1.PrintOut()
            # wb.active =0
            # wb.save(print_file_path)
            # win32api.ShellExecute(0, "print", print_file_path, win32print.GetDefaultPrinter(), "", 0)
        else:
            xlSht2.PrintOut()
            # wb.active =rukudan_sheet
            # wb.save(print_file_path)
            # win32api.ShellExecute(0, "print", print_file_path, win32print.GetDefaultPrinter(), "", 0)
        # ws.print_area = 'B2:AT36'  # 设置打印区域
        # ws.print_options.horizontalCentered = True  # 水平居中
        # ws.print_options.verticalCentered = True  # 垂直居中
        xlBook.Close(SaveChanges=0)  # 完成 关闭保存文件
        now = time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time()))
        message = '成功写入Excel。已传送至打印机。在：%s'%now
        sound_path += 'printing.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        return message
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')
        print(e.args)
        message = '打印传输未知错误。'
        sound_path += 'printing_failed.wav'
        sound.play(sound_path, winsound.SND_ALIAS)
        if xlBook:
            xlBook.Close(SaveChanges=0)
        return message
    finally:
        print('打印传输完毕')


# ws = workbook.active
# ws.print_area = 'A1:F10'  # 设置打印区域
# ws.print_options.horizontalCentered = True  # 水平居中
# ws.print_options.verticalCentered = True  # 垂直居中
# win32api.ShellExecute(0, "print", path, win32print.GetDefaultPrinter(), "", 0)


def get_data_from_excel(database_path, sheet_name):
    try:
        workbook = openpyxl.load_workbook(database_path)
        sheet = workbook[sheet_name]
        # max_row = sheet.max_row
        # area = 'A1:E' + str(max_row)
        # data = sheet[area]
        data = []

        for row in sheet.values:
            data.append(row)
        return data
    except Exception as e:
        f = open('d:/err_info.txt', 'a')
        f.writelines(str(datetime.datetime.now()))
        f.writelines(repr(e))
        f.writelines('\r\n')
        return ''

Auto_Print.py模块代码

from PyQt5.QtWidgets import QApplication, QMainWindow, QSystemTrayIcon, QMenu, QAction, qApp, QListWidget
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtGui import QPalette, QBrush, QPixmap
from PyQt5.QtCore import QSettings
from PyQt5.QtCore import QDate, Qt
from AutoPrintSystem import Ui_MainWindow
from pynput.keyboard import Key, Controller, Listener
import winsound, json, serial, re, time, icon_rc, os, threading, datetime, sys
import sound, data_handle, AutoPrintSystem

print_mode = '1'
banci = '0'
jihao = '0'
zubie = '0'

def settings():
    # 创建json配置文件
    settings_file_path = 'd:/settings.json'
    if os.path.exists(settings_file_path):
        settings_file = open(settings_file_path, 'r')
        mysettings = json.loads(settings_file.read())
        return mysettings
    else:
        mysettings = {
            "parent_access_path": "C:/Users/john/Desktop/Auto_Print/axl/AXL产品数据库-后端.accdb",
            "database_path": "C:/Users/john/Desktop/Auto_Print/database.xlsx",
            "product_info_path": "C:/Users/john/Desktop/Auto_Print/axl/QMS318吹膜式样书.mdb",
            "print_file_path": "C:/Users/john/Desktop/Auto_Print/print_file.xlsx",
            "sound_path": "C:/Users/john/Desktop/Auto_Print/提示音/",
            "PWD": "8110",
            "jilubiao_sheet_name": "吹膜记录表",
            "jilubiao_area": "B2:AT36",
            "rukudan_area": "A1:B7",
            "rukudan_sheet_name": "入库单",
            "all_jihao": ['28', '27', '25', '24', '23', '22', '21', '16','13','06', '04'],
            "dingdanhao_cell": (3, 20),
            "jihao_cell": (2, 26),
            "guanlibianhao_cell": (3, 6),
            "pinming_cell": (3, 12),
            "chicun_cell": (3, 28),
            "jizhunzhongliang_cell": (16, 7),
            "riqi_cell": (2, 12),
            "pihao_cell": (2, 4),
            "j_s_cell": (18, 7),
            "j_x_cell": (17, 7),
            "kuandu_cell": (16, 10),
            "k_s_cell": (18, 10),
            "k_x_cell": (17, 10),
            "tongkuan_cell": (16, 13),
            "t_s_cell": (18, 13),
            "t_x_cell": (17, 13),
            "juanchang_cell": (17, 15),
            "yuanfanzhongliang_cell": (16, 18),
            "y_s_cell": (18, 18),
            "y_x_cell": (17, 18),
            "zhishishu_cell": (4, 6),
            "banci_cell": (2, 19),  # 早晚
            "zubie_cell": (2, 23),  # 甲乙
            "rukudan_dingdanhao_cell": (2, 2),
            "rukudan_guanlibianhao_cell": (3, 2),
            "rukudan_pinming_cell": (4, 2),
            "rukudan_riqi_cell": (1, 2),
            "rukudan_pihao_cell": (6, 2)
        }
        context = json.dumps(mysettings, ensure_ascii=False)
        settings_file = open(settings_file_path, 'w')
        settings_file.write(context)
        settings_file.close()
        return mysettings


mysettings = settings()

database_path = mysettings['database_path']
parent_access_path = mysettings['parent_access_path']
print_file_path = mysettings['print_file_path']
product_info_path = mysettings['product_info_path']
jilubiao_sheet_name = mysettings['jilubiao_sheet_name']
jilubiao_area = mysettings['jilubiao_area']
rukudan_sheet_name = mysettings['rukudan_sheet_name']
rukudan_area = mysettings['rukudan_area']
PWD = mysettings['PWD']
all_jihao = mysettings['all_jihao']

sound_path = mysettings['sound_path']
dingdanhao_cell = mysettings["dingdanhao_cell"]
guanlibianhao_cell = mysettings["guanlibianhao_cell"]
pinming_cell = mysettings["pinming_cell"]
chicun_cell = mysettings["chicun_cell"]
jizhunzhongliang_cell = mysettings["jizhunzhongliang_cell"]
riqi_cell = mysettings["riqi_cell"]
jihao_cell = mysettings["jihao_cell"]
pihao_cell = mysettings["pihao_cell"]
j_s_cell = mysettings["j_s_cell"]
j_x_cell = mysettings["j_x_cell"]
kuandu_cell = mysettings["kuandu_cell"]
k_s_cell = mysettings["k_s_cell"]
k_x_cell = mysettings["k_x_cell"]
tongkuan_cell = mysettings["tongkuan_cell"]
t_s_cell = mysettings["t_s_cell"]
t_x_cell = mysettings["t_x_cell"]
juanchang_cell = mysettings["juanchang_cell"]
yuanfanzhongliang_cell = mysettings["yuanfanzhongliang_cell"]
y_s_cell = mysettings["y_s_cell"]
y_x_cell = mysettings["y_x_cell"]
zhishishu_cell = mysettings["zhishishu_cell"]
banci_cell = mysettings["banci_cell"]
zubie_cell = mysettings["zubie_cell"]
rukudan_dingdanhao_cell = mysettings['rukudan_dingdanhao_cell']
rukudan_guanlibianhao_cell = mysettings['rukudan_guanlibianhao_cell']
rukudan_pinming_cell = mysettings['rukudan_pinming_cell']
rukudan_riqi_cell = mysettings['rukudan_riqi_cell']
rukudan_pihao_cell = mysettings['rukudan_pihao_cell']


def get_today():
    current_time = datetime.datetime.now()
    today = [current_time.year, current_time.month, current_time.day, current_time.hour, current_time.minute]
    return today




# 继承Qmainwindow用于重写各种事件
class MainWindow(QMainWindow):
    def closeEvent(self, event):
        # 一键停止所有py进程
        os.system("taskkill /F /IM Auto_Print.exe")
        os.system("taskkill /F /IM Auto_Print_test.exe")

        os.system("taskkill /F /IM python.exe")
        print('窗口关闭事件生效')


class MyUi(Ui_MainWindow):

    def setupUi(self, MainWindow):
        super().setupUi(MainWindow)  # 很关键，没有这个获取不到继承的属性

        # 用于绑定menubar事件
        def get_data_from_menubar():
            message = data_handle.from_access_update_into_excel_xlsx(parent_access_path, product_info_path,
                                                                     database_path, PWD, sound_path)
            self.statusBar.showMessage(message)

        # 绑定menubar事件
        self.actionUpdate_database.triggered.connect(get_data_from_menubar)

        # 重写日期
        today = get_today()
        self.dateEdit.setDate(QDate.currentDate())

        # 重写窗口图标，需要将ico转为python
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap(":/favicon.ico"), QtGui.QIcon.Normal, QtGui.QIcon.Off)
        MainWindow.setWindowIcon(icon)

        # 重写listwidget
        def list_clicked(item):
            global jihao
            jihao = item.text()

        for jihao in all_jihao:
            self.listWidget.addItem(jihao)

        self.listWidget.itemClicked.connect(list_clicked)

        self.radioButton.setChecked(True)

        # 绑定radio事件
        def set_print_mode(btn):
            global print_mode
            if btn.text() == '记录表和入库单':
                if btn.isChecked() == True:
                    print_mode = '1'
                    print('mode1')
            if btn.text() == '仅记录表':
                if btn.isChecked() == True:
                    print_mode = '2'
                    print('mode2')
            if btn.text() == '仅入库单':
                if btn.isChecked() == True:
                    print_mode = '3'
                    print('mode3')

        self.radioButton.toggled.connect(lambda: set_print_mode(self.radioButton))
        self.radioButton_2.toggled.connect(lambda: set_print_mode(self.radioButton_2))
        self.radioButton_3.toggled.connect(lambda: set_print_mode(self.radioButton_3))

        # 绑定banci radio事件
        def set_banci(btn):
            global banci
            if btn.text() == '早':
                if btn.isChecked() == True:
                    banci = '1'
                    print('班次1')
            if btn.text() == '夜':
                if btn.isChecked() == True:
                    banci = '2'
                    print('班次2')

        self.radioButton_4.toggled.connect(lambda: set_banci(self.radioButton_4))
        self.radioButton_5.toggled.connect(lambda: set_banci(self.radioButton_5))

        def set_zubie(btn):
            global zubie
            if btn.text() == '甲':
                if btn.isChecked() == True:
                    zubie = '1'
                    print('甲班')
            if btn.text() == '乙':
                if btn.isChecked() == True:
                    zubie = '2'
                    print('乙班')

        self.radioButton_6.toggled.connect(lambda: set_zubie(self.radioButton_6))
        self.radioButton_7.toggled.connect(lambda: set_zubie(self.radioButton_7))

        # # 隐藏托盘功能
        # def tray_event(self):
        #     self.tray.show()
        #
        # self.tray = QSystemTrayIcon()
        # self.tray.setIcon(icon)
        # # 设置托盘点击事件函数
        # self.tray.activated.connect(tray_event)
        # # 创建菜单
        # tray_menu = QMenu(QApplication.desktop())
        # # 添加1级菜单动作选项，还原主窗口
        # self.RestoreAction = QAction(u'还原', self, triggered=MainWindow.show)
        # # 添加1级菜单动作选项退出主程序
        # self.QuitAction = QAction(u'退出', self, triggered=qApp.quit)
        # # 为菜单添加动作
        # self.tray_menu.addAction(self.RestoreAction)
        # self.tray_menu.addAction(self.QuitAction)
        # # 设置系统托盘菜单
        # self.tray.setContextMenu(self.tray_menu)

        # 改变标签背景
        # ui.label_3.setAutoFillBackground(True)
        # palette = QPalette()
        # palette.setColor(QPalette.Window, Qt.red)
        # ui.label_3.setPalette(palette)

        # 改变窗体颜色
        # palette = QPalette()
        # # palette.setColor(QPalette.Background, Qt.dark)
        # palette.setBrush(QPalette.Background,QBrush(QPixmap("back.PNG")))
        # mainWindow.setPalette(palette)


if __name__ == '__main__':
    # 启动声音
    start_sound_path = sound_path + 'start.wav'
    sound.play(start_sound_path, winsound.SND_ALIAS)

    # 定义应用
    app = QApplication(sys.argv)
    icon = QtGui.QIcon()
    icon.addPixmap(QtGui.QPixmap(":/favicon1.ico"), QtGui.QIcon.Normal, QtGui.QIcon.Off)

    app.setWindowIcon(icon)
    # 实例化UI窗口
    ui = MyUi()


    # 从串口获取数据
    def send_from_serial_port():
        # scan_listener_sound_path = sound_path + 'scan_listener.wav'
        # sound.play(scan_listener_sound_path, winsound.SND_ALIAS)

        # 等待myui对象创建
        time.sleep(1)

        scan_val_line = ui.lineEdit
        dingdanhao_line = ui.lineEdit_7
        guanlibianhao_line = ui.lineEdit_2
        pinming_line = ui.lineEdit_5
        chicun_line = ui.lineEdit_6
        riqi_line = ui.dateEdit
        zhishishu_line = ui.lineEdit_40
        jizhunzhongliang_line = ui.lineEdit_3
        j_s_line = ui.lineEdit_4
        j_x_line = ui.lineEdit_8
        PE_hou_line = ui.lineEdit_41
        PE_s_line = ui.lineEdit_42
        PE_x_line = ui.lineEdit_43
        kuandu_line = ui.lineEdit_9
        k_s_line = ui.lineEdit_10
        k_x_line = ui.lineEdit_11
        tongkuan_line = ui.lineEdit_15
        t_s_line = ui.lineEdit_16
        t_x_line = ui.lineEdit_17
        juanchang_line = ui.lineEdit_18
        yuanfanzhongliang_line = ui.lineEdit_12
        y_s_line = ui.lineEdit_13
        y_x_line = ui.lineEdit_14

        def receive(ser):
            data = ''
            try:
                while ser.inWaiting() > 0:
                    data += ser.read().decode()
                return data
            except Exception as e:
                return data

        try:
            ser = serial.Serial("com3", 9600, timeout=0.5)
        except Exception as e:
            # 在D盘创建了一个错误日志，此处代码为写入错误日志。
            f = open('d:/err_info.txt', 'a')
            f.writelines(str(datetime.datetime.now()))
            f.writelines(repr(e))
            f.writelines('\r\n')

            f.close()
            ser = ''
            com3_not_found_sound_path = sound_path + 'COM3_not_found.wav'
            sound.play(com3_not_found_sound_path, winsound.SND_ALIAS)
            print(e.args)
            mainWindow.close()

        while True:
            scan_val = receive(ser)
            if scan_val != '':
                # scan_val = '123456|YJJ001-16|10.09'
                if re.match('.+[|].+[|].+', scan_val):
                    print('扫描到数据:%s' % scan_val)

                    dingdanhao_line.clear()
                    guanlibianhao_line.clear()
                    pinming_line.clear()
                    chicun_line.clear()
                    scan_val_line.clear()

                    zhishishu_line.clear()
                    jizhunzhongliang_line.clear()
                    j_s_line.clear()
                    j_x_line.clear()
                    PE_hou_line.clear()
                    PE_s_line.clear()
                    PE_x_line.clear()
                    kuandu_line.clear()
                    k_s_line.clear()
                    k_x_line.clear()
                    tongkuan_line.clear()
                    t_s_line.clear()
                    t_x_line.clear()
                    juanchang_line.clear()
                    yuanfanzhongliang_line.clear()
                    y_s_line.clear()
                    y_x_line.clear()

                    print('窗口清理完毕')

                    scan_val_line.setText(scan_val)
                    arr = scan_val.split('|')
                    dingdanhao = arr[0]
                    dingdanhao_line.setText(dingdanhao)
                    guanlibianhao = arr[1]
                    guanlibianhao_line.setText(guanlibianhao)
                    zhishishu = float(arr[2])
                    xiaoshu = zhishishu - int(zhishishu)
                    if xiaoshu > 0.09:
                        zhishishu = int(zhishishu) + 1
                    else:
                        zhishishu = int(zhishishu)
                    zhishishu_line.setText(str(zhishishu))

                    print('获取到初步信息')

                    # 获取管理编号对应的产品信息赋给窗体
                    data = data_handle.get_data_from_excel(database_path, 'data')
                    found = 0
                    for d in data:
                        if d[1] == guanlibianhao:
                            found = 1
                            pinming = d[2]
                            chicun = d[3]
                            jizhunzhongliang = round(float(d[4]), 2) if d[4] is not None else 0
                            j_x = round(float(d[5]), 2) if d[5] is not None else 0
                            j_s = round(float(d[6]), 2) if d[6] is not None else 0
                            PE_hou = round(float(d[7]), 2) if d[7] is not None else 0
                            PE_x = round(float(d[8]), 3) if d[8] is not None else 0
                            PE_s = round(float(d[9]), 3) if d[9] is not None else 0
                            kuandu = float(d[10]) if d[10] is not None else 0
                            k_x = round(float(d[11]), 2) if d[11] is not None else 0
                            k_s = round(float(d[12]), 2) if d[12] is not None else 0
                            tongkuan = float(d[13]) if d[13] is not None else 0
                            juanchang = float(d[14]) / 1000 if d[14] is not None else 0
                            yuanfanzhongliang = round(float(d[15]), 2) if d[15] is not None else 0
                            y_s = round(float(d[16]), 2) if d[16] is not None else 0
                            y_x = round(float(d[17]), 2) if d[17] is not None else 0

                            pinming_line.setText(pinming)
                            chicun_line.setText(chicun)
                            jizhunzhongliang_line.setText(str(jizhunzhongliang))
                            j_s_line.setText(str(j_s))
                            j_x_line.setText(str(j_x))
                            PE_hou_line.setText(str(PE_hou))
                            PE_s_line.setText(str(PE_s))
                            PE_x_line.setText(str(PE_x))
                            kuandu_line.setText(str(kuandu))
                            k_s_line.setText(str(k_s))
                            k_x_line.setText(str(k_x))
                            tongkuan_line.setText(str(tongkuan))
                            t_s = round(float(tongkuan), 2) * 1.03
                            t_x = round(float(tongkuan), 2) * 0.98
                            t_s_line.setText(str(t_s))
                            t_x_line.setText(str(t_x))

                            juanchang_line.setText(str(juanchang))
                            yuanfanzhongliang_line.setText(str(yuanfanzhongliang))
                            y_s_line.setText(str(y_s))
                            y_x_line.setText(str(y_x))

                            print('窗体内容添加完毕')

                            today = ui.dateEdit.date().toPyDate()
                            global jihao

                            if (banci == '0') or (jihao == '0') or (zubie == '0'):
                                scan_listener_sound_path = sound_path + 'banci_or_jihao_not_selected.wav'
                                sound.play(scan_listener_sound_path, winsound.SND_ALIAS)
                                continue
                            else:
                                pihao = today.strftime('%Y%m%d')[2:] + banci + jihao
                            print(pihao)
                            today = today.strftime('%Y/%m/%d')

                            # 判断是防锈还是PE
                            if re.match('YJJ.*?|YHJ.*', guanlibianhao):
                                print('PE')
                                jizhunzhongliang = PE_hou
                                j_s = PE_s
                                j_x = PE_x

                            lis_jilubiao = [
                                [dingdanhao_cell, dingdanhao],
                                [jihao_cell, jihao],
                                [guanlibianhao_cell, guanlibianhao],
                                [pinming_cell, pinming],
                                [chicun_cell, chicun],
                                [jizhunzhongliang_cell, jizhunzhongliang],
                                [riqi_cell, today],
                                [pihao_cell, pihao],
                                [j_s_cell, j_s],
                                [j_x_cell, j_x],
                                [kuandu_cell, kuandu],
                                [k_s_cell, k_s],
                                [k_x_cell, k_x],
                                [tongkuan_cell, tongkuan],
                                [t_s_cell, t_s],
                                [t_x_cell, t_x],
                                [juanchang_cell, juanchang],
                                [yuanfanzhongliang_cell, yuanfanzhongliang],
                                [y_s_cell, y_s],
                                [y_x_cell, y_x],
                                [zhishishu_cell, zhishishu],
                                [banci_cell, "早" if banci == '1' else "夜"],
                                [zubie_cell,"甲" if zubie == '1' else "乙"]

                            ]

                            lis_rukudan = [
                                [rukudan_dingdanhao_cell, dingdanhao],
                                [rukudan_guanlibianhao_cell, guanlibianhao],
                                [rukudan_pinming_cell, pinming],
                                [rukudan_riqi_cell, today],
                                [rukudan_pihao_cell, pihao]

                            ]

                            message = data_handle.scan_val_to_excel(print_file_path, jilubiao_sheet_name, jilubiao_area,
                                                                    rukudan_sheet_name, rukudan_area, lis_jilubiao,
                                                                    lis_rukudan,
                                                                    sound_path, print_mode)
                            jihao = '0'
                            ui.listWidget.clearSelection()

                            ui.statusBar.showMessage(message)

                            break

                    if found != 1:
                        ui.statusBar.showMessage('未找到此产品！')
                        scan_listener_sound_path = sound_path + 'not_found.wav'
                        sound.play(scan_listener_sound_path, winsound.SND_ALIAS)
                    print('本次扫描结束')
                else:
                    scan_val_line.setText(scan_val)
                    scan_failed = sound_path + 'scan_failed.wav'
                    sound.play(scan_failed, winsound.SND_ALIAS)

            time.sleep(0.1)


    # 开启监听线程
    thread = threading.Thread(target=send_from_serial_port, name='port_listener')
    print('下面开始线程')
    thread.start()

    # 将Qt5的窗口内容传递进来
    mainWindow = MainWindow()
    ui.setupUi(mainWindow)

    # 更新数据库
    upadate_database_result = data_handle.from_access_update_into_excel_xlsx(parent_access_path, product_info_path,
                                                                             database_path, PWD, sound_path)
    ui.statusBar.showMessage(upadate_database_result)

    mainWindow.show()
    ready_sound_path = sound_path + 'ready.wav'
    sound.play(ready_sound_path, winsound.SND_ALIAS)

    sys.exit(app.exec_())

D盘中放入settings.json配置文件

{"parent_access_path": "C:/Users/john/Desktop/Auto_Print/axl/AXL产品数据库-后端.accdb", "database_path": "C:/Users/john/Desktop/Auto_Print/database.xlsx", "product_info_path": "C:/Users/john/Desktop/Auto_Print/axl/QMS318吹膜式样书.mdb", "print_file_path": "C:/Users/john/Desktop/Auto_Print/print_file.xlsx", "sound_path": "C:/Users/john/Desktop/Auto_Print/提示音/", "PWD": "8110", "jilubiao_sheet_name": "吹膜记录表", "jilubiao_area": "B2:AT36", "rukudan_area": "A1:B7", "rukudan_sheet_name": "入库单", "all_jihao": ["28", "27", "25", "24", "23", "22", "21", "16", "13", "06", "04"], "dingdanhao_cell": [3, 20], "jihao_cell": [2, 26], "guanlibianhao_cell": [3, 6], "pinming_cell": [3, 12], "chicun_cell": [3, 28], "jizhunzhongliang_cell": [16, 7], "riqi_cell": [2, 12], "pihao_cell": [2, 4], "j_s_cell": [18, 7], "j_x_cell": [17, 7], "kuandu_cell": [16, 10], "k_s_cell": [18, 10], "k_x_cell": [17, 10], "tongkuan_cell": [16, 13], "t_s_cell": [18, 13], "t_x_cell": [17, 13], "juanchang_cell": [17, 15], "yuanfanzhongliang_cell": [16, 18], "y_s_cell": [18, 18], "y_x_cell": [17, 18], "zhishishu_cell": [4, 6], "banci_cell": [2, 19], "zubie_cell": [2, 23], "rukudan_dingdanhao_cell": [2, 2], "rukudan_guanlibianhao_cell": [3, 2], "rukudan_pinming_cell": [4, 2], "rukudan_riqi_cell": [1, 2], "rukudan_pihao_cell": [6, 2]}

爱学习的懒汉

关注

6
点赞
踩
37

收藏

觉得还不错? 一键收藏
1
评论
PyQt5+Serial+pypyodbc+win32com实现监听扫描枪串口，从ACCESS数据库中获取数据写入Excel自动打印

1项目简介我公司是生产制造公司，每切换一个订单，员工需要手动填写生产记录表，填写内容包括订单号、产品管理编号、产品名称、产品尺寸、产品类别、宽度、厚度、长度、基准重量、订单数量等等大量信息。人工书写易出错，浪费时间。事实上，设想，我公司产品数据都保存在ACCESS数据库中，只要通过扫描枪获取产品管理编号便可从数据库中获取其余信息，之后将所有信息写入Excel自动打印即可。...
复制链接

扫一扫