制作网站的网站,短视频营销策略,实体店100个营销策略,网站结构图Excel制作
新建一个Excel#xff0c;后缀为“.xls”
工作本名称改为“CAN_Matrix”
在首行按照列来起名字#xff0c;在里面只需要填写必须的内容即可。
列数名称第0列Message Name第1列Message Format第2列Message ID第3列Message Length (byte)第4列Message Transmitte…Excel制作
新建一个Excel后缀为“.xls”
工作本名称改为“CAN_Matrix”
在首行按照列来起名字在里面只需要填写必须的内容即可。
列数名称第0列Message Name第1列Message Format第2列Message ID第3列Message Length (byte)第4列Message Transmitter ECU第5列Message Receiver ECU第6列Message Send Method第7列Message Cycle Period(ms)第8列Message Cycle Period Fast(ms)第9列Message Delay Time(ms)第10列Message IL Support 第11列Message Num Of Repetition第12列Message Start Delay Time(ms)第13列NM Message第14列CANFD BRS第15列Message Comment第16列Signal Name第17列Signal Length (Bit)第18列Signal Start Bit第19列Singal Byte Order第20列Signal Unit第21列Signal Data Type第22列Signal Resolution第23列Signal OffSet第24列Signal Min Value第25列Signal Max Value第26列Singal Inactive Value第27列Singal Send Method第28列Singal Start Value第29列NWM- Wakeup Allowed第30列Signal Value Descriptions第31列Signal Comment
代码源码
# encodingutf-8
import xlrd
from decimal import Decimalimport sys
global Table #表格
global RowCount #行数
global ColumnCount #列数
global OutputFile #输出文件ExcelTable CAN_Matrix #表格
Note xin1 #节点名称MessageName_Position 0 #报文名称序号
MessageFormat_Position 1 #报文格式序号
MessageID_Position 2 #报文ID序号
MessageLength_Position 3 #报文长度序号
MessageTransmitterNote_Position 4 #报文发送节点序号
MessageReceiverNote_Position 5 #报文接收节点序号
MessageSendMethod_Position 6 #报文发送方式序号
MessagePeriod_Position 7 #报文周期序号
MessagePeriodFast_Position 8 #报文快速周期序号
MessageDelayTime_Position 9 #报文延迟时间序号
MessageILSupport_Position 10 #报文支持IL序号
MessageNumOfRepetition_Position 11 #报文重发次数序号
MessageStartDelayTime_Position 12 #报文开始延迟时间序号
MessageNM_Position 13 #报文网络管理序号
MessageCANFDBRS_Position 14 #报文CANFDBRS序号
MessageComment_Position 15 #报文注释序号SignalName_Position 16 #信号名称序号
SignalStartLength_Position 17 #信号长度序号
SignalStartBit_Position 18 #信号起始位序号
SignalByteOrder_Position 19 #信号字节序序号
SignalUnit_Position 20 #信号单位序号
SignalDataType_Position 21 #信号数据类型序号
SignalResolution_Position 22 #信号比率序号
SignalOffset_Position 23 #信号偏移序号
SignalMinValue_Position 24 #信号最小值序号
SignalMaxValue_Position 25 #信号最大值序号
SingalInactiveValue_Position 26 #信号未激活值序号
SingalSendMethod_Position 27 #信号发送方式序号
SingalStartValue_Position 28 #信号开始值序号
SingalNWMWakeupAllowed_Position 29 #信号网管唤醒序号
SignalValueDescriptions_Position 30 #信号数值描述序号
SignalComment_Position 31 #信号标注序号def remove_exponent(num): # 去掉读取到的数值的小数点后边的0return num.to_integral() if num num.to_integral() else num.normalize()def Read_Base_Infomation(): # 获取基本信息global Table #表格global RowCount #行数global ColumnCount #列数global OutputFile #输出文件if len(sys.argv) 1:InputFilePath sys.argv[1]else:InputFilePath DBCDemo.xls#print(fInputFilePath:{InputFilePath}\n)LastIndex InputFilePath.rfind(\\)StartIndex InputFilePath.find(.xls, LastIndex)InputFileSuffix InputFilePath[LastIndex 1:]#print(fInputFileSuffix:{InputFileSuffix}\n)Xlsx xlrd.open_workbook(InputFileSuffix) # 找到输入文件Table Xlsx.sheet_by_name(ExcelTable) # 找到sheetRowCount Table.nrows # 提取行数ColumnCount Table.ncols # 提取列数InputFileNoSuffix InputFilePath[LastIndex 1:StartIndex - 3]#输出文件#print(fInputFileNoSuffix:{InputFileNoSuffix}\n)OutputFile InputFileNoSuffix .dbc#print(fOutputFile:{OutputFile}\n)def Output_DBC_Head():#打印文件头部global OutputFile #输出文件with open(OutputFile, w,encodingutf-8) as f:f.write(VERSION \\)f.write(\n\n)f.write(
NS_ : NS_DESC_CM_BA_DEF_BA_VAL_CAT_DEF_CAT_FILTERBA_DEF_DEF_EV_DATA_ENVVAR_DATA_SGTYPE_SGTYPE_VAL_BA_DEF_SGTYPE_BA_SGTYPE_SIG_TYPE_REF_VAL_TABLE_SIG_GROUP_SIG_VALTYPE_SIGTYPE_VALTYPE_BO_TX_BU_BA_DEF_REL_BA_REL_BA_DEF_DEF_REL_BU_SG_REL_BU_EV_REL_BU_BO_REL_SG_MUL_VAL_BS_:\n\n)f.write(fBU_: {Note}\n\n\n)def Output_MessageMappingSignal():#报文和信号映射关系global Table #表格global RowCount #行数global OutputFile #输出文件with open(OutputFile, a,encodingutf-8) as f:i 1 #跳过表头while i RowCount:#逐行轮询MessageName Table.cell_value(i, MessageName_Position)# 报文名称if MessageName : # 本行为空print(fEmpty Message exists\n)returnelse: # 本行有内容#MessageID Table.cell_value(i, MessageID_Position)MessageID int((Table.cell_value(i, MessageID_Position)), 16)# 报文ID十六进制#print(fMessageID:{MessageID}\n)MessageLength remove_exponent(Decimal(Table.cell_value(i, MessageLength_Position)).quantize(Decimal(0)))# 报文长度MessageTransmitterNote Table.cell_value(i, MessageTransmitterNote_Position)# 报文发送节点MessageReceiverNote Table.cell_value(i, MessageReceiverNote_Position)# 报文接收节点if MessageTransmitterNote Note :# 写入报文ID信息f.write(fBO_ {MessageID} {MessageName}: {MessageLength} {Note}\n) else:f.write(fBO_ {MessageID} {MessageName}: {MessageLength} Vector__XXX\n) while 1:#扫描这个报文里面的所有信号if (i RowCount) or (MessageID ! int((Table.cell_value(i, MessageID_Position)), 16)):#后面没有该报文的信号或者已经到尽头f.write(\n)breakelse:# 后面还有该报文的信号SingleName Table.cell_value(i, SignalName_Position)#信号名称#print(fSingleName:{SingleName}\n)SignalStartBit remove_exponent(Decimal(Table.cell_value(i, SignalStartBit_Position)).quantize(Decimal(0)))#信号起始位去除小数点后边的0以及保留小数点后O位#print(fSignalStartBit:{SignalStartBit}\n)SignalLength remove_exponent(Decimal(Table.cell_value(i, SignalStartLength_Position)).quantize(Decimal(0))) #信号长度#print(fSignalLength:{SignalLength}\n)if Table.cell_value(i, SignalByteOrder_Position) Motorola MSB or Table.cell_value(i, SignalByteOrder_Position) Motorola or Table.cell_value(i, SignalByteOrder_Position) :#信号字节序SignalByteOrder 0else:SignalByteOrder 1#print(fSignalByteOrder:{SignalByteOrder}\n)if Table.cell_value(i, SignalDataType_Position) Unsigned or Table.cell_value(i, SignalDataType_Position) unsigned or Table.cell_value(i, SignalDataType_Position) :#信号有无符号SignalDateType else:SignalDateType -#print(fSignalDateType:{SignalDateType}\n)if Table.cell_value(i, SignalUnit_Position) :#信号单位SignalUnit else:SignalUnit Table.cell_value(i, SignalUnit_Position)#print(fSignalUnit:{SignalUnit}\n)if Table.cell_value(i, SignalResolution_Position) :#信号精度SignalResolution 1else:SignalResolution remove_exponent(Decimal(Table.cell_value(i, SignalResolution_Position)).quantize(Decimal(0.00000)))if Table.cell_value(i, SignalOffset_Position) :#信号偏移SignalOffset 0else:SignalOffset remove_exponent(Decimal(Table.cell_value(i, SignalOffset_Position)).quantize(Decimal(0.00000)))#print(fSignalOffset:{SignalOffset}\n) if Table.cell_value(i, SignalOffset_Position) :SignalMinValue 0;else:HexValue str(Table.cell_value(i, SignalMinValue_Position))#信号最小值if HexValue.startswith(0x) or HexValue.startswith(0X):SignalMinValue int(HexValue, 16) # 将字符串转换为10进制整数else:SignalMinValue remove_exponent(Decimal(Table.cell_value(i, SignalMinValue_Position)).quantize(Decimal(0)))#print(fSignalMinValue:{SignalMinValue}\n) if Table.cell_value(i, SignalMaxValue_Position) :SignalMaxValue 0xFF;else: HexValue str(Table.cell_value(i, SignalMaxValue_Position))#信号最大值if HexValue.startswith(0x) or HexValue.startswith(0X):SignalMaxValue int(HexValue, 16) # 将字符串转换为10进制整数else:SignalMaxValue remove_exponent(Decimal(Table.cell_value(i, SignalMaxValue_Position)).quantize(Decimal(0))) #print(fSignalMaxValue:{SignalMaxValue}\n) if MessageTransmitterNote Note :f.write(f SG_ {SingleName} : {SignalStartBit}|{SignalLength}{SignalByteOrder}{SignalDateType} ({SignalResolution},{SignalOffset}) [{SignalMinValue}|{SignalMaxValue}] \{SignalUnit}\ Vector__XXX\n)else :f.write(f SG_ {SingleName} : {SignalStartBit}|{SignalLength}{SignalByteOrder}{SignalDateType} ({SignalResolution},{SignalOffset}) [{SignalMinValue}|{SignalMaxValue}] \{SignalUnit}\ {Note} \n) i i 1 #扫描下一行#print(fi:{i}\n)f.write(\n)def Output_DBC_Comment(): global Noteglobal OutputFile #输出文件with open(OutputFile, a, encodingGB2312) as f: f.write(
BA_DEF_ SG_ GenSigStartValue FLOAT 0 100000000000;
BA_DEF_ SG_ GenSigSendType ENUM Cyclic,OnWrite,OnWriteWithRepetition,OnChange,OnChangeWithRepetition,IfActive,IfActiveWithRepetition,NoSigSendType,NotUsed,NotUsed,NotUsed,NotUsed,NotUsed;
BA_DEF_ SG_ GenSigInactiveValue INT 0 100000;
BA_DEF_ SG_ NWM-WakeupAllowed ENUM No,Yes;
BA_DEF_ BO_ GenMsgSendType ENUM Cyclic,NotUsed,NotUsed,NotUsed,NotUsed,Cyclic,NotUsed,IfActive,NoMsgSendType,NotUsed,vector_leerstring;
BA_DEF_ BO_ GenMsgNrOfRepetition INT 0 999999;
BA_DEF_ BO_ GenMsgDelayTime INT 0 1000;
BA_DEF_ BO_ GenMsgCycleTime INT 0 50000;
BA_DEF_ BO_ GenMsgCycleTimeFast INT 0 50000;
BA_DEF_ BO_ GenMsgILSupport ENUM No,Yes;
BA_DEF_ BO_ NmMessage ENUM no,yes;
BA_DEF_ BO_ GenMsgStartDelayTime INT 0 65535;
BA_DEF_ BU_ NmStationAddress INT 0 63;
BA_DEF_ BU_ NmNode ENUM no,yes;
BA_DEF_ NmBaseAddress HEX 1024 1087;
BA_DEF_ BO_ CANFD_BRS ENUM 0,1;
BA_DEF_ DBName STRING ;
BA_DEF_ BusType STRING ;
BA_DEF_ BU_ NodeLayerModules STRING ;
BA_DEF_ BU_ ECU STRING ;
BA_DEF_ BU_ CANoeJitterMax INT 0 0;
BA_DEF_ BU_ CANoeJitterMin INT 0 0;
BA_DEF_ BU_ CANoeDrift INT 0 0;
BA_DEF_ BU_ CANoeStartDelay INT 0 0;
BA_DEF_ BO_ VFrameFormat ENUM StandardCAN,ExtendedCAN,reserved,reserved,reserved,reserved,reserved,reserved,reserved,reserved,reserved,reserved,reserved,reserved,StandardCAN_FD,ExtendedCAN_FD;
BA_DEF_DEF_ GenSigStartValue 0;
BA_DEF_DEF_ GenSigSendType Cyclic;
BA_DEF_DEF_ GenSigInactiveValue 0;
BA_DEF_DEF_ NWM-WakeupAllowed ;
BA_DEF_DEF_ GenMsgSendType NoMsgSendType;
BA_DEF_DEF_ GenMsgNrOfRepetition 0;
BA_DEF_DEF_ GenMsgDelayTime 0;
BA_DEF_DEF_ GenMsgCycleTime 0;
BA_DEF_DEF_ GenMsgCycleTimeFast 0;
BA_DEF_DEF_ GenMsgILSupport Yes;
BA_DEF_DEF_ NmMessage no;
BA_DEF_DEF_ GenMsgStartDelayTime 0;
BA_DEF_DEF_ NmStationAddress 0;
BA_DEF_DEF_ NmNode no;
BA_DEF_DEF_ NmBaseAddress 1024;
BA_DEF_DEF_ CANFD_BRS 1;
BA_DEF_DEF_ DBName ;
BA_DEF_DEF_ BusType ;
BA_DEF_DEF_ NodeLayerModules ;
BA_DEF_DEF_ ECU ;
BA_DEF_DEF_ CANoeJitterMax 0;
BA_DEF_DEF_ CANoeJitterMin 0;
BA_DEF_DEF_ CANoeDrift 0;
BA_DEF_DEF_ CANoeStartDelay 0;
BA_DEF_DEF_ VFrameFormat StandardCAN;
BA_ BusType CAN FD;
)f.write(fBA_ DBName \{Note}\;\n\n)def Output_SignalAttributes():#添加信号属性global Table #表格global RowCount #行数global OutputFile #输出文件with open(OutputFile, a, encodingutf-8) as f:#with open(OutputFile, a, encodingGB2312) as f: i 1 #跳过表头while i RowCount:#逐行轮询SingleName Table.cell_value(i, SignalName_Position)#信号名称#print(fSingleName:{SingleName}\n)MessageID int((Table.cell_value(i, MessageID_Position)), 16)# 报文ID十六进制if Table.cell_value(i, SingalInactiveValue_Position) ! :#信号未激活值SingalInactiveValue remove_exponent(Decimal(Table.cell_value(i, SingalInactiveValue_Position)).quantize(Decimal(0)))#print(fSingalInactiveValue:{SingalInactiveValue}\n)f.write(fBA_ \GenSigInactiveValue\ SG_ {MessageID} {SingleName} {SingalInactiveValue};\n)if Table.cell_value(i, SingalSendMethod_Position) ! :#信号发送方式SingalSendMethod Table.cell_value(i, SingalSendMethod_Position)#print(fSingalSendMethod:{SingalSendMethod}\n)if SingalSendMethod Cyclic:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 0;\n)elif SingalSendMethod OnWrite:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 1;\n)elif SingalSendMethod OnWriteWithRepetition:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 2;\n) elif SingalSendMethod OnChange:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 3;\n)elif SingalSendMethod OnChangeWithRepetition:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 4;\n)elif SingalSendMethod IfActive:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 5;\n)elif SingalSendMethod IfActiveWithRepetition:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 6;\n)else:f.write(fBA_ \GenSigSendType\ SG_ {MessageID} {SingleName} 0;\n)if Table.cell_value(i, SingalStartValue_Position) ! :#信号开始值SingalStartValue remove_exponent(Decimal(Table.cell_value(i, SingalStartValue_Position)).quantize(Decimal(0)))#print(fSingalStartValue:{SingalStartValue}\n)f.write(fBA_ \GenSigStartValue\ SG_ {MessageID} {SingleName} {SingalStartValue};\n)if Table.cell_value(i, SingalNWMWakeupAllowed_Position) ! :#信号网管唤醒序号SingalNWMWakeupAllowed Table.cell_value(i, SingalNWMWakeupAllowed_Position)#print(fSingalNWMWakeupAllowed:{SingalNWMWakeupAllowed}\n)if SingalNWMWakeupAllowed Yes or SingalNWMWakeupAllowed YES:f.write(fBA_ \NWM-WakeupAllowed\ SG_ {MessageID} {SingleName} 1;\n)else:f.write(fBA_ \NWM-WakeupAllowed\ SG_ {MessageID} {SingleName} 0;\n) i i 1def Output_SignalValue():#打印信号值定义global RowCount #行数global Table global OutputFile #输出文件with open(OutputFile, a, encodingutf-8) as f:i 1 #跳过表头while i RowCount:#逐行轮询if Table.cell_value(i, SignalName_Position) ! :MessageID int((Table.cell_value(i, MessageID_Position)), 16)if Table.cell_value(i, SignalValueDescriptions_Position) ! :SingleName Table.cell_value(i, SignalName_Position)SignalValueDescription Table.cell_value(i, SignalValueDescriptions_Position).strip().replace(\r, ).split(\n)SignalValueDescription list(reversed(SignalValueDescription))if SignalValueDescription ! []:#处理列表元素使其生成DBC文件格式中一样的格式ELE \ .join(SignalValueDescription).replace(0x,).replace(:, \)\f.write(fVAL_ {MessageID} {SingleName} {ELE} ;\n) i i 1 def Output_SignalComment():#添加信号注释global Table #表格global RowCount #行数global OutputFile #输出文件with open(OutputFile, a, encodingutf-8) as f:#信号注释不提倡添加中文注释#with open(OutputFile, a, encodingGB2312) as f: i 1 #跳过表头while i RowCount:#逐行轮询if Table.cell_value(i, SignalName_Position) ! :MessageID int((Table.cell_value(i, MessageID_Position)), 16)if Table.cell_value(i, SignalValueDescriptions_Position) ! :SingleName Table.cell_value(i, SignalName_Position)if Table.cell_value(i, SignalComment_Position) ! :#信号标注序号SignalComment Table.cell_value(i, SignalComment_Position).strip().replace(\n, ).replace(\r, )f.write(fCM_ SG_ {MessageID} {SingleName} \{SignalComment}\;\n)i i 1def Output_MessageAttribute():#打印报文属性global RowCount #行数global Tableglobal OutputFile #输出文件with open(OutputFile, a,encodingutf-8) as f:i 1 #跳过表头while i RowCount:#逐行轮询MessageID int((Table.cell_value(i, MessageID_Position)), 16)# 报文IDif Table.cell_value(i, MessageFormat_Position) ! :# 报文格式MessageFormat Table.cell_value(i, MessageFormat_Position)#print(fMessageFormat:{MessageFormat}\n)if MessageFormat CAN Standard: f.write(fBA_ \VFrameFormat\ BO_ {MessageID} 0;\n)elif MessageFormat CAN Extended: f.write(fBA_ \VFrameFormat\ BO_ {MessageID} 1;\n) elif MessageFormat CAN FD Standard: f.write(fBA_ \VFrameFormat\ BO_ {MessageID} 14;\n) elif MessageFormat CAN FD Extended: f.write(fBA_ \VFrameFormat\ BO_ {MessageID} 15;\n) else: f.write(fBA_ \VFrameFormat\ BO_ {MessageID} 0;\n) if Table.cell_value(i, MessageSendMethod_Position) ! :# 报文发送方式MessageSendMethod Table.cell_value(i, MessageSendMethod_Position)#print(fMessageSendMethod:{MessageSendMethod}\n)if MessageSendMethod Cyclic:f.write(fBA_ \GenMsgSendType\ BO_ {MessageID} 0;\n) elif MessageSendMethod IfActive: f.write(fBA_ \GenMsgSendType\ BO_ {MessageID} 7;\n) else:f.write(fBA_ \GenMsgSendType\ BO_ {MessageID} 0;\n) if Table.cell_value(i, MessagePeriod_Position) ! : MessagePeriod Table.cell_value(i, MessagePeriod_Position)if MessagePeriod ! Event:# 报文周期 MessagePeriod remove_exponent(Decimal(Table.cell_value(i, MessagePeriod_Position)).quantize(Decimal(0))) #print(fMessagePeriod:{MessagePeriod}\n)f.write(fBA_ \GenMsgCycleTime\ BO_ {MessageID} {MessagePeriod};\n) if Table.cell_value(i, MessagePeriodFast_Position) ! :#报文快速周期 MessagePeriodFast remove_exponent(Decimal(Table.cell_value(i, MessagePeriodFast_Position)).quantize(Decimal(0))) f.write(fBA_ \GenMsgCycleTimeFast\ BO_ {MessageID} {MessagePeriodFast};\n) if Table.cell_value(i, MessageDelayTime_Position) ! :#报文延迟时间MessageDelayTime remove_exponent(Decimal(Table.cell_value(i, MessageDelayTime_Position)).quantize(Decimal(0))) f.write(fBA_ \GenMsgDelayTime\ BO_ {MessageID} {MessageDelayTime};\n) if Table.cell_value(i, MessageILSupport_Position) ! :#报文支持IL序号MessageILSupport Table.cell_value(i, MessageILSupport_Position)if MessageILSupport Yes or MessageILSupport YES:f.write(fBA_ \GenMsgILSupport\ BO_ {MessageID} 1;\n) else:f.write(fBA_ \GenMsgILSupport\ BO_ {MessageID} 0;\n) if Table.cell_value(i, MessageNumOfRepetition_Position) ! :#报文重发次数MessageNumOfRepetition remove_exponent(Decimal(Table.cell_value(i, MessageNumOfRepetition_Position)).quantize(Decimal(0))) f.write(fBA_ \GenMsgNrOfRepetition\ BO_ {MessageID} {MessageNumOfRepetition};\n) if Table.cell_value(i, MessageStartDelayTime_Position) ! :#报文开始延迟时间MessageStartDelayTime remove_exponent(Decimal(Table.cell_value(i, MessageStartDelayTime_Position)).quantize(Decimal(0))) f.write(fBA_ \GenMsgStartDelayTime\ BO_ {MessageID} {MessageStartDelayTime};\n) if Table.cell_value(i, MessageNM_Position) ! :#报文网络管理MessageNM Table.cell_value(i, MessageNM_Position) if MessageNM Yes or MessageNM YES:f.write(fBA_ \NmMessage\ BO_ {MessageID} 1;\n) else:f.write(fBA_ \NmMessage\ BO_ {MessageID} 0;\n)if Table.cell_value(i, MessageCANFDBRS_Position) ! :#报文CANFDBRSMessageCANFDBRS Table.cell_value(i, MessageCANFDBRS_Position) if MessageCANFDBRS Yes or MessageCANFDBRS YES:f.write(fBA_ \CANFD_BRS\ BO_ {MessageID} 1;\n) else:f.write(fBA_ \CANFD_BRS\ BO_ {MessageID} 0;\n)i i 1def Output_MessageComment():#打印报文注释global Table #表格global RowCount #行数global OutputFile #输出文件with open(OutputFile, a, encodingGB2312) as f:#打印报文注释 i 1 #跳过表头while i RowCount:#逐行轮询MessageID int((Table.cell_value(i, MessageID_Position)), 16)# 报文IDMessageComment Table.cell_value(i, MessageComment_Position)# 报文注释if MessageComment ! :f.write(fCM_ BO_ {MessageID} \{MessageComment}\;\n) i i 1if __name__ __main__ :print(fStart)#打印的顺序不要乱不然会打不开文件Read_Base_Infomation()Output_DBC_Head()Output_MessageMappingSignal()Output_SignalComment()Output_MessageComment() Output_DBC_Comment() Output_SignalAttributes()Output_MessageAttribute()Output_SignalValue()print(fSuccess!!)
