一、前言
最近新项目要用到Excel导表工具，然后我就去网上找啊找，找了个我感觉还不错的，我稍作了些修改，为了方便整了个Excel导表工具，最终导出的excel数据是二进制文件，这里也分享给大家看看，let’s go，开整！
二、新建项目
咱们先新建一个项目，命名为UnityExcelTools，用的是2021.3.14f1版本，后面会把版本链接分享给大家
在项目的同级目录新建一个excel表格，我的excel表格模板是这样子的，其中id属于唯一key值，规定是必须要的且放在第一列，如果你不喜欢这种方式的话，也可以根据自己喜欢的样式做出修改。表格在后面我也会给出。
注：这里说下前4行表达的意思：
第一行：字段的描述，相当于代码里的注释
第二行：值的类型，其中包含int，string，float，int[]，string[]，float[]。（如果这些不满足的话可以自己加上类型）
第三行：字段名，相当于我们导出后的变量名，首字母大小写都可以，因为我会在导表的时候统一改成大写。不过为了美观，还是统一一下。
第四行：导出的端。“c”表示只导出客户端，“s”表示只导出服务端，“a”表示导出双端。
在Unity工程中，按下面的方式建好我们的文件目录。
首先咱们来定义我们读写二进制数据的基类，在"Assets/Scripts/Runtime/Config/Base"目录下新建一个PickData.cs，直接上代码。
using System.Collections.Generic;
using System;
using System.Text;
public class PickData
{
/// <summary>
/// 读取字节数据
/// </summary>
/// <param name="datas">数据</param>
/// <param name="index">当前读取到第几个索引</param>
public virtual void ReadData(byte[] datas, ref int index, ref int uid) { }
public static T[] GetObjs<T>(byte[] datas) where T : PickData, new()
{
int index = 0;
int uid = 0;
int count = PickData.ReadInt(datas, ref index);
T[] results = new T[count];
for (int i = 0; i < count; i++)
{
results[i] = new T();
results[i].ReadData(datas, ref index, ref uid);
}
return results;
}
public static byte[] WriteInt(int value)
{
byte[] data = System.BitConverter.GetBytes(value);
return data;
}
public static byte[] WriteFloat(float value)
{
byte[] data = System.BitConverter.GetBytes(value);
return data;
}
public static byte[] WriteString(string value)
{
byte[] data = Encoding.UTF8.GetBytes(value);
int length = data.Length;
List<byte> byteList = new List<byte>();
byteList.AddRange(WriteInt(length));
byteList.AddRange(data);
return byteList.ToArray();
}
public static byte[] WriteIntArray(int[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteInt(values[i]));
}
return byteList.ToArray();
}
public static byte[] WriteFloatArray(float[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteFloat(values[i]));
}
return byteList.ToArray();
}
public static byte[] WriteStringArray(string[] values)
{
List<byte> byteList = new List<byte>();
int count = values.Length;
byteList.AddRange(WriteInt(count));
for (int i = 0; i < count; i++)
{
byteList.AddRange(WriteString(values[i]));
}
return byteList.ToArray();
}
public static int ReadInt(byte[] data, ref int index)
{
byte[] read = new byte[sizeof(int)];
Array.Copy(data, index, read, 0, read.Length);
index += read.Length;
return BitConverter.ToInt32(read, 0);
}
public static float ReadFloat(byte[] data, ref int index)
{
byte[] read = new byte[sizeof(float)];
Array.Copy(data, index, read, 0, read.Length);
index += read.Length;
return BitConverter.ToSingle(read, 0);
}
public static string ReadString(byte[] data, ref int index)
{
int length = ReadInt(data, ref index);
if (length <= 0)
return "";
byte[] read = new byte[length];
Array.Copy(data, index, read, 0, length);
index += length;
return Encoding.UTF8.GetString(read);
}
public static int[] ReadIntArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
int[] array = new int[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadInt(data, ref index);
}
return array;
}
public static float[] ReadFloatArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
float[] array = new float[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadFloat(data, ref index);
}
return array;
}
public static string[] ReadStringArray(byte[] data, ref int index)
{
int count = ReadInt(data, ref index);
string[] array = new string[count];
for (int i = 0; i < count; i++)
{
array[i] = ReadString(data, ref index);
}
return array;
}
}
现在开始处理我们的导表逻辑~
这里要用到Excel.dll和ICSharpCode.SharpZipLib.dll这两个库，所以我们先把这两个库弄进来。
接着创建一个ConfigAsset的ScriptableObject。
using UnityEngine;
[CreateAssetMenu(menuName = "ScriptableObject/ConfigAsset")]
public class ConfigAsset : ScriptableObject
{
public TextAsset[] configs;
#if UNITY_EDITOR
public Object folder;
#endif
}
再创建一个asset文件，用来读取所有的二进制文件。
将Byte文件夹拖到Folder上，Configs字段的数据会通过导表工具生成。
在"Assets/Scripts/Editor/Config"目录下创建两个.txt格式的模版，一个是表格对象模版CfgTmp，一个是工具模板ConfigUtilsTmp。
public class {{className}}: PickData
{
{{content}}
/// <summary>
/// 解析数据
/// <summary>
public override void ReadData(byte[] datas, ref int index, ref int uid)
{
{{parseData}}
}
}
using System.Collections.Generic;
/// <summary>
/// 工具自动生成，请勿手动修改
/// </summary>
public static class ConfigUtils
{
private static Dictionary<string, Dictionary<int, PickData>> _cfgDict = new Dictionary<string, Dictionary<int, PickData>>();
{{content}}
public static void InitConfig(string name, byte[] datas)
{
switch (name)
{
{{condition}}
}
}
private static Dictionary<int, T> ParseData<T>(byte[] data) where T : PickData, new()
{
int index = 0;
int uid = 0;
int count = PickData.ReadInt(data, ref index);
Dictionary<int, T> results = new Dictionary<int, T>();
for (int i = 0; i < count; i++)
{
T item = new T();
item.ReadData(data, ref index, ref uid);
results.Add(uid, item);
}
return results;
}
private static Dictionary<int, PickData> Convert<T>(Dictionary<int, T> datas) where T : PickData, new()
{
Dictionary<int, PickData> results = new Dictionary<int, PickData>();
foreach (var kv in datas)
{
results.Add(kv.Key, kv.Value);
}
return results;
}
public static Dictionary<int, PickData> GetConfig(string name)
{
if (_cfgDict.TryGetValue(name, out var results))
{
return results;
}
return null;
}
public static void Clear()
{
_cfgDict.Clear();
}
}
然后在"Assets/Scripts/Editor/Config"目录下新建一个ExcelTool.cs类，处理导表的逻辑。
注：代码里的路径都是根据我本地项目的路径来的，你需要根据自己的路径去做修改。
using System.Collections.Generic;
using UnityEngine;
using UnityEditor;
using System.IO;
using System.Text;
using Excel;
using System;
using System.Data;
public class ExcelTool : Editor
{
public class ExcelClassData
{
public string excelName; //表名
public string className; //类名
public string[] infos; //注释
public string[] types; //类型
public string[] propertyName; //属性名
public List<string[]> datas; //数据
}
const string File_Path = "L:/A_Learn/UnityExcelTools/config"; // excel表路径，根据自己的存放路径修改
const string Asset_Path = "Assets/Resources/Config/ConfigAsset.asset";
const string Byte_Path = "Assets/Resources/Config/Byte";
[MenuItem("ExcelTool/生成数据")]
static void ExcelToData()
{
//所有xlsx表格
string[] files = Directory.GetFiles(File_Path, "*.xlsx");
//所有数据信息
List<ExcelClassData> allClassDatas = new List<ExcelClassData>();
for (int i = 0; i < files.Length; i++)
{
string file = files[i];
string excelName = Path.GetFileNameWithoutExtension(file);
if (excelName.StartsWith("~")) continue;
// 打开文件流
FileStream fileStream = File.Open(file, FileMode.Open, FileAccess.Read);
// 创建Excel数据阅读器
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
if (!excelReader.IsValid)
{
Debug.Log("读取excel失败" + file);
continue;
}
// 读取数据为DataSet（如果只需要单个工作表的数据，可以使用AsDataSet(false)）
DataSet dataSet = excelReader.AsDataSet(false);
// 获取第一个工作表（通常为0索引）
DataTable dataTable = dataSet.Tables[0];
// 访问第4行数据
DataRow fourRow = dataTable.Rows[3];
List<string> clients = new List<string>();
// 读取数据
foreach (DataColumn column in dataTable.Columns)
{
object cellValue = fourRow[column.ColumnName];
clients.Add(cellValue.ToString());
}
//构建数据
ExcelClassData exdata = new ExcelClassData();
exdata.excelName = excelName;
exdata.className = excelReader.Name;
exdata.datas = new List<string[]>();
int line = 1;
while (excelReader.Read())
{
//一行数据
int len = excelReader.FieldCount;
List<string> list = new List<string>();
for (int j = 0; j < len; j++)
{
var client = clients[j];
if (client.Equals("s") || client.Equals("")) continue;
var val = excelReader.GetString(j);
list.Add(val);
}
if (list[0] == null) break;
string[] strLineDatas = list.ToArray();
//注释行
if (line == 1)
{
exdata.infos = strLineDatas;
}
//类型行
else if (line == 2)
{
exdata.types = strLineDatas;
}
//属性名行
else if (line == 3)
{
strLineDatas[0] = "id"; // 项目规范，可删除
exdata.propertyName = strLineDatas;
}
//数据行
else if (line > 4)
{
exdata.datas.Add(strLineDatas);
}
line++;
}
allClassDatas.Add(exdata);
}
//写出数据和脚本操作
Writer(allClassDatas);
AddConfigAsset();
AssetDatabase.Refresh();
// 新增的表格第一次会没添加到ConfigAsset上，所以多执行一遍
AddConfigAsset();
AssetDatabase.Refresh();
Debug.Log("----->excel datas trans finish!");
}
static void AddConfigAsset()
{
ConfigAsset configAsset = AssetDatabase.LoadAssetAtPath<ConfigAsset>(Asset_Path);
if (configAsset == null)
{
Debug.LogError("ConfigAsset not found!");
return;
}
string[] bytesFilePaths = Directory.GetFiles(Byte_Path, "*.bytes");
configAsset.configs = new TextAsset[bytesFilePaths.Length];
for (int i = 0; i < bytesFilePaths.Length; i++)
{
TextAsset textAsset = AssetDatabase.LoadAssetAtPath<TextAsset>(bytesFilePaths[i]);
if (textAsset != null)
{
configAsset.configs[i] = textAsset;
}
}
EditorUtility.SetDirty(configAsset);
AssetDatabase.SaveAssets();
}
static void Writer(List<ExcelClassData> exDataList)
{
#region//---bytes---
StringBuilder utilContentSb = new StringBuilder();
StringBuilder utilConditionSb = new StringBuilder();
for (int i = 0; i < exDataList.Count; i++)
{
ExcelClassData exData = exDataList[i];
List<byte> byteList = new List<byte>();
int dataCount = exData.datas.Count;
byteList.AddRange(PickData.WriteInt(dataCount));
int tempIndex = 0;
while (tempIndex < dataCount)
{
string[] data = exData.datas[tempIndex];
for (int j = 0; j < exData.types.Length; j++)
{
if (j == 0 && data[j] == null) break;
byte[] tbytes = GetBytes(exData.types[j], data[j]);
byteList.AddRange(tbytes);
}
tempIndex++;
}
//bytes数据文件生成
string savePath = Application.dataPath + "/Resources/Config/Byte/" + exData.className + ".bytes";
File.WriteAllBytes(savePath, byteList.ToArray());
//脚本生成
string saveCodePath = Application.dataPath + "/Scripts/Runtime/Config/Excel";
if (!Directory.Exists(saveCodePath)) Directory.CreateDirectory(saveCodePath);
string clsName = exData.className + "Cfg";
string strCode = CreateCode(clsName, exData.types, exData.propertyName, exData.infos);
File.WriteAllText(saveCodePath + "/" + clsName + ".cs", strCode);
utilContentSb.Append(CreateUtilContentCode(exData.excelName, exData.className));
utilConditionSb.Append(CreateUtilConditionCode(exData.className));
}
string saveUtilPath = Application.dataPath + "/Scripts/Runtime/Config/Utils";
if (!Directory.Exists(saveUtilPath)) Directory.CreateDirectory(saveUtilPath);
string utilCode = CreateUtilCode(utilContentSb.ToString(), utilConditionSb.ToString());
File.WriteAllText(saveUtilPath + "/ConfigUtils.cs", utilCode);
#endregion
#region//---json---
//AssemblyName assemblyName = new AssemblyName("dynamicAssembly");
//AssemblyBuilder assemblyBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly(assemblyName, AssemblyBuilderAccess.Run);
//ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule(assemblyName.Name);
//for (int i = 0; i < exDataList.Count; i++)
//{
// ExcelClassData exData = exDataList[i];
// //定义类型
// TypeBuilder typeBuilder = moduleBuilder.DefineType(exData.className, TypeAttributes.Public);
// //定义属性
// for (int j = 0; j < exData.types.Length; j++)
// {
// typeBuilder.DefineField(exData.propertyName[j], GetType(exData.types[j]), FieldAttributes.Public);
// }
// //t
// Type t = typeBuilder.CreateType();
// List<object> allObjList = new List<object>();
// for (int j = 0; j < exData.datas.Count; j++)
// {
// //一行数据
// string[] strDatas = exData.datas[j];
// //反射实例
// object obj = Activator.CreateInstance(t);
// for (int k = 0; k < exData.types.Length; k++)
// {
// //设置属性值
// FieldInfo fieldInfo = t.GetField(exData.propertyName[k]);
// object value = GetValue(exData.types[k], strDatas[k]);
// fieldInfo.SetValue(obj, value);
// }
// allObjList.Add(obj);
// }
// string jsonData = Newtonsoft.Json.JsonConvert.SerializeObject(allObjList, Newtonsoft.Json.Formatting.Indented);
// string dataFloder = Application.streamingAssetsPath + "/Datas";
// if (!System.IO.Directory.Exists(dataFloder))
// {
// Directory.CreateDirectory(dataFloder);
// }
// File.WriteAllText(dataFloder + "/" + exData.className + ".json", jsonData);
//}
#endregion
}
/// <summary>
/// 获取类型
/// </summary>
static Type GetType(string typeName)
{
switch (typeName)
{
case "int":
return typeof(int);
case "float":
return typeof(float);
case "string":
return typeof(string);
case "int[]":
return typeof(int[]);
case "float[]":
return typeof(float[]);
case "string[]":
return typeof(string[]);
//default:
// return null;
}
return null;
}
/// <summary>
/// 获取数据Obj
/// </summary>
static object GetValue(string typeName, string data)
{
var len = 0;
string[] ss = null;
if (data != null)
{
ss = data.Split('|');
len = ss.Length;
}
switch (typeName)
{
case "int":
return data != null ? int.Parse(data) : 0;
case "float":
return data != null ? float.Parse(data) : 0f;
case "string":
return data ?? "";
case "int[]":
int[] intArray = new int[len];
for (int i = 0; i < len; i++)
{
intArray[i] = int.Parse(ss[i]);
}
return intArray;
case "float[]":
float[] floatArray = new float[len];
for (int i = 0; i < len; i++)
{
floatArray[i] = float.Parse(ss[i]);
}
return floatArray;
case "string[]":
return ss ?? new string[0];
//default:
// return null;
}
return null;
}
/// <summary>
/// 获取数据Obj的Bytes
/// </summary>
static byte[] GetBytes(string typeName, string data)
{
List<byte> bytes = new List<byte>();
object obj = GetValue(typeName, data);
switch (typeName)
{
case "int":
bytes.AddRange(PickData.WriteInt((int)obj));
break;
case "float":
bytes.AddRange(PickData.WriteFloat((float)obj));
break;
case "string":
bytes.AddRange(PickData.WriteString((string)obj));
break;
case "int[]":
bytes.AddRange(PickData.WriteIntArray((int[])obj));
break;
case "float[]":
bytes.AddRange(PickData.WriteFloatArray((float[])obj));
break;
case "string[]":
bytes.AddRange(PickData.WriteStringArray((string[])obj));
break;
default:
break;
}
return bytes.ToArray();
}
/// <summary>
/// 生成代码
/// </summary>
static string CreateCode(string className, string[] types, string[] names, string[] texts)
{
string tmpPath = Application.dataPath + "/Scripts/Editor/Config/CfgTmp.txt";
string tmpStr = GetTemplate(tmpPath);
string replacedTmp = tmpStr.Replace("{{className}}", className);
StringBuilder stringBuilder = new StringBuilder();
//属性定义
for (int i = 0; i < types.Length; i++)
{
//注释
stringBuilder.Append(StrNotes(texts[i], 2));
//定义
stringBuilder.Append("\t\tpublic " + types[i] + " " + ConvertFirstChar(names[i]) + ";\n");
}
replacedTmp = replacedTmp.Replace("{{content}}", stringBuilder.ToString());
stringBuilder.Length = 0;
//---bytes解析---
for (int i = 0; i < types.Length; i++)
{
string readInfo = "";
switch (types[i])
{
case "int":
readInfo = "PickData.ReadInt";
break;
case "float":
readInfo = "PickData.ReadFloat";
break;
case "string":
readInfo = "PickData.ReadString";
break;
case "int[]":
readInfo = "PickData.ReadIntArray";
break;
case "float[]":
readInfo = "PickData.ReadFloatArray";
break;
case "string[]":
readInfo = "PickData.ReadStringArray";
break;
default:
break;
}
stringBuilder.Append("\t\t\t" + ConvertFirstChar(names[i]) + " = " + readInfo + "(datas, ref index);\n");
}
stringBuilder.Append("\t\t\tuid = Id;");
replacedTmp = replacedTmp.Replace("{{parseData}}", stringBuilder.ToString());
return replacedTmp;
}
/// <summary>
/// 生成ConfigUtils.cs代码
/// </summary>
static string CreateUtilCode(string content, string condition)
{
string tmpPath = Application.dataPath + "/Scripts/Editor/Config/ConfigUtilsTmp.txt";
string tmpStr = GetTemplate(tmpPath);
string replacedTmp = tmpStr.Replace("{{content}}", content);
replacedTmp = replacedTmp.Replace("{{condition}}", condition);
return replacedTmp;
}
static string CreateUtilContentCode(string excelName, string className)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(StrNotes(excelName, 2));
stringBuilder.Append("\t\tpublic static Dictionary<int, PickData> " + className + "s { get { return GetConfig(\"" + className + "\"); } }\n");
return stringBuilder.ToString();
}
static string CreateUtilConditionCode(string className)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("\t\t\t\tcase \"" + className + "\":\n");
stringBuilder.Append("\t\t\t\t\t_cfgDict.Add(name, Convert(ParseData<" + className + "Cfg>(datas)));\n");
stringBuilder.Append("\t\t\t\t\tbreak;\n");
return stringBuilder.ToString();
}
/// <summary>
/// 注释
/// </summary>
static string StrNotes(string tip, int t = 0)
{
StringBuilder stringBuilder = new StringBuilder();
string st = "";
for (int i = 0; i < t; i++)
{
st += "\t";
}
stringBuilder.Append(st + "/// <summary>\n");
stringBuilder.Append(st + "/// " + tip + "\n");
stringBuilder.Append(st + "/// <summary>\n");
return stringBuilder.ToString();
}
/// <summary>
/// 获取代码模板
/// </summary>
/// <param name="templatePath">模板路径</param>
static string GetTemplate(string templatePath)
{
if (File.Exists(templatePath))
{
return File.ReadAllText(templatePath);
}
else
{
Debug.LogError("Template file not found at: " + templatePath);
return string.Empty;
}
}
/// <summary>
/// 转换首字母大小写
/// </summary>
/// <param name="content">内容</param>
/// <param name="isUpper">是否转换成大写</param>
/// <returns></returns>
static string ConvertFirstChar(string content, bool isUpper = true)
{
if (string.IsNullOrEmpty(content)) return content;
char firstChar = isUpper ? char.ToUpper(content[0]) : char.ToLower(content[0]);
string restOfContent = content.Substring(1);
return firstChar + restOfContent;
}
}
自此，导表的逻辑就写完了，我们可以生成数据看行不行。
打印这个，就代表导出成功了！
咱们生成的内容包括以下4个部分：
三、应用
接下来咱们来调用一下我们的表格数据看看~
在场景中新建一个空物体，名字为Game，再新建一个Game脚本，挂载到Game对象上。
using UnityEngine;
public class Game : MonoBehaviour
{
[SerializeField]
private ConfigAsset _configAsset;
void Start()
{
LoadConfig();
}
private void LoadConfig()
{
foreach (var item in _configAsset.configs)
{
ConfigUtils.InitConfig(item.name, item.bytes);
}
}
void Update()
{
if (Input.GetKeyDown(KeyCode.A))
{
var loginCfg = ConfigUtils.Logins[1] as LoginCfg;
Debug.Log(loginCfg.Name);
var roleCfg = ConfigUtils.Roles[1] as RoleCfg;
Debug.Log(roleCfg.Decs);
}
}
}
注：一般项目里ConfigAsset都是通过加载的方式加进来的，我这里为了方便就直接引用这个资源了。
在Game视图中点击键盘A，出现下面打印就表示咱们已经成功了。
完结撒花~
四、项目源码
github地址：https://github.com/Lucky-xb/UnityExcelTools.git
unity2021.3.14f1版本地址：https://unity.com/releases/editor/whats-new/2021.3.14
注：也可以通过UnityHub下载，在浏览器里打开这个链接就会跳转到UnityHub下载：unityhub://2021.3.14f1/eee1884e7226
五、参考
Unity编辑Excel一键数据转换，转json或bytes
六、小结
如果遇到什么问题可以在评论区流言，小悠看到后会立马给大家解答！有什么好的建议也可以提出来，目前我也还有很多需要学习的地方。
扫一扫
1266
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
