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

利用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()