欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

利用Python serial和openpyxl模块进行数据的读取和存储

程序员文章站 2022-06-03 20:30:16
...

利用Python可以做很多我们日常工作中的很多事情,例如:操作excel文件数据,对串口进行数据通信等等,总的来说Python用的好的话可以帮助我们完成许多琐事。下面我就举一个用serial和openpyxl模块来完成串口的数据通信和excel数据存储。

对于串口:

ser = serial.Serial(port=COM6, baudrate=9600, bytesize=8, parity='N', stopbits=1, timeout=2)  # 打开串口
ser.write(commands)  # 向串口发送指令
response = ser.readline()  # 读取串口的返回数据

串口发送时数据要求是byte类型,在进行指令的发送,或者是字符串处理的时候要利用encode()方法将char类型转化为byte类型,利用decode()方法来将byte类型转化为char类。否则会报错误

对于excel文档:

excel文档中主要有三要素:workbooks、 sheets、 cells 所有的excel表格数据都是从这三个来定位的。

import openpyxl  
wb = openpyxl.load_workbook(r'D:\Desktop\auto_test.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
start_cell = 'B2'
end_cell = 'G' + str(sheet.max_row - 1)
for rowOfCellObjects in sheet[start_cell: end_cell]:
# 获取发送的指令并发送
command_cell = rowOfCellObjects[0]
practice_cell = rowOfCellObjects[1]
test_time_cell = rowOfCellObjects[2]
practice_result_cell = rowOfCellObjects[3]
result_cell = rowOfCellObjects[4]
commands = command_cell.value
使用时不能有相同的excel表格已经打开,否则没有权限操作excel,如果要替换原来的excel,需要调用wb.save()方法。

总的代码:

import serial
import serial.tools.list_ports
import openpyxl
import time


class AutoTest(object):
    def __init__(self, port, baudrate):
        self.port = port
        self.baudrate = baudrate

    def command_test(self):
        ser = serial.Serial(port=self.port, baudrate=self.baudrate, bytesize=8, parity='N', stopbits=1, timeout=2)
        if ser is None:
            print("串口打开失败...")
        else:
            # 获取测测试指令的表格
            wb = openpyxl.load_workbook(r'D:\Desktop\auto_test.xlsx')
            sheet = wb.get_sheet_by_name('Sheet1')
            start_cell = 'B2'
            end_cell = 'G' + str(sheet.max_row - 1)
            for rowOfCellObjects in sheet[start_cell: end_cell]:
                # 获取发送的指令并发送
                command_cell = rowOfCellObjects[0]
                practice_cell = rowOfCellObjects[1]
                test_time_cell = rowOfCellObjects[2]
                practice_result_cell = rowOfCellObjects[3]
                result_cell = rowOfCellObjects[4]
                commands = command_cell.value
                commands = commands.encode('utf-8')
                test_time_cell.value = time.strftime("%Y/%m/%d")
                ser.write(commands)
                # 获取指令回复结果
                response = ser.readline().decode().replace('\r\n', '')
                if response == practice_cell.value:
                    result_cell.value = '通过'
                else:
                    result_cell.value = '失败'
                if response == '':
                    practice_result_cell.value = 'Null'
                    print("there is nothing received.")
                else:
                    print(commands.decode() + ' ' + response)
                    practice_result_cell.value = response
                wb.save(r'D:\Desktop\auto_test.xlsx')
        ser.close()


if __name__ == '__main__':
    test1 = AutoTest("COM6", 9600)
    test1.command_test()