box-data/script/exceltolua/excel_to_lua.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}}
    其它：直接用文本输出。
'''

from fileinput import filename
import os
import sys
import shutil
# from tkinter import filedialog
import ConfigParser
import json
import math
import time
from optparse import OptionParser
from xlrd import open_workbook
import commands

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.luaList = []

    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

        self.check_lua()
        # self.print_file_map()

    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")
        parser.add_option("-l", action="store", type="string",
                          dest="lua", help="need check lua")
        (options, args) = parser.parse_args()

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

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

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

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

        try:
            sourcePath = unicode(sourcePath, 'gbk')
        except Exception as e:
            pass

        try:
            outputPath = unicode(outputPath, 'gbk')
        except Exception as e:
            pass

        print("sourcePath", sourcePath)
        print("outputPath", outputPath)

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

        self.excelDirList = [""]
        self.luaDirList = [""]
        self.configRoot = ""
        self.needCheckLua = options.lua != None

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

        return True

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

        return True

    def add_lua_info(self, fileDir, filename, excelName, label, excelMode):
        ''' 记录lua信息
        '''

        self.luaList.append({
            "fileDir": fileDir,
            "filename": filename,
            "excelName": excelName,
            "label": label,
            "excelMode": excelMode
        })

    def check_lua(self):
        ''' 检查lua是否有语法错误
        '''

        if not self.needCheckLua:
            return

        hasError = False
        for v in self.luaList:
            excelName = v["excelName"]
            fileDir = v["fileDir"]
            filename = v["filename"]
            ok = self.checkLua(fileDir, filename)
            if not ok:
                hasError = True
                msg = "\033[31m---- lua语法错误：{0},{1}\033[0m".format(
                    excelName, filename)
                print(msg)

        if not hasError:
            print("\033[32m---- 无lua语法错误\033[0m")

    def checkLua(self, fileDir, filename):
        ''' 检查lua是否有语法错误
        '''

        cmdFormat = 'lua -e \" \
        package.path = [[{0}?.lua;]] .. package.path; \
        local ok, err = pcall(require, [[{1}]]) \
        print(ok); \
        \"'

        cmd = cmdFormat.format(fileDir, filename)

        content = commands.getoutput(cmd)
        if content == "true":
            return True
        else:
            return False

    def print_file_map(self):
        ''' 打印文件信息：每个excel包含哪些配置
        '''

        if not self.needCheckLua:
            return

        strList = []
        for v in self.luaList:
            filename = v["filename"]
            excelName = v["excelName"]
            label = v["label"]
            excelMode = v["excelMode"]
            strList.append(",".join([excelName, filename, label, excelMode]))

        strList.sort()

        content = "\n".join(strList)
        excelRootPath = self.excelRootPath
        fo = open(excelRootPath + "/../excel-lua.csv", "w")
        fo.write(content)
        fo.close()

    def loop_exchange(self):
        try:
            ok = self.loop_exchange_try()
        except Exception, err:
            ok = False
            print("error", err)

        return ok

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

        excelDirList = self.excelDirList
        luaDirList = self.luaDirList
        excelRootPath = self.excelRootPath
        luaRootPath = self.luaRootPath
        configRoot = self.configRoot
        mode = self.mode

        for i in range(0, len(excelDirList)):
            excelDir = excelDirList[i]
            luaDir = luaDirList[i]
            sourcePath = os.path.join(excelRootPath, excelDir)
            outputPath = os.path.join(luaRootPath, luaDir, 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":
                    i = name.find("~$")
                    if i < 0:
                        # 批量创建
                        self.excel2lua(filePath, outputPath, mode)

        return True

    # 生成lua文件

    def excel2lua(self, filePath, outputPath, mode):
        ''' 转换
        '''
        fileName = os.path.basename(filePath)
        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]

            sheetLabel = ""
            if len(row_1) > 2:
                sheetLabel = row_1[2]
            sheetLabel = str(sheetLabel)

            if dataMode == "" or excelMode == "":
                continue

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

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

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

            if outputOk:
                self.add_lua_info(outputPath, name, fileName,
                                  sheetLabel, excelMode)

    # 打开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()

        return True

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

        return True

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

        return True

    # 以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):
            # 读取一行
            onRow = sheet.row_values(x)
            mergeRow = merge.get(x)

            # 检查这行是否有值
            hasValue = False
            for cc in xrange(start_cc, ncols):
                keyStr = keyRow[cc]
                # 忽略key是空的列
                if keyStr == "":
                    continue

                # 忽略空值的属性
                value = onRow[cc]
                if mergeRow:
                    mergeCell = mergeRow.get(cc)
                    if mergeCell != None:
                        value = mergeCell
                if value == "":
                    continue

                hasValue = True
            # 这行没值，跳过
            if not hasValue:
                continue

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

            # 第一个属性需要特殊处理
            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()

        return True

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

        return True


def main():
    # try:
    ExcaleToLuaClass().start()
    # except Exception as e:
    #     print "数据导出异常，请处理，问题如下(对应EXCEL为以上最后一个EXCEL)："
    #     print e.message
    #     time.sleep(20)


if __name__ == '__main__':
    main()