一、Excel 讀取操作
（一）打開(kāi) Excel 表
-
IExcelDataReader：從流中讀取 Excel 數(shù)據(jù) -
DataSet：數(shù)據(jù)集合類(lèi)，存儲(chǔ) Excel 數(shù)據(jù)
using Excel; // 引入命名空間
private static void OpenExcel() {
using (FileStream fs = File.Open(Application.dataPath + "/ArtRes/Excel/PlayerInfo.xlsx", FileMode.Open, FileAccess.Read)) {
// 傳入excel表的文件流獲取數(shù)據(jù)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
// 將excel表中的數(shù)據(jù)轉(zhuǎn)換為DataSet數(shù)據(jù)類(lèi)型
DataSet result = excelReader.AsDataSet();
// 得到Excel文件中的所有表信息
for (int i = 0; i < result.Tables.Count; i++) {
Debug.Log("表名：" + result.Tables[i].TableName);
Debug.Log("行數(shù)：" + result.Tables[i].Rows.Count);
Debug.Log("列數(shù)：" + result.Tables[i].Columns.Count);
}
fs.Close();
}
}
（二）獲取單元格信息
-
DataTable：數(shù)據(jù)表類(lèi)，表示 Excel 文件中的一個(gè)表 -
DataRow：數(shù)據(jù)行類(lèi)，表示某張表中的一行數(shù)據(jù)
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++) {
// 得到其中一張表的具體數(shù)據(jù)
DataTable table = result.Tables[i];
// 得到其中一行的數(shù)據(jù)
// 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 表配置工具
（一）基礎(chǔ)知識(shí)
-
添加 Unity 菜單欄按鈕
通過(guò) Unity 提供的 MenuItem 特性在菜單欄添加選項(xiàng)按鈕
- 特性名：
MenuItem - 命名空間：
UnityEditor
規(guī)則一：一定是靜態(tài)方法
規(guī)則二：菜單欄按鈕必須有至少一個(gè)斜杠，不然會(huì)報(bào)錯(cuò)，不支持只有一個(gè)菜單欄入口
規(guī)則三：這個(gè)特性可以用在任意的類(lèi)當(dāng)中
[MenuItem("GameTool/Test")] private static void Test() { AssetDatabase.Refresh(); } - 特性名：
-
刷新 Project 窗口內(nèi)容
- 類(lèi)名：AssetDatabase
- 命名空間：UnityEditor
- 方法：Refresh
AssetDatabase.Refresh(); -
Editor 文件夾
- Editor 文件夾可以放在項(xiàng)目的任何文件夾下，可以有多個(gè)
- 放在其中的內(nèi)容，項(xiàng)目打包時(shí)不會(huì)被打包到項(xiàng)目中
- 一般編輯器相關(guān)代碼都可以放在該文件夾中
（二）配置工具
-
ExcelTool.cs
用于將 Excel 配置表內(nèi)容生成對(duì)應(yīng)的數(shù)據(jù)結(jié)構(gòu)類(lèi)、容器類(lèi)、2 進(jìn)制存儲(chǔ)文件。
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>
/// 數(shù)據(jù)結(jié)構(gòu)類(lèi)腳本存儲(chǔ)位置
/// </summary>
private static readonly string DATA_CLASS_PATH = BinaryDataMgr.DATA_CLASS_PATH;
/// <summary>
/// 數(shù)據(jù)容器類(lèi)腳本存儲(chǔ)位置
/// </summary>
private static readonly string DATA_CONTAINER_PATH = BinaryDataMgr.DATA_CONTAINER_PATH;
/// <summary>
/// 2進(jìn)制數(shù)據(jù)存儲(chǔ)位置
/// </summary>
private static readonly string DATA_BINARY_PATH = BinaryDataMgr.DATA_BINARY_PATH;
/// <summary>
/// 2進(jìn)制文件后綴名
/// </summary>
private static readonly string BINARY_SUFFIX = BinaryDataMgr.BINARY_SUFFIX;
/// <summary>
/// 變量名所在行
/// </summary>
private static readonly int VARIABLE_NAME_ROW = BinaryDataMgr.VARIABLE_NAME_ROW;
/// <summary>
/// 變量類(lèi)型所在行
/// </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>
/// 變量主鍵標(biāo)識(shí)符
/// </summary>
private static readonly string[] VARIABLE_KEYS = BinaryDataMgr.VARIABLE_KEYS;
/// <summary>
/// 數(shù)據(jù)內(nèi)容開(kāi)始的行號(hào)
/// </summary>
private static readonly int DATA_BEGIN_ROW_INDEX = BinaryDataMgr.DATA_BEGIN_ROW_INDEX;
/// <summary>
/// 生成 Excel 數(shù)據(jù)信息
/// </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 文件則跳過(guò)
if (files[i].Extension != ".xlsx" && files[i].Extension != ".xls") continue;
// 獲取所有表的數(shù)據(jù)
DataTableCollection tableCollection; // 數(shù)據(jù)表容器
using (FileStream fs = new FileStream(files[i].FullName, FileMode.Open, FileAccess.Read)) {
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateOpenXmlReader(fs);
tableCollection = excelDataReader.AsDataSet().Tables;
}
// 遍歷所有表的信息，生成相應(yīng)內(nèi)容
foreach (DataTable table in tableCollection) {
GenerateExcelDataClass(table); // 生成數(shù)據(jù)結(jié)構(gòu)類(lèi)
GenerateExcelContainer(table); // 生成容器類(lèi)
GenerateExcelBinaryData(table); // 生成2進(jìn)制數(shù)據(jù)
}
}
}
/// <summary>
/// 生成 Excel 表對(duì)應(yīng)的數(shù)據(jù)結(jié)構(gòu)類(lèi)
/// </summary>
/// <param name="table">數(shù)據(jù)表</param>
private static void GenerateExcelDataClass(DataTable table) {
DataRow nameRow = table.Rows[VARIABLE_NAME_ROW]; // 變量名行
DataRow typeRow = table.Rows[VARIABLE_TYPE_ROW]; // 變量類(lèi)型行
// 確保數(shù)據(jù)結(jié)構(gòu)類(lèi)腳本路徑存在
if (!Directory.Exists(DATA_CLASS_PATH)) {
Directory.CreateDirectory(DATA_CLASS_PATH);
}
// 拼接數(shù)據(jù)結(jié)構(gòu)類(lèi)內(nèi)容
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";
// 覆蓋寫(xiě)入文件并刷新 Project 窗口
File.WriteAllText($"{DATA_CLASS_PATH}{table.TableName}.cs", str);
AssetDatabase.Refresh();
}
/// <summary>
/// 生成 Excel 表對(duì)應(yīng)的數(shù)據(jù)容器類(lèi)
/// </summary>
/// <param name="table">數(shù)據(jù)表</param>
private static void GenerateExcelContainer(DataTable table) {
int keyIndex = GetKeyColumnIndex(table);
DataRow typeRow = table.Rows[VARIABLE_TYPE_ROW]; // 變量類(lèi)型行
// 確保數(shù)據(jù)容器類(lèi)腳本路徑存在
if (!Directory.Exists(DATA_CONTAINER_PATH)) {
Directory.CreateDirectory(DATA_CONTAINER_PATH);
}
// 拼接數(shù)據(jù)結(jié)構(gòu)類(lèi)內(nèi)容
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";
// 覆蓋寫(xiě)入文件并刷新 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++) {
// 如果該列內(nèi)容在主鍵標(biāo)識(shí)符內(nèi)，則返回該列
if (VARIABLE_KEYS.Contains(row[i].ToString())) {
return i;
}
}
return 0; // 否則，返回第一列
}
/// <summary>
/// 生成 Excel 表對(duì)應(yīng)的2進(jìn)制數(shù)據(jù)
/// </summary>
/// <param name="table">數(shù)據(jù)表</param>
private static void GenerateExcelBinaryData(DataTable table) {
// 確保2進(jìn)制數(shù)據(jù)路徑存在
if (!Directory.Exists(DATA_BINARY_PATH)) {
Directory.CreateDirectory(DATA_BINARY_PATH);
}
// 創(chuàng)建 2 進(jìn)制文件
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 行是配置規(guī)則，不是 2 進(jìn)制內(nèi)容
// 1. 先寫(xiě)入存儲(chǔ)的行數(shù)
fs.Write(BitConverter.GetBytes(rowNum), 0, 4);
// 2. 獲取主鍵的變量名
string keyName = table.Rows[VARIABLE_NAME_ROW][GetKeyColumnIndex(table)].ToString();
byte[] keyBytes = Encoding.UTF8.GetBytes(keyName);
// 先寫(xiě)長(zhǎng)度后寫(xiě)內(nèi)容
fs.Write(BitConverter.GetBytes(keyBytes.Length), 0, 4);
fs.Write(keyBytes, 0, keyBytes.Length);
// 3. 遍歷所有數(shù)據(jù)內(nèi)容，進(jìn)行寫(xiě)入
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); // 先寫(xiě)入字符串長(zhǎng)度
fs.Write(strBytes, 0, strBytes.Length); // 再寫(xiě)入字符串內(nèi)容
break;
}
}
}
}
}
}
-
BinaryDataMgr.cs
存儲(chǔ)對(duì)象數(shù)據(jù)為 2 進(jìn)制文件并讀取。
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>
/// 數(shù)據(jù)存儲(chǔ)位置
/// </summary>
private static readonly string SAVE_PATH = Application.persistentDataPath + "/Data/";
/// <summary>
/// Excel 存放的路徑
/// </summary>
public static readonly string EXCEL_PATH = Application.dataPath + "/ArtRes/Excel/";
/// <summary>
/// 數(shù)據(jù)結(jié)構(gòu)類(lèi)腳本存儲(chǔ)位置
/// </summary>
public static readonly string DATA_CLASS_PATH = Application.dataPath + "/Scripts/ExcelData/DataClass/";
/// <summary>
/// 數(shù)據(jù)容器類(lèi)腳本存儲(chǔ)位置
/// </summary>
public static readonly string DATA_CONTAINER_PATH = Application.dataPath + "/Scripts/ExcelData/Container/";
/// <summary>
/// 2進(jìn)制數(shù)據(jù)存儲(chǔ)位置
/// </summary>
public static readonly string DATA_BINARY_PATH = Application.streamingAssetsPath + "/Binary/";
/// <summary>
/// 2進(jìn)制文件后綴名
/// </summary>
public static readonly string BINARY_SUFFIX = ".hl";
/// <summary>
/// 變量名所在行
/// </summary>
public static readonly int VARIABLE_NAME_ROW = 0;
/// <summary>
/// 變量類(lèi)型所在行
/// </summary>
public static readonly int VARIABLE_TYPE_ROW = 1;
/// <summary>
/// 變量主鍵所在行
/// </summary>
public static readonly int VARIABLE_KEY_ROW = 2;
/// <summary>
/// 變量主鍵標(biāo)識(shí)符
/// </summary>
public static readonly string[] VARIABLE_KEYS = { "key", "Key", "KEY" };
/// <summary>
/// 數(shù)據(jù)內(nèi)容開(kāi)始的行號(hào)
/// </summary>
public static readonly int DATA_BEGIN_ROW_INDEX = 4;
/// <summary>
/// 存儲(chǔ)所有 Excel 表的容器
/// </summary>
private Dictionary<string, object> tableDic = new Dictionary<string, object>();
public static BinaryDataMgr Instance { get; set; } = new BinaryDataMgr(); // 需要放在所有字段的最后，保證其他字段先被初始化
private BinaryDataMgr() {
InitData();
}
/// <summary>
/// 初始化表格數(shù)據(jù)
/// </summary>
private void InitData() {
// LoadTable<TowerInfoContainer, TowerInfo>();
}
/// <summary>
/// 加載 Excel 表的2進(jìn)制數(shù)據(jù)到內(nèi)存中
/// </summary>
/// <typeparam name="T">容器類(lèi)名</typeparam>
/// <typeparam name="K">數(shù)據(jù)結(jié)構(gòu)體類(lèi)名</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; // 讀取偏移量
// 讀取行數(shù)
byte[] rowCountBytes = StreamRead(fs, ref offset, 4);
int rowCount = BitConverter.ToInt32(rowCountBytes, 0);
// 讀取主鍵名
byte[] keyNameLengthBytes = StreamRead(fs, ref offset, 4); // 主鍵名長(zhǎng)度
int keyNameLength = BitConverter.ToInt32(keyNameLengthBytes, 0);
byte[] keyNameBytes = StreamRead(fs, ref offset, keyNameLength); // 主鍵名內(nèi)容
string keyName = Encoding.UTF8.GetString(keyNameBytes, 0, keyNameLength);
// 創(chuàng)建容器類(lèi)對(duì)象
Type containerType = typeof(T);
object container = Activator.CreateInstance(containerType); // 實(shí)例化容器
Type classType = typeof(K);
FieldInfo[] infos = classType.GetFields(); // 數(shù)據(jù)結(jié)構(gòu)類(lèi)所有字段的信息
// 實(shí)例化表的數(shù)據(jù)內(nèi)容
for (int i = 0; i < rowCount; i++) {
// 實(shí)例化數(shù)據(jù)結(jié)構(gòu)類(lèi) 對(duì)象
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); // 長(zhǎng)度
int len = BitConverter.ToInt32(bytes, 0);
byte[] valueBytes = StreamRead(fs, ref offset, len); // 內(nèi)容
string value = Encoding.UTF8.GetString(valueBytes);
info.SetValue(dataObj, value);
}
}
// 添加對(duì)象到容器中
FieldInfo dicInfo = containerType.GetField("dataDic"); // 字典字段信息
object dicObj = dicInfo.GetValue(container); // 獲取字典對(duì)象
FieldInfo keyInfo = classType.GetField(keyName); // 主鍵字段信息
object keyValue = keyInfo.GetValue(dataObj); // 獲取主鍵對(duì)象
MethodInfo mInfo = dicObj.GetType().GetMethod("Add"); // Add 方法信息
mInfo?.Invoke(dicObj, new object[] { keyValue, dataObj }); // 執(zhí)行 Add 方法
}
// 記錄表的內(nèi)容
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>
/// 讀取對(duì)應(yīng)長(zhǎng)度的字節(jié)流，offset 會(huì)進(jìn)行更新
/// </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>
/// 存儲(chǔ)二進(jìn)制數(shù)據(jù)
/// </summary>
/// <param name="obj"></param>
/// <param name="filename"></param>
public void Save(object obj, string filename) {
// 如果文件夾不存在，則創(chuàng)建
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>
/// 讀取二進(jìn)制數(shù)據(jù)轉(zhuǎn)換為對(duì)象
/// </summary>
/// <param name="filename"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public T Load<T>(string filename) where T : class {
// 如果文件不存在，則返回泛型對(duì)象的默認(rèn)值
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;
}
}
（三）演示步驟
1 ExcelTool
? 在 “BinaryDataMgr.cs” 中的 EXCEL_PATH 下存放數(shù)據(jù)表 “PlayerInfo.xlsx”，其內(nèi)容如下：
? 點(diǎn)擊菜單欄的 GameTool -> GenerateExcel，將在 DATA_CLASS_PATH 和 DATA_CONTAINER_PATH 路徑下分別生成數(shù)據(jù)信息腳本和容器腳本：
2 BinaryMgr
? 參考如下代碼：
- 存儲(chǔ)數(shù)據(jù)
Test t = new Test();
t.i = 985;
t.str = "歡迎你";
print(Application.persistentDataPath);
BinaryDataMgr.Instance.Save(t, "哈哈哈");
- 獲取數(shù)據(jù)
Test t = BinaryDataMgr.Instance.Load<Test>("哈哈哈");
? 存儲(chǔ)位置可修改 “BinaryDataMgr.cs” 中的 DATA_BINARY_PATH 路徑。文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-467606.html
? 具體使用參考代碼注釋。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-467606.html
到了這里，關(guān)于2023-05-29 Unity 2進(jìn)制5——Excel配置表工具的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
