该Excel工具主要由Python语言完成,版本为3.x
主要功能:
1.转换后的数据存储结构为二进制。
2.excel文件可以选择多种数据类型:int、float、string、一维(int、float、string)、二维int、Map(int/int、int/string、int/float、string/int、string/float)
3.多个字段串联作为一个Key、单个字段作为一个Key
4.导出二进制(Unity使用),导出json(服务器使用)。
主要代码(Unity部分):
Config.py
-
- # --------------------------------Excel--------------------------------
- # Excel文件目录
- EXCEL_DIR = "./Excel/"
-
- # excel文件的后缀
- EXCEL_EXT = ".xlsx"
-
- # unity表格字段的过滤
- UNITY_TABLE_FIELD_FILTER = ["cs", "c", "CS", "C"]
-
- # 服务器表格字段的过滤
- SERVER_TABLE_FIELD_FILTER = ["cs", "s", "CS", "S"]
-
- # key的修饰符名字
- KEY_MODIFIER_NAME = "KEY"
-
- # --------------------------------Unity--------------------------------
-
- # 数据文件名
- DataFileName = "Tables.bytes"
-
- # 数据生成路径
- UnityDataDir = "./../BilliardGame/Assets/Res/Tables/"
-
- # 代码生成路径
- UnityCodeDir = "./../BilliardGame/Assets/Scripts/Billiard/Plugin/TableGenerate/"
-
- # --------------------------------Go--------------------------------
-
- # 数据文件名
- JsonFileName = "table.json"
-
- # 代码生成路径
- GoCodeDir = "./"
Excel2Unity.py
- import os
- import xlrd
- from Config import EXCEL_DIR
- from Config import EXCEL_EXT
- from Config import UNITY_TABLE_FIELD_FILTER
- from Config import UnityDataDir
- from ConfigDataGen import ConfigDataGen
- from UnityCodeGen import UnityCodeGen
-
- class Excel2Unity:
- # 构造函数
- def __init__(self):
- self.mExcelFiles = [] # 所有的excel文件
-
- # 外部处理函数
- def Process(self):
- self.RecursiveSearchExcel(EXCEL_DIR)
- self.ProcessExcelExportUnity()
-
- # 递归查找文件
- def RecursiveSearchExcel(self, path):
- for pathdir in os.listdir(path): # 遍历当前目录
- fullpath = os.path.join(path, pathdir)
-
- if os.path.isdir(fullpath):
- self.RecursiveSearchExcel(fullpath)
- elif os.path.isfile(fullpath):
- if os.path.splitext(fullpath)[1] == EXCEL_EXT:
- self.mExcelFiles.append(fullpath)
-
- # 处理excel文件
- def ProcessExcelExportUnity(self):
-
- allbytesdata = bytes()
-
- # 处理每个文件
- for filename in self.mExcelFiles:
- print("导出Unity-文件名:%s" %filename)
- data = xlrd.open_workbook(filename)
- table = data.sheets()[0]
- fields = self.FilterFieldData(table, UNITY_TABLE_FIELD_FILTER)
-
- #增加多语言配置
- languageKeys = ["EN"]
- languageTables = []
- for x in data.sheets():
- if x != table and x.name in languageKeys:
- languageTables.append(x)
- # 数据
- cfgbytes = ConfigDataGen.Process(fields, table, languageTables)
- allbytesdata += cfgbytes
-
- # 代码
- UnityCodeGen.Process(filename, fields, table, languageTables)
-
- # 后处理
- ConfigDataGen.Save(allbytesdata, UnityDataDir)
- UnityCodeGen.GenConfigMangerCode(self.mExcelFiles)
-
- # 筛选字段数据
- def FilterFieldData(self, table, fieldfilter):
- fields = []
- for index in range(table.ncols):
- row = table.cell(1, index).value
- for field in fieldfilter:
- if row == field:
- fields.append(index)
-
- return fields
UnityCodeGen.py
-
- import os
- from FieldFormat import FieldFormat
- from Config import KEY_MODIFIER_NAME
- from Config import EXCEL_DIR
- from Config import UnityCodeDir
- class UnityCodeGen:
-
- @staticmethod
- def Tab(count):
- return " " * count;
-
- # 代码生成函数
- @staticmethod
- def Process(filepath, fields, table, languageTables):
-
- # -----------------------table cfg class-----------------------
- filecontent = "\n"
- filecontent += "//-----------------------------------------------\n"
- filecontent += "// 生成代码不要修改\n"
- filecontent += "//-----------------------------------------------\n"
- filecontent += "\n"
- filecontent += "using System.Collections.Generic;\n"
- filecontent += "using System.IO;\n"
- filecontent += "using System.Text;\n"
- filecontent += "using UnityEngine;\n"
- filecontent += "\n"
-
- tablebasename = os.path.basename(filepath)
- tablebasename = tablebasename.split(".")[0]
- tablebasename = tablebasename.split("_")[0]
- tableclassname = tablebasename + "Cfg"
- filecontent += "public class " + tableclassname + "\n"
- filecontent += "{\n"
-
- for index in fields:
- fielddesc = table.cell(0, index).value
- fieldtype = table.cell(2, index).value
- fieldname = table.cell(3, index).value
- fieldvar = FieldFormat.Type2format[fieldtype][1]
- filecontent += UnityCodeGen.Tab(1) + "public " + fieldvar + " " + fieldname + ";"
- filecontent += UnityCodeGen.Tab(1) + "// " + fielddesc + "\n"
- for x in languageTables:
- for xcol in range(x.ncols):
- if x.cell(3, xcol).value == table.cell(3, index).value :
- filecontent += UnityCodeGen.Tab(1) + "public " + "string" + " " + x.name + fieldname + ";"
- filecontent += UnityCodeGen.Tab(1) + "// " + fielddesc + "\n"
-
- # Deserialize函数
- filecontent += "\n"
- filecontent += UnityCodeGen.Tab(1) + "public void Deserialize (DynamicPacket packet)\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
-
- for index in fields:
- fielddesc = table.cell(0, index).value
- fieldtype = table.cell(2, index).value
- fieldname = table.cell(3, index).value
- fieldfunc = FieldFormat.Type2format[fieldtype][2]
- filecontent += UnityCodeGen.Tab(2) + fieldname + " = " + fieldfunc + ";\n"
- for x in languageTables:
- for xcol in range(x.ncols):
- if x.cell(3, xcol).value == table.cell(3, index).value :
- filecontent += UnityCodeGen.Tab(2) + x.name + fieldname + " = " + fieldfunc + ";\n"
-
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- transposed = {}
- if len(languageTables) > 0:
- for i in range(languageTables[0].ncols):
- new_row = []
- for sheet in languageTables:
- new_row.append(sheet.name)
- transposed[languageTables[0].cell(3, i).value] = new_row
-
-
- for name in transposed:
- filecontent += UnityCodeGen.Tab(1) + "public string Get{0}\n".format(name,tableclassname)
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "get\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "if(MultilingualUtil.CurrentLanguage == 0)\n"
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "return {0};\n".format(name)
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- for index in range(len(transposed[name])):
- filecontent += UnityCodeGen.Tab(3) + "if(MultilingualUtil.CurrentLanguage == (LANGUAGE_TYPE){0})\n".format(index+1)
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "return {0};\n".format(transposed[name][index] + name)
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- filecontent += UnityCodeGen.Tab(3) + "return null;\n"
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
-
-
- filecontent += "}\n"
-
- # -----------------------table cfg manager class-----------------------
- filecontent += "\n"
- tableclassmgrname = tablebasename + "CfgMgr"
- filecontent += "public class " + tableclassmgrname + "\n"
- filecontent += "{\n"
-
- filecontent += UnityCodeGen.Tab(1) + "private static " + tableclassmgrname + " mInstance;\n"
- filecontent += UnityCodeGen.Tab(1) + "\n"
- filecontent += UnityCodeGen.Tab(1) + "public static " + tableclassmgrname + " Instance\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "get\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "if (mInstance == null)\n"
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "mInstance = new " + tableclassmgrname + "();\n"
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- filecontent += UnityCodeGen.Tab(3) + "\n"
- filecontent += UnityCodeGen.Tab(3) + "return mInstance;\n"
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- # 获得keylist
- keylist = []
- for index in fields:
- value = table.cell(4, index).value
- if value == KEY_MODIFIER_NAME:
- keylist.append(index)
-
- # 根据keylist判断
- keylen = keylist.__len__()
- uselist = (keylen != 1)
- filecontent += "\n"
- if uselist:
- filecontent += UnityCodeGen.Tab(1) + "private List<{0}> mList = new List<{0}>();\n".format(tableclassname)
- else:
- fieldtype = table.cell(2, keylist[0]).value
- keytype = FieldFormat.Type2format[fieldtype][1]
- filecontent += UnityCodeGen.Tab(1) + "private Dictionary<{0}, {1}> mDict = new Dictionary<{0}, {1}>();\n".format(keytype, tableclassname)
-
- filecontent += UnityCodeGen.Tab(1) + "\n"
- if uselist:
- filecontent += UnityCodeGen.Tab(1) + "public List<{0}> List\n".format(tableclassname)
- else:
- filecontent += UnityCodeGen.Tab(1) + "public Dictionary<{0}, {1}> Dict\n".format(keytype, tableclassname)
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- if uselist:
- filecontent += UnityCodeGen.Tab(2) + "get {return mList;}\n"
- else:
- filecontent += UnityCodeGen.Tab(2) + "get {return mDict;}\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- # Deserialize函数
- filecontent += "\n"
- filecontent += UnityCodeGen.Tab(1) + "public void Deserialize (DynamicPacket packet)\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "int num = (int)packet.PackReadInt32();\n"
- filecontent += UnityCodeGen.Tab(2) + "for (int i = 0; i < num; i++)\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + tableclassname + " item = new " + tableclassname + "();\n"
- filecontent += UnityCodeGen.Tab(3) + "item.Deserialize(packet);\n"
- if uselist:
- filecontent += UnityCodeGen.Tab(3) + "mList.Add(item);\n"
- else:
- keyname = table.cell(3, keylist[0]).value
- filecontent += UnityCodeGen.Tab(3) + "if (mDict.ContainsKey(item.{0}))\n".format(keyname)
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "mDict[item.{0}] = item;\n".format(keyname)
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- filecontent += UnityCodeGen.Tab(3) + "else\n"
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "mDict.Add(item.{0}, item);\n".format(keyname)
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- # GetData函数
- if keylen == 1: # 有一个key值使用dict取值
- fieldtype = table.cell(2, keylist[0]).value
- keytype = FieldFormat.Type2format[fieldtype][1]
- keyname = table.cell(3, keylist[0]).value
- filecontent += UnityCodeGen.Tab(1) + "\n"
- filecontent += UnityCodeGen.Tab(1) + "public {0} GetDataBy{1}({2} {3})\n".format(tableclassname, keyname, keytype, keyname.lower())
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "if(mDict.ContainsKey({0}))\n".format(keyname.lower())
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "return mDict[{0}];\n".format(keyname.lower())
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "\n"
- filecontent += UnityCodeGen.Tab(2) + "return null;\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
- elif keylen > 1: # 有多个key值
- filecontent += UnityCodeGen.Tab(1) + "\n"
- filecontent += UnityCodeGen.Tab(1) + "public " + tableclassname + " GetDataBy"
-
- keycount = 0
- for keyindex in keylist:
- keyval = table.cell(3, keyindex).value
- filecontent += keyval
- if keycount < (keylen - 1):
- filecontent += "And"
- keycount += 1
-
- filecontent += "("
-
- keycount = 0
- for keyindex in keylist:
- keytype = table.cell(2, keyindex).value
- keytype = FieldFormat.Type2format[keytype][1]
- keyval = table.cell(3, keyindex).value
- keyval = keyval.lower()
- filecontent += keytype + " " + keyval
- if keycount < (keylen - 1):
- filecontent += ", "
- keycount += 1
- filecontent += ")\n"
-
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "foreach (" + tableclassname + " data in mList)\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
-
- filecontent += UnityCodeGen.Tab(3) + "if ("
- keycount = 0
- for keyindex in keylist:
- keyval1 = table.cell(3, keyindex).value
- keyval2 = keyval1.lower()
- filecontent += "data." + keyval1 + " == " + keyval2
- if keycount < (keylen - 1):
- filecontent += " && "
- keycount += 1
- filecontent += ")\n"
-
- filecontent += UnityCodeGen.Tab(3) + "{\n"
- filecontent += UnityCodeGen.Tab(4) + "return data;\n"
- filecontent += UnityCodeGen.Tab(3) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "\n"
- filecontent += UnityCodeGen.Tab(2) + "return null;\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
-
- for name in transposed:
- filecontent += UnityCodeGen.Tab(1) + "public string GetMultiLang{0}({1} cfg)\n".format(name,tableclassname)
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "if(MultilingualUtil.CurrentLanguage == 0)\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "return cfg.{0};\n".format(name)
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- for index in range(len(transposed[name])):
- filecontent += UnityCodeGen.Tab(2) + "if(MultilingualUtil.CurrentLanguage == (LANGUAGE_TYPE){0})\n".format(index+1)
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "return cfg.{0};\n".format(transposed[name][index] + name)
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "return null;\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- filecontent += "}\n"
-
- # 保存
- path = filepath.replace(EXCEL_DIR, "")
- path = UnityCodeDir + path
- path = os.path.splitext(path)[0]
- path = path.split("_")[0]
- path = path + "Cfg" + ".cs"
-
- # 生成文件目录, 不重复创建目录
- filedir = os.path.dirname(path)
- if os.path.exists(filedir) == False:
- os.makedirs(filedir)
-
- file = open(path, "wb")
- file.write(filecontent.encode())
- file.close()
-
- # 生成配置管理类
- @staticmethod
- def GenConfigMangerCode(files):
- path = UnityCodeDir + "ConfigManager.cs"
-
- filecontent = "\n"
- filecontent += "//-----------------------------------------------\n"
- filecontent += "// 生成代码不要修改\n"
- filecontent += "//-----------------------------------------------\n"
- filecontent += "\n"
- filecontent += "using System.Collections;\n"
- filecontent += "using System.Collections.Generic;\n"
- filecontent += "using UnityEngine;\n"
- filecontent += "using System.IO;\n"
- filecontent += "\n"
- filecontent += "public class ConfigManager\n"
- filecontent += "{\n"
-
- # 生成字段
- for file in files:
- tablebasename = os.path.basename(file)
- tablebasename = tablebasename.split(".")[0]
- tablebasename = tablebasename.split("_")[0]
- filecontent += UnityCodeGen.Tab(1) + "public static " + tablebasename + "CfgMgr "
- filecontent += "_"+tablebasename+"CfgMgr"
- filecontent += " = " +tablebasename+"CfgMgr.Instance; \n"
-
- # Deserialize函数
- filecontent += UnityCodeGen.Tab(1) + "private static void Deserialize(DynamicPacket dynamicPacket)\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- for file in files:
- tablebasename = os.path.basename(file)
- tablebasename = tablebasename.split(".")[0]
- tablebasename = tablebasename.split("_")[0]
- filecontent += UnityCodeGen.Tab(2) + tablebasename + "CfgMgr.Instance.Deserialize(dynamicPacket);\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- # LoadCsv函数
- filecontent += UnityCodeGen.Tab(1) + "\n"
- filecontent += UnityCodeGen.Tab(1) + "public static void LoadConfig(string cfgdatapath)\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "FileStream fileStream = new FileStream(cfgdatapath, FileMode.Open, FileAccess.Read);\n"
- filecontent += UnityCodeGen.Tab(2) + "BinaryReader binaryReader = new BinaryReader(fileStream);\n"
- filecontent += UnityCodeGen.Tab(2) + "int cnt = binaryReader.ReadInt32();\n"
- filecontent += UnityCodeGen.Tab(2) + "byte[] bytes = binaryReader.ReadBytes(cnt);\n"
- filecontent += UnityCodeGen.Tab(2) + "DynamicPacket dynamicPacket = new DynamicPacket(bytes);\n"
- filecontent += UnityCodeGen.Tab(2) + "Deserialize(dynamicPacket);\n"
- filecontent += UnityCodeGen.Tab(2) + "binaryReader.Close();\n"
- filecontent += UnityCodeGen.Tab(2) + "fileStream.Close();\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- # LoadCsv函数
- filecontent += UnityCodeGen.Tab(1) + "\n"
- filecontent += UnityCodeGen.Tab(1) + "public static void LoadConfig(byte[] bytes)\n"
- filecontent += UnityCodeGen.Tab(1) + "{\n"
- filecontent += UnityCodeGen.Tab(2) + "byte[] newBytes = new byte[bytes.Length];\n"
- filecontent += UnityCodeGen.Tab(2) + "for (int i = 4; i < bytes.Length; i++)\n"
- filecontent += UnityCodeGen.Tab(2) + "{\n"
- filecontent += UnityCodeGen.Tab(3) + "newBytes[i - 4] = bytes[i];\n"
- filecontent += UnityCodeGen.Tab(2) + "}\n"
- filecontent += UnityCodeGen.Tab(2) + "DynamicPacket dynamicPacket = new DynamicPacket(newBytes);\n"
- filecontent += UnityCodeGen.Tab(2) + "Deserialize(dynamicPacket);\n"
- filecontent += UnityCodeGen.Tab(1) + "}\n"
-
- filecontent += "}\n"
-
- # 保存
- file = open(path, "wb")
- file.write(filecontent.encode())
- file.close()
完整项目链接:https://github.com/dazhu-z/UnityExcelTool
如果遇到打开文件失败类的问题
可以先创建对应文件夹