-- coding: utf-8 --

Reading an excel file using Python

import xlrd
import os
import re

def re_write_line(secoc, line_value, dict_ad_sec):
for line in line_value:
# print(line)
for id_dec in dict_ad_sec.keys(): # 遍历Key值 347
num = re.findall(r'(?<=BA_ "GenMsgCycleTime" BO_ )\d+', line) # 提取以字符串开头的数字，findall返回列表
# print('num = ', num)
if (not num) == 0:
# print('num = ', num)
if id_dec == num[0]:
# print('id_dec = ', id_dec)
secoc.writelines('BA_ "SC_Message" BO_ ' + id_dec + ' ' + '1' + ';\n')
#
# elif line == 'BA_ "SCP_AuthInfoTxLength" BO_ ' + id_dec + ' ' + '28' + ';\n': # default 28 to 48
# print(line)
# # SCP_AuthInfoTxLength = 48
secoc.writelines('BA_ "SCP_AuthInfoTxLength" BO_ ' + id_dec + ' ' + '48' + ';\n')
#
# elif line == 'BA_ "SCP_DataId" BO_ ' + id_dec + ' ' + '0;\n': # default 0
# # Data Id = id_dec, input from xl
secoc.writelines('BA_ "SCP_DataId" BO_ ' + id_dec + ' ' + id_dec + ';\n')
# #
# elif line == 'BA_ "SCP_FreshnessValueId" BO_ ' + id_dec + ' ' + '0' + ';\n': # default 0
# # FV Id = input id from xl
secoc.writelines('BA_ "SCP_FreshnessValueId" BO_ ' + id_dec + ' ' + dict_ad_sec[id_dec] + ';\n')
# #
# elif line == 'BA_ "SCP_FreshnessValueLength" BO_ ' + id_dec + ' ' + '48' + ';\n': # default 48 to 64
# # FV length = 64
secoc.writelines('BA_ "SCP_FreshnessValueLength" BO_ ' + id_dec + ' ' + '64' + ';\n')
#
# elif line == 'BA_ "SCP_FreshnessValueTxLength" BO_ ' + id_dec + ' ' + '4' + ';\n': # default 4 to 16
# # FV Tx length = 16
secoc.writelines('BA_ "SCP_FreshnessValueTxLength" BO_ ' + id_dec + ' ' + '16' + ';\n')

    secoc.writelines(line)

def read_sccan_secoc_list():
wb_secoc_et2 = xlrd.open_workbook(r'C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series'
r'\SecOC_script\SecOC_Message_ET2_Cusotmer.xlsx')
sheet_sccan = wb_secoc_et2.sheet_by_name('SC_CAN')
dict_sec = {}
for index_row in range(2, sheet_sccan.nrows):
message_id = sheet_sccan.cell_value(index_row, 6)[2:] # G column
# 先取出来是16进制字符串，转化成10进制整型，再转成字符串
message_id_dec = str(int(message_id, 16)) # base 是要转换数据的数据类型

    if sheet_sccan.cell_type(index_row, 8) == 0:  # I column
        fv_id = False
    else:
        fv_id = str(int(sheet_sccan.cell_value(index_row, 8)))
        print(message_id, fv_id, message_id_dec)
        dict_sec[message_id_dec] = fv_id

print(dict_sec)

with open(r"C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series\SecOC_script\DBC\V71-APTIV_SC-CANFD__V2.2.dbc", "r") as ad:
    with open(r"C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series\SecOC_script"
                  r"\DBC_SecOc\V71-APTIV_SC-CANFD__V2.2_SecOC.dbc", "w") as sc_SecOC:
        # line_value = SC.readline()  # 返回字符串对象
        line_value = ad.readlines()  # 返回字符串内容列表,包括换行符
        re_write_line(secoc=sc_SecOC, line_value=line_value, dict_ad_sec=dict_sec)

def read_adcan_secoc_list():
wbad_secoc_et2 = xlrd.open_workbook(r'C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series'
r'\SecOC_script\SecOC_Message_ET2_Cusotmer.xlsx')
sheet_sccan = wbad_secoc_et2.sheet_by_name('AD_CAN')
dict_sec = {}
for index_row in range(2, sheet_sccan.nrows):
message_id = sheet_sccan.cell_value(index_row, 6)[2:] # G column
# 先取出来是16进制字符串，转化成10进制整型，再转成字符串
message_id_dec = str(int(message_id, 16)) # base 是要转换数据的数据类型

    if sheet_sccan.cell_type(index_row, 8) == 0:  # I column
        fv_id = False
    else:
        fv_id = str(int(sheet_sccan.cell_value(index_row, 8)))  # 原格式为浮点型，转为整型，再转为字符串
        print(message_id, fv_id, message_id_dec)
        dict_sec[message_id_dec] = fv_id

print(dict_sec)

with open(r"C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series\SecOC_script\DBC\V71-APTIV_ADAS-CANFD__V2.2.dbc", "r") as ad:
    with open(r"C:\01_Work\01_Project\11_V3_GW\02_V3_V_Series\SecOC_script"
              r"\DBC_SecOc\V71-APTIV_ADAS-CANFD__V2.2_SecOC.dbc", "w") as sc_SecOC:
        # line_value = SC.readline()  # 返回字符串对象
        line_value = ad.readlines()  # 返回字符串内容列表,包括换行符
        re_write_line(secoc=sc_SecOC, line_value=line_value, dict_ad_sec=dict_sec)

if name == 'main':

print('SCCAN SecOC message')
read_sccan_secoc_list()
print('ADCAN SecOC message')
read_adcan_secoc_list()