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();
}
}
添加 Unity 菜单栏按钮
通过 Unity 提供的 MenuItem 特性在菜单栏添加选项按钮
MenuItem
UnityEditor
规则一：一定是静态方法
规则二：菜单栏按钮必须有至少一个斜杠，不然会报错，不支持只有一个菜单栏入口
规则三：这个特性可以用在任意的类当中
[MenuItem("GameTool/Test")]
private static void Test() {
AssetDatabase.Refresh();
}
刷新 Project 窗口内容
AssetDatabase.Refresh();
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;
}
}
在 “BinaryDataMgr.cs” 中的 EXCEL_PATH 下存放数据表 “PlayerInfo.xlsx”，其内容如下：
点击菜单栏的 GameTool -> GenerateExcel，将在 DATA_CLASS_PATH 和 DATA_CONTAINER_PATH 路径下分别生成数据信息脚本和容器脚本：
参考如下代码：
Test t = new Test();
t.i = 985;
t.str = "欢迎你";
print(Application.persistentDataPath);
BinaryDataMgr.Instance.Save(t, "哈哈哈");
Test t = BinaryDataMgr.Instance.Load<Test>("哈哈哈");
存储位置可修改 “BinaryDataMgr.cs” 中的 DATA_BINARY_PATH 路径。
具体使用参考代码注释。