python解析Excel并转存为.h文件

     废话不多说，本次分享自己最近写的代码，主要是用python解析定义好的结构体数据表格，并转为C++头文件:

'''
Author: solitary
Date: 2023-01-17 17:50:08
LastEditTime: 2023-01-30 11:25:51
LastEditors: your name
Description: 转换excel表格为struct文件
FilePath: /src/parse_excel/excelTocpp.py
'''
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string, get_column_letter
import os, sys
import string

def get_all_struct_mems(struct_dict_, wb:Workbook, sheet_title):
    print(sheet_title)
    ws = wb[sheet_title]
    weidu_col = 0
    bianliang_col = 0
    leixing_col = 0
    #找到"维度"字段所在列的index
    for col in ws.iter_rows(min_row=1, max_row=1, min_col=1):
        for cell in col:
            weidu_col = weidu_col + 1
            if(cell.value == "维度"):
                break
    #找到"变量类型"字段所在列的index
    for col in ws.iter_rows(min_row=1, max_row=1, min_col=1):
        for cell in col:
            leixing_col = leixing_col + 1
            if(cell.value == "变量类型"):
                break
    #找到"变量名"字段所在列的index
    for col in ws.iter_rows(min_row=1, max_row=1, min_col=1):
        for cell in col:
            bianliang_col = bianliang_col + 1
            if(cell.value == "变量名"):
                break
    weidu_col_index = column_index_from_string(get_column_letter(weidu_col))
    bianliang_col_index = column_index_from_string(get_column_letter(bianliang_col))
    leixing_col_index = column_index_from_string(get_column_letter(leixing_col))
    #遍历所有的合并单元格
    for merged_range in ws.merged_cell_ranges:
        #只保存第一列的合并列表数据
        if merged_range.min_col == 1:
            #存储结构体成员名称和类型名称的字典
            merged_struct_mems_dict = {}
            #存储合并单元格的起止坐标
            for rowx in range(merged_range.min_row, merged_range.max_row + 1):
                if(int(ws.cell(row = rowx, column = weidu_col_index).value) > 1):
                    items = ws.cell(row = rowx, column = leixing_col_index).value.split('[')
                    value = items[0]
                    key = ws.cell(row = rowx, column = bianliang_col_index).value + "[" + items[1]
                else:
                    key = ws.cell(row = rowx, column = bianliang_col_index).value
                    value = ws.cell(row = rowx, column = leixing_col_index).value

                #存储struct的成员信息. 以变量名为key,变量类型为value.
                merged_struct_mems_dict[key] = value
                #以单元格名称为key，以合并单元格的起止坐标为value组成字典
                struct_dict_[ws.cell(row = merged_range.min_row, 
                        column = merged_range.min_col).value] = merged_struct_mems_dict


def write_struct_to_file(struct_dict):
    #表格里的数据类型到C++的映射
    type_transform_dict = { "int8" : "int8_t",
                            "int32" : "int32_t",
                            "int64" : "int64_t",
                            "float32" : "float", 
                            "int16" : "int16_t",
                            "float64" : "double"}
    fd = os.open("out/structs.h", os.O_RDWR | os.O_CREAT)   
    signal_n = 0
    struct_n = 0
    ss = "#pragma once\n#include <iostream>\n"
    for struct_name, struct_mems in struct_dict.items():
        struct_n += 1
        ss += "\ntypedef struct\n{"
        for mem_name, mem_type in struct_mems.items():
            signal_n += 1
            # print(struct_name,mem_type,mem_name)
            ss += "\n\t%s %s;" % (mem_type.replace(" ",""), mem_name.replace(" ",""))
        ss += "\n}%s;\n" % struct_name.replace("[","").replace("]","")
        
    for excel_type, cxx_type in type_transform_dict.items():
        print(excel_type, cxx_type)
        ss = ss.replace(excel_type, cxx_type)

    print(ss)
    os.write(fd, ss.encode())   #str→bytes : encode() bytes→str: decode()
    os.close(fd)
    print("========= 转换完成! 共 %d 个结构体，%d 个信号 =========" % (struct_n, signal_n))
        
        
if __name__ == "__main__":

    '''
    @ struct_dict_:
    存储所有struct的字典. eg: 
    {'VehicleBodyButtonInfo':{'cruise_control_on': 'uint8'},{'cruise_control_off': 'uint8'} ...}
            |                         |               |
         结构体名称                  成员名称         成员类型 
    '''
    struct_dict_ = {} 

    #加载需要读取的表格
    wb = load_workbook("excel/DataType定义模板.xlsx")

    #解析表格获取所有结构体字典集
    for sheet_tilte in wb.sheetnames:
        get_all_struct_mems(struct_dict_, wb, sheet_tilte)
        # get_all_merged_cell(merged_dict, wb, sheet_tilte)
        
    print(struct_dict_)

    #把从表格中提取好的结构体写入文件
    write_struct_to_file(struct_dict_)
    

解析的表格定义示例:

结构名称	周期	变量类型	带宽(Byte)	变量名	信号值描述	初始值	单位	最小值	最大值	说明	维度
People		header	8+4*4	header							1
		uint8	1	age							1
		int16[100]	2*100	money							100
		uint32[4]	4*4	second							4
header		uint64	8	eye		0					1
		uint32	4	hair		0					1
		uint32	4	hand		0					1
		uint32	4	face		0					1
		uint32	4	finger		0					1

 

代码运行结果示例 (struct.h): 

#pragma once
#include <iostream>

typedef struct
{
	header header;
	uint8_t age;
	int16_t money[100];
	uint32_t second[4];
}People;

typedef struct
{
	uint64_t eye;
	uint32_t hair;
	uint32_t hand;
	uint32_t face;
	uint32_t finger;
}header;

觉得有用点个支持吧，回见！