2023-05-29 Unity 2进制5——Excel配置表工具

文章目录

- - - 一、Excel 读取操作
    - - （一）打开 Excel 表
      - （二）获取单元格信息
    - 二、Excel 表配置工具
    - - （一）基础知识
      - （二）配置工具

一、Excel 读取操作

（一）打开 Excel 表

IExcelDataReader：从流中读取 Excel 数据
DataSet：数据集合类，存储 Excel 数据

using Excel; // 引入命名空间private static void OpenExcel() {using (FileStream fs = File.Open(Application.dataPath + "/ArtRes/Excel/PlayerInfo.xlsx", FileMode.Open, FileAccess.Read)) {// 传入excel表的文件流获取数据IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);// 将excel表中的数据转换为DataSet数据类型 DataSet result = excelReader.AsDataSet();// 得到Excel文件中的所有表信息for (int i = 0; i < result.Tables.Count; i++) {Debug.Log("表名：" + result.Tables[i].TableName);Debug.Log("行数：" + result.Tables[i].Rows.Count);Debug.Log("列数：" + result.Tables[i].Columns.Count);}fs.Close();}
}

（二）获取单元格信息

DataTable：数据表类，表示 Excel 文件中的一个表
DataRow：数据行类，表示某张表中的一行数据

private static void ReadExcel() {using (FileStream fs = File.Open(Application.dataPath + "/ArtRes/Excel/PlayerInfo.xlsx", FileMode.Open, FileAccess.Read)) {IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);DataSet result = excelReader.AsDataSet();for (int i = 0; i < result.Tables.Count; i++) {// 得到其中一张表的具体数据DataTable table = result.Tables[i];// 得到其中一行的数据// DataRow row = table.Rows[0];// 得到行中某一列的信息// Debug.Log(row[1].ToString());DataRow row;for (int j = 0; j < table.Rows.Count; j++) {// 得到每一行的信息row = table.Rows[j];Debug.Log("*********新的一行************");for (int k = 0; k < table.Columns.Count; k++) {Debug.Log(row[k].ToString());}}}fs.Close();}
}

二、Excel 表配置工具

（一）基础知识

添加 Unity 菜单栏按钮

通过 Unity 提供的 MenuItem 特性在菜单栏添加选项按钮
- 特性名：MenuItem
- 命名空间：UnityEditor
规则一：一定是静态方法
规则二：菜单栏按钮必须有至少一个斜杠，不然会报错，不支持只有一个菜单栏入口
规则三：这个特性可以用在任意的类当中
```
[MenuItem("GameTool/Test")]
private static void Test() {AssetDatabase.Refresh();
}
```
刷新 Project 窗口内容
- 类名：AssetDatabase
- 命名空间：UnityEditor
- 方法：Refresh
```
AssetDatabase.Refresh();
```
Editor 文件夹
- Editor 文件夹可以放在项目的任何文件夹下，可以有多个
- 放在其中的内容，项目打包时不会被打包到项目中
- 一般编辑器相关代码都可以放在该文件夹中

（二）配置工具

ExcelTool.cs

用于将 Excel 配置表内容生成对应的数据结构类、容器类、2 进制存储文件。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Excel;
using UnityEditor;
using UnityEngine;public class ExcelTool
{/// <summary>/// Excel 存放的路径/// </summary>private static readonly string EXCEL_PATH = BinaryDataMgr.EXCEL_PATH;/// <summary>/// 数据结构类脚本存储位置/// </summary>private static readonly string DATA_CLASS_PATH = BinaryDataMgr.DATA_CLASS_PATH;/// <summary>/// 数据容器类脚本存储位置/// </summary>private static readonly string DATA_CONTAINER_PATH = BinaryDataMgr.DATA_CONTAINER_PATH;/// <summary>/// 2进制数据存储位置/// </summary>private static readonly string DATA_BINARY_PATH = BinaryDataMgr.DATA_BINARY_PATH;/// <summary>/// 2进制文件后缀名/// </summary>private static readonly string BINARY_SUFFIX = BinaryDataMgr.BINARY_SUFFIX;/// <summary>/// 变量名所在行/// </summary>private static readonly int VARIABLE_NAME_ROW = BinaryDataMgr.VARIABLE_NAME_ROW;/// <summary>/// 变量类型所在行/// </summary>private static readonly int VARIABLE_TYPE_ROW = BinaryDataMgr.VARIABLE_TYPE_ROW;/// <summary>/// 变量主键所在行/// </summary>private static readonly int VARIABLE_KEY_ROW = BinaryDataMgr.VARIABLE_KEY_ROW;/// <summary>/// 变量主键标识符/// </summary>private static readonly string[] VARIABLE_KEYS = BinaryDataMgr.VARIABLE_KEYS;/// <summary>/// 数据内容开始的行号/// </summary>private static readonly int DATA_BEGIN_ROW_INDEX = BinaryDataMgr.DATA_BEGIN_ROW_INDEX;/// <summary>/// 生成 Excel 数据信息/// </summary>[MenuItem("GameTool/GenerateExcel")]private static void GenerateExcelInfo() {DirectoryInfo dInfo = Directory.CreateDirectory(EXCEL_PATH); // 指定路径的文件夹信息FileInfo[]    files = dInfo.GetFiles();                      // 指定路径下的所有 Excel 文件信息for (int i = 0; i < files.Length; i++) {// 不是 Excel 文件则跳过if (files[i].Extension != ".xlsx" && files[i].Extension != ".xls") continue;// 获取所有表的数据DataTableCollection tableCollection; // 数据表容器using (FileStream fs = new FileStream(files[i].FullName, FileMode.Open, FileAccess.Read)) {IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fs);tableCollection = excelDataReader.AsDataSet().Tables;}// 遍历所有表的信息，生成相应内容foreach (DataTable table in tableCollection) {GenerateExcelDataClass(table);  // 生成数据结构类GenerateExcelContainer(table);  // 生成容器类GenerateExcelBinaryData(table); // 生成2进制数据}}}/// <summary>/// 生成 Excel 表对应的数据结构类/// </summary>/// <param name="table">数据表</param>private static void GenerateExcelDataClass(DataTable table) {DataRow nameRow = table.Rows[VARIABLE_NAME_ROW]; // 变量名行DataRow typeRow = table.Rows[VARIABLE_TYPE_ROW]; // 变量类型行// 确保数据结构类脚本路径存在if (!Directory.Exists(DATA_CLASS_PATH)) {Directory.CreateDirectory(DATA_CLASS_PATH);}// 拼接数据结构类内容string str = $"public class {table.TableName}\n" +$"{{\n";for (int i = 0; i < table.Columns.Count; i++) {str += $"    public {typeRow[i]} {nameRow[i]};\n";}str += "}\n";// 覆盖写入文件并刷新 Project 窗口File.WriteAllText($"{DATA_CLASS_PATH}{table.TableName}.cs", str);AssetDatabase.Refresh();}/// <summary>/// 生成 Excel 表对应的数据容器类/// </summary>/// <param name="table">数据表</param>private static void GenerateExcelContainer(DataTable table) {int     keyIndex = GetKeyColumnIndex(table);DataRow typeRow  = table.Rows[VARIABLE_TYPE_ROW]; // 变量类型行// 确保数据容器类脚本路径存在if (!Directory.Exists(DATA_CONTAINER_PATH)) {Directory.CreateDirectory(DATA_CONTAINER_PATH);}// 拼接数据结构类内容string str = $"using System.Collections.Generic;\n\n" +$"public class {table.TableName}Container\n" +$"{{\n" +$"    public Dictionary<{typeRow[keyIndex]}, {table.TableName}> dataDic = new Dictionary<{typeRow[keyIndex]}, {table.TableName}>();\n" +$"}}\n";// 覆盖写入文件并刷新 Project 窗口File.WriteAllText($"{DATA_CONTAINER_PATH}{table.TableName}Container.cs", str);AssetDatabase.Refresh();}/// <summary>/// 获得主键所在的列/// </summary>/// <param name="table"></param>/// <returns></returns>private static int GetKeyColumnIndex(DataTable table) {DataRow row = table.Rows[VARIABLE_KEY_ROW]; // 获取变量主键行for (int i = 0; i < table.Columns.Count; i++) {// 如果该列内容在主键标识符内，则返回该列if (VARIABLE_KEYS.Contains(row[i].ToString())) {return i;}}return 0; // 否则，返回第一列}/// <summary>/// 生成 Excel 表对应的2进制数据/// </summary>/// <param name="table">数据表</param>private static void GenerateExcelBinaryData(DataTable table) {// 确保2进制数据路径存在if (!Directory.Exists(DATA_BINARY_PATH)) {Directory.CreateDirectory(DATA_BINARY_PATH);}// 创建 2 进制文件using (FileStream fs = new FileStream($"{DATA_BINARY_PATH}{table.TableName}{BINARY_SUFFIX}", FileMode.OpenOrCreate, FileAccess.Write)) {int rowNum = table.Rows.Count - DATA_BEGIN_ROW_INDEX + 1; // -DATA_BEGIN_ROW_INDEX 的原因是前 DATA_BEGIN_ROW_INDEX 行是配置规则，不是 2 进制内容// 1. 先写入存储的行数fs.Write(BitConverter.GetBytes(rowNum), 0, 4);// 2. 获取主键的变量名string keyName  = table.Rows[VARIABLE_NAME_ROW][GetKeyColumnIndex(table)].ToString();byte[] keyBytes = Encoding.UTF8.GetBytes(keyName);// 先写长度后写内容fs.Write(BitConverter.GetBytes(keyBytes.Length), 0, 4);fs.Write(keyBytes, 0, keyBytes.Length);// 3. 遍历所有数据内容，进行写入DataRow typeRow = table.Rows[VARIABLE_TYPE_ROW];for (int i = DATA_BEGIN_ROW_INDEX; i < table.Rows.Count; i++) {DataRow row = table.Rows[i];for (int j = 0; j < table.Columns.Count; j++) {switch (typeRow[j].ToString()) {case "int":fs.Write(BitConverter.GetBytes(int.Parse(row[j].ToString())), 0, 4);break;case "float":fs.Write(BitConverter.GetBytes(float.Parse(row[j].ToString())), 0, 4);break;case "bool":fs.Write(BitConverter.GetBytes(bool.Parse(row[j].ToString())), 0, 1);break;case "string":byte[] strBytes = Encoding.UTF8.GetBytes(row[j].ToString());fs.Write(BitConverter.GetBytes(strBytes.Length), 0, 4); // 先写入字符串长度fs.Write(strBytes, 0, strBytes.Length);                 // 再写入字符串内容break;}}}}}
}

BinaryDataMgr.cs

存储对象数据为 2 进制文件并读取。

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using UnityEngine;public class BinaryDataMgr
{/// <summary>/// 数据存储位置/// </summary>private static readonly string SAVE_PATH = Application.persistentDataPath + "/Data/";/// <summary>/// Excel 存放的路径/// </summary>public static readonly string EXCEL_PATH = Application.dataPath + "/ArtRes/Excel/";/// <summary>/// 数据结构类脚本存储位置/// </summary>public static readonly string DATA_CLASS_PATH = Application.dataPath + "/Scripts/ExcelData/DataClass/";/// <summary>/// 数据容器类脚本存储位置/// </summary>public static readonly string DATA_CONTAINER_PATH = Application.dataPath + "/Scripts/ExcelData/Container/";/// <summary>/// 2进制数据存储位置/// </summary>public static readonly string DATA_BINARY_PATH = Application.streamingAssetsPath + "/Binary/";/// <summary>/// 2进制文件后缀名/// </summary>public static readonly string BINARY_SUFFIX = ".hl";/// <summary>/// 变量名所在行/// </summary>public static readonly int VARIABLE_NAME_ROW = 0;/// <summary>/// 变量类型所在行/// </summary>public static readonly int VARIABLE_TYPE_ROW = 1;/// <summary>/// 变量主键所在行/// </summary>public static readonly int VARIABLE_KEY_ROW = 2;/// <summary>/// 变量主键标识符/// </summary>public static readonly string[] VARIABLE_KEYS = { "key", "Key", "KEY" };/// <summary>/// 数据内容开始的行号/// </summary>public static readonly int DATA_BEGIN_ROW_INDEX = 4;/// <summary>/// 存储所有 Excel 表的容器/// </summary>private Dictionary<string, object> tableDic = new Dictionary<string, object>();public static BinaryDataMgr Instance { get; set; } = new BinaryDataMgr(); // 需要放在所有字段的最后，保证其他字段先被初始化private BinaryDataMgr() {InitData();}/// <summary>/// 初始化表格数据/// </summary>private void InitData() {// LoadTable<TowerInfoContainer, TowerInfo>();}/// <summary>/// 加载 Excel 表的2进制数据到内存中/// </summary>/// <typeparam name="T">容器类名</typeparam>/// <typeparam name="K">数据结构体类名</typeparam>public void LoadTable<T, K>() {using (FileStream fs = new FileStream($"{DATA_BINARY_PATH}{typeof(K)}{BINARY_SUFFIX}", FileMode.Open, FileAccess.Read)) {int offset = 0; // 读取偏移量// 读取行数byte[] rowCountBytes = StreamRead(fs, ref offset, 4);int    rowCount      = BitConverter.ToInt32(rowCountBytes, 0);// 读取主键名byte[] keyNameLengthBytes = StreamRead(fs, ref offset, 4); // 主键名长度int    keyNameLength      = BitConverter.ToInt32(keyNameLengthBytes, 0);byte[] keyNameBytes       = StreamRead(fs, ref offset, keyNameLength); // 主键名内容string keyName            = Encoding.UTF8.GetString(keyNameBytes, 0, keyNameLength);// 创建容器类对象Type   containerType = typeof(T);object container     = Activator.CreateInstance(containerType); // 实例化容器Type        classType = typeof(K);FieldInfo[] infos     = classType.GetFields(); // 数据结构类所有字段的信息// 实例化表的数据内容for (int i = 0; i < rowCount; i++) {// 实例化数据结构类 对象object dataObj = Activator.CreateInstance(classType);foreach (FieldInfo info in infos) {if (info.FieldType == typeof(int)) {byte[] bytes = StreamRead(fs, ref offset, 4);int    value = BitConverter.ToInt32(bytes, 0);info.SetValue(dataObj, value);}else if (info.FieldType == typeof(float)) {byte[] bytes = StreamRead(fs, ref offset, 4);float  value = BitConverter.ToSingle(bytes, 0);info.SetValue(dataObj, value);}else if (info.FieldType == typeof(bool)) {byte[] bytes = StreamRead(fs, ref offset, 1);bool   value = BitConverter.ToBoolean(bytes, 0);info.SetValue(dataObj, value);}else if (info.FieldType == typeof(string)) {byte[] bytes      = StreamRead(fs, ref offset, 4); // 长度int    len        = BitConverter.ToInt32(bytes, 0);byte[] valueBytes = StreamRead(fs, ref offset, len); // 内容string value      = Encoding.UTF8.GetString(valueBytes);info.SetValue(dataObj, value);}}// 添加对象到容器中FieldInfo  dicInfo  = containerType.GetField("dataDic");   // 字典字段信息object     dicObj   = dicInfo.GetValue(container);         // 获取字典对象FieldInfo  keyInfo  = classType.GetField(keyName);         // 主键字段信息object     keyValue = keyInfo.GetValue(dataObj);           // 获取主键对象MethodInfo mInfo    = dicObj.GetType().GetMethod("Add");   // Add 方法信息mInfo?.Invoke(dicObj, new object[] { keyValue, dataObj }); // 执行 Add 方法}// 记录表的内容tableDic.Add(typeof(T).Name, container);}}/// <summary>/// 获取表/// </summary>/// <typeparam name="T"></typeparam>/// <returns></returns>public T GetTable<T>() where T : class {string tableName = typeof(T).Name;if (tableDic.TryGetValue(tableName, out object value)) {return value as T;}return null;}/// <summary>/// 读取对应长度的字节流，offset 会进行更新/// </summary>/// <param name="stream"></param>/// <param name="offset"></param>/// <param name="count"></param>/// <returns></returns>public byte[] StreamRead(Stream stream, ref int offset, int count) {byte[] bytes = new byte[count];offset = stream.Read(bytes, 0, count);return bytes;}/// <summary>/// 存储二进制数据/// </summary>/// <param name="obj"></param>/// <param name="filename"></param>public void Save(object obj, string filename) {// 如果文件夹不存在，则创建if (!Directory.Exists(SAVE_PATH)) {Directory.CreateDirectory(SAVE_PATH);}using (FileStream fs = new FileStream(SAVE_PATH + filename, FileMode.OpenOrCreate, FileAccess.Write)) {BinaryFormatter bf = new BinaryFormatter();bf.Serialize(fs, obj);fs.Close();}}/// <summary>/// 读取二进制数据转换为对象/// </summary>/// <param name="filename"></param>/// <typeparam name="T"></typeparam>/// <returns></returns>public T Load<T>(string filename) where T : class {// 如果文件不存在，则返回泛型对象的默认值if (!File.Exists(SAVE_PATH + filename)) {return default(T);}T obj = null;using (FileStream fs = new FileStream(SAVE_PATH + filename, FileMode.Open, FileAccess.Read)) {BinaryFormatter bf = new BinaryFormatter();obj = bf.Deserialize(fs) as T;fs.Close();}return obj;}
}