PyQt5 + Excel 读写操作 + 扫码枪检测

import os
import sys
import time
import datetime
import traceback
import xlwt
import xlsxwriter
import xlrd
import traceback
from xlutils import copy
from PyQt5 import sip
from PyQt5.QtGui import QIcon, QFont, QTextCursor
from PyQt5.QtCore import QCoreApplication, pyqtSignal, QThread, QObject,Qt
from PyQt5.Qt import QKeyEvent
from PyQt5.QtWidgets import (QApplication,QWidget, QLabel,QLineEdit, QTextEdit,QPushButton, QGroupBox,
                             QVBoxLayout,QHBoxLayout,QGridLayout)

class CompareTool(QWidget):

    def __init__(self):
        super().__init__()
        self.compare_tool_init_ui()
        self.c_t_wbook = ''

    def compare_tool_init_ui(self):

        self.c_t_font = QFont()
        self.c_t_font.setFamily("Courier New")
        self.c_t_font.setPointSize(10)
        self.setFont(self.c_t_font)

        self.setGeometry(500, 300, 650, 300)
        self.setFixedSize(650, 400)
        self.setWindowTitle('CompareTool')
        self.setWindowIcon(QIcon('CompareTool.ico'))
        self.setFont(self.c_t_font)

        main_layout = QVBoxLayout()

        self.c_t_group_box = QGroupBox('Compare Tool')
        self.c_t_grid_layout = QGridLayout()
        self.c_t_label_product_num = QLabel("工单号:")
        self.c_t_edit_product_num = QLineEdit()
        self.c_t_label_device_id = QLabel("设备ID:")
        self.c_t_edit_device_id = QLineEdit()
        self.c_t_label_pack_id = QLabel("包装ID:")
        self.c_t_edit_pack_id = QLineEdit()

        self.c_t_grid_layout.setSpacing(10)

        self.c_t_btn_compare = QPushButton("对比")
        self.c_t_btn_compare.clicked.connect(self.compare_tool_compare_btn_confirm)
        self.c_t_btn_generate_excel = QPushButton("生成Excel")
        self.c_t_btn_generate_excel.clicked.connect(self.compare_tool_generate_excel_confirm)
        #self.c_t_btn_exit = QPushButton("关闭")


        self.c_t_grid_layout.addWidget(self.c_t_label_product_num,1,0)
        self.c_t_grid_layout.addWidget(self.c_t_edit_product_num,1,1)
        self.c_t_grid_layout.addWidget(self.c_t_label_device_id,2,0)
        self.c_t_grid_layout.addWidget(self.c_t_edit_device_id,2,1)
        self.c_t_grid_layout.addWidget(self.c_t_label_pack_id,3,0)
        self.c_t_grid_layout.addWidget(self.c_t_edit_pack_id,3,1)

        self.c_t_grid_layout.addWidget(self.c_t_btn_compare,1,2)
        self.c_t_grid_layout.addWidget(self.c_t_btn_generate_excel,3,2)


        self.c_t_group_box.setLayout(self.c_t_grid_layout)

        self.c_t_text_edit = QTextEdit()
        '''这里不用hbox也可以.直接用addWidget'''
        #hbox_layout = QHBoxLayout()
        #hbox_layout.addWidget(self.c_t_group_box)
        #main_layout.addLayout(hbox_layout)
        main_layout.addWidget(self.c_t_group_box)
        main_layout.addWidget(self.c_t_text_edit)

        '''下面这两句要加上，刚才忘记加了，导致无法显示'''
        self.setLayout(main_layout)
        self.show()

    def compare_tool_get_excel_ready(self):
        '''先生成一份excel'''
        try:

            self.c_t_wbook = xlwt.Workbook()
            ws = self.c_t_wbook.add_sheet('Result')
            #ws.set_column('C:C',40)
            #ws.set_column('B:B',40)
            ws.col(0).width = 256 * 20
            ws.col(1).width = 256 * 20
            ws.col(2).width = 256 * 20


            #style1 = self.c_t_wbook.add_format({'bold': True, 'border': 1, 'align': 'center', 'bg_color': 'CBCBCB'})
            #style2 = self.c_t_wbook.add_format({'border': 1, 'align': 'center'})

            #ws.write(0,0,'No.',style1)
            #ws.write(0,1,'Device ID',style1)
            #ws.write(0,2,'Active Code',style2)
            ws.write(0,0,'No.')
            ws.write(0,1,'Device ID')
            ws.write(0,2,'Active Code')

            #self.c_t_wbook.close()
            self.c_t_wbook.save(self.c_t_book_name)
            print("get excel ready!")
        except Exception as e:
            print("compare_tool_get_excel_ready exception!")
            traceback.print_exc()

    def compare_tool_compare_btn_confirm(self):
        self.c_t_device_id = self.c_t_edit_device_id.text()
        self.c_t_pack_id = self.c_t_edit_pack_id.text()
        self.c_t_product_num = self.c_t_edit_product_num.text()

        if self.c_t_product_num == '':
            self.compare_tool_print_message("No product num.",1)
            self.c_t_edit_product_num.setFocus()
            return False

        if self.c_t_device_id == '':
            self.compare_tool_print_message("Device ID is NULL",1)
            self.c_t_edit_device_id.setFocus()
            return False
        elif self.c_t_pack_id == '':
            self.compare_tool_print_message("Abnormal ID",1)
            self.c_t_edit_pack_id.setFocus()
            return False
        else:
            msg = "Device ID:" + self.c_t_device_id
            self.compare_tool_print_message(msg,0)
            msg = "Pack ID:" + self.c_t_pack_id
            self.compare_tool_print_message(msg,0)
            if self.c_t_device_id == self.c_t_pack_id:
                self.compare_tool_print_message("Compare OK",0)
                return True
            else:
                self.compare_tool_print_message("Compare Fail",1)
                return False


    def compare_tool_generate_excel_confirm(self):
        '''先去对比是否两个ID相同'''
        if self.compare_tool_compare_btn_confirm() == True:
            self.compare_tool_generate_excel()
            self.c_t_edit_device_id.clear()
            self.c_t_edit_pack_id.clear()
            self.c_t_edit_device_id.setFocus()

    '''keyPressEvent是QWidget的函数，这里重载来监听按键消息'''
    def keyPressEvent(self, evt):
        print('evt.key = 0x%x'%evt.key())
        '''如果当前焦点在设备ID上并且有Shift按键按下（扫码枪）'''
        if self.c_t_edit_device_id.isActiveWindow() and evt.key() == Qt.Key_Return:
            self.c_t_product_num = self.c_t_edit_product_num.text()
            if self.c_t_product_num == '':
                msg = 'Please input Product Num.'
                self.compare_tool_print_message(msg,1)
                self.c_t_edit_product_num.setFocus()
                return
            self.c_t_device_id = self.c_t_edit_device_id.text()
            print("device ID = %s"%self.c_t_device_id)
            msg = "Device ID = " + self.c_t_device_id
            if self.c_t_device_id == '':
                self.compare_tool_print_message(msg,1)
                self.compare_tool_print_message("Device ID abnormal!Please input again",1)
                self.c_t_edit_device_id.clear()
                self.c_t_edit_device_id.setFocus()
                return
            else:
                self.compare_tool_print_message(msg,0)


            self.c_t_pack_id = self.c_t_edit_pack_id.text()
            print("pack ID = %s"%self.c_t_pack_id)
            if self.c_t_pack_id == '':
                self.c_t_edit_pack_id.setFocus()
            else:
                msg = "Pack ID = " + self.c_t_pack_id
                self.compare_tool_print_message(msg,0)
                '''compare action'''
                if self.compare_tool_do_compare() == False:
                    '''compare fail'''
                    self.c_t_edit_device_id.clear()
                    self.c_t_edit_pack_id.clear()
                    self.c_t_edit_device_id.setFocus()
                else:
                    '''to generate excel'''
                    ret = self.compare_tool_generate_excel()
                    self.c_t_edit_device_id.clear()
                    self.c_t_edit_pack_id.clear()
                    self.c_t_edit_device_id.setFocus()



        elif self.c_t_edit_pack_id.isActiveWindow() and evt.key() == Qt.Key_Return:
            self.c_t_product_num = self.c_t_edit_product_num.text()
            if self.c_t_product_num == '':
                msg = 'Please input Product Num.'
                self.compare_tool_print_message(msg,1)
                self.c_t_edit_product_num.setFocus()
                return

            self.c_t_pack_id = self.c_t_edit_pack_id.text()
            print("Pack ID = %s"%self.c_t_pack_id)
            msg = "Pack ID = " + self.c_t_pack_id

            if self.c_t_pack_id == '':
                self.compare_tool_print_message(msg,1)
                self.compare_tool_print_message("Pack ID abnormal!Please input again",1)
                self.c_t_edit_pack_id.clear()
                self.c_t_edit_pack_id.setFocus()
                return
            else:
                self.compare_tool_print_message(msg,0)

            self.c_t_device_id = self.c_t_edit_device_id.text()
            print("Device ID = %s"%self.c_t_device_id)
            if self.c_t_device_id == '':
                self.c_t_edit_device_id.setFocus()
            else:
                '''compare action'''
                msg = "Device ID = " + self.c_t_device_id
                self.compare_tool_print_message(msg,0)
                if self.compare_tool_do_compare() == False:
                    '''compare fail'''
                    self.c_t_edit_device_id.clear()
                    self.c_t_edit_pack_id.clear()
                    self.c_t_edit_device_id.setFocus()
                else:
                    '''to generate excel'''
                    self.compare_tool_generate_excel()
                    self.c_t_edit_device_id.clear()
                    self.c_t_edit_pack_id.clear()
                    self.c_t_edit_device_id.setFocus()
                return


    def compare_tool_generate_excel(self):
        print("generate excel")
        '''判断文件是否存在'''
        self.c_t_book_name = self.c_t_product_num + '_' + time.strftime('%Y-%m-%d',time.localtime(time.time())) + '.xls'
        if os.path.exists(self.c_t_book_name) == False:
            self.compare_tool_print_message("First Generate Excel! ...",0)
            self.compare_tool_get_excel_ready()
            #return

        '''往里面追加写入product num. & product id & pack id'''
        try:
            '''用xlrd读取这个文件'''
            res_excel = xlrd.open_workbook(self.c_t_book_name)

            table = res_excel.sheets()[0]
            now_rows = table.nrows
            print("now_rows = %d"%now_rows)


            '''用copy一份的excel做剩下的操作'''
            new_book = copy.copy(res_excel)
            '''获取当前的sheet,这里要验证下是否可通过sheet名字来获取sheet'''
            res_sheet = new_book.get_sheet(0)

            res_sheet.write(now_rows,0,(str)(self.c_t_product_num)
                            )
            res_sheet.write(now_rows,1,(str)(self.c_t_device_id))
            res_sheet.write(now_rows,2,(str)(self.c_t_pack_id))

            msg = 'Current Record: ' + (str)(now_rows)
            self.compare_tool_print_message(msg,0)

            new_book.save(self.c_t_book_name)
            msg = 'Save Success!'
            self.compare_tool_print_message(msg,0)
            return True
        except Exception as e:
            print("compare_tool_generate_excel exception")
            msg = 'Save Failed!'
            self.compare_tool_print_message(msg,1)
            traceback.print_exc()
            return False


    def compare_tool_do_compare(self):
        print("do compare....")
        if self.c_t_device_id == self.c_t_pack_id:
            self.compare_tool_print_message("Compare OK",0)
            self.compare_tool_print_message("Generate Excel...",0)
            return True
        else:
            self.compare_tool_print_message("Compare Fail!",1)
            self.compare_tool_print_message("Please input again...",0)
            return False


    def compare_tool_print_message(self,msg,msg_style):
        now_time = time.strftime("%m-%d %H:%M:%S ",time.localtime())
        msg = now_time + msg

        self.c_t_text_edit.setFont(self.c_t_font)

        try:
            if msg_style == 0:
                self.c_t_text_edit.setStyleSheet('color:rgb(0,0,255)')
            else:
                self.c_t_text_edit.setStyleSheet('color:rgb(255,0,0)')

            self.c_t_text_edit.append(msg)
            self.c_t_text_edit.moveCursor(QTextCursor.End)
        except Exception as e:
            print("compare_tool_print_message exception!")
            traceback.print_exc()

if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = CompareTool()
    sys.exit(app.exec_())