[python]用python获取EXCEL文件内容并保存到DBC

关键词

==python、excel、DBC、openpyxl ==

平台说明

背景

在搭建自动化测试平台的时候经常会提取DBC文件中的信息并保存为excel或者其他文件格式，用于自动化测试。本文介绍了如何用python3.6实现获取EXCEL文件内容并保存到DBC。

所需库

1.canmatrix：需要它库来解析 DBC 文件，它提供了对 Controller Area Network (CAN) 数据的解析功能，包括 DBC 文件的支持。
2.openpyxl ：是一个用于读写 Excel 文件的 Python 库。

实现过程

方法1.

1.安装相关库

pip install openpyxl canmatrix

2.代码实现

from openpyxl import load_workbook
import canmatrixclass MyDBC_Obj:def __init__ (self,dbc_path,excel_path):self.name = "self"self.sheet = ""self.dbc = ""self.dbc_path = dbc_pathself.excel_path = excel_pathdef read_excel(self):workbook = load_workbook(filename = self.excel_path)self.sheet = workbook.activedef generate_dbc(self):self.dbc = canmatrix.CanMatrix()self.dbc.add_global_defines("BusType","STRING")self.dbc.add_define_default("BusType","CAN")# 报文发送类型属性以及默认值self.dbc.add_frame_defines("GenMsgSendType", 'ENUM "Cyclic", "Event"' )self.dbc.add_define_default("GenMsgSendType", "Cyclic")self.dbc.add_frame_defines("GenMsgCycleTime", 'INT 0 65535')self.dbc.add_define_default("GenMsgCycleTime", "0")def write_dbc(self):self.read_excel()self.generate_dbc()ecu_obj = canmatrix.Ecu(self.sheet.cell(1,29).value)ecu_obj.name = self.sheet.cell(1,29).valueself.dbc.add_ecu(ecu_obj)for i in range(2,self.sheet.max_row+1):if self.sheet.cell(row=i,column=1).value == None:self.sheet.cell(row=i,column=1).value = self.sheet.cell(row=i-1,column=1).valueself.sheet.cell(row=i,column=2).value = self.sheet.cell(row=i-1,column=2).valueself.sheet.cell(row=i,column=3).value = self.sheet.cell(row=i-1,column=3).valueself.sheet.cell(row=i,column=4).value = self.sheet.cell(row=i-1,column=4).valueself.sheet.cell(row=i,column=5).value = self.sheet.cell(row=i-1,column=5).valueself.sheet.cell(row=i,column=6).value = self.sheet.cell(row=i-1,column=6).valuemessage_name = ""message_id = 0message_length = 0message_type_is_fd = Falsemessage_type_is_extended = Falsemessage_cycle = 0message_recive = ""frame_obj = canmatrix.Frame()signal_obj=canmatrix.Signal()for row in self.sheet.iter_rows(min_row=3,values_only=True):            signal_name = row[6]if signal_name != None:signal_obj.name = row[6]message_name = row[0]message_id = int(row[2],16)if row[11] != None:signal_obj.start_bit = int(row[11])else:signal_obj.start_bit = 0signal_obj.size = int(row[13])if row[9] == "Motorola":signal_obj.is_little_endian = Falseelse:signal_obj.is_little_endian = Truesignal_obj.initial_value = int(row[21])signal_obj.add_attribute("GenSigStartValue",signal_obj.initial_value)signale_sendtype = 0if row[12] == "Cycle":signale_sendtype = 0else:signale_sendtype = 2signal_obj.add_attribute("GenSigSendType",signale_sendtype)if row[14] == "unsigned":signal_obj.is_float = Falsesignal_obj.is_signed = Falseelif row[14] == "signed":signal_obj.is_float = Falsesignal_obj.is_signed = Trueelse:signal_obj.is_float = Truesignal_obj.is_signed = Falsesignal_obj.min = float(row[17])signal_obj.max = float(row[18])signal_obj.factor = float(row[15])signal_obj.offset = float(row[16])if row[23] != None:signal_obj.unit = row[23]if row[28] == "RX":signal_obj.add_receiver(ecu_obj.name)elif row[28] == "TX":message_recive = ecu_obj.namemessage_sig_group_name = row[8]if  message_sig_group_name != None:signalGroups = frame_obj.signal_group_by_name(message_sig_group_name)if signalGroups == None:frame_obj.add_signal_group(message_sig_group_name,1,signal_obj.name)else:signalGroups.add_signal(signal_obj)if row[24] != None:sigvalue = row[24]st=sigvalue.split("\n")for i in st:if i == "":st.remove(i)d = dict(x.split(":") for x in st)for k, v in d.items():signal_obj.add_values(k,v)             frame_obj.add_signal(signal_obj)#*********** frame type set ***************signal_obj=canmatrix.Signal()if row[5] != None:message_length = int(row[5])else:message_length = 0if row[4] != None:message_cycle = int(row[4])if row[1] == "CAN standard":message_type_is_fd = Falsemessage_type_is_extended = Falseelif row[1] == "CANFD standard":message_type_is_fd = Truemessage_type_is_extended = Falseelif row[1] == "CAN extended":message_type_is_extended = Truemessage_type_is_fd = Falseelif row[1] == "CANFD extended":message_type_is_extended = Truemessage_type_is_fd = Trueelse:frame_obj.name = message_nameframe_obj.size = message_lengthframe_obj.arbitration_id.id = message_idframe_obj.cycle_time = message_cycleframe_obj.is_fd = message_type_is_fdframe_obj.is_j1939 = message_type_is_extendedif message_recive!= "":frame_obj.add_transmitter(ecu_obj.name)self.dbc.add_frame(frame_obj)message_name = ""message_id = 0message_length = 0message_type = Falsemessage_recive = ""signalGroups = []frame_obj = canmatrix.Frame()# 导出到DBC文件file_out = open(self.dbc_path, "wb")canmatrix.formats.dbc.dump(self.dbc,file_out,dbcExportEncoding='utf-8')file_out.close()if __name__ == "__main__":dbc_file_path = "excel.dbc"  # Replace with the actual DBC file pathexcel_file_path = "dbc.xlsx"  # Replace with the desired Excel output pathMyDbc = MyDBC_Obj(dbc_file_path,excel_file_path)MyDbc.write_dbc()