Unity 編輯器開發之 Excel導表工具

一個簡單的Excel導表工具，可以用來熱更數據配置

工具使用：

執行菜單 SDGSupporter/Excel/1.Excel2Cs 生成c#腳本。
等待C#類編譯完成
執行菜單 SDGSupporter/Excel/2.Excel2Bytes 生成XmI臨時文件，生成bytes二進制表格數據，刪除xml臨時文件。
運行場景SampleScene.unity Console面板打印表格內容。

工具代碼：

Assets\Editor\Excel2CsBytesTool.cs 文件夾路徑可配置在這里Assets\Scripts\BaseTable.cs Excel配置時支持的類型數組

源Excel文件夾：

ExcelData

生成的C#類：

eg: Assets \Scripts \DataTable \weapon.cs

生成的bytes文件：

eg: Resources \DataTable \weapon.bytes

首先準備一個Excel表填入數據，注意需要放在Edior目錄下

然后編寫Excel2CsBytesTool.cs腳本

附上完整代碼：

using System.IO;
using Excel;
using System.Data;
using UnityEditor;
using UnityEngine;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using Table;
using System;
using System.Xml;
using System.Xml.Serialization;
using System.Runtime.Serialization.Formatters.Binary;/// <summary>
/// Excel生成bytes和cs工具
/// </summary>
public class Excel2CsBytesTool
{static string ExcelDataPath = Application.dataPath + "/../ExcelData";//源Excel文件夾,xlsx格式static string BytesDataPath = Application.dataPath + "/Resources/DataTable";//生成的bytes文件夾static string CsClassPath = Application.dataPath + "/Scripts/DataTable";//生成的c#腳本文件夾static string XmlDataPath = ExcelDataPath + "/tempXmlData";//生成的xml(臨時)文件夾..static string AllCsHead = "all";//序列化結構體的數組類.類名前綴static char ArrayTypeSplitChar = '#';//數組類型值拆分符: int[] 1#2#34 string[] 你好#再見 bool[] true#false ...static bool IsDeleteXmlInFinish = true;//生成bytes后是否刪除中間文件xml[MenuItem("SDGSupporter/Excel/Excel2Cs")]static void Excel2Cs(){Init();Excel2CsOrXml(true);}[MenuItem("SDGSupporter/Excel/Excel2Bytes")]static void Excel2Xml2Bytes(){Init();//生成中間文件xmlExcel2CsOrXml(false);//生成bytesWriteBytes();}static void Init(){if (!Directory.Exists(CsClassPath)){Directory.CreateDirectory(CsClassPath);}if (!Directory.Exists(XmlDataPath)){Directory.CreateDirectory(XmlDataPath);}if (!Directory.Exists(BytesDataPath)){Directory.CreateDirectory(BytesDataPath);}}static void WriteCs(string className, string[] names, string[] types, string[] descs){try{StringBuilder stringBuilder = new StringBuilder();stringBuilder.AppendLine("using System;");stringBuilder.AppendLine("using System.Collections.Generic;");stringBuilder.AppendLine("using System.IO;");stringBuilder.AppendLine("using System.Runtime.Serialization.Formatters.Binary;");stringBuilder.AppendLine("using System.Xml.Serialization;");stringBuilder.Append("\n");stringBuilder.AppendLine("namespace Table");stringBuilder.AppendLine("{");stringBuilder.AppendLine("    [Serializable]");stringBuilder.AppendLine("    public class " + className);stringBuilder.AppendLine("    {");for (int i = 0; i < names.Length; i++){stringBuilder.AppendLine("        /// <summary>");stringBuilder.AppendLine("        /// " + descs[i]);stringBuilder.AppendLine("        /// </summary>");stringBuilder.AppendLine("        [XmlAttribute(\"" + names[i] + "\")]");string type = types[i];if (type.Contains("[]")){//type = type.Replace("[]", "");//stringBuilder.AppendLine("        public List<" + type + "> " + names[i] + ";");//可選代碼：//用_name字段去反序列化，name取_name.item的值,直接返回list<type>。//因為xml每行可能有多個數組字段，這樣就多了一層變量item，所以訪問的時候需要.item才能取到list<type>//因此用額外的一個變量直接返回List<type>。type = type.Replace("[]", "");stringBuilder.AppendLine("        public List<" + type + "> " + names[i] + "");stringBuilder.AppendLine("        {");stringBuilder.AppendLine("            get");stringBuilder.AppendLine("            {");stringBuilder.AppendLine("                if (_" + names[i] + " != null)");stringBuilder.AppendLine("                {");stringBuilder.AppendLine("                    return _" + names[i] + ".item;");stringBuilder.AppendLine("                }");stringBuilder.AppendLine("                return null;");stringBuilder.AppendLine("            }");stringBuilder.AppendLine("        }");stringBuilder.AppendLine("        [XmlElementAttribute(\"" + names[i] + "\")]");stringBuilder.AppendLine("        public " + type + "Array _" + names[i] + ";");}else{stringBuilder.AppendLine("        public " + type + " " + names[i] + ";");}stringBuilder.Append("\n");}stringBuilder.AppendLine("        public static List<" + className + "> LoadBytes()");stringBuilder.AppendLine("        {");stringBuilder.AppendLine("            string bytesPath = \"" + BytesDataPath + "/" + className + ".bytes\";");stringBuilder.AppendLine("            if (!File.Exists(bytesPath))");stringBuilder.AppendLine("                return null;");stringBuilder.AppendLine("            using (FileStream stream = new FileStream(bytesPath, FileMode.Open))");stringBuilder.AppendLine("            {");stringBuilder.AppendLine("                BinaryFormatter binaryFormatter = new BinaryFormatter();");stringBuilder.AppendLine("                all" + className + " table = binaryFormatter.Deserialize(stream) as all" + className + ";");stringBuilder.AppendLine("                return table." + className + "s;");stringBuilder.AppendLine("            }");stringBuilder.AppendLine("        }");stringBuilder.AppendLine("    }");stringBuilder.Append("\n");stringBuilder.AppendLine("    [Serializable]");stringBuilder.AppendLine("    public class " + AllCsHead + className);stringBuilder.AppendLine("    {");stringBuilder.AppendLine("        public List<" + className + "> " + className + "s;");stringBuilder.AppendLine("    }");stringBuilder.AppendLine("}");string csPath = CsClassPath + "/" + className + ".cs";if (File.Exists(csPath)){File.Delete(csPath);}using (StreamWriter sw = new StreamWriter(csPath)){sw.Write(stringBuilder);Debug.Log("生成:" + csPath);}}catch (System.Exception e){Debug.LogError("寫入CS失敗:" + e.Message);throw;}}static void WriteXml(string className, string[] names, string[] types, List<string[]> datasList){try{StringBuilder stringBuilder = new StringBuilder();stringBuilder.AppendLine("<?xml version=\"1.0\" encoding=\"utf-8\"?>");stringBuilder.AppendLine("<" + AllCsHead + className + ">");stringBuilder.AppendLine("<" + className + "s>");for (int d = 0; d < datasList.Count; d++){stringBuilder.Append("\t<" + className + " ");//單行數據string[] datas = datasList[d];//填充屬性節點for (int c = 0; c < datas.Length; c++){string type = types[c];if (!type.Contains("[]")){string name = names[c];string value = datas[c];stringBuilder.Append(name + "=\"" + value + "\"" + (c == datas.Length - 1 ? "" : " "));}}stringBuilder.Append(">\n");//填充子元素節點(數組類型字段)for (int c = 0; c < datas.Length; c++){string type = types[c];if (type.Contains("[]")){string name = names[c];string value = datas[c];string[] values = value.Split(ArrayTypeSplitChar);stringBuilder.AppendLine("\t\t<" + name + ">");for (int v = 0; v < values.Length; v++){stringBuilder.AppendLine("\t\t\t<item>" + values[v] + "</item>");}stringBuilder.AppendLine("\t\t</" + name + ">");}}stringBuilder.AppendLine("\t</" + className + ">");}stringBuilder.AppendLine("</" + className + "s>");stringBuilder.AppendLine("</" + AllCsHead + className + ">");string xmlPath = XmlDataPath + "/" + className + ".xml";if (File.Exists(xmlPath)){File.Delete(xmlPath);}using (StreamWriter sw = new StreamWriter(xmlPath)){sw.Write(stringBuilder);Debug.Log("生成文件:" + xmlPath);}}catch (System.Exception e){Debug.LogError("寫入Xml失敗:" + e.Message);}}static void Excel2CsOrXml(bool isCs){string[] excelPaths = Directory.GetFiles(ExcelDataPath, "*.xlsx");for (int e = 0; e < excelPaths.Length; e++){//0.讀Excelstring className;//類型名string[] names;//字段名string[] types;//字段類型string[] descs;//字段描述List<string[]> datasList;//數據try{string excelPath = excelPaths[e];//excel路徑  className = Path.GetFileNameWithoutExtension(excelPath).ToLower();FileStream fileStream = File.Open(excelPath, FileMode.Open, FileAccess.Read);IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);// 表格數據全部讀取到result里DataSet result = excelDataReader.AsDataSet();// 獲取表格列數int columns = result.Tables[0].Columns.Count;// 獲取表格行數int rows = result.Tables[0].Rows.Count;// 根據行列依次讀取表格中的每個數據names = new string[columns];types = new string[columns];descs = new string[columns];datasList = new List<string[]>();for (int r = 0; r < rows; r++){string[] curRowData = new string[columns];for (int c = 0; c < columns; c++){//解析：獲取第一個表格中指定行指定列的數據string value = result.Tables[0].Rows[r][c].ToString();//清除前兩行的變量名、變量類型 首尾空格if (r < 2){value = value.TrimStart(' ').TrimEnd(' ');}curRowData[c] = value;}//解析：第一行類變量名if (r == 0){names = curRowData;}//解析：第二行類變量類型else if (r == 1){types = curRowData;}//解析：第三行類變量描述else if (r == 2){descs = curRowData;}//解析：第三行開始是數據else{datasList.Add(curRowData);}}}catch (System.Exception exc){Debug.LogError("請關閉Excel:" + exc.Message);return;}if (isCs){//寫CsWriteCs(className, names, types, descs);}else{//寫XmlWriteXml(className, names, types, datasList);}}AssetDatabase.Refresh();}static void WriteBytes(){string csAssemblyPath = Application.dataPath + "/../Library/ScriptAssemblies/Assembly-CSharp.dll";Assembly assembly = Assembly.LoadFile(csAssemblyPath);if (assembly != null){Type[] types = assembly.GetTypes();for (int i = 0; i < types.Length; i++){Type type = types[i];if (type.Namespace == "Table" && type.Name.Contains(AllCsHead)){string className = type.Name.Replace(AllCsHead, "");//讀取xml數據string xmlPath = XmlDataPath + "/" + className + ".xml";if (!File.Exists(xmlPath)){Debug.LogError("Xml文件讀取失敗:" + xmlPath);continue;}object table;using (Stream reader = new FileStream(xmlPath, FileMode.Open)){//讀取xml實例化table: all+classname//object table = assembly.CreateInstance("Table." + type.Name);XmlSerializer xmlSerializer = new XmlSerializer(type);table = xmlSerializer.Deserialize(reader);}//obj序列化二進制string bytesPath = BytesDataPath + "/" + className + ".bytes";if (File.Exists(bytesPath)){File.Delete(bytesPath);}using (FileStream fileStream = new FileStream(bytesPath, FileMode.Create)){BinaryFormatter binaryFormatter = new BinaryFormatter();binaryFormatter.Serialize(fileStream, table);Debug.Log("生成:" + bytesPath);}if (IsDeleteXmlInFinish){File.Delete(xmlPath);Debug.Log("刪除:" + bytesPath);}}}}if (IsDeleteXmlInFinish){Directory.Delete(XmlDataPath);Debug.Log("刪除:" + XmlDataPath);}}
}

接著是自定義的數據配置類:

using System;
using System.Collections.Generic;
using System.Xml.Serialization;/// <summary>
/// Excel2CsBytesTool
/// Excel可以配置的數組類型：string[] int[] bool[] 
/// 可自行擴展
/// </summary>
namespace Table
{[System.SerializableAttribute()][System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]public class stringArray{[System.Xml.Serialization.XmlElementAttribute("item")]public List<string> item { get; set; }}[System.SerializableAttribute()][System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]public class intArray{[System.Xml.Serialization.XmlElementAttribute("item")]public List<int> item { get; set; }}[System.SerializableAttribute()][System.Xml.Serialization.XmlTypeAttribute(AnonymousType = true)]public class boolArray{[System.Xml.Serialization.XmlElementAttribute("item")]public List<bool> item { get; set; }}
}

然后就可以在Unity菜單界面看到我們的編輯器工具了