### 说明文档
# 重要写在开头 文件必须为.xlsx 后缀 需引用openpyxl第三方模块
# 将Excel文件转换为Json和C# Code
# 第一行为中文名 人物编号 人物名称 人物年龄 人物简介 _性别
# 第二行为英文名 personID personName personAge personIntro sex
# 第三行字段类型 int string int string int
# 1 hyrule 20 I am hyrule 1
# 2 link 20 I am link 1
#
# PS: 第一行描述前加上下划线表示忽略当前列的字段例如： _性别 列 会被忽略不生成代码和Json
# PS: 第一行描述后加上#表示默认值 例如 人物年龄#18 则表示若当前列下的单元格为空则设置为默认值
# PS: SheetName后面加上#表示该表在生成Code的时候是需要序列化的
###
import os
from openpyxl import Workbook
from openpyxl import load_workbook
isCreateCode = True #是否生成C# 代码文件
isCreateJson = True #是否生成Json 文件
quotation = "\"" #双引号 单引号 , 表示json文件中字符串表示形式
projectParentPath = "" #工程所在路径,如果指定则追加,不指定则默认为当前py脚本所在的项目
excelDirPath = "D:/art" #指定Excel所在路径,不指定则默认为当前py脚本位置
codePath = "/Assets/Scripts/Code"
jsonPath = "/Assets/Resources/Json"
stripstr = '\r\n\t '
####################### FUNC Create CODE ##########################
def CreateCodeFile(titleName,lstCs,lstEs,lstTs):
if not os.path.exists(codePath):
os.makedirs(codePath) #创建目标文件夹
splitTitleName = titleName.split('#')
sheetName = splitTitleName[0]
if len(splitTitleName)>1: #表示存在#则需要被序列化
codeContent = "using System;\n\n[Serializable]\npublic class "+sheetName
else:
codeContent = "public class "+sheetName
codeContent += "\n{\n"
for i in range(len(lstTs)):
codeContent += "\t//"+lstCs[i]+"\n"
codeContent += "\tpublic "+lstTs[i]+" "+lstEs[i]+";\n\n"
codeContent += "}"
fileName = codePath+"/"+sheetName+".cs"
with open(fileName,'w',encoding='utf_8') as f:
f.write(codeContent)
print("Create "+sheetName+".cs OK!!!")
####################### FUNC Create JSON ##########################
def CreateJsonFile(titleName,lstEs,lstTs,lstallR):
if not os.path.exists(jsonPath):
os.makedirs(jsonPath) #创建目标文件夹
jsonContent = "["
splitTitleName = titleName.split('#')
sheetName = splitTitleName[0]
#增加元素
for row in lstallR:
jsonContent += "{"
for i,t in enumerate(lstTs):
if t in ('float','int','double','long'):
jsonContent += quotation + lstEs[i] + quotation + ":"+str(row[i])+","
else:
jsonContent += quotation + lstEs[i] + quotation + ":"+quotation + str(row[i])+ quotation +","
jsonContent = jsonContent.strip(',')
jsonContent += "},"
jsonContent = jsonContent.strip(',')
jsonContent += "]"
jsonContent = jsonContent.replace('\r','').replace('\n','')
fileName = jsonPath+"/"+sheetName+".json"
with open(fileName,'w',encoding='utf_8') as f:
f.write(jsonContent)
print("Create "+sheetName+".json OK!!!")
def SetPath():
global codePath,jsonPath,projectParentPath
if projectParentPath.strip()=='': #自动根据当前py脚本所在位置确定项目Code和Json文件路径
codePath = "Code/"
jsonPath = "Json/"
else: #指定绝对路径解析,则追加就完事了
codePath = projectParentPath + codePath
jsonPath = projectParentPath + jsonPath
def Main():
allfiles = []
global excelDirPath
if excelDirPath.strip()=='': #如果没有指定Excel所在路径,那么默认为当前所在路径
excelDirPath=os.getcwd()
if os.path.isfile(excelDirPath):
allfiles.append(excelDirPath)
elif os.path.isdir(excelDirPath):
allfiles = os.listdir(excelDirPath)
files = []
for i in allfiles:
if os.path.splitext(i)[1]=='.xlsx':
if excelDirPath.strip()=='':
files.append(i)
else:
files.append(os.path.join(excelDirPath,i))
if(len(files)==0):
print("没有找到任何 .xlsx 文件")
return
for xlsx in files:
wb = load_workbook(filename=xlsx,data_only=True) #path,data_only=True表示只读取数值,如果是公式会取出计算的数值
lstSheets = wb.sheetnames
for i in lstSheets:
sheet = wb[i]
if sheet.max_row < 3:
print(sheet.title+' is NonStandard Excel Doc , Can not Deal!!!')
lstCName = []
lstEName = []
lstType = []
lstAllRow = []
lstIgnoreIndex = []
lstDefault = []
for r in range(sheet.max_row):
if r == 0:
for cCName in range(sheet.max_column):
CNameValue = sheet.cell(row=r+1,column=cCName+1).value
if CNameValue is None:
print("\nERROR : Sheet " + sheet.title + " Cell ("+str(r+1)+","+str(cCName+1)+") Is NONE\n")
if CNameValue.find('#') != -1:
lstDefault.append(CNameValue.split('#')[1])
else:
lstDefault.append('')
if CNameValue[0] == '_':
lstIgnoreIndex.append(cCName)
else:
lstCName.append(CNameValue)
if r == 1:
for cEName in range(sheet.max_column):
if not cEName in lstIgnoreIndex:
ENameValue = sheet.cell(row=r+1,column=cEName+1).value
if ENameValue is None:
print("\nERROR : Sheet : " + sheet.title + " Cell ("+str(r+1)+","+str(cEName+1)+") Is NONE\n")
lstEName.append(ENameValue)
if r == 2:
for cType in range(sheet.max_column):
if not cType in lstIgnoreIndex:
TypeValue = sheet.cell(row=r+1,column=cType+1).value
if TypeValue is None:
print("\nERROR : Sheet : " + sheet.title + " Cell ("+str(r+1)+","+str(cType+1)+") Is NONE\n")
lstType.append(TypeValue)
if r>2:
lstOneRow = []
for c in range(sheet.max_column):
if not c in lstIgnoreIndex:
cValue = sheet.cell(row=r+1,column=c+1).value
if cValue is None:
cValue = lstDefault[c]
if isinstance(cValue,str):
cValue = cValue.strip(stripstr)
lstOneRow.append(cValue)
lstAllRow.append(lstOneRow)
if isCreateCode:
CreateCodeFile(sheet.title,lstCName,lstEName,lstType)
if isCreateJson:
CreateJsonFile(sheet.title,lstEName,lstType,lstAllRow)
if __name__ == '__main__':
SetPath()
Main()
网友评论