对于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
最新发布