box-data/script/exceltolua/excel_to_json.py

# -*- coding: utf-8 -*-

'''
一、支持的表头
    单值Key-Value形式的：【KV】、【MAP】；即第2行是key，第3行是类型，第5行是value
        如：{a=1, b=3, ...}
    单值Key-Value形式的：【K-V】；即第1列是key，第2列是类型，第4列是value
        如：{a=1, b=3, ...}
    对象列表形式的：【V】；即第1列
        如：{1, 2, 3, ...}
    对象Key-Value形式的：【M】；
        如：{[1]={a=1, b=3, ...}, [2]={a=1, b=3, ...}}
    单值列表形式的：【LIST】、【L】。
        如：{{a=1, b=3, ...}, {a=1, b=3, ...}}

    推荐使用：
        单值Key-Value形式的：【K-V】
        对象列表形式的：【V】
        对象Key-Value形式的：【M】
        单值列表形式的：【L】

二、支持的数据类型：
    整数：int；
    整数字符串：intstring；
    布尔值：bool、boolean。为true的值："TRUE"、"true"、"T"、"1"、1:
    字符串：text、string；
    浮点型：double、float；
    lua表达式：table、lua；
        如：{{id=101,count=2222}}
    其它：直接用文本输出。
'''

import os
import sys
import commands
import json
import shutil
import ConfigParser
import json
import math
from optparse import OptionParser
from xlrd import open_workbook

reload(sys)
sys.setdefaultencoding('utf-8')


class ConfigParserEx(ConfigParser.ConfigParser):
    def __init__(self,defaults=None):
        ConfigParser.ConfigParser.__init__(self,defaults=None)
    def optionxform(self, optionstr):
        return optionstr

def load_config(path):
    ''' 读取配置表
    '''
    config = ConfigParserEx()
    config.read(path)

    return config

kSpace = "    "


class ExcaleToLuaClass(object):

    def __init__(self):
        self.currentPyPath = sys.path[0]
        self.configPath = None
        self.errorMap = []


    def start(self):
        print("开始转换...")
        print("++++ 检查参数...")
        ok = self.check_arguments()
        if ok:
            print("---- 检查参数正确!!!")
        else:
            print("---- 检查参数错误!!!")
            return

        print("++++ 加载配置...")
        ok = self.load_arguments_config()
        if ok:
            print("---- 加载配置正确!!!")
        else:
            print("---- 加载配置错误!!!")
            return

        print("++++ 转换...")
        ok = self.loop_exchange()
        if ok:
            print("---- 转换正确!!!")
        else:
            print("---- 转换错误!!!")
            return

        for errorName in self.errorMap:
            print(json.dumps("\033[31m---- 错误配置：" + errorName + "\033[0m").decode("unicode-escape"))


    def check_arguments(self):
        ''' 检查参数
        '''

        parser = OptionParser()
        parser.add_option("-s", action="store", type="string", dest="source", help="source path")
        parser.add_option("-o", action="store", type ="string", dest="output", help="output path")
        parser.add_option("-t", action="store", type ="string", dest="type", help="type")
        # 不必输入，固定是输出json格式
        # parser.add_option("--json", action="store_true", dest="outputJson", help="output json")
        (options, args) = parser.parse_args()

        ok = True
        sourcePath = options.source
        if options.source == None:
            ok = False
            print("-s config path is not set")

        outputPath = options.output
        if options.output == None:
            #如果输出目录没有写,则在源目录中新建一个output目录作为输出目录
            outputPath = os.path.join(cur_path, "out")

        onlyClient = False
        if options.type == "c":
            onlyClient = True
        else:
            onlyClient = False

        onlyServer = False
        if options.type == "s":
            onlyServer = True
        else:
            onlyServer = False

        outputJson = True # options.outputJson

        print "sourcePath", sourcePath
        print "outputPath", outputPath
        if outputJson:
            print "输出 json格式"
        else:
            print "输出 lua格式"

        if not os.path.exists(outputPath):
            os.makedirs(outputPath)


        self.excelDirList = [""]
        self.jsonDirList = [""]
        self.configRoot = ""
        self.configRoot = ""

        self.excelRootPath = sourcePath
        self.jsonRootPath = outputPath
        if onlyClient:
            self.mode = "CLIENT"
        elif onlyServer:
            self.mode = "SERVER"
        else:
            self.mode = "ALL"

        self.outputJson = outputJson

        return True


    def load_arguments_config(self):
        ''' 加载参数
        '''

        return True


    def loop_exchange(self):
        ''' 循环转换
        '''

        excelDirList = self.excelDirList
        jsonDirList = self.jsonDirList
        excelRootPath = self.excelRootPath
        jsonRootPath = self.jsonRootPath
        configRoot = self.configRoot
        mode = self.mode

        for i in range(0, len(excelDirList)):
            excelDir = excelDirList[i]
            jsonDir = jsonDirList[i]
            sourcePath = os.path.join(excelRootPath, excelDir)
            outputPath = os.path.join(jsonRootPath, jsonDir, configRoot)

            if not os.path.exists(outputPath):
                os.makedirs(outputPath)

            dirList = []
            # 填充所有目录和文件名称
            for dirpath, dirnames, filenames in os.walk(sourcePath):
                for filename in filenames:
                    dirList.append(os.path.join(dirpath, filename))

            # 过滤文件
            for filePath in dirList:
                name, ext = os.path.splitext(filePath)
                if ext == ".xlsx":
                    if name.find("~$") < 0 and name.find(".~") < 0:
                        # 批量创建
                        self.excel2lua(filePath, outputPath, mode)

        return True


    # 生成lua文件
    def excel2lua(self, filePath, outputPath, mode):
        ''' 转换
        '''

        excelData = self.openExecl(filePath)
        # 遍历所有的sheet
        for sheet in excelData.sheets():
            name = sheet.name
            nrows = sheet.nrows
            ncols = sheet.ncols
            #print(nrows, ncols)#行列数

            #row 1 作为lua表的属性用
            row_1 = sheet.row_values(0)
            dataMode = row_1[0]
            excelMode = row_1[1]

            if dataMode == "" or excelMode == "":
                # self.errorMap.append(filePath)
                continue

            if mode != "ALL":
                if mode == "CLIENT" and (excelMode != "CLIENT" and excelMode != "ALL"):
                    # 只需要导出客户端需要的，该表不是客户端需要的，过掉
                    continue
                elif mode == "SERVER" and (excelMode != "SERVER" and excelMode != "ALL"):
                    # 只需要导出客户端需要的，该表不是客户端需要的，过掉
                    continue

            outputFilePath = os.path.join(outputPath, name + ".lua")
            if dataMode == "K-V":
                self.outputColKeyValue(sheet, outputFilePath)
            elif dataMode == "V":
                self.outputList(sheet, outputFilePath)
            elif dataMode == "M":
                self.outputObjMap(sheet, outputFilePath)
            elif dataMode == "L":
                self.outputObjList(sheet, outputFilePath)

            # 下面是兼容旧版本的标签
            elif dataMode == "KV" or dataMode == "MAP":
                self.outputKeyValue(sheet, outputFilePath)
            elif dataMode == "LIST":
                self.outputObjList(sheet, outputFilePath)
            else:
                continue

            if self.outputJson:
                self.lua2json(outputPath, name)

    # lua转json
    def lua2json(self, outputPath, name):
        cmdFormat = 'lua -e \" \
        local cjson = require([[cjson]]); \
        package.path = [[{0}?.lua;]] .. package.path; \
        local function updateKey(tab)  \
            if type(tab) ~= [[table]] then \
                return tab; \
            end \
            local count = 0; \
            for key, value in pairs(tab) do \
                count = count + 1; \
            end \
            local ret = {{}}; \
            if count == #tab then \
                for i, value in ipairs(tab) do \
                    ret[i] = updateKey(value); \
                end \
            else \
                for key, value in pairs(tab) do \
                    ret[tostring(key)] = updateKey(value); \
                end \
            end \
            return ret; \
        end \
        local fileName = [[{1}]]; \
        local config = require(fileName); \
        config = updateKey(config); \
        local jsonStr = cjson.encode(config); \
        print(jsonStr); \
        \"'

        cmd = cmdFormat.format(outputPath, name)

        content = commands.getoutput(cmd)
        data = json.loads(content)
        jsonStr = json.dumps(data, sort_keys=True, indent=4, separators=(',', ': '), ensure_ascii=False)
        jsonStr = jsonStr.decode("utf-8")
        jsonStr = jsonStr.replace(',\n                            ', ', ');
        jsonStr = jsonStr.replace(',\n                        ', ', ');
        jsonStr = jsonStr.replace(',\n                    ', ', ');
        jsonStr = jsonStr.replace(',\n                ', ', ');
        jsonStr = jsonStr.replace(',\n            ', ', ');

        jsonStr = jsonStr.replace('[\n                            ', '[');
        jsonStr = jsonStr.replace('[\n                        ', '[');
        jsonStr = jsonStr.replace('[\n                    ', '[');
        jsonStr = jsonStr.replace('[\n                ', '[');
        jsonStr = jsonStr.replace('[\n            ', '[');

        jsonStr = jsonStr.replace('\n                            ]', ']');
        jsonStr = jsonStr.replace('\n                        ]', ']');
        jsonStr = jsonStr.replace('\n                    ]', ']');
        jsonStr = jsonStr.replace('\n                ]', ']');
        jsonStr = jsonStr.replace('\n            ]', ']');
        jsonStr = jsonStr.replace('\n        ]', ']');

        jsonStr = jsonStr.replace('{\n                            ', '{');
        jsonStr = jsonStr.replace('{\n                        ', '{');
        jsonStr = jsonStr.replace('{\n                    ', '{');
        jsonStr = jsonStr.replace('{\n                ', '{');
        jsonStr = jsonStr.replace('{\n            ', '{');

        jsonStr = jsonStr.replace('\n                            }', '}');
        jsonStr = jsonStr.replace('\n                        }', '}');
        jsonStr = jsonStr.replace('\n                    }', '}');
        jsonStr = jsonStr.replace('\n                }', '}');
        jsonStr = jsonStr.replace('\n            }', '}');
        jsonStr = jsonStr.replace('\n        }', '}');

        outputFilePath = os.path.join(outputPath, name + ".json")
        output = open(outputFilePath , "w")
        output.write(jsonStr)
        output.close()

        luaOutputFilePath = os.path.join(outputPath, name + ".lua")
        os.remove(luaOutputFilePath)

    # 打开execl
    def openExecl(self, filePath):
        try:
            print("openExecl file name : " + filePath)
            data = open_workbook(filePath)
            return data
        except Exception,e:
            print "openExecl error:" + str(e)


    def cellValue(self, value, typeStr):
        ret = ""
        if value == "":
            return ""

        if value == "无" and typeStr != "text":
            return ""

        if typeStr == "int":
            retInt = float(value)
            ret = str(int(math.floor(retInt + 0.5)))
        elif typeStr == "intstring":
            if type(value) == type(0.1):
                retInt = float(value)
                ret = "\'" + str(int(math.floor(retInt + 0.5))) + "\'"
            else:
                ret = "\'" + str(value) + "\'"
        elif typeStr == "bool" or typeStr == "boolean":
            if value == "TRUE" or value == "true" or value == "T" or value == "1" or value == 1:
                ret = "true"
            else:
                ret = "false"
        elif typeStr == "text" or typeStr == "string":
            ret = "\'" + str(value) + "\'"
        elif typeStr == "double" or typeStr == "float":
            ret = str(value)
        elif typeStr == "table" or typeStr == "lua":
            ret = str(value)
        else:
            ret = str(value)

        return ret


    # 以Map形式输出 {a=1, b=3, ...}
    def outputColKeyValue(self, sheet, outputFilePath):
        name = sheet.name
        nrows = sheet.nrows
        ncols = sheet.ncols
        #print(nrows, ncols)#行列数


        output = open(outputFilePath, "w")
        output.write("local root = {")

        # 从第2行开始读
        start_rr = 1
        #第一行需要特殊处理
        firstLine = True
        for x in xrange(start_rr, nrows):
            onRow = sheet.row_values(x)
            keyStr = onRow[0]
            # 忽略key是空的列
            if keyStr == "":
                continue

            valueType = onRow[1]
            value = onRow[3]

            valueStr = self.cellValue(value, valueType)
            # 忽略空值的属性
            if valueStr == "":
                continue

            if firstLine:
                output.write("\n" + kSpace + keyStr + " = " + valueStr)
                firstLine = False
            else:
                output.write(",\n" + kSpace + keyStr + " = " + valueStr)

        output.write("\n}\nreturn root")
        output.close()


    # 以List形式输出 {1, 2, 3, ...}
    def outputList(self, sheet, outputFilePath):
        nrows = sheet.nrows
        ncols = sheet.ncols
        #print(nrows, ncols)#行列数

        # 第3行是数据类型的声明
        typeRow = sheet.row_values(2)
        # 因为是list，就一个类型
        valueType = typeRow[0]


        output = open(outputFilePath, "w")
        output.write("local root = {")

        # 从第5行开始读
        start_rr = 4
        #第一行需要特殊处理
        firstLine = True
        for x in xrange(start_rr, nrows):
            onRow = sheet.row_values(x)
            value = onRow[0]

            valueStr = self.cellValue(value, valueType)

            if firstLine:
                output.write("\n" + kSpace + valueStr)
                firstLine = False
            else:
                output.write(",\n" + kSpace + valueStr)

        output.write("\n}\nreturn root")
        output.close()


    # 以Map形式输出 {[1]={a=1, b=3, ...}, [2]={a=1, b=3, ...}}
    def outputObjMap(self, sheet, outputFilePath):
        name = sheet.name
        nrows = sheet.nrows
        ncols = sheet.ncols
        #print(nrows, ncols)#行列数

        # 支持合并单元格
        merge = {}
        for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
            for r in xrange(rlow, rhigh):
                if not merge.get(r):
                    merge[r] = {}
                for c in xrange(clow, chigh):
                    merge[r][c] = sheet.row_values(rlow)[clow]


        #第1行的第3个单元格声明哪个作为key
        row_1 = sheet.row_values(0)
        key = row_1[2]
        # 第2行是key名称
        keyRow = sheet.row_values(1)
        # 第3行是数据类型的声明
        typeRow = sheet.row_values(2)

        output = open(outputFilePath, "w")
        output.write("local root = {")


        # 从第0列开始读
        start_cc = 0
        # 从第5行开始读
        start_rr = 4
        #第一行需要特殊处理
        firstLine = True
        for x in xrange(start_rr, nrows):

            objStr = "{"
            keyValue = ""

            # 读取一行
            oneRow = sheet.row_values(x)
            mergeRow = merge.get(x)
            # 第一个属性需要特殊处理
            firstProperty = True
            for cc in xrange(start_cc, ncols):
                keyStr = keyRow[cc]
                # 忽略key是空的列
                if keyStr == "":
                    continue

                value = oneRow[cc]
                valueType = typeRow[cc]

                if mergeRow:
                    mergeCell = mergeRow.get(cc)
                    if mergeCell != None:
                        value = mergeCell

                value = self.cellValue(value, valueType)
                if value == "":
                    continue

                if firstProperty:
                    objStr = objStr + "\n" + kSpace + kSpace + keyStr + " = " + value
                    firstProperty = False
                else:
                    objStr = objStr + ",\n" + kSpace + kSpace + keyStr + " = " + value

                if keyStr == key:
                    keyValue = value

            if firstLine:
                objStr = "\n" + kSpace + "[" + keyValue + "] = " + objStr + "\n" + kSpace + "}"
                firstLine = False
            else:
                objStr = ",\n" + kSpace + "[" + keyValue + "] = " + objStr + "\n" + kSpace + "}"
            output.write(objStr)

        output.write("\n}\nreturn root")
        output.close()


    # 以Map形式输出 {{a=1, b=3, ...}, {a=1, b=3, ...}}
    def outputObjList(self, sheet, outputFilePath):
        name = sheet.name
        nrows = sheet.nrows
        ncols = sheet.ncols
        #print(nrows, ncols)#行列数

        # 支持合并单元格
        merge = {}
        for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
            for r in xrange(rlow, rhigh):
                if not merge.get(r):
                    merge[r] = {}
                for c in xrange(clow, chigh):
                    merge[r][c] = sheet.row_values(rlow)[clow]


        # 第2行是key名称
        keyRow = sheet.row_values(1)
        # 第3行是数据类型的声明
        typeRow = sheet.row_values(2)

        output = open(outputFilePath, "w")
        output.write("local root = {")


        # 从第0列开始读
        start_cc = 0
        # 从第5行开始读
        start_rr = 4
        #第一行需要特殊处理
        firstLine = True
        for x in xrange(start_rr, nrows):
            if firstLine:
                output.write("\n" + kSpace + "{")
                firstLine = False
            else:
                output.write(",\n" + kSpace + "{")

            # 读取一行
            onRow = sheet.row_values(x)
            mergeRow = merge.get(x)
            # 第一个属性需要特殊处理
            firstProperty = True
            for cc in xrange(start_cc, ncols):
                keyStr = keyRow[cc]
                # 忽略key是空的列
                if keyStr == "":
                    continue

                value = onRow[cc]
                valueType = typeRow[cc]

                if mergeRow:
                    mergeCell = mergeRow.get(cc)
                    if mergeCell != None:
                        value = mergeCell

                valueStr = self.cellValue(value, valueType)
                # 忽略空值的属性
                if valueStr == "":
                    continue

                if firstProperty:
                    output.write("\n" + kSpace + kSpace + keyStr + " = " + valueStr)
                    firstProperty = False
                else:
                    output.write(",\n" + kSpace + kSpace + keyStr + " = " + valueStr)

            output.write("\n" + kSpace + "}")

        output.write("\n}\nreturn root")
        output.close()


    # 以Map形式输出 {a=1, b=3, ...}
    def outputKeyValue(self, sheet, outputFilePath):
        name = sheet.name
        nrows = sheet.nrows
        ncols = sheet.ncols
        #print(nrows, ncols)#行列数

        # 支持合并单元格
        merge = {}
        for (rlow, rhigh, clow, chigh) in sheet.merged_cells:
            for r in xrange(rlow, rhigh):
                if not merge.get(r):
                    merge[r] = {}
                for c in xrange(clow, chigh):
                    merge[r][c] = sheet.row_values(rlow)[clow]


        # 第2行是key名称
        keyRow = sheet.row_values(1)
        # 第3行是数据类型的声明
        typeRow = sheet.row_values(2)
        # 第3行是值
        valueRow = sheet.row_values(4)
        mergeRow = merge.get(4)


        output = open(outputFilePath, "w")
        output.write("local root = {")

        # 从第0列开始读
        start_cc = 0
        #第一行需要特殊处理
        firstLine = True
        for cc in xrange(start_cc, ncols):
            keyStr = keyRow[cc]
            # 忽略key是空的列
            if keyStr == "":
                continue

            value = valueRow[cc]
            valueType = typeRow[cc]

            if mergeRow:
                mergeCell = mergeRow.get(cc)
                if mergeCell != None:
                    value = mergeCell

            valueStr = self.cellValue(value, valueType)
            # 忽略空值的属性
            if valueStr == "":
                continue

            if firstLine:
                output.write("\n" + kSpace + keyStr + " = " + valueStr)
                firstLine = False
            else:
                output.write(",\n" + kSpace + keyStr + " = " + valueStr)

        output.write("\n}\nreturn root")
        output.close()


def main():
    ExcaleToLuaClass().start()

if __name__ == '__main__':
    main()