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;
    }
}