pyqt5_openpyxy笔记
小工具功能生成一个excl表格，若干产品以行为单位编辑记录产品工艺参数，每行若干列记录n段数据，通过串口导入到mcu按控制过程执行工艺；
使用pycharm开发环境，需要的模块pyserial和openpyxy都已经安装，直接导入使用；
为以后方便修改移植，遵循高内聚低耦合思路，小工具都新建独立文件并在文件内实现自身功能，
新建mySerial.py文件MySerial类继承pyserial的Serial类，并实现modbus串口通信功能，
import time
import serial
import serial.tools.list_ports
from PyQt5.QtCore import QCoreApplication, Qt, QTimer, QThread, pyqtSignal
from PyQt5.QtWidgets import QMessageBox
from crcmodbus import checksum
Baud = ["9600", "19200", "38400", "57600", "115200"]
PARITY_NONE, PARITY_EVEN, PARITY_ODD, PARITY_MARK, PARITY_SPACE = 'N', 'E', 'O', 'M', 'S'
STOPBITS_ONE, STOPBITS_ONE_POINT_FIVE, STOPBITS_TWO = (1, 1.5, 2)
FIVEBITS, SIXBITS, SEVENBITS, EIGHTBITS = (5, 6, 7, 8)
PARITY_NAMES = {
PARITY_NONE: 'None',
PARITY_EVEN: 'Even',
PARITY_ODD: 'Odd',
PARITY_MARK: 'Mark',
PARITY_SPACE: 'Space',
}
def port_scan():
lis = []
port_lis = serial.tools.list_ports.comports()
if len(port_lis) > 0:
for port in port_lis:
lis.append(port[0])
else:
lis.append('无可用端口')
return lis
class MySerial(serial.Serial):
def __init__(self):
super().__init__()
self.baudrate = 9600
self.port = None
self.bytesize = EIGHTBITS
self.stopbits = STOPBITS_ONE
self.timeout = 10
self.stat = False
self.addr = 0x01
def serTx(self, lis):
self.write(lis)
def serOpen(self):
if self.is_open:
self.close()
time.sleep(0.1)
self.open()
def serClose(self):
if self.is_open:
self.close()
def serGetNum(self):
return self.inWaiting()
def __del__(self):
if self.is_open:
self.close()
def read_modbus03(self, add=0):
lis = [self.addr, 0x03, (add >> 8) & 0xff, add & 0xff, 0x00, 0x02]
s = 'timeout'
dat = 0
crc16 = checksum(lis)
lis.append(crc16 >> 8)
lis.append(crc16 & 0xff)
self.serTx(lis)
rxlis = self.ser_mbus03_Rx()
if len(rxlis) > 0:
dat = int((rxlis[3] << 24) | (rxlis[4] << 16) | (rxlis[5] << 8) | rxlis[6])
if dat & (1 << 31):
dat = ~(dat ^ 0xffffffff)
s = 'ok'
else:
print('timeout')
return dat, s
def write_i16(self, add=0, dat=0):
lis = [self.addr, 0x06, (add >> 8) & 0xff, add & 0xff, (dat >> 8) & 0xff, dat & 0xff]
s = ' '
crc16 = checksum(lis)
lis.append(crc16 >> 8)
lis.append(crc16 & 0xff)
self.serTx(lis)
def write_i32(self, add=0, dat=0):
lis = [self.addr, 0x10, (add >> 8) & 0xff, add & 0xff, 0x00, 0x02, 0x04, (dat >> 24) & 0xff, (dat >> 16) & 0xff,
(dat >> 8) & 0xff, dat & 0xff]
crc16 = checksum(lis)
lis.append(crc16 >> 8)
lis.append(crc16 & 0xff)
self.serTx(lis)
def ser_mbus03_Rx(self): # 查询接收
lis = list()
cnt = 20
while cnt > 0:
time.sleep(0.005)
try:
cnt = cnt - 1
num = self.inWaiting()
if num >= 9:
lis += self.read(num)
if checksum(lis[:9]) == 0:
pass
else:
lis.clear()
break
except Exception as e:
print(e)
return lis
def ser_mbus_06_10_Rx(self): # 查询接收
lis = list()
cnt = 10
s = 'timeout'
while cnt > 0:
time.sleep(0.005)
cnt = cnt - 1
try:
num = self.inWaiting()
if num >= 8:
lis += self.read(num)
if checksum(lis[:8]) == 0:
s = 'ok'
break
except Exception as e:
print(e)
s = 'timeout'
return s
if __name__ == "__main__":
pass
新建myTabel.py文件MyTabel类继承TableWidget类实现表格功能，
import os
from PyQt5.QtCore import QDateTime
from PyQt5.QtWidgets import QTableWidget, QFileDialog, QTableWidgetItem
from openpyxl import load_workbook, Workbook
HEDER = ['序号', '产品编号', '段选择',
'设定1', '上差1', '下差1',
'设定2', '上差2', '下差2',
'设定3', '上差3', '下差3',
'设定4', '上差4', '下差4',
'设定5', '上差5', '下差5',
'设定6', '上差6', '下差6',
'设定7', '上差7', '下差7',
'设定8', '上差8', '下差8']
class MyTableWidget(QTableWidget):
def __init__(self):
super().__init__()
self.mybook = None
self.myfilename = None
pass
def create_workbook_file(self):
"""Create a new workbook file and return the path"""
date = QDateTime.currentDateTime()
path, fil = QFileDialog.getSaveFileName(self, "保存文件", date.toString('yyyyMMddHHmmss'),
"Excel 文件(*.xlsx)")
self.mybook = self.create_mybook(path)
self.myfilename = path
def open_workbook_file(self):
"""open workbook file and return the workbook object and path"""
try:
filename, fil = QFileDialog.getOpenFileName(self, "打开文件", ".", "Excel 文件(*.xlsx)")
if os.path.exists(filename):
self.mybook = load_workbook(filename=filename, read_only=False) # 获取工作簿
self.myfilename = filename
else:
self.mybook = None
self.myfilename = None
except Exception as e:
print(e)
def create_mybook(self, path):
"""Create a new workbook file at the specified path"""
wbook = Workbook()
wsheet = wbook.create_sheet(title='Sheet1', index=0)
wsheet.append(HEDER)
wbook.save(path)
column = len(HEDER)
self.setRowCount(101)
self.setColumnCount(column)
self.setHorizontalHeaderLabels(HEDER)
for i in range(column):
self.resizeColumnToContents(i) # 设置表格自动宽度
return wbook
def write_mybook_cell(self, row, col, s):
"""write specified cell data """
sheet = self.mybook.active
sheet.cell(row=row, col=col, value=s)
pass
def read_mybook_cell(self, row, col):
sheet = self.mybook.active
s = sheet.cell(row, col).value
def read_mybook_row(self):
lis = []
try:
sheet = self.mybook.active
for i in sheet.values:
lis.append(list(i))
except Exception as e:
print(e)
return lis
def write_mytable_cell_all(self, lis):
row = len(lis)
col = len(lis[0])
for i in range(row):
for j in range(col):
cell = QTableWidgetItem()
cell.setText(lis[i][j])
self.setItem(i,j,cell)
pass
def save_mybook(self):
self.mybook.save(self.myfilename)
if __name__ == '__main__':
pass
新建mywindow文件，初始化窗口，
import datetime
import time
from PyQt5.QtCore import QTimer, QDateTime, QCoreApplication, Qt
import sys
import os
from PyQt5.QtWidgets import (QApplication, QWidget, QMenuBar, QVBoxLayout, QHBoxLayout, QFileDialog, QTableWidget,
QTableWidgetItem, QComboBox, QLabel, QPushButton, QMessageBox, QTextEdit, QLineEdit,
QCheckBox, QStatusBar)
from openpyxl import load_workbook, Workbook
from crcmodbus import checksum
# from PyQt5.QtCore import QDateTime
import serial
import serial.tools.list_ports
import mySerial
import threading
import myTableWidget
class myWindow(QWidget):
def __init__(self, parent=None):
super().__init__()
self.resize(800, 600)
self.my_setupUI()
self.mySer = mySerial.MySerial()
self.action_newfile.triggered.connect(self.action_newfile_slot)
self.action_openfile.triggered.connect(self.action_openfile_slot)
self.scanButton.clicked.connect(self.action_scanPort)
self.writeButton.clicked.connect(self.action_writeButton_slot)
self.fileName = list()
self.fileName.clear()
self.row_cnt = 0
self.column_cnt = 5
def my_setupUI(self):
TopHlayout = QHBoxLayout()
BottumHlayout = QHBoxLayout()
menumBar = QMenuBar(self)
menumBar.setStyleSheet('background:#e2e2e2 ')
file_menum = menumBar.addMenu('file')
self.action_newfile = file_menum.addAction('new')
self.action_openfile = file_menum.addAction('open')
self.action_savefile = file_menum.addAction('save')
self.action_saveAsfile = file_menum.addAction('save as')
file_menum.addSeparator()
self.action_exit = file_menum.addAction('exit')
self.action_savefile.setEnabled(False)
self.action_saveAsfile.setEnabled(False)
TopHlayout.addWidget(menumBar)
self.scanButton = QPushButton('刷新端口')
TopHlayout.addWidget(self.scanButton)
self.portLabal = QLabel('可用端口:')
TopHlayout.addWidget(self.portLabal)
self.serialPort_comBox = QComboBox()
self.action_scanPort()
TopHlayout.addWidget(self.serialPort_comBox)
baudLabal = QLabel('Baud:')
TopHlayout.addWidget(baudLabal)
self.serialBaud_comBox = QComboBox()
self.serialBaud_comBox.addItems(mySerial.Baud)
TopHlayout.addWidget(self.serialBaud_comBox)
self.openButton = QPushButton('打开串口')
TopHlayout.addWidget(self.openButton)
self.readButton = QPushButton('读数据')
BottumHlayout.addWidget(self.readButton)
self.writeButton = QPushButton('写数据')
BottumHlayout.addWidget(self.writeButton)
self.statusBar = QStatusBar()
self.label_version = QLabel('菜鸟')
self.statusBar.addPermanentWidget(self.label_version)
self.myTable = myTableWidget.MyTableWidget()
v = QVBoxLayout(self)
v.addLayout(TopHlayout)
v.addWidget(self.myTable)
v.addLayout(BottumHlayout)
v.addWidget(self.statusBar)
def action_writeButton_slot(self):
lis = self.myTable.read_mybook_row()
self.myTable.write_mytable_cell_all(lis)
def action_newfile_slot(self):
self.myTable.create_workbook_file()
self.action_savefile.setEnabled(True)
self.action_saveAsfile.setEnabled(True)
def action_openfile_slot(self): # 打开excel文件
self.myTable.open_workbook_file()
def action_savefile_slot(self):
self.myTable.save_mybook()
def action_scanPort(self):
self.serialPort_comBox.clear()
port_lis = mySerial.port_scan()
self.serialPort_comBox.addItems(port_lis)
if __name__ == '__main__':
QCoreApplication.setAttribute(Qt.AA_EnableHighDpiScaling) # 高分辨率支持
app = QApplication(sys.argv)
window = myWindow()
window.setWindowTitle('数据记录工具')
window.show()
sys.exit(app.exec())
浙公网安备 33010602011771号