vbCrLf

Cr = Carriage-Return = 回车 = \r = \13 = CHR$(13)

Lf =  Line-Feed = 换行  = \n = \10 = CHR$(10)

 

vbCrLf = "\r\n"

REF: https://en.wikipedia.org/wiki/Newline

 

转载于:https://www.cnblogs.com/jiceberg420/p/10767977.html

对于EB报文的解析是根据CANID为0x1CECF456进行判断的,如果该ID的DATA(如:11 02 01 FF FF 00 06 00 )的第一字节为11,则说明要发送EB类型的报文了,根据第二字节如07 则说明要发送7帧EB类型的报文,这时候就需要根据DATA的第六-八字节的三个字节,如00 02 00和00 06 00的区分解析,具体根据下列代码进行分析和改写: ’ 在模块顶部声明全局变量 Dim lastPGN As String ’ 存储最近EC帧的PGN(3字节) Dim lastCANID As String ’ 存储最近EC帧的CANID Sub 充电信号解析_27930() Dim ws As Worksheet Dim lastRow As Long Dim currentRow As Long Dim canID As String Dim canData As String Dim idBytes() As String Dim dataBytes() As String Dim i As Integer ’ 设置工作表 Set ws = ActiveSheet ’ 获取最后一行数据行号 lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row ’ 检查是否有数据(从第2行开始) If lastRow < 2 Then MsgBox “没有找到数据!请确保从第2行开始输入数据。”, vbExclamation Exit Sub End If ’ 设置标题 With ws.Range(“C1:E1”) .Value = Array(“CAN ID解析”, “DATA解析”, “备注”) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Font.Bold = True End With ’ 设置列宽 ws.Columns(“C:C”).ColumnWidth = 35 ws.Columns(“D:D”).ColumnWidth = 35 ws.Columns(“E:E”).ColumnWidth = 15 ’ 设置整体对齐方式 With ws.Range(“C2:E” & lastRow) .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True End With ’ 在遍历数据行的循环前初始化PGN存储变量 lastPGNSecondByte = “” ’ 遍历数据行 For currentRow = 2 To lastRow ’ 清除旧结果 With ws.Range(“C” & currentRow & “:E” & currentRow) .ClearContents .ClearFormats End With ' 获取CAN ID和DATA canID = Trim(ws.Cells(currentRow, 1).Value) canData = Trim(ws.Cells(currentRow, 2).Value) ' 跳过空行 If canID = "" Or canData = "" Then GoTo NextRow End If ' ========== CAN ID解析 ========== ' 移除0x前缀和空格 canID = Replace(canID, "0x", "") canID = Replace(canID, " ", "") ' 检查CAN ID长度 If Len(canID) <> 8 Then ws.Cells(currentRow, 3).Value = "错误: CAN ID应为4字节(8字符)" GoTo NextRow End If ' 分割CAN ID字节 ReDim idBytes(3) For i = 0 To 3 idBytes(i) = Mid(canID, i * 2 + 1, 2) Next i ' 解析CAN ID Dim idResult As String idResult = "" ' 1. 解析优先级(直接映射) Select Case idBytes(0) Case "18" idResult = idResult & "0x" & idBytes(0) & " → 优先级: 6" & vbCrLf Case "10" idResult = idResult & "0x" & idBytes(0) & " → 优先级: 4" & vbCrLf Case "08" idResult = idResult & "0x" & idBytes(0) & " → 优先级: 2" & vbCrLf Case "1C" idResult = idResult & "0x" & idBytes(0) & " → 请求发送多包报文" & vbCrLf Case Else idResult = idResult & "0x" & idBytes(0) & " → 优先级: 未知" & vbCrLf End Select ' 2. 解析功能码(第二个字节) Select Case idBytes(1) Case "26" idResult = idResult & "0x" & idBytes(1) & " → 功能码: CHM(充电机握手信号)" & vbCrLf Case "27" idResult = idResult & "0x" & idBytes(1) & " → 功能码: BHM(车端握手信号)" & vbCrLf Case "01" idResult = idResult & "0x" & idBytes(1) & " → 功能码: CRM(充电辨识报文)" & vbCrLf Case "EC" idResult = idResult & "0x" & idBytes(1) & " → 功能码: 信息交互ing......" & vbCrLf Case "EB" idResult = idResult & "0x" & idBytes(1) & " → 功能码: BMS多包报文数据传输中)" & vbCrLf Case Else idResult = idResult & "0x" & idBytes(1) & " → 未知功能码" & vbCrLf End Select ' 3. 解析源地址(第三个字节) Select Case idBytes(2) Case "56" idResult = idResult & "0x" & idBytes(2) & " → 源地址: 充电机" & vbCrLf Case "F4" idResult = idResult & "0x" & idBytes(2) & " → 源地址: 车端" & vbCrLf Case Else idResult = idResult & "0x" & idBytes(2) & " → 未知源地址" & vbCrLf End Select ' 4. 解析目标地址(第四个字节) Select Case idBytes(3) Case "56" idResult = idResult & "0x" & idBytes(3) & " → 目标地址: 充电机" & vbCrLf Case "F4" idResult = idResult & "0x" & idBytes(3) & " → 目标地址: 车端" & vbCrLf Case Else idResult = idResult & "0x" & idBytes(3) & " → 未知目标地址" & vbCrLf End Select ' ========== DATA解析 ========== Dim dataResult As String dataResult = "" ' 移除DATA中的空格 canData = Replace(canData, " ", "") ' 根据功能码进行不同解析 Select Case idBytes(1) Case "26" ' CHM功能码 ' 检查DATA长度 If Len(canData) < 6 Then dataResult = dataResult & "错误: CHM需要至少3字节数据" GoTo SaveResults End If ' 分割DATA字节(取前3字节) ReDim dataBytes(2) For i = 0 To 2 dataBytes(i) = Mid(canData, i * 2 + 1, 2) Next i ' 协议版本解析 Dim majorVer As Integer Dim minorVer As Integer majorVer = CInt("&H" & dataBytes(0)) minorVer = CInt("&H" & dataBytes(1)) dataResult = dataResult & "协议版本: V" & majorVer & "." & minorVer & vbCrLf ws.Cells(currentRow, 5).Value = "V" & majorVer & "." & minorVer ' 状态解析 If dataBytes(2) = "00" Then dataResult = dataResult & "状态: 正常" Else dataResult = dataResult & "状态: 异常(代码0x" & dataBytes(2) & ")" End If Case "27" ' BHM功能码 ' 检查DATA长度 If Len(canData) < 4 Then dataResult = dataResult & "错误: BHM需要至少2字节数据" GoTo SaveResults End If ' 分割DATA字节(取前2字节) ReDim dataBytes(1) For i = 0 To 1 dataBytes(i) = Mid(canData, i * 2 + 1, 2) Next i ' 计算充电电压(小端序) Dim voltageHex As String Dim voltageDec As Long Dim voltageValue As Single voltageHex = dataBytes(1) & dataBytes(0) ' 低字节在前(43 E0 → 0xE043) voltageDec = CLng("&H" & voltageHex) voltageValue = voltageDec * 0.1 ' 分辨率0.1 dataResult = dataResult & "最高允许充电电压:" & vbCrLf dataResult = dataResult & "原始值: 0x" & voltageHex & " (" & voltageDec & ")" & vbCrLf dataResult = dataResult & "计算值: " & Format(voltageValue, "0.0") & " V" ws.Cells(currentRow, 5).Value = voltageValue & " V" Case "01" ' CRM功能码 ' 检查DATA长度 If Len(canData) < 10 Then ' 至少需要5字节(00 + 4字节编号) dataResult = dataResult & "错误: CRM需要至少5字节数据" GoTo SaveResults End If ' 分割DATA字节 ReDim dataBytes(7) ' 包含所有8字节 For i = 0 To 7 dataBytes(i) = Mid(canData, i * 2 + 1, 2) Next i ' 状态解析 Select Case dataBytes(0) Case "00" dataResult = dataResult & "状态: 首次发送/未收到回复" & vbCrLf Case Else dataResult = dataResult & "状态: 未知状态(代码0x" & dataBytes(0) & ")" & vbCrLf End Select ' 充电机编号解析(小端序转换) Dim chargerIDHex As String Dim chargerIDDec As Long chargerIDHex = dataBytes(4) & dataBytes(3) & dataBytes(2) & dataBytes(1) ' 反转字节序 chargerIDDec = CLng("&H" & chargerIDHex) dataResult = dataResult & "充电机编号: " & vbCrLf dataResult = dataResult & "原始数据: " & Join(Array(dataBytes(1), dataBytes(2), dataBytes(3), dataBytes(4)), " ") & vbCrLf dataResult = dataResult & "转换结果: " & chargerIDDec & " (0x" & chargerIDHex & ")" Case "EC" ' 信息交互中 canData = Replace(canData, " ", "") ' 检查最小数据长度 If Len(canData) < 16 Then ' 8字节*2=16字符 dataResult = dataResult & "错误: EC需要完整8字节数据" GoTo SaveResults End If ' 分割DATA字节 ReDim dataBytes(7) For i = 0 To 7 dataBytes(i) = Mid(canData, i * 2 + 1, 2) Next i ' 解析控制节 Select Case dataBytes(0) Case "10" Dim pgnStage As String dataResult = dataResult & "控制节类型: 10(传输请求)" & vbCrLf ' 解析数据长度(小端模式) Dim dataLength As Long dataLength = CLng("&H" & dataBytes(2) & dataBytes(1)) dataResult = dataResult & "需接收字节长度: " & dataLength & vbCrLf ' 解析包数量 dataResult = dataResult & "接收包数量: " & CInt("&H" & dataBytes(3)) & vbCrLf Case "11" dataResult = dataResult & "控制节类型: 11(传输响应)" & vbCrLf ' 解析接收能力 dataResult = dataResult & "可接收包数: " & CInt("&H" & dataBytes(1)) & vbCrLf ' 解析下一个包编号 dataResult = dataResult & "即将接收包编号: " & CInt("&H" & dataBytes(2)) & vbCrLf Case "13" dataResult = dataResult & "控制节类型: 13(传输状态反馈)" & vbCrLf ' 解析已接收字节长度(小端模式) Dim receivedLength As Long receivedLength = CLng("&H" & dataBytes(2) & dataBytes(1)) dataResult = dataResult & "已接收字节长度: " & receivedLength & vbCrLf ' 解析已接收包数量 Dim receivedPackets As Integer receivedPackets = CInt("&H" & dataBytes(3)) dataResult = dataResult & "已接收包数量: " & receivedPackets & vbCrLf ' 解析预留字段 dataResult = dataResult & "预留字段: 0x" & dataBytes(4) & vbCrLf Case "AA" ' 新增控制节类型 dataResult = dataResult & "控制节类型: AA(BSM识别成功)" & vbCrLf ' 验证数据长度 If Len(canData) < 8 Then dataResult = dataResult & "错误: 需要4字节充电机编号" GoTo SaveResults End If ' 解析充电机编号(小端序转换) Dim chargerIDBytes(3) As String For i = 0 To 3 chargerIDBytes(i) = Mid(canData, i * 2 + 1, 2) Next i chargerIDHex = chargerIDBytes(3) & chargerIDBytes(2) & chargerIDBytes(1) & chargerIDBytes(0) chargerIDDec = CLng("&H" & chargerIDHex) dataResult = dataResult & "充电机编号: " & vbCrLf dataResult = dataResult & "原始数据: " & Join(chargerIDBytes, " ") & vbCrLf dataResult = dataResult & "转换结果: " & Format(chargerIDDec, "#,##0") & " (0x" & chargerIDHex & ")" If Len(canData) >= 16 Then dataResult = dataResult & vbCrLf & "区域信息: " & Mid(canData, 9, 6) & " (填充值)" End If Case Else ' 记录PGN信息(5-7字节组成PGN) lastPGN = dataBytes(5) & dataBytes(6) & dataBytes(7) lastCANID = canID ' 记录当前CANID dataResult = dataResult & "未知控制节类型: " & dataBytes(0) End Select Case "EB" ' EB功能码 canData = Replace(canData, " ", "") If Len(canData) < 2 Then dataResult = dataResult & "错误: EB需要至少1字节数据" GoTo SaveResults End If ' 检查前一帧是否符合条件 If lastCANID = "1CECF456" And lastPGN = "000200" Then Dim packetNumber As String ' 获取包号(第一个字节) packetNumber = Left(canData, 2) canData = Mid(canData, 3) ' 移除包号字节 Select Case packetNumber Case "01" ' 第一包 If Len(canData) < 14 Then ' 需要7字节数据 dataResult = dataResult & "错误: 01包需要7字节数据" GoTo SaveResults End If ' 协议版本解析 dataResult = dataResult & "【第一包】" & vbCrLf dataResult = dataResult & "协议版本: V" & CInt("&H" & Mid(canData, 1, 2)) & "." & CInt("&H" & Mid(canData, 3, 2)) & vbCrLf ' 电池类型 Select Case Mid(canData, 7, 2) Case "06": dataResult = dataResult & "电池类型: 三元材料电池" & vbCrLf Case Else: dataResult = dataResult & "电池类型: 未知(0x" & Mid(canData, 5, 2) & ")" & vbCrLf End Select ' 蓄电池容量(小端序) Dim capacity As Long capacity = CLng("&H" & Mid(canData, 11, 2) & Mid(canData, 9, 2)) dataResult = dataResult & "额定容量: " & capacity * 0.1 & " Ah" & vbCrLf ' 衔接标志 'dataResult = dataResult & "衔接标志: 0x" & Mid(canData, 13, 2) Case "02" ' 第二包 dataResult = dataResult & "【第二包】" & vbCrLf ' 额定电压(小端序:430E → 0x0E43) Dim voltage As Long voltage = CLng("&H" & Mid(canData, 1, 2) & Mid(canData, 3, 2)) dataResult = dataResult & "额定电压: " & voltage * 0.1 & " V" & vbCrLf ' 厂商名称(4字节转十进制) Dim manufacturer As Long manufacturer = CLng("&H" & Mid(canData, 5, 2) & Mid(canData, 7, 2) & Mid(canData, 9, 2) & Mid(canData, 11, 2)) dataResult = dataResult & "厂商编码: " & manufacturer & vbCrLf ' 衔接标志 'dataResult = dataResult & "衔接标志: 0x" & Mid(canData, 11, 2) & " 0x" & Mid(canData, 13, 2) Case "03" ' 第三包 dataResult = dataResult & "【第三包】" & vbCrLf ' 电池组序号(4字节) Dim batteryGroupID As Long batteryGroupID = CLng("&H" & Mid(canData, 1, 8)) dataResult = dataResult & "电池组序号: " & batteryGroupID & vbCrLf ' 生产日期解析 Dim year As Integer, month As Integer, day As Integer year = 1985 + CInt("&H" & Mid(canData, 9, 2)) month = CInt("&H" & Mid(canData, 11, 2)) day = CInt("&H" & Mid(canData, 13, 2)) dataResult = dataResult & "生产日期: " & year & "-" & Format(month, "00") & "-" & Format(day, "00") Case "04" ' 第四包 dataResult = dataResult & "【第四包】" & vbCrLf ' 充电次数(3字节) Dim chargeCount As Long chargeCount = CLng("&H" & Mid(canData, 1, 6)) dataResult = dataResult & "充电次数: " & chargeCount & vbCrLf ' 产权标识 Select Case Mid(canData, 7, 2) Case "00": dataResult = dataResult & "产权: 租赁" Case "01": dataResult = dataResult & "产权: 自有" Case Else: dataResult = dataResult & "产权: 未知标识" End Select Case "05", "06", "07" ' 第五、六、七包 dataResult = dataResult & "【第" & CInt("&H" & packetNumber) & "包】" & vbCrLf ' VIN/BMS版本解析(ASCII转换) Dim asciiStr As String For i = 1 To Len(canData) Step 2 asciiStr = asciiStr & Chr("&H" & Mid(canData, i, 2)) Next i dataResult = dataResult & "信息内容: " & Replace(asciiStr, Chr(0), "") ' 去除空字符 Case Else dataResult = dataResult & "未知包号: 0x" & packetNumber End Select ElseIf lastCANID = "1CECF456" And lastPGN = "000600" Then ' 新增00 06 00 PGN解析逻辑 Dim packetType As String packetType = Left(canData, 2) canData = Mid(canData, 3) Select Case packetType Case "01" ' 第一包 If Len(canData) < 12 Then dataResult = "错误: 第一包需要6字节有效数据" GoTo SaveResults End If dataResult = dataResult & "【充电配置阶段-第一包】" & vbCrLf ' 解析单体电压(小端序) Dim cellVoltage As Long cellVoltage = CLng("&H" & Mid(canData, 3, 2) & Mid(canData, 1, 2)) dataResult = dataResult & "单体最高电压: " & Format(cellVoltage * 0.01, "0.00") & "V" & vbCrLf ' 解析充电电流(小端序) Dim chargeCurrent As Long chargeCurrent = CLng("&H" & Mid(canData, 7, 2) & Mid(canData, 5, 2)) dataResult = dataResult & "最大充电电流: " & (chargeCurrent * 0.1 - 400) & "A" & vbCrLf ' 解析总能量(小端序) Dim totalEnergy As Long totalEnergy = CLng("&H" & Mid(canData, 11, 2) & Mid(canData, 9, 2)) dataResult = dataResult & "蓄电池总能量: " & Format(totalEnergy * 0.1, "0.0") & "kWh" Case "02" ' 第二包 If Len(canData) < 12 Then dataResult = "错误: 第二包需要6字节有效数据" GoTo SaveResults End If dataResult = dataResult & "【充电配置阶段-第二包】" & vbCrLf ' 解析总电压(小端序) Dim totalVoltage As Long totalVoltage = CLng("&H" & Mid(canData, 3, 2) & Mid(canData, 1, 2)) dataResult = dataResult & "系统最高电压: " & Format(totalVoltage * 0.1, "0.0") & "V" & vbCrLf ' 解析温度 Dim maxTemp As Integer maxTemp = CInt("&H" & Mid(canData, 5, 2)) dataResult = dataResult & "最高温度: " & (maxTemp - 50) & "℃" & vbCrLf ' 解析SOC(小端序) Dim soc As Integer soc = CInt("&H" & Mid(canData, 7, 2) & Mid(canData, 5, 2)) dataResult = dataResult & "SOC: " & Format(soc * 0.1, "0.0") & "%" & vbCrLf ' 解析当前电压(小端序) Dim currentVoltage As Long currentVoltage = CLng("&H" & Mid(canData, 11, 2) & Mid(canData, 9, 2)) dataResult = dataResult & "当前电压: " & Format(currentVoltage * 0.1, "0.0") & "V" Case Else dataResult = dataResult & "未知包类型: " & packetType End Select Else dataResult = dataResult & "未识别的PGN配置: " & lastPGN End If Case Else dataResult = dataResult & "当前功能码无定义的数据解析规则" End Select SaveResults: ’ 写入解析结果并设置格式 With ws.Cells(currentRow, 3) .Value = idResult .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True End With With ws.Cells(currentRow, 4) .Value = dataResult .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True End With NextRow: Next currentRow ’ 自动调整行高以适应内容 ws.Rows(“2:” & lastRow).AutoFit ’ 完成提示 MsgBox “CAN数据解析完成! 共分析了 " & (lastRow - 1) & " 条记录。”, vbInformation, “完成” End Sub
最新发布
05-14
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值