python + openpyxl + Jinja2（解析excel生成javaBean和json）

模版

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()