詳解如何使用python腳本制作生成CANdbc

更新時間：2024年01月03日 15:56:45 作者：凍雨冷霧

這篇文章主要為大家詳細介紹了如何使用python腳本制作生成CANdbc,文中的示例代碼講解詳細,具有一定的學習價值,感興趣的小伙伴可以了解下

最近在工作中，有同事拿了一個excel的dbc表格，在用官方的dbc工具一個一個創(chuàng)建信號，大概看了一下共累計20多個節(jié)點，300多個信號，居然在手動處理，頓感無語。。

于是在網(wǎng)絡上搜相關的dbc 通過腳本生成方式，竟然沒搜到！那只能全網(wǎng)首發(fā)一個給廣大汽車軟件同行謀個福利。

（經(jīng)過國內一番搜索，一無所獲。于是乎轉谷歌搜索，在cantools 這個庫官網(wǎng)文件下找到這么一個指令：）

import cantools
db = cantools.database.load_file('MS11_E4U2_CCU_PTCANFD_230701.dbc')
cantools.database.dump_file(db, 'bar.dbc')

上文代碼實現(xiàn)了一個讀取加轉存，讀取了一個dbc并轉而生成了一個dbc，于是自行分析讀取的candbc數(shù)據(jù)結構，再結合chatgpt給出的示例，進行重構數(shù)據(jù)。最終簡單的示例代碼如下：

# -*- coding: utf-8 -*-
"""
Created on Wed Dec 27 18:09:18 2023

@author: xm

如何創(chuàng)建生成canfd還待挖掘

"""

import cantools

# db = cantools.database.load_file('MS11_E4U2_CCU_PTCANFD_230701.dbc')
# cantools.database.dump_file(db, 'bar.dbc')

# 定義一個消息
message1 = cantools.db.Message( #cantools.database.can.message.Message
    frame_id=0x19FB5101, #435900673
    name='PFC_VacReport',
    length=8,
    bus_name='PFC',
    header_byte_order='big_endian',
    is_extended_frame=True,
    # is_multiplexed=True,
    # refresh=True,
    is_fd=False,
    send_type='cyclic',
    senders=['PFC'],
    cycle_time=20,
    comment='input voltage',
    unused_bit_pattern=255,
    # unpack_container=True,
    signals=[
        cantools.db.Signal(
            name='P_VacRpt_VacRms_A',
            start=0,
            length=12,
            is_signed=False,
            scale=0.1,
            offset=0,
            minimum=0,
            maximum=409.5,
            unit='V',
            invalid=None,
            initial=0,
            # is_multiplexer=False,
            # is_float=False,
            receivers=['IFB','LC'],
            # multiplexer_signal=None,
            # multiplexer_ids=None,
            byte_order='little_endian', #little_endian big_endian
            comment='valid voltage phase A',
        ),
        cantools.db.Signal(
            name='P_VacRpt_VacRms_B',
            start=12,
            length=12,
            is_signed=False,
            scale=0.1,
            offset=0,
            minimum=0,
            maximum=409.5,
            unit='V',
            invalid=None,
            initial=0,
            # is_multiplexer=False,
            # is_float=False,
            receivers=['IFB','LC'],
            # multiplexer_signal=None,
            # multiplexer_ids=None,
            byte_order='little_endian',
            comment='valid voltage phase b',
        ),
        cantools.db.Signal(
            name='P_VacRpt_VacRms_C',
            start=24,
            length=12,
            is_signed=False,
            scale=0.1,
            offset=0,
            minimum=0,
            maximum=409.5,
            unit='V',
            invalid=None,
            initial=0,
            # is_multiplexer=False,
            # is_float=False,
            receivers=['IFB','LC'],
            # multiplexer_signal=None,
            # multiplexer_ids=None,
            byte_order='little_endian',
            comment='valid voltage phase c',
        ),
    ]
)



node1 = cantools.db.Node(
    name='PFC'
)



# 定義一個數(shù)據(jù)庫
database = cantools.db.Database(
    messages=[message1],
    nodes=[node1,],
    version='1.0'
)

# 保存為DBC文件
cantools.database.dump_file(database, 'example.dbc')

接下來就是解析對應的excel，創(chuàng)建不同的msg和signal再進行組合了。這里要注意，如果excel內涉及合并單元格，以及公式計算，需要用下文代碼中的兩個 In【0】部分來處理掉

# -*- coding: utf-8 -*-
"""
Created on Wed Dec 27 20:35:47 2023

@author: xm Azhe

"""

import pandas as pd
import cantools
import openpyxl

file_path = r'D:\代碼\dbc文件處理\xx.xlsx'

# In[0] 文件改造，合并單元格填充
# 打開Excel文件
workbook = openpyxl.load_workbook(file_path)

# 遍歷所有工作表
for worksheet in workbook.worksheets:
    # 獲取所有合并單元格
    merged_cells = worksheet.merged_cells
    # if worksheet.title=='IFB協(xié)議E2E ':
    #     print(merged_cells)
    # 遍歷合并單元格
    for merged_cell in list(merged_cells):

        # 獲取合并單元格的起始行、列和結束行、列
        start_row, start_col, end_row, end_col = merged_cell.min_row,merged_cell.min_col,merged_cell.max_row,merged_cell.max_col,
        # 拆分
        worksheet.unmerge_cells(start_row=start_row,start_column=start_col,end_row=end_row,end_column=end_col)
         # 獲取合并單元格的值
        cell_value = worksheet.cell(start_row, start_col).value
        # if worksheet.title=='IFB協(xié)議E2E ':
            # print(merged_cell)
            # print('\n')
        # 將合并單元格內的所有單元格都替換為該值
        for row in range(start_row, end_row + 1):
            for col in range(start_col, end_col + 1):
                worksheet.cell(row, col).value = cell_value
file_path1=file_path.replace('.xlsx','_dealed.xlsx')
# 保存Excel文件
workbook.save(file_path1)

# In[0] excel內包含公式，需要刷新公式，否則會讀取為nan
import win32com.client
# Start an instance of Excel
xlapp = win32com.client.DispatchEx("Excel.Application")
# Open the workbook in said instance of Excel
wb = xlapp.workbooks.open(file_path1)
# Optional, e.g. if you want to debug
# xlapp.Visible = True
# Refresh all data connections.
wb.RefreshAll()
wb.Save()
# Quit
xlapp.Quit()


# In[1] df讀取，刪除信號名稱為空，忽略第一行
def str_to_num(s):
    try:
        return int(s)
    except ValueError:
        try:
            return float(s)
        except ValueError:
            return s
        
df={}
for s in pd.ExcelFile(file_path1).sheet_names:
    # print(s)
    if ('協(xié)議' in s) & (s!='協(xié)議說明'):
        df[s] = pd.read_excel(file_path1, sheet_name=s,skiprows=1, engine='openpyxl')
        df[s] = df[s].dropna(subset=['數(shù)據(jù)名稱'])
        


node_list=[]
msg_list=[]
# cnt=1
for s in list(df.keys()):
    node_list.append(cantools.db.Node(name=s.replace(' ','').replace('協(xié)議E2E','')))
    msg_list_tmp=list(df[s]['ID'].drop_duplicates())
    for i in msg_list_tmp:
        # if s=='LVDC協(xié)議E2E':
        #     cnt1=1
        #     print('y')
        signals_list_tmp=[]
        df_sig_list_tmp=list(df[s]['數(shù)據(jù)名稱'][df[s]['ID']==i])
        for j in df_sig_list_tmp:
            signals_list_tmp.append(
                cantools.db.Signal(
                    name=j,
                    start=str_to_num(df[s]['起始位'][df[s]['數(shù)據(jù)名稱']==j]),
                    length=str_to_num(df[s]['長度'][df[s]['數(shù)據(jù)名稱']==j]),
                    is_signed=False,
                    scale=df[s]['精度'][df[s]['數(shù)據(jù)名稱']==j].values[0],
                    offset=str_to_num(df[s]['OFFSET'][df[s]['數(shù)據(jù)名稱']==j]),
                    minimum=df[s]['Min'][df[s]['數(shù)據(jù)名稱']==j].values[0],
                    maximum=df[s]['Max'][df[s]['數(shù)據(jù)名稱']==j].values[0],
                    unit=str(df[s]['單位'][df[s]['數(shù)據(jù)名稱']==j].values[0]),
                    invalid=None, #未采用dbc
                    initial=str_to_num(df[s]['Initial'][df[s]['數(shù)據(jù)名稱']==j]),
                    # is_multiplexer=False,
                    # is_float=False,
                    receivers=df[s]['接收'][df[s]['數(shù)據(jù)名稱']==j].values[0].split('&'),
                    # multiplexer_signal=None,
                    # multiplexer_ids=None,
                    byte_order='little_endian', #little_endian big_endian
                    comment=str(df[s]['說明'][df[s]['數(shù)據(jù)名稱']==j].values[0]),
                    # comments=None
                ))

        msg_list.append(cantools.db.Message( #cantools.database.can.message.Message
            frame_id=int(i[:i.index("\n")], 16),
            name=i[i.index("(")+1:i.index(")")],
            length=8,
            bus_name=df[s]['發(fā)送'][df[s]['ID']==i].values[0],
            header_byte_order='big_endian',
            is_extended_frame=True,
            is_fd=False,
            send_type='cyclic',
            senders=[df[s]['發(fā)送'][df[s]['ID']==i].values[0]],
            cycle_time=str_to_num(df[s]['周期'][df[s]['ID']==i].values[0][:df[s]['周期'][df[s]['ID']==i].values[0].index("ms")]),
            comment=str(df[s]['備注'][df[s]['ID']==i].values[0]),
            unused_bit_pattern=255,
            # unpack_container=True,
            signals=signals_list_tmp
            ))
        # if s=='LVDC協(xié)議E2E':
        #     cnt1=cnt1+1
        #     if cnt1==3:
        #         break
    # cnt=cnt+1
    # if cnt==5:
    #     break

database = cantools.db.Database(
    # messages=[message1],
    messages=msg_list,
    nodes=node_list,
    version='1.0'
)
    
cantools.database.dump_file(database, file_path.split('\\')[-1].replace('.xlsx','.dbc').replace('.xls','.dbc').replace('.csv','.dbc'))

有制作dbc的小伙伴們有福了