DBC文件解析写入Excel问题：SG_信号无法逐行写入B列

Got it, let's sort out why only the last signal ends up in your Excel file, and get both SG_ signals and VAL_ data written correctly.

First, let's break down the issues in your original code:

• You're re-creating the workbook every time: Every time you hit an SG_ or VAL_ line, you initialize a new Workbook() instance. This wipes out all previous data, so only the last operation's content gets saved.
• Incorrect signal writing logic: When you use enumerate(n) where n is a single signal name, you're iterating over each character in the name (e.g., splitting "VehicleSpeed" into V, e, h, etc.) instead of writing the full signal as one row.
• Broken VAL_ handling: Same workbook-overwrite problem, plus you're writing variable names and value table entries to the same column, which causes unnecessary overwrites.

Here's the corrected approach: #

1. Parse the entire DBC file first to collect all signals and their associated value tables.
2. Create the workbook and worksheet once at the start (not inside loops).
3. Write all collected signals to column B, then map VAL_ entries to their corresponding signals.

Corrected Code #

import re
from openpyxl import Workbook

# Step 1: Parse the DBC file and collect all relevant data
signal_list = []
signal_value_maps = {}

with open('car.dbc', 'r') as rf:
    for line in rf:
        line = line.strip()
        if not line:
            continue
        
        # Handle SG_ signal lines
        if line.startswith('SG_'):
            # Split line using regex to handle multiple spaces reliably
            tokens = re.split(r'\s+', line)
            signal_name = tokens[1]
            signal_list.append(signal_name)
        
        # Handle VAL_ value table lines
        elif line.startswith('VAL_'):
            tokens = re.split(r'\s+', line)
            target_signal = tokens[2]
            # Extract value-label pairs (ignore the trailing semicolon)
            value_entries = tokens[3:-1]
            # Organize into a dictionary of {value: label}
            value_map = {}
            for i in range(0, len(value_entries), 2):
                value = value_entries[i]
                label = value_entries[i+1].strip('"')
                value_map[value] = label
            signal_value_maps[target_signal] = value_map

# Step 2: Write collected data to Excel
wb = Workbook()
ws = wb.active

# Add headers for clarity
ws['B1'] = 'SG_ Signal Names'
ws['C1'] = 'Value Table Values'
ws['D1'] = 'Value Table Labels'

# Write signals to column B
for row_num, signal in enumerate(signal_list, start=2):  # Start at row 2 (after header)
    ws[f'B{row_num}'] = signal
    # If the signal has a value table, write it to adjacent columns
    if signal in signal_value_maps:
        value_table = signal_value_maps[signal]
        offset = 0
        for val, label in value_table.items():
            ws[f'C{row_num + offset}'] = val
            ws[f'D{row_num + offset}'] = label
            offset += 1

# Save the final workbook
wb.save("dbc_signals.xlsx")

Key Improvements: #

• Single workbook setup: No more overwriting data—we create the workbook once and add all content to it.
• Clean signal collection: We gather all SG_ signals in a list first, then write them to column B in one pass.
• Linked VAL_ data: Value tables are mapped directly to their parent signals, so you can clearly see which labels belong to which signal.
• Robust parsing: Using re.split(r'\s+') handles multiple spaces in DBC lines, which is more reliable than splitting on single spaces.

内容的提问来源于stack exchange，提问作者Venkatesh Kaduru