转换规则
定义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)
后续找到更好的方法再扩展,
新加:
#!/usr/bin/env python
# -*- coding: UTF-8 -*-
import os,sys
import xlrd
#填表规则:前三行分别为备注,变量,变量类型
#变量类型:Uid,Int,String,RawData,Bool,Func,Number
#Uid:表的主键,可以是int型数字([1001]=xxx ,[1001]是主键),也可以是字符串直接作为Lua的key
#RawData:填表时需要注意这个格式是Lua支持的,直接作为value而使用的
#Func:方法类型的变量,[xxxxx]=value ,value是表里配置的格式是:function() end
#变量类型也可以填:Int|List,String|List,Number|List,
#这样的类型的值填写如下:
#Int|List: 2|3|3|4|45|6 =》 最终被解析为{2,3,3,4,45,6}
#String|List: str1|str2|str3 => 最终被解析为{"str1","str2","str3"}
#Number|List: 2.3|1.2|2.5 =>最终被解析为{2.3,1.2,2.5}
# 解析规则
# {%content%},{%tablename%},{%key%},{%value%}为替换内容
# %~制表符替换
xlsTemplate="""
local DataTable =
{
{%content%}
}
function GetTable() return DataTable end
function GetContent(SheetName) return DataTable[SheetName].Content end"""
TableTemplate="""%~{%tablename%} = {\n{%content%}\n%~},\n"""
FuncTemplate="""%~{%key%} = function() return {%value%} end,\n"""
KeyValueTemp="""%~{%key%} = {%value%},\n"""
StringValTemp="""%~{%key%} = [=[ {%value%} ]=],\n"""
ListTemp="""%~{%key%} ={ {%value%} },\n"""
# typeList=["Uid","Int","String","RawData","Bool","Func","Number","Int|List","String|List","Bool|List","Number|List"] #支持的数据类型
vTypeDic={
"Uid":KeyValueTemp,
"Int":KeyValueTemp,
"String":StringValTemp,
"RawData":KeyValueTemp,
"Bool":KeyValueTemp,
"Func":FuncTemplate,
"Number":KeyValueTemp,
"Int|List":ListTemp,
"String|List":ListTemp,
"Bool|List":ListTemp,
"Number|List":ListTemp
}
#处理key值:因为key值可能是字符串类型,可能是数字
def HandleKey(key):
if len(str(key))==0 or "//" in str(key):
return ""
if isinstance(key,int) or isinstance(key,float):#如果表key为数字
return "["+str(int(key))+"]"
else:
return str(key)
#处理Value值
def HandleValue(vType,value):
if len(str(value))==0 or "//" in str(value):
return ""
if vType=="Int|List" or vType=="Bool|List" or vType=="Number|List":
value=value.replace("|",",")
if vType=="String|List":
value=value.replace("|",",")
array=value.split(",")
list1 =[]
for a in array:
tempStr="\""+a+"\""
list1.append(tempStr)
value=",".join(list1)
return value
def AddTab(target,depth):
target=target.replace(r"%~",depth*"\t")
return target
def MakeTable(target,tablename,content,depth):
target=target.replace("{%tablename%}",HandleKey(tablename))
if content:
target=target.replace("{%content%}",content)
target=AddTab(target,depth)
return target
def MakeKeyValue(target,key,vType,value,depth):#根据类型拿到相应模板
if len(str(key))==0 or "//" in str(key):
return ""
target=target.replace("{%key%}",HandleKey(key))
if value:
val=HandleValue(vType,value)
target=target.replace("{%value%}",str(val))
target=AddTab(target,depth)
return target
def ReadFile(filePath):
fileName=os.path.basename(filePath)
currentDirname=os.path.dirname(os.path.realpath(__file__))
fileNameNoExten=fileName.split('.')[0]
fileExten=fileName.split('.')[1]
saveFile=os.path.join(currentDirname,fileNameNoExten+".lua")
if fileExten=="xlsx":
workBook=xlrd.open_workbook(filePath)
content=ParseXlsx(workBook)
content=xlsTemplate.replace(r"{%content%}",content)
Write2Lua(saveFile,RemoveEnterChar(content))
def RemoveEnterChar(content):
getContent=""
array=content.split("\n")
for line in array:
if line!="":
getContent=getContent+line+"\n"
return getContent
def ParseXlsx(workbook):
content=""
allSheetNames = workbook.sheet_names()
for sheetName in allSheetNames:
if sheetName=="main":
continue
sheetContent=workbook.sheet_by_name(sheetName)
rowTable=""
for row in range(0,sheetContent.nrows):
if row<3: #前三行分别为备注,变量,变量类型
continue
rowTableName=sheetContent.cell_value(row,0) #第一列是表的主键值
if len(str(rowTableName))==0:
continue
key_value=""
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)
if vaType in vTypeDic:
key_value=key_value+MakeKeyValue(vTypeDic[vaType],key,vaType,value,4)
else:
print("xlsx can`t use the Type:"+vaType)
rowTable=rowTable+MakeTable(TableTemplate,rowTableName,key_value,3)
content=content+MakeTable(TableTemplate,sheetName,MakeTable(TableTemplate,"Content",rowTable,2),1)
return content
def Write2Lua(saveFile,content):
with open(saveFile,"w") as file:
file.write(content)
def SearchXlsxFile(baseRoot):
for root,dirNames,files in os.walk(baseRoot,followlinks=True):
for f in files:
filePath=os.path.join(root,f)
print(filePath)
ReadFile(filePath)
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)
ReadFile(targetFileName)
else:
currentDirname=os.path.dirname(os.path.realpath(__file__))
SearchXlsxFile(currentDirname)
参考文献:
xlrd读表应用举例参照:https://blog.csdn.net/zijikanwa/article/details/89577326
os文件参照:https://www.runoob.com/python/os-file-methods.html