Categories
Development

Binary to Decimal Implementation for different signals in CAN bus in python openpyxl

I am trying to implement a bits extractor for CAN bus data in Excel. I struggle with the algorithm part therefore this is mainly independent from Excel/openpyxl.

I have a table with the ID being stored in column B and the binary code per time step in column F. I want to parse through the binary numbers and extract the parts which contains the signals’ value. One ID can have one or more signals. Depending on the ID, I want to go through all the rows and convert the binary to decimal (as shown below). The IDs contain either 3, 4, 5, 6, 7 or 8 bytes, which is defined for each ID.

Input:

A  B  C D E F            G   H  .. M
.. 2E ....  1010100000..
...4A ...   0101010000.. 

Output:

A B  C D E F   G    H  .. M
..2E .... ...  672 
..4A .......   10  16

I have 3 main issues (that seem so big that I have broken down into smaller questions in another StackOverflow Question):

1) Openpyxl: Search for a HEX substring in cell and append the first zero's to the BIN string

2) For each signal, I need to add the following in order to calculate the decimal value of that signal: the resolution, the offset: CurrentDEC = (Dec_Value*Resolution)+Offset These parameters are different for each signal. I want to put the CurrentDEC value in column G, H, I (if there are three signals, otherwise only in col G if only 1 signal exists). I haven’t implemented the latter as I first want to calculate correct values before writing them in the table. The implementation of calculating the CurrentDEC with individual resolution and offset is only possible if I put the content of extract_k_bits(inputBIN,k,p) into each ID’s if-clause, which creates a big mess, as you can see here:

if cell.value == '28A':
    for cell in sheet["F"]:
        inputBIN= cell.value
        k=16
        p=11
        end = len(inputBIN) - p
        start = end - k + 1
        kBitSub = inputBIN[start : end+1]
        print(kBitSub)
        Dec_Values=int(kBitSub,2)
        print(Dec_Values*0.01)

Do you know another, more elegant way to do it?

3) The extract_k_bits() function is a standard function but still when I test it for a 6/7/8 byte long binary code it is not correct. When I have a 1 byte code it is little-endian but once I use a 8 byte code it is shifted. For one signal (of a 7byte code) it was shifted +7bits but another signal (another ID, 8byte code) was shifted by -21bits. I cannot explain this to myself so I thought of playing around and manually add or subtract bits in order to use the correct bits for calculation. Do you have any idea why this is happening?

This is the code I am working with:

import openpyxl
from openpyxl import Workbook

theFile = openpyxl.load_workbook('Adapted_T013.xlsx')
allSheetNames = theFile.sheetnames 
print("All sheet names {} " .format(theFile.sheetnames)) 
sheet = theFile.active
def convertHEXtoBIN():
    max_row_var = sheet.max_row+1
    for row in sheet.iter_rows(min_row=1, max_row=max_row_var,values_only = True): 
        for cell in sheet["E"]:
            if cell.value is not None:
                inputHEX = str(cell.value)
                res = "{0:08b}".format(int(inputHEX,16))
                sheet.cell(row=cell.row, column=6).value = res 
        break

def extract_k_bits(inputBIN,k,p):
    end = len(inputBIN) - p
    start = end - k + 1
    kBitSub = inputBIN[start : end+1]
    print(kBitSub)
    Dec_Values=int(kBitSub,2)

for row_values in range(1,sheet.max_row+1):
    for row in sheet.iter_rows(min_row=row_values, max_row=row_values):
        for cell in sheet["B"]:
            if cell.value =='2A':
                for cell in sheet["F"]:
                    #Signal A 
                    extract_k_bits(cell.value,16,0)
                    print(Dec_Values*0.041667)

                    #Signal B
                    extract_k_bits(cell.value,16,16)
                    print(Dec_Values*0.041667)

                    #Signal C
                    extract_k_bits(cell.value,16,32)
                    print(Dec_Values*0.01)

Leave a Reply

Your email address will not be published. Required fields are marked *