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