Python将策划表xlsx转为Lua可用文件---bering

转换规则

定义xlsx文件的前三行分别为：字段描述（即备注字段代表什么），字段名，字段类型

每一行，每一个sheet都分别代表一张table，每一列代表table中的字段

转换效果实现方法

定义好模板类型，根据表类型，字段类型得到对应的模板，然后读取Excel的内容填充到模板中，这里记录一下实现的代码，以供需要时拿出来改改用，代码通过变量名应该能读懂，就不写注释了

#!/usr/bin/env python # -*- coding: UTF-8 -*- # @Date : 2020-03-21 18:06:21 # @Author : yan nan fei # @Version : $Id$ import os,sys import xlrd template="""local DataTable = n{n[]n} function GetTable() return DataTable end function GetContent(SheetName) return DataTable[SheetName].Content end """ tableTemplate="""%{tablename}={{n[]n%}},n""" KeyValueTemp="""%{key}={value},n""" StringValTemp="""%{key}=[=[{value}]=],n""" BoolValTemp="""%{key}={value},n""" FuncValTemp="""%{key}=function() return {value} end,""" vTypeDic={"Int":KeyValueTemp,"String":StringValTemp,"RawData":KeyValueTemp,"Bool":BoolValTemp,"Func":FuncValTemp} def TemplateParse(template,reg): template=template.replace("%",reg) return template def MakeTable(template,tablename,content,depth): template=TemplateParse(template,depth*"t") if content!=None: template=template.replace("[]",content) if isinstance(tablename,float):#如果表key为数字 tablename="[{key}]".format(key=int(tablename)) if "tablename" in template: template=template.format(tablename=tablename) return template def MakeSubTable(parent,tble2): tble=parent.replace("[]",tble2) return tble def MakeKeyValue(vaType,key,value,depth):#根据类型拿到相应模板 if len(str(key))==0 or len(str(value))==0 or "//" in str(key): return "" if vaType in vTypeDic: template=vTypeDic[vaType] template=TemplateParse(template,depth*"t") template=template.format(key=key,value=value) return template class ReadSheetContent: def __init__(self,workbook,sheetName): self.workbook=workbook self.sheetName=sheetName def GetContent(self): content="" sheetContent=self.workbook.sheet_by_name(self.sheetName) for row in range(0,sheetContent.nrows): if row<3: #前三行分别为备注，变量，变量类型 continue rowTableName=sheetContent.cell_value(row,0) rowTableObject=MakeTable(tableTemplate,rowTableName,"[]",3) colContent="" for col in range(0,sheetContent.ncols): key=sheetContent.cell_value(1,col) #第二行表示变量 vaType=sheetContent.cell_value(2,col)#第三行表示变量类型 value=sheetContent.cell_value(row,col) keyValueObj=MakeKeyValue(vaType,key,value,5) colContent=colContent+keyValueObj rowTableObject=rowTableObject.replace("[]",colContent) content=content+rowTableObject return content if __name__=="__main__": if len(sys.argv)>1: targetFileName=sys.argv[1] targetDir=os.path.dirname(targetFileName) if os.path.isdir(targetDir): os.makedirs(targetDir) workbook=xlrd.open_workbook(targetFileName) allSheetNames = workbook.sheet_names(); fileNameNoExten=targetFileName.split('.')[0] with open(fileNameNoExten+".lua","w") as file: luaContent="" for sheetName in allSheetNames: if sheetName=="main": continue sheetTable=MakeTable(tableTemplate,sheetName,"[]",1) ContentTable=MakeTable(tableTemplate,"Content","[]",2) sheetTable=MakeSubTable(sheetTable,ContentTable) readSheetContent=ReadSheetContent(workbook,sheetName) SheetContent=readSheetContent.GetContent() luaContent=luaContent+MakeSubTable(sheetTable,SheetContent) template=template.replace("[]",luaContent) file.write(template)

后续找到更好的方法再扩展

参考文献： xlrd读表应用举例参照：https://blog.csdn.net/zijikanwa/article/details/89577326 os文件参照：https://www.runoob.com/python/os-file-methods.html

---来自腾讯云社区的---bering