[Python知识库]python读取excel生成.h

?执行python读取ttt.xlsx文件，拼成结构体等头文件。

#! /usr/bin/env python
#coding=utf-8
import os,sys,re,traceback
from openpyxl import load_workbook
from datetime import datetime
from string import Template

class Generator:
    def findStr(self, str, keyvalue):
        item = '0'
        if str in keyvalue.keys():
            item = keyvalue[str][0]
        return item

    def findValueRange(self, str, keyvalue):
        item = []
        for k in keyvalue:
            if str in k:
                item.append(keyvalue[k][0])
        return list(set(item))

    def generate(self):
        tableName = 'Test'
        className = '%sAuto' %  tableName.capitalize()
        filePath = r'%s.h' % className
        class_file = open(filePath,'w')

        # header
        lines = []
        template_file = open(r'Header.template','r', encoding='UTF-8')
        tmpl = Template(template_file.read())
        lines.append(tmpl.substitute(
            PRIMER_KEY= 'test',
            CLASSNAME=className,
            TABLE_NAME=tableName,
            TABLE_NAME_UPPER=tableName.upper(),
            GENE_DATE=datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            ))
        class_file.writelines(lines)

        # event property
        for k, v in self.excelData.items():
            lines = []
            find_result = Generator.findStr(self, 'max ok size', v)
            maxOkSize = (find_result if find_result !=
                           '0' else Generator.findStr(self, 'max size', v))
            lines = "    { 0x" + str(k) + ", " + str(find_result if find_result !=
                '0' else Generator.findStr(self, 'max size', v)) + ", " + str(maxOkSize) + ", "+ str(Generator.findStr(self, 'max fail size', v))
            class_file.writelines(lines)

            # value range
            lines = []
            lines.append(", { ")
            find_value = Generator.findValueRange(self, 'event', v)
            for value in find_value:
                lines.append(str(value))
                lines.append("" if value == find_value[-1] else ", ")

            class_file.writelines(lines)
            class_file.writelines(" }")

            class_file.writelines(" },\n")
        class_file.writelines("};\n")

        # tailer
        class_file.writelines("\n#endif    // " + str(className) + "_H")

        class_file.close()
        print ('generate %s over. ~ ~' % filePath)

    def readExcel(self):
        excelName = 'ttt.xlsx'
        if os.path.exists(excelName):
            wb = load_workbook(excelName)
            wsheet = wb.active
            rows = wsheet.columns

            header = {}
            data = {}
            temp_id = (wsheet.cell(row=2, column=1)).value
            for i in range(2, wsheet.max_row+1):  # 从2开始，第一行为title
                #遍历Excel表格两列的内容
                columnData = []
                pro_n = (wsheet.cell(row=i, column=1)).value
                if temp_id != pro_n:
                    temp_id = pro_n
                    header = {}

                for j in range(3, wsheet.max_column+1):
                    columnData.append((wsheet.cell(row=i, column=j)).value)
                header[(wsheet.cell(row=i, column=2)).value] = columnData
                data[temp_id] = header
        self.excelData = data

my_gene = Generator()
my_gene.readExcel()
my_gene.generate()

/// Header.template
/// @class ${CLASSNAME}
/// @generate date: ${GENE_DATE}
/// [note: This file is automatically generated and does not need to be manually edited.
/// If you want to modify it, use the py script to generate it again.]
#ifndef ${CLASSNAME}_H
#define ${CLASSNAME}_H
#include <vector>

using namespace std;

typedef struct _EventInfo
{
    int id;     // identifer
    int size;   // total package size
    int success_event_max_num;  // max num of success event
    int reject_event_max_num;  // max num of reject event
    std::vector<int> valueRange;    // value valid range
} EventInfo;

static const std::vector<EventInfo> eventDataVec = {

自动生成的头文件。?

///
/// @class TestAuto
/// @generate date: 2022-01-02 20:57:44
/// [note: This file is automatically generated and does not need to be manually edited.
/// If you want to modify it, use the py script to generate it again.]
#ifndef TestAuto_H
#define TestAuto_H
#include <vector>

using namespace std;

typedef struct _EventInfo
{
    int id;     // identifer
    int size;   // total package size
    int success_event_max_num;  // max num of success event
    int reject_event_max_num;  // max num of reject event
    std::vector<int> valueRange;    // value valid range
} EventInfo;

static const std::vector<EventInfo> eventDataVec = {
    { 0x1, 3, 3, 5, { 0x03 } },
    { 0x2, 4, 4, 0, { 0x02 } },
    { 0x3, 4, 4, 0, { 0x02, 0x01 } },
};

#endif    // TestAuto_H

测试主程序

#include "TestAuto.h"

#include <deque>
#include <iostream>
#include <typeinfo>

using namespace std;

class EventPackage
{
private:
    /* data */
public:
    EventPackage(const EventInfo &event) {
        std::deque<int> tdeque;
        mQueue.push_back(tdeque);
        if (event.reject_event_max_num > 0) {
            mQueue.push_back(tdeque);
        }
    }
    ~EventPackage() {
    }

    std::vector<std::deque<int> > mQueue;
};

int main ()
{
    if (eventDataVec.size() > 2) {
        EventPackage tst(eventDataVec[0]);
        cout << tst.mQueue.size() << endl;

        EventPackage tst1(eventDataVec[1]);
        cout << tst1.mQueue.size() << endl;

        cout << eventDataVec[0].valueRange[0] << endl;
    }

    return 0;
}

输出

2
1
3