Unity的Excel转表工具

该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  UnityCodeGenclass 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).valuefor 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:@staticmethoddef Tab(count):return "    " * count;# 代码生成函数@staticmethoddef 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).valuefieldtype = table.cell(2, index).valuefieldname = table.cell(3, index).valuefieldvar = 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).valuefieldtype = table.cell(2, index).valuefieldname = table.cell(3, index).valuefieldfunc = 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"# 获得keylistkeylist = []for index in fields:value = table.cell(4, index).valueif 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]).valuekeytype = 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]).valuefilecontent += 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]).valuekeytype = FieldFormat.Type2format[fieldtype][1]keyname = table.cell(3, keylist[0]).valuefilecontent += 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 = 0for keyindex in keylist:keyval = table.cell(3, keyindex).valuefilecontent += keyvalif keycount < (keylen - 1):filecontent += "And"keycount += 1filecontent += "("keycount = 0for keyindex in keylist:keytype = table.cell(2, keyindex).valuekeytype = FieldFormat.Type2format[keytype][1]keyval = table.cell(3, keyindex).valuekeyval = keyval.lower()filecontent += keytype + " " + keyvalif keycount < (keylen - 1):filecontent += ", "keycount += 1filecontent += ")\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 = 0for keyindex in keylist:keyval1 = table.cell(3, keyindex).valuekeyval2 = keyval1.lower()filecontent += "data." + keyval1 + " == " + keyval2if keycount < (keylen - 1):filecontent += " && "keycount += 1filecontent += ")\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 + pathpath = 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()# 生成配置管理类@staticmethoddef 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

如果遇到打开文件失败类的问题

可以先创建对应文件夹