③从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]}