欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

python + openpyxl + Jinja2(解析excel生成javaBean和json)

程序员文章站 2024-03-21 16:32:52
...

模版

package {{ package }};

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.Map.Entry;
import com.mongodb.BasicDBObject;
import com.lehoo.util.io.resource.ResourceListener;
import com.lehoo.util.io.resource.ResourceManager;
import mmorpg.server.util.log.Logger;
import mmorpg.server.util.log.Logger.LoggerSystem;
import mmorpg.server.game.common.JSONListener;
import java.util.Collections;
import java.util.Comparator;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
{% if superClass != "" %} import com.lehoo.sob.confsuper.{{ superClass }}; {% endif %}
/**
 * excel|{{ excelName }}
 * @author administrator
 * 此类是系统自动生成类 不要直接修改,修改后也会被覆盖
 */
@JSONListener
{% if superClass == "" %}public class Conf{{ sheetName }} { {% else %} public class Conf{{ sheetName }} extends {{ superClass }} { {% endif %}
    /** 对应的数据文件 */
    private static final String JSON_NAME = "Conf{{ sheetName }}.json";

    /**索引*/
    private static final String [] INDEXS = {{ indexs }};

    {% for prop in fileds %}/** {{ prop.note }} */
    private {{ prop.type }} {{ prop.name }}; 
    {% endfor %}

    /** 配置数据 */
    private static Map<Object,Conf{{ sheetName }}> datas = new LinkedHashMap<>();

    /**索引结构,加快查询速度*/
    private static Map<String,Map<Object,List<Conf{{ sheetName }}>>> indexs = new HashMap<>();


    /** 私有构造函数 */
    private Conf{{ sheetName }}(){ }

    /**初始化索引*/
    private static void initIndex(){
        //初始化索引结构
        for(String index : INDEXS){
            Map<Object,List<Conf{{ sheetName }}>> map = new HashMap<>();
            indexs.put(index, map);
        }
    }

    /**
     * 加载数据,并注册监听
     * @param file
     */
    public static void load(String path) {
        final File file = new File(path + JSON_NAME);
        ResourceListener listener = new ResourceListener() {
            @Override
            public File listenedFile() {
                return file;
            }

            @Override
            public void onResourceChange(File file) {
                try {
                    datas.clear();
                    indexs.clear();
                    initIndex();

                    BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file),"UTF-8"));
                    String line = null;
                    while ((line = reader.readLine()) != null) {
                        BasicDBObject jsonObj = BasicDBObject.parse(line);
                        Conf{{ sheetName }} conf{{ sheetName }} = new Conf{{ sheetName }}();
                        {% for prop in fileds %}{% if prop.type == "short" %}
                        conf{{ sheetName }}.{{ prop.name }} = (short)jsonObj.getInt("{{ prop.name }}");
                        {% elif prop.type == "byte" %}
                        conf{{ sheetName }}.{{ prop.name }} = (byte)jsonObj.getInt("{{ prop.name }}");
                        {% elif prop.type == "Date" %}
                        conf{{ sheetName }}.{{ prop.name }} = DateUtil.parseDataTime(jsonObj.getString("{{ prop.name }}"));
                        {% elif prop.type == "float" %}
                        conf{{ sheetName }}.{{ prop.name }} = (float)jsonObj.getDouble("{{ prop.name }}");
                        {% else %}
                        conf{{ sheetName }}.{{ prop.name }} = jsonObj.get{{ prop.utype }}("{{ prop.name }}");{% endif %}{% endfor %}
                        datas.put(jsonObj.getString("sn"), conf{{ sheetName }});

                        //处理索引结构
                        for(String index : INDEXS){
                            Map<Object, List<Conf{{ sheetName }}>> indexMap = indexs.get(index);
                            Object indexValue = conf{{ sheetName }}.getFieldValue(index);

                            List<Conf{{ sheetName }}> list = indexMap.get(indexValue);
                            if(list == null){
                                list = new ArrayList<Conf{{ sheetName }}>();
                                indexMap.put(indexValue, list);
                            }
                            list.add(conf{{ sheetName }});
                        }
                    }
                    reader.close();
                    // 此json文件加载完成后,加载特定manager单例类的指定方法
                    ConfJSONLoad confJSONLoad = ConfJSONLoad.findBy(toString());
                    if (confJSONLoad != null) {
                        Class<?> forName = Class.forName(confJSONLoad.getClassName().replaceAll("/", "."));
                        // getInstance方法获取单实例
                        Method declaredMethod = forName.getDeclaredMethod("getInstance", new Class[]{});
                        Object obj = declaredMethod.invoke(null, new Object[]{});
                        // 执行指定方法
                        Method executeMethod = forName.getDeclaredMethod(confJSONLoad.getMethod(), new Class[]{});
                        executeMethod.setAccessible(true);
                        executeMethod.invoke(obj, new Object[]{});
                    }
                    Logger.info(Logger.LoggerSystem.LOADING, "==============载入"+file.getName()+"==============");
                } catch (Exception e) {
                    Logger.error(LoggerSystem.LOADING, e, "加载配置文件失败", file.getName());
                }
            }

            @Override
            public String toString() {
                return "Conf{{ sheetName }}";
            }
        };
        listener.onResourceChange(file);
        ResourceManager.getInstance().registerResourceListener(listener);
    }


    /**
     * 根据主键获得数据
     * @param sn
     * @return
     */
    public static Conf{{ sheetName }} getSn(String sn) {
        return datas.get(sn);
    }

    /**
     * 清除所有数据
     * @return
     */
    public static void clearAll() {
        datas.clear();
        indexs.clear();
    }

    /**
     * 数据大小
     * @return
     */
    public static int size(){
        return datas.size();
    }

    /**
     * 获得所有数据
     * @return
     */
    public static Collection<Conf{{ sheetName }}> findAll() {
        Collection<Conf{{ sheetName }}> values = datas.values();
        List<Conf{{ sheetName }}> result = new ArrayList<>(0);
        result.addAll(values);


        // 对结果进行排序,排序的规则是看本类中是否存在order字段
        // 如果有order字段,那么就按照此字段排序,如果没有则认为不需要进行排序。
        try {
            final Field oderFiled = Conf{{ sheetName }}.class.getDeclaredField("order");
            Collections.sort(result, new Comparator<Conf{{ sheetName }}>() {
                @Override
                public int compare(Conf{{ sheetName }} o1, Conf{{ sheetName }} o2) {
                    oderFiled.setAccessible(true);
                    try {
                        int value1 = (int) oderFiled.get(o1);
                        int value2 = (int) oderFiled.get(o2);
                        return value1 - value2;
                    } catch (Exception e) {
                        e.printStackTrace();
                    } 
                    return 0;
                }
            });
        } catch(NoSuchFieldException e) {
            // 没有order 属性不进行排序
        }
        return result;
    } 

    /**
     * 根据条件获得单条数据
     * @param params
     * @return
     */
    public static Conf{{ sheetName }} findBy(Object object) {
        return datas.containsKey(object) ? datas.get(object) : null;
    } 

    /**
     * 根据条件获得多条数据
     * @param params
     * @return
     */
    public static Collection<Conf{{ sheetName }}> findBy(Object ... params) {
        return utilBase(params);
    } 

    /**
     * 根据条件获得一条数据
     * @param params
     * @return
     */
    public static Conf{{ sheetName }} getBy(Object ... params) {
        List<Conf{{ sheetName }}> utilBase = utilBase(params);
        if (utilBase.size() > 0) {
            return utilBase.get(0);
        }
        return null;
    }

        /**
     * 根据条件获得第一条数据
     * @param params
     * @return
     */
    public static Conf{{ sheetName }} findFirst(Object ... params){
        List<Conf{{ sheetName }}> result = utilBase(params);
        if(result.size() == 0){
            return null;
        }

        return result.get(0);
    }

    /**
     * 根据条件获得最后一条数据
     * @param params
     * @return
     */
    public static Conf{{ sheetName }} findLast(Object ... params){
        List<Conf{{ sheetName }}> result = utilBase(params);
        if(result.size() == 0){
            return null;
        }

        return result.get(result.size() - 1);
    }

    /**
     * 通过属性获取数据集合 支持排序
     * @param params
     * @return
     */
    public static List<Conf{{ sheetName }}> utilBase(Object...params) {
        List<Object> settings = new ArrayList<>(0);
        for (Object obj : params) {
            settings.add(obj);
        }

        // 查询参数
        final Map<String, Object> paramsFilter = new LinkedHashMap<>(0);        //过滤条件

        // 参数数量
        int len = settings.size();

        // 参数必须成对出现
        if (len % 2 != 0) {
            String param = "";
            for (Object p : params) {
                param += p + ",";
            }
            throw new RuntimeException("查询参数必须成对出现:query={" + param +"}");
        }

        // 处理成对参数
        for (int i = 0; i < len; i += 2) {
            String key = (String)settings.get(i);
            Object val = settings.get(i + 1);

            // 参数 
            paramsFilter.put(key, val);
        }

        // 返回结果
        List<Conf{{ sheetName }}> result = null;
        try {
            result = utilBase(paramsFilter);

            // 对结果进行排序,排序的规则是看本类中是否存在order字段
            // 如果有order字段,那么就按照此字段排序,如果没有则认为不需要进行排序。
            try {
                final Field oderFiled = Conf{{ sheetName }}.class.getDeclaredField("order");
                Collections.sort(result, new Comparator<Conf{{ sheetName }}>() {
                    @Override
                    public int compare(Conf{{ sheetName }} o1, Conf{{ sheetName }} o2) {
                        oderFiled.setAccessible(true);
                        try {
                            int value1 = (int) oderFiled.get(o1);
                            int value2 = (int) oderFiled.get(o2);
                            return value1 - value2;
                        } catch (Exception e) {
                            e.printStackTrace();
                        } 
                        return 0;
                    }
                });
            } catch(NoSuchFieldException e) {
                // 没有order 属性不进行排序
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        // 对结果进行排序
        return result;
    }


    /**
     * 查询匹配的结果
     * @param paramsFilter
     * @return
     */
    private static List<Conf{{ sheetName }}> utilBase(Map<String, Object> paramsFilter){
        List<String> indexHit = new ArrayList<>();
        for(String index : INDEXS){
            if(paramsFilter.containsKey(index)){
                indexHit.add(index);
            }
        }

        //先找到索引命中的结果
        List<Conf{{ sheetName }}> hitResult = null;
        if(indexHit.size() > 0){
            for(String hit : indexHit){
                Map<Object, List<Conf{{ sheetName }}>> map = indexs.get(hit);
                List<Conf{{ sheetName }}> list = map.get(paramsFilter.get(hit));
                if(hitResult == null){
                    hitResult = list;
                }else{
                    hitResult.retainAll(list);//求交集
                }
                paramsFilter.remove(hit);
            }
        }

        if(hitResult == null){
            hitResult = new ArrayList<>();
        }

        // 返回结果
        if(paramsFilter.size() == 0){
            return hitResult;
        }

        Collection<Conf{{ sheetName }}> loopCollections = null;
        if(hitResult.size() == 0){
            loopCollections = datas.values();
        }else{
            loopCollections = hitResult;
        }

        List<Conf{{ sheetName }}> result = new ArrayList<>(0);
        // 通过条件获取结果
        for (Conf{{ sheetName }} c : loopCollections) {
            // 本行数据是否符合过滤条件
            boolean bingo = true;

            // 判断过滤条件
            for (Entry<String, Object> p : paramsFilter.entrySet()) {

                // 实际结果
                Object valTrue = c.getFieldValue(p.getKey());
                // 期望结果
                Object valWish = p.getValue();

                // 有不符合过滤条件的
                if (!valWish.toString().equals(valTrue.toString())) {
                    bingo = false;
                    break;
                }
            }

            // 记录符合结果
            if (bingo) {
                result.add(c);
            }
        }
        return result;
    }


    /**
     * 获得字段值
     * @param key
     * @return
     */
    private Object getFieldValue(String key) {
        Object value = null;

        switch (key) { {% for prop in fileds %}
            case "{{ prop.name }}":
                value = this.{{ prop.name }};   
                break;
                {% endfor %}
            default: break;
        }   

        return value;
    }
    {% for prop in fileds %} 
    /**
     *获得{{ prop.note }}
     */
    public {{ prop.type }} get{{ prop.uname }}() {
        return {{ prop.name }};
    } {% endfor %}

    /**
     * byte 类型
     * @param fieldName
     * @return
     */
    public byte getByteValue(String fieldName) {
        return Byte.parseByte(getFieldValue(fieldName) + "");
    }

    /**
     * short 类型
     * @param fieldName
     * @return
     */
    public short getShortValue(String fieldName) {
        return Short.parseShort(getFieldValue(fieldName) + "");
    }

    /**
     * int 类型
     * @param fieldName
     * @return
     */
    public int getIntValue(String fieldName) {
        return Integer.parseInt(getFieldValue(fieldName) + "");
    }

    /**
     * long 类型
     * @param fieldName
     * @return
     */
    public long getLongValue(String fieldName) {
        return Long.parseLong(getFieldValue(fieldName) + "");
    }

    /**
     * boolean 类型
     * @param fieldName
     * @return
     */
    public boolean getBooleanValue(String fieldName) {
        return Boolean.parseBoolean(getFieldValue(fieldName) + "");
    }

    /**
     * float 类型
     * @param fieldName
     * @return
     */
    public float getFloatValue(String fieldName) {
        return Float.parseFloat(getFieldValue(fieldName) + "");
    }

    /**
     * double 类型
     * @param fieldName
     * @return
     */
    public double getDoubleValue(String fieldName) {
        return Double.parseDouble(getFieldValue(fieldName) + "");
    }

    /**
     * String 类型
     * @param fieldName
     * @return
     */
    public String getStringValue(String fieldName) {
        return getFieldValue(fieldName) + "";
    }


    /**
     * 获得数据集中的第一条数据
     * @return
     */
    public static Conf{{ sheetName }} getFirst() {
        Collection<Conf{{ sheetName }}> all = findAll();
        List<Conf{{ sheetName }}> result = new ArrayList<>();
        result.addAll(all);     
        return result.get(0);
    }


    /**
     * 获得数据集中的最后一条数据
     * @return
     */
    public static Conf{{ sheetName }} getLast() {
        Collection<Conf{{ sheetName }}> all = findAll();
        List<Conf{{ sheetName }}> result = new ArrayList<>(0);
        result.addAll(all);     
        if (result.size() > 0) {
            return result.get(result.size() - 1);
        }

        return null;
    }

    /**
     * 数据字段
     * @author chuer
     */
    public static final class K {

        {% for prop in fileds %} /**{{ prop.note }}*/
        public static final String {{ prop.name }} = "{{ prop.name }}";
        {% endfor %}

    }

}

python代码

import jinja2
import openpyxl
import os
import sys
import json
import collections
import datetime


RESOURCE_PATH = "../resource"
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"
PACKAGE = "mmorpg.server.game.common.conf"
JAVA_FILE_PATH = "../mmorpg/src/mmorpg/server/game/common/conf"

sheetNames = []
javaFiles = []
allFils = []


def allExcelFils(path):
    parents = os.listdir(path)
    for parent in parents:
        childPath = os.path.join(path,parent)
        if os.path.isdir(childPath):
            allExcelFils(childPath)
        elif childPath.endswith(".xlsx") and childPath.find("~") == -1:
            allFils.append(childPath)
        else:
            pass


def getRowContents(row):
    contents = []

    for cell in row:
        if cell.value == None:
            print("ERROR TITLE getRowContents:",row)
            sys.exit(0)
        contents.append(cell.value)
    return contents

def getNameAndIndexs(row):
    names = []
    indexs = []
    for cell in row:
        if cell.value == None:
            print("ERROR TITLE getNameAndIndexs:",row)
            sys.exit(0)
        pos = str(cell.value).find("-key")
        if pos != -1:
            names.append(cell.value[0:pos])
            indexs.append(cell.value[0:pos])
        else:
            names.append(cell.value)
    return names,indexs


def getIntValue(value):
    return int(value)

def getStrValue(value):
    if isinstance(value,datetime.datetime):
        return datetime.datetime.strftime(value,TIME_FORMAT)
    return str(value)

def getBoolValue(value):
    return bool(value)

def getFloatValue(value):
    return float(value)


def parseCellValue(type,value):
    if type in ("byte","short","int","long"):
        if value == None or value == "":
            return 0
        else:
            return getIntValue(value)
    elif type in ("float","double"):
        if value == None:
            return 0.0
        elif isinstance(value,str):
            value = value.replace(" ","")
        else:
            if value == "":
                return 0.0
            else:
                return getFloatValue(value)
    elif type == "String":
        if value == None:
            return ""
        else:
            return getStrValue(value)
    elif type == "boolean":
        if value == None or value == "":
            return False
        else:
            return getBoolValue(value)
    else:
        print("WARN:unkonw field type !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
        return value

def generateJSON(sheetName,names,types,rows):
    pos = sheetName.find("|")
    if pos != -1:
        sheetName = sheetName[0:pos]

    pos = sheetName.find("-")
    if pos != -1:
        sheetName = sheetName[0:pos]

    pathDir = RESOURCE_PATH+"/conf"
    if not os.path.exists(pathDir):
        os.makedirs(pathDir)

    filePah = pathDir+"/Conf"+sheetName+".json"
    print(filePah)
    file = open(filePah,"a+",encoding="utf8")
    for row in rows:
        cells = list(row)
        data = collections.OrderedDict()
        for index in range(len(names)):
            data[names[index]] = parseCellValue(types[index],cells[index].value)
        jsonData = json.dumps(data,ensure_ascii=False)
        file.write(jsonData+"\n")
    file.close()




"""
生成文件内容
"""
def parseTemplate(templateFile,context):
    env = jinja2.Environment(loader=jinja2.FileSystemLoader("config"))
    template = env.get_template(templateFile)
    return template.render(context)


"""
生成java文件
"""
def generateJAVA(excelPath,sheetName,names,noteds,indexs,types):
    superClassName = ""
    pos = sheetName.find("|")
    if pos != -1:
        superClassName = sheetName[pos+1:len(sheetName)]
        sheetName = sheetName[0:pos]

    pos = sheetName.find("-")
    if pos != -1:
        sheetName = sheetName[0:pos]

    if sheetName in javaFiles:
        return

    context = {}
    context["package"] = PACKAGE
    context["superClass"] = superClassName
    context["excelName"] = os.path.basename(excelPath)
    context["sheetName"] = sheetName

    indexStr = "{"
    for ind in range(len(indexs)):
        if ind == len(indexs) - 1:
            indexStr += "\""+indexs[ind]+"\""
        else:
            indexStr+= "\""+indexs[ind]+"\","
    indexStr += "}"
    context["indexs"] = indexStr
    fields = []
    for index in range(len(names)):
        data = {}
        data["name"] = names[index]
        data["uname"] = names[index][0].upper()+names[index][1:]
        data["note"] = noteds[index]
        data["type"] = types[index]
        data["utype"] = types[index][0].upper()+types[index][1:]
        fields.append(data)
    context["fileds"] = fields 
    javaClassContent = parseTemplate("template.html",context)
    filePah = JAVA_FILE_PATH+"/Conf"+sheetName+".java"
    print(filePah)
    file = open(filePah,"a+",encoding="utf8")
    file.write(javaClassContent)
    file.close()
    javaFiles.append(sheetName)


def generate():
    for excelPath in allFils:
        wb = openpyxl.load_workbook(excelPath,read_only=True,keep_links=False)
        for sheet in wb:
            if sheet.title.find("策划") > -1 or sheet.title.startswith("sheet") or sheet.title.startswith("Sheet") or sheet.title.find("coder") > -1 or sheet.title[0].islower():
                continue
            if sheet.title in sheetNames:
                print("相同的sheet名称:"+sheet.title)
                sys.exit(0)
            sheetNames.append(sheet.title)
            rows = list(sheet.rows)
            if len(rows) < 3:
                continue

            #注释
            note_row = rows.pop(0)
            field_row = rows.pop(0)
            type_row = rows.pop(0)
            #名字
            names,indexs = getNameAndIndexs(field_row)
            if names[0] != "sn":
                continue

            notes = getRowContents(note_row)

            #类型
            row_field_types = getRowContents(type_row)
            if len(names) != len(row_field_types):
                print("excel 数据错误:"+excelPath)
                sys.exit(0)

            generateJSON(sheet.title,names,row_field_types,rows)
            generateJAVA(excelPath,sheet.title,names,notes,indexs,row_field_types)


def startup():
    allExcelFils(RESOURCE_PATH)
    generate()

#启动
if __name__ == '__main__':
    startup()
相关标签: 代码生成

上一篇: 3.DDL

下一篇: