• 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

    1. # --------------------------------Excel--------------------------------
    2. # Excel文件目录
    3. EXCEL_DIR = "./Excel/"
    4. # excel文件的后缀
    5. EXCEL_EXT = ".xlsx"
    6. # unity表格字段的过滤
    7. UNITY_TABLE_FIELD_FILTER = ["cs", "c", "CS", "C"]
    8. # 服务器表格字段的过滤
    9. SERVER_TABLE_FIELD_FILTER = ["cs", "s", "CS", "S"]
    10. # key的修饰符名字
    11. KEY_MODIFIER_NAME = "KEY"
    12. # --------------------------------Unity--------------------------------
    13. # 数据文件名
    14. DataFileName = "Tables.bytes"
    15. # 数据生成路径
    16. UnityDataDir = "./../BilliardGame/Assets/Res/Tables/"
    17. # 代码生成路径
    18. UnityCodeDir = "./../BilliardGame/Assets/Scripts/Billiard/Plugin/TableGenerate/"
    19. # --------------------------------Go--------------------------------
    20. # 数据文件名
    21. JsonFileName = "table.json"
    22. # 代码生成路径
    23. GoCodeDir = "./"

    Excel2Unity.py

    1. import os
    2. import xlrd
    3. from Config import EXCEL_DIR
    4. from Config import EXCEL_EXT
    5. from Config import UNITY_TABLE_FIELD_FILTER
    6. from Config import UnityDataDir
    7. from ConfigDataGen import ConfigDataGen
    8. from UnityCodeGen import UnityCodeGen
    9. class Excel2Unity:
    10. # 构造函数
    11. def __init__(self):
    12. self.mExcelFiles = [] # 所有的excel文件
    13. # 外部处理函数
    14. def Process(self):
    15. self.RecursiveSearchExcel(EXCEL_DIR)
    16. self.ProcessExcelExportUnity()
    17. # 递归查找文件
    18. def RecursiveSearchExcel(self, path):
    19. for pathdir in os.listdir(path): # 遍历当前目录
    20. fullpath = os.path.join(path, pathdir)
    21. if os.path.isdir(fullpath):
    22. self.RecursiveSearchExcel(fullpath)
    23. elif os.path.isfile(fullpath):
    24. if os.path.splitext(fullpath)[1] == EXCEL_EXT:
    25. self.mExcelFiles.append(fullpath)
    26. # 处理excel文件
    27. def ProcessExcelExportUnity(self):
    28. allbytesdata = bytes()
    29. # 处理每个文件
    30. for filename in self.mExcelFiles:
    31. print("导出Unity-文件名:%s" %filename)
    32. data = xlrd.open_workbook(filename)
    33. table = data.sheets()[0]
    34. fields = self.FilterFieldData(table, UNITY_TABLE_FIELD_FILTER)
    35. #增加多语言配置
    36. languageKeys = ["EN"]
    37. languageTables = []
    38. for x in data.sheets():
    39. if x != table and x.name in languageKeys:
    40. languageTables.append(x)
    41. # 数据
    42. cfgbytes = ConfigDataGen.Process(fields, table, languageTables)
    43. allbytesdata += cfgbytes
    44. # 代码
    45. UnityCodeGen.Process(filename, fields, table, languageTables)
    46. # 后处理
    47. ConfigDataGen.Save(allbytesdata, UnityDataDir)
    48. UnityCodeGen.GenConfigMangerCode(self.mExcelFiles)
    49. # 筛选字段数据
    50. def FilterFieldData(self, table, fieldfilter):
    51. fields = []
    52. for index in range(table.ncols):
    53. row = table.cell(1, index).value
    54. for field in fieldfilter:
    55. if row == field:
    56. fields.append(index)
    57. return fields

    UnityCodeGen.py

    1. import os
    2. from FieldFormat import FieldFormat
    3. from Config import KEY_MODIFIER_NAME
    4. from Config import EXCEL_DIR
    5. from Config import UnityCodeDir
    6. class UnityCodeGen:
    7. @staticmethod
    8. def Tab(count):
    9. return " " * count;
    10. # 代码生成函数
    11. @staticmethod
    12. def Process(filepath, fields, table, languageTables):
    13. # -----------------------table cfg class-----------------------
    14. filecontent = "\n"
    15. filecontent += "//-----------------------------------------------\n"
    16. filecontent += "// 生成代码不要修改\n"
    17. filecontent += "//-----------------------------------------------\n"
    18. filecontent += "\n"
    19. filecontent += "using System.Collections.Generic;\n"
    20. filecontent += "using System.IO;\n"
    21. filecontent += "using System.Text;\n"
    22. filecontent += "using UnityEngine;\n"
    23. filecontent += "\n"
    24. tablebasename = os.path.basename(filepath)
    25. tablebasename = tablebasename.split(".")[0]
    26. tablebasename = tablebasename.split("_")[0]
    27. tableclassname = tablebasename + "Cfg"
    28. filecontent += "public class " + tableclassname + "\n"
    29. filecontent += "{\n"
    30. for index in fields:
    31. fielddesc = table.cell(0, index).value
    32. fieldtype = table.cell(2, index).value
    33. fieldname = table.cell(3, index).value
    34. fieldvar = FieldFormat.Type2format[fieldtype][1]
    35. filecontent += UnityCodeGen.Tab(1) + "public " + fieldvar + " " + fieldname + ";"
    36. filecontent += UnityCodeGen.Tab(1) + "// " + fielddesc + "\n"
    37. for x in languageTables:
    38. for xcol in range(x.ncols):
    39. if x.cell(3, xcol).value == table.cell(3, index).value :
    40. filecontent += UnityCodeGen.Tab(1) + "public " + "string" + " " + x.name + fieldname + ";"
    41. filecontent += UnityCodeGen.Tab(1) + "// " + fielddesc + "\n"
    42. # Deserialize函数
    43. filecontent += "\n"
    44. filecontent += UnityCodeGen.Tab(1) + "public void Deserialize (DynamicPacket packet)\n"
    45. filecontent += UnityCodeGen.Tab(1) + "{\n"
    46. for index in fields:
    47. fielddesc = table.cell(0, index).value
    48. fieldtype = table.cell(2, index).value
    49. fieldname = table.cell(3, index).value
    50. fieldfunc = FieldFormat.Type2format[fieldtype][2]
    51. filecontent += UnityCodeGen.Tab(2) + fieldname + " = " + fieldfunc + ";\n"
    52. for x in languageTables:
    53. for xcol in range(x.ncols):
    54. if x.cell(3, xcol).value == table.cell(3, index).value :
    55. filecontent += UnityCodeGen.Tab(2) + x.name + fieldname + " = " + fieldfunc + ";\n"
    56. filecontent += UnityCodeGen.Tab(1) + "}\n"
    57. transposed = {}
    58. if len(languageTables) > 0:
    59. for i in range(languageTables[0].ncols):
    60. new_row = []
    61. for sheet in languageTables:
    62. new_row.append(sheet.name)
    63. transposed[languageTables[0].cell(3, i).value] = new_row
    64. for name in transposed:
    65. filecontent += UnityCodeGen.Tab(1) + "public string Get{0}\n".format(name,tableclassname)
    66. filecontent += UnityCodeGen.Tab(1) + "{\n"
    67. filecontent += UnityCodeGen.Tab(2) + "get\n"
    68. filecontent += UnityCodeGen.Tab(2) + "{\n"
    69. filecontent += UnityCodeGen.Tab(3) + "if(MultilingualUtil.CurrentLanguage == 0)\n"
    70. filecontent += UnityCodeGen.Tab(3) + "{\n"
    71. filecontent += UnityCodeGen.Tab(4) + "return {0};\n".format(name)
    72. filecontent += UnityCodeGen.Tab(3) + "}\n"
    73. for index in range(len(transposed[name])):
    74. filecontent += UnityCodeGen.Tab(3) + "if(MultilingualUtil.CurrentLanguage == (LANGUAGE_TYPE){0})\n".format(index+1)
    75. filecontent += UnityCodeGen.Tab(3) + "{\n"
    76. filecontent += UnityCodeGen.Tab(4) + "return {0};\n".format(transposed[name][index] + name)
    77. filecontent += UnityCodeGen.Tab(3) + "}\n"
    78. filecontent += UnityCodeGen.Tab(3) + "return null;\n"
    79. filecontent += UnityCodeGen.Tab(2) + "}\n"
    80. filecontent += UnityCodeGen.Tab(1) + "}\n"
    81. filecontent += "}\n"
    82. # -----------------------table cfg manager class-----------------------
    83. filecontent += "\n"
    84. tableclassmgrname = tablebasename + "CfgMgr"
    85. filecontent += "public class " + tableclassmgrname + "\n"
    86. filecontent += "{\n"
    87. filecontent += UnityCodeGen.Tab(1) + "private static " + tableclassmgrname + " mInstance;\n"
    88. filecontent += UnityCodeGen.Tab(1) + "\n"
    89. filecontent += UnityCodeGen.Tab(1) + "public static " + tableclassmgrname + " Instance\n"
    90. filecontent += UnityCodeGen.Tab(1) + "{\n"
    91. filecontent += UnityCodeGen.Tab(2) + "get\n"
    92. filecontent += UnityCodeGen.Tab(2) + "{\n"
    93. filecontent += UnityCodeGen.Tab(3) + "if (mInstance == null)\n"
    94. filecontent += UnityCodeGen.Tab(3) + "{\n"
    95. filecontent += UnityCodeGen.Tab(4) + "mInstance = new " + tableclassmgrname + "();\n"
    96. filecontent += UnityCodeGen.Tab(3) + "}\n"
    97. filecontent += UnityCodeGen.Tab(3) + "\n"
    98. filecontent += UnityCodeGen.Tab(3) + "return mInstance;\n"
    99. filecontent += UnityCodeGen.Tab(2) + "}\n"
    100. filecontent += UnityCodeGen.Tab(1) + "}\n"
    101. # 获得keylist
    102. keylist = []
    103. for index in fields:
    104. value = table.cell(4, index).value
    105. if value == KEY_MODIFIER_NAME:
    106. keylist.append(index)
    107. # 根据keylist判断
    108. keylen = keylist.__len__()
    109. uselist = (keylen != 1)
    110. filecontent += "\n"
    111. if uselist:
    112. filecontent += UnityCodeGen.Tab(1) + "private List<{0}> mList = new List<{0}>();\n".format(tableclassname)
    113. else:
    114. fieldtype = table.cell(2, keylist[0]).value
    115. keytype = FieldFormat.Type2format[fieldtype][1]
    116. filecontent += UnityCodeGen.Tab(1) + "private Dictionary<{0}, {1}> mDict = new Dictionary<{0}, {1}>();\n".format(keytype, tableclassname)
    117. filecontent += UnityCodeGen.Tab(1) + "\n"
    118. if uselist:
    119. filecontent += UnityCodeGen.Tab(1) + "public List<{0}> List\n".format(tableclassname)
    120. else:
    121. filecontent += UnityCodeGen.Tab(1) + "public Dictionary<{0}, {1}> Dict\n".format(keytype, tableclassname)
    122. filecontent += UnityCodeGen.Tab(1) + "{\n"
    123. if uselist:
    124. filecontent += UnityCodeGen.Tab(2) + "get {return mList;}\n"
    125. else:
    126. filecontent += UnityCodeGen.Tab(2) + "get {return mDict;}\n"
    127. filecontent += UnityCodeGen.Tab(1) + "}\n"
    128. # Deserialize函数
    129. filecontent += "\n"
    130. filecontent += UnityCodeGen.Tab(1) + "public void Deserialize (DynamicPacket packet)\n"
    131. filecontent += UnityCodeGen.Tab(1) + "{\n"
    132. filecontent += UnityCodeGen.Tab(2) + "int num = (int)packet.PackReadInt32();\n"
    133. filecontent += UnityCodeGen.Tab(2) + "for (int i = 0; i < num; i++)\n"
    134. filecontent += UnityCodeGen.Tab(2) + "{\n"
    135. filecontent += UnityCodeGen.Tab(3) + tableclassname + " item = new " + tableclassname + "();\n"
    136. filecontent += UnityCodeGen.Tab(3) + "item.Deserialize(packet);\n"
    137. if uselist:
    138. filecontent += UnityCodeGen.Tab(3) + "mList.Add(item);\n"
    139. else:
    140. keyname = table.cell(3, keylist[0]).value
    141. filecontent += UnityCodeGen.Tab(3) + "if (mDict.ContainsKey(item.{0}))\n".format(keyname)
    142. filecontent += UnityCodeGen.Tab(3) + "{\n"
    143. filecontent += UnityCodeGen.Tab(4) + "mDict[item.{0}] = item;\n".format(keyname)
    144. filecontent += UnityCodeGen.Tab(3) + "}\n"
    145. filecontent += UnityCodeGen.Tab(3) + "else\n"
    146. filecontent += UnityCodeGen.Tab(3) + "{\n"
    147. filecontent += UnityCodeGen.Tab(4) + "mDict.Add(item.{0}, item);\n".format(keyname)
    148. filecontent += UnityCodeGen.Tab(3) + "}\n"
    149. filecontent += UnityCodeGen.Tab(2) + "}\n"
    150. filecontent += UnityCodeGen.Tab(1) + "}\n"
    151. # GetData函数
    152. if keylen == 1: # 有一个key值使用dict取值
    153. fieldtype = table.cell(2, keylist[0]).value
    154. keytype = FieldFormat.Type2format[fieldtype][1]
    155. keyname = table.cell(3, keylist[0]).value
    156. filecontent += UnityCodeGen.Tab(1) + "\n"
    157. filecontent += UnityCodeGen.Tab(1) + "public {0} GetDataBy{1}({2} {3})\n".format(tableclassname, keyname, keytype, keyname.lower())
    158. filecontent += UnityCodeGen.Tab(1) + "{\n"
    159. filecontent += UnityCodeGen.Tab(2) + "if(mDict.ContainsKey({0}))\n".format(keyname.lower())
    160. filecontent += UnityCodeGen.Tab(2) + "{\n"
    161. filecontent += UnityCodeGen.Tab(3) + "return mDict[{0}];\n".format(keyname.lower())
    162. filecontent += UnityCodeGen.Tab(2) + "}\n"
    163. filecontent += UnityCodeGen.Tab(2) + "\n"
    164. filecontent += UnityCodeGen.Tab(2) + "return null;\n"
    165. filecontent += UnityCodeGen.Tab(1) + "}\n"
    166. elif keylen > 1: # 有多个key值
    167. filecontent += UnityCodeGen.Tab(1) + "\n"
    168. filecontent += UnityCodeGen.Tab(1) + "public " + tableclassname + " GetDataBy"
    169. keycount = 0
    170. for keyindex in keylist:
    171. keyval = table.cell(3, keyindex).value
    172. filecontent += keyval
    173. if keycount < (keylen - 1):
    174. filecontent += "And"
    175. keycount += 1
    176. filecontent += "("
    177. keycount = 0
    178. for keyindex in keylist:
    179. keytype = table.cell(2, keyindex).value
    180. keytype = FieldFormat.Type2format[keytype][1]
    181. keyval = table.cell(3, keyindex).value
    182. keyval = keyval.lower()
    183. filecontent += keytype + " " + keyval
    184. if keycount < (keylen - 1):
    185. filecontent += ", "
    186. keycount += 1
    187. filecontent += ")\n"
    188. filecontent += UnityCodeGen.Tab(1) + "{\n"
    189. filecontent += UnityCodeGen.Tab(2) + "foreach (" + tableclassname + " data in mList)\n"
    190. filecontent += UnityCodeGen.Tab(2) + "{\n"
    191. filecontent += UnityCodeGen.Tab(3) + "if ("
    192. keycount = 0
    193. for keyindex in keylist:
    194. keyval1 = table.cell(3, keyindex).value
    195. keyval2 = keyval1.lower()
    196. filecontent += "data." + keyval1 + " == " + keyval2
    197. if keycount < (keylen - 1):
    198. filecontent += " && "
    199. keycount += 1
    200. filecontent += ")\n"
    201. filecontent += UnityCodeGen.Tab(3) + "{\n"
    202. filecontent += UnityCodeGen.Tab(4) + "return data;\n"
    203. filecontent += UnityCodeGen.Tab(3) + "}\n"
    204. filecontent += UnityCodeGen.Tab(2) + "}\n"
    205. filecontent += UnityCodeGen.Tab(2) + "\n"
    206. filecontent += UnityCodeGen.Tab(2) + "return null;\n"
    207. filecontent += UnityCodeGen.Tab(1) + "}\n"
    208. for name in transposed:
    209. filecontent += UnityCodeGen.Tab(1) + "public string GetMultiLang{0}({1} cfg)\n".format(name,tableclassname)
    210. filecontent += UnityCodeGen.Tab(1) + "{\n"
    211. filecontent += UnityCodeGen.Tab(2) + "if(MultilingualUtil.CurrentLanguage == 0)\n"
    212. filecontent += UnityCodeGen.Tab(2) + "{\n"
    213. filecontent += UnityCodeGen.Tab(3) + "return cfg.{0};\n".format(name)
    214. filecontent += UnityCodeGen.Tab(2) + "}\n"
    215. for index in range(len(transposed[name])):
    216. filecontent += UnityCodeGen.Tab(2) + "if(MultilingualUtil.CurrentLanguage == (LANGUAGE_TYPE){0})\n".format(index+1)
    217. filecontent += UnityCodeGen.Tab(2) + "{\n"
    218. filecontent += UnityCodeGen.Tab(3) + "return cfg.{0};\n".format(transposed[name][index] + name)
    219. filecontent += UnityCodeGen.Tab(2) + "}\n"
    220. filecontent += UnityCodeGen.Tab(2) + "return null;\n"
    221. filecontent += UnityCodeGen.Tab(1) + "}\n"
    222. filecontent += "}\n"
    223. # 保存
    224. path = filepath.replace(EXCEL_DIR, "")
    225. path = UnityCodeDir + path
    226. path = os.path.splitext(path)[0]
    227. path = path.split("_")[0]
    228. path = path + "Cfg" + ".cs"
    229. # 生成文件目录, 不重复创建目录
    230. filedir = os.path.dirname(path)
    231. if os.path.exists(filedir) == False:
    232. os.makedirs(filedir)
    233. file = open(path, "wb")
    234. file.write(filecontent.encode())
    235. file.close()
    236. # 生成配置管理类
    237. @staticmethod
    238. def GenConfigMangerCode(files):
    239. path = UnityCodeDir + "ConfigManager.cs"
    240. filecontent = "\n"
    241. filecontent += "//-----------------------------------------------\n"
    242. filecontent += "// 生成代码不要修改\n"
    243. filecontent += "//-----------------------------------------------\n"
    244. filecontent += "\n"
    245. filecontent += "using System.Collections;\n"
    246. filecontent += "using System.Collections.Generic;\n"
    247. filecontent += "using UnityEngine;\n"
    248. filecontent += "using System.IO;\n"
    249. filecontent += "\n"
    250. filecontent += "public class ConfigManager\n"
    251. filecontent += "{\n"
    252. # 生成字段
    253. for file in files:
    254. tablebasename = os.path.basename(file)
    255. tablebasename = tablebasename.split(".")[0]
    256. tablebasename = tablebasename.split("_")[0]
    257. filecontent += UnityCodeGen.Tab(1) + "public static " + tablebasename + "CfgMgr "
    258. filecontent += "_"+tablebasename+"CfgMgr"
    259. filecontent += " = " +tablebasename+"CfgMgr.Instance; \n"
    260. # Deserialize函数
    261. filecontent += UnityCodeGen.Tab(1) + "private static void Deserialize(DynamicPacket dynamicPacket)\n"
    262. filecontent += UnityCodeGen.Tab(1) + "{\n"
    263. for file in files:
    264. tablebasename = os.path.basename(file)
    265. tablebasename = tablebasename.split(".")[0]
    266. tablebasename = tablebasename.split("_")[0]
    267. filecontent += UnityCodeGen.Tab(2) + tablebasename + "CfgMgr.Instance.Deserialize(dynamicPacket);\n"
    268. filecontent += UnityCodeGen.Tab(1) + "}\n"
    269. # LoadCsv函数
    270. filecontent += UnityCodeGen.Tab(1) + "\n"
    271. filecontent += UnityCodeGen.Tab(1) + "public static void LoadConfig(string cfgdatapath)\n"
    272. filecontent += UnityCodeGen.Tab(1) + "{\n"
    273. filecontent += UnityCodeGen.Tab(2) + "FileStream fileStream = new FileStream(cfgdatapath, FileMode.Open, FileAccess.Read);\n"
    274. filecontent += UnityCodeGen.Tab(2) + "BinaryReader binaryReader = new BinaryReader(fileStream);\n"
    275. filecontent += UnityCodeGen.Tab(2) + "int cnt = binaryReader.ReadInt32();\n"
    276. filecontent += UnityCodeGen.Tab(2) + "byte[] bytes = binaryReader.ReadBytes(cnt);\n"
    277. filecontent += UnityCodeGen.Tab(2) + "DynamicPacket dynamicPacket = new DynamicPacket(bytes);\n"
    278. filecontent += UnityCodeGen.Tab(2) + "Deserialize(dynamicPacket);\n"
    279. filecontent += UnityCodeGen.Tab(2) + "binaryReader.Close();\n"
    280. filecontent += UnityCodeGen.Tab(2) + "fileStream.Close();\n"
    281. filecontent += UnityCodeGen.Tab(1) + "}\n"
    282. # LoadCsv函数
    283. filecontent += UnityCodeGen.Tab(1) + "\n"
    284. filecontent += UnityCodeGen.Tab(1) + "public static void LoadConfig(byte[] bytes)\n"
    285. filecontent += UnityCodeGen.Tab(1) + "{\n"
    286. filecontent += UnityCodeGen.Tab(2) + "byte[] newBytes = new byte[bytes.Length];\n"
    287. filecontent += UnityCodeGen.Tab(2) + "for (int i = 4; i < bytes.Length; i++)\n"
    288. filecontent += UnityCodeGen.Tab(2) + "{\n"
    289. filecontent += UnityCodeGen.Tab(3) + "newBytes[i - 4] = bytes[i];\n"
    290. filecontent += UnityCodeGen.Tab(2) + "}\n"
    291. filecontent += UnityCodeGen.Tab(2) + "DynamicPacket dynamicPacket = new DynamicPacket(newBytes);\n"
    292. filecontent += UnityCodeGen.Tab(2) + "Deserialize(dynamicPacket);\n"
    293. filecontent += UnityCodeGen.Tab(1) + "}\n"
    294. filecontent += "}\n"
    295. # 保存
    296. file = open(path, "wb")
    297. file.write(filecontent.encode())
    298. file.close()

    完整项目链接:https://github.com/dazhu-z/UnityExcelTool

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

    可以先创建对应文件夹

  • 相关阅读:
    Python 进阶语法:JSON
    java word转pdf、word中关键字位置插入图片 工具类
    PyQt界面里如何加载本地视频以及调用摄像头实时检测(小白入门必看)
    Linux云计算之网络基础9——园区网络架构项目
    mysql5.7免安装版本
    业务中如何拓展微前端架构
    tslib库编译与移植
    https加密协议 https证书
    dubbo原理深入剖析
    ros1 实现Server端自定义四 Topic模式控制海龟运动
  • 原文地址:https://blog.csdn.net/u013654125/article/details/139922233