###  说明文档
#    重要写在开头  文件必须为.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()