Python 读写.XML，EXCEL表格和.C文件

#Find index and subindex from  code and write them to excel

import os
import re
import xml.etree.ElementTree as ET
import xlsxwriter
import openpyxl
import datetime

def getFileList(rootDir, allFiles=[], isShow = False):
    files = os.listdir(rootDir)
    for file in files:
        path = rootDir + '/' +  file
        if not os.path.isdir(path):
            if path.endswith('.xml'):
                allFiles.append(path)
        else:
            getFileList(path, allFiles, False)
    if isShow:
        print('\n'.join(allFiles))
    return allFiles
 

def findAllId(xmlFiles):
    now=datetime.datetime.now()
    newFileRoot=ET.Element('parmeters', {'time':now.strftime("%Y-%m-%d %H:%M:%S")})
    newFileRoot.tail='\n'
    newFileTree=ET.ElementTree(newFileRoot)    
    newFileTree.write(SUMMARY_XML) 

    for eachFile in xmlFiles:
        fileName = os.path.basename(eachFile)
        tree = ET.parse(eachFile)
        root = tree.getroot()
        #print('root_tag:',root.tag)
        for item in root.findall('Id'):  
            if item.get('index'):
                item.set('file', fileName)
                item.tail='\n'                  
                newFileRoot.append(item)
                 
    newFileTree.write(SUMMARY_XML)  

def delDuplicateId(xmlFile):
    tree = ET.parse(xmlFile)    
    root = tree.getroot()

    for eachId in root.findall('Id'):
        IdVal = eachId.get('IdDef')
        matchPath = "*[@IdDef='" + IdVal + "']"
        thisIdList = root.findall(matchPath)
        for i in range(1,len(thisIdList)):
            root.remove(thisIdList[i])

    tree.write(xmlFile)    

def findAllXmlFiles(dirList=[]):
    fileList=[]    
    for each in dirList:
        fileList = getFileList(each, fileList)

    findAllId(fileList)
    #删除重复的条目
    delDuplicateId(SUMMARY_XML)

#Open  XML file and find index and subindex
def findIndex(xmlFile):
    work_dir = os.getcwd()
    filePath = work_dir+'/'+XLSX_FILE
    #print(filePath)
    excel_file = xlsxwriter.Workbook(filePath)
    excel_file.add_worksheet()
    excel_file.close()

    workbook = openpyxl.load_workbook(filePath)
    sheet = workbook['Sheet1']
    # total_column = sheet['A']  # 替换为你要查找最后一行的列字母
    # last_row = len(total_column)  # 找到列中的最后一行
    # print("最后一行是：", last_row)  

    var_row=1
    sheet.cell(row=var_row, column=1, value='IdDef')
    sheet.cell(row=var_row, column=2, value='index')
    sheet.cell(row=var_row, column=3, value='subIndex')
    sheet.cell(row=var_row, column=4, value='min')
    sheet.cell(row=var_row, column=5, value='max')
    sheet.cell(row=var_row, column=6, value='type')
    sheet.cell(row=var_row, column=7, value='file')
    sheet.cell(row=var_row, column=8, value='mdbus address')
    var_row += 1

    #print(xmlFile)
    tree = ET.parse(xmlFile)
    root = tree.getroot()
    #print('root_tag:',root.tag)
    for item in root:
        if item.tag == "Id":
            IdDef = item.attrib['IdDef']
            try:
                index = item.attrib['index']
            except:
                index = ''
            try:
                subIndex = item.attrib['subIndex']
            except:
                subIndex = '' 

            try:
                minVal = item.attrib['min']
            except:
                minVal = '' 

            try:
                maxVal = item.attrib['max']
            except:
                maxVal = '' 

            try:
                typeVal = item.attrib['type']
            except:
                typeVal = ''                                                 

            try:
                xmlFileName = item.attrib['file']
            except:
                xmlFileName = ''                    

            if  index != '':                    
                #print('IdDef:', IdDef, 'index:', index, 'subIndex:', subIndex)

                sheet.cell(row=var_row, column=1, value=IdDef)
                sheet.cell(row=var_row, column=2, value=str(index))
                sheet.cell(row=var_row, column=3, value=str(subIndex))
                sheet.cell(row=var_row, column=4, value=str(minVal))
                sheet.cell(row=var_row, column=5, value=str(maxVal))
                sheet.cell(row=var_row, column=6, value=str(typeVal))
                sheet.cell(row=var_row, column=7, value=xmlFileName)
                var_row += 1

    workbook.save(filePath)
    workbook.close()  # 关闭表        

def writeModbusAddrToExcel(CFile, excelFile):
    with open(CFile, "r") as file:
        new_file_read = file.read()
        file_lines = new_file_read.splitlines()
        find_lines = ['']
        for eachLine in file_lines:
            find_line = re.findall('{.*?[4][0-9]{4,4}.*?Id.*?},', eachLine, re.S)
            if len(find_line):
                find_lines.append(find_line[0])
        # for each in find_lines:
        #     print(each)
        #print(len(find_lines))

        workbook = openpyxl.load_workbook(excelFile)
        sheet = workbook['Sheet1']
        max_row = sheet.max_row

        findFailRows = []
        #第一遍找Id完全一致的
        for i in range(2,max_row+1):
            targetId = sheet[i][0].value
            #print(str(i) + ':' + targetId)
            for eachLine in find_lines:
                mb_addr = ''
                mb_Id = ''
                newLine = re.sub(r"\s+", "", eachLine)
                subStrList = re.split(r'[{,]', newLine)
                find_mb_addr = False
                for each in subStrList:
                    if find_mb_addr==False and each.startswith('4'):
                        #print(each) 
                        mb_addr = each
                        find_mb_addr = True

                    if find_mb_addr == True and each.startswith('Id'):
                        #print(each)
                        mb_Id = each
                        break

                #print(targetId)
                if mb_Id != '' and mb_Id==targetId:
                    sheet.cell(row=i, column=8, value=mb_addr)
                    break

            if sheet[i][7].value is None: 
                findFailRows.append(i)
                    


        #第二遍找Id不完全一致的，比如后缀新增了_0
        for i in findFailRows:
            targetId = sheet[i][0].value
            for eachLine in find_lines:
                mb_addr = ''
                mb_Id = ''
                newLine = re.sub(r"\s+", "", eachLine)
                subStrList = re.split(r'[{,]', newLine)
                find_mb_addr = False
                for each in subStrList:
                    if find_mb_addr==False and each.startswith('4'):
                        #print(each) 
                        mb_addr = each
                        find_mb_addr = True

                    if find_mb_addr == True and each.startswith('Id'):
                        #print(each)
                        mb_Id = each
                        break

                #print(targetId)
                if mb_Id != '' and targetId in mb_Id:
                    diff = mb_Id.replace(targetId, '')
                    if re.search('[a-zA-Z]', diff) is None:
                        sheet.cell(row=i, column=8, value=mb_addr)
                        sheet.cell(row=i, column=9, value=diff)
                    break                

        workbook.save(excelFile)
        workbook.close()  # 关闭表 

#End definition


    
#Start run from here 
print("找出XML文件中的index和subindex等属性值写入表格,然后根据某个属性值在C文件中搜索找到其他信息也添加到表格中.")

#XML文件项目路径
XML_PATH_1="D:/example1/"
XML_PATH_2="D:/example1/"

dir_list=[]
dir_list.append(XML_PATH_1)
dir_list.append(XML_PATH_2)

print('XML文件路径:')
for each in dir_list:
    print(each)

#把所有指定路径下的XML里面的参数定义合并为一个XML文件
SUMMARY_XML="ALL_PARMETERS.xml"
findAllXmlFiles(dir_list)

XLSX_FILE="result.xlsx"
findIndex(SUMMARY_XML)

C_FILE = '../addIndexTo/test.c'
print('C文件路径:')
print(C_FILE)

writeModbusAddrToExcel(C_FILE, XLSX_FILE)
work_dir = os.getcwd()
filePath = work_dir+'/'+XLSX_FILE
print('产生的EXCEL文件路径:')
print(filePath)
os.system(XLSX_FILE)