思路介紹

借助EPPlus讀取Excel文件中的配置數據，根據指定的不同類型的數據配置規則來解析成對應的代碼文本，將解析出的字符串內容寫入到XXX.lua.txt文件中即可

EPPlus常用API

//命名空間
using OfficeOpenXml;//Excel文件路徑
var fileExcel = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//加載Excel數據資源
var excelPackage = new ExcelPackage(fileExcel);//工作表列表
var sheetList = excelPackage.Workbook.Worksheets;foreach (var sheet in sheetList)
{var sheetName = sheet.Name;//工作表名稱var rowCount = sheet.Dimension.Rows;//總行數var columnCount = sheet.Dimension.Columns;//總列數for (var i = 4; i <= rowCount; i++){for (var j = 1; j <= columnCount; j++){//獲取第幾行第幾列的數據var value = sheet.GetValue(i, j);}}
}

制定Excel數據類型配置規則

編寫導表工具之前，需要和策劃、后端溝通制定數據配置規則

例如：

類型	標記
數值型	number
布爾型	bool
字符串	string
列表	list<type> type支持:number,bool,string 示例：2,3,4
字典	dic<key|value> type支持: number,bool,string 示例：語文|88;數學|91;英語|67

?

導入EPPlus.dll

在Unity的Assets下創建Plugins文件夾，并將EPPlus.dll文件放到該文件夾中

核心代碼

using System.Collections.Generic;
using System.IO;
using System.Text;
using UnityEngine;
using OfficeOpenXml;
using UnityEditor;public class ExcelToLua
{//Excel導表文件路徑public static readonly string ExcelFolderPath = "D:\\Study\\Excel";//lua文件路徑private static readonly string LuaFolderPath = Path.Combine(Application.dataPath, "LuaScripts");//Table文件后綴拼接private const string LuaNameEnd = "Table.lua.txt";[MenuItem("新項目工具/導表工具/導入Excel表數據", false, 1)]static void ImportSingleExcelFile(){if (Directory.Exists(ExcelFolderPath)){var path = EditorUtility.OpenFilePanel("打開文件", ExcelFolderPath, "xlsx");if (path.Length != 0){CreateLuaFile(path);AssetDatabase.Refresh();}}}/// <summary>/// 獲取Excel數據并創建Lua文件/// </summary>/// <param name="filePath"></param>public static void CreateLuaFile(string filePath){//Excel文件路徑var fileExcel = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//加載Excel數據資源var excelPackage = new ExcelPackage(fileExcel);//工作表列表var sheetList = excelPackage.Workbook.Worksheets;StringBuilder sb = new StringBuilder();//變量字典var variableDic = new Dictionary<int, string>();//變量類型字典var typeDic = new Dictionary<int, string>();var fileName = Path.GetFileName(filePath); //文件名稱帶后綴  例：A-活動_activity.xlssb.Append($"--{fileName}\nreturn {{\n");foreach (var sheet in sheetList){variableDic.Clear();typeDic.Clear();var sheetName = sheet.Name;//工作表名稱var rowCount = sheet.Dimension.Rows;//總行數var columnCount = sheet.Dimension.Columns;//總列數//變量類型列表InitSheetDic(sheet, typeDic, 1);//變量列表InitSheetDic(sheet, variableDic, 2);sb.Append($"[\"{sheetName}\"] = {{\n");for (var i = 4; i <= rowCount; i++){for (var j = 1; j <= columnCount; j++){if (!typeDic.ContainsKey(j) || !variableDic.ContainsKey(j)){break;}var type = typeDic[j];var variable = variableDic[j];if (type == null || variable == null){continue;}//獲取第幾行第幾列的數據var value = sheet.GetValue(i, j);var valueStr = GetTypeValue(type, value);if (j == 1){sb.Append($"    [{valueStr}] = {{");}sb.Append($"{variable} = {valueStr};");}sb.Append("};\n");}sb.Append("},\n");}sb.Append("}");var txtName = Path.GetFileNameWithoutExtension(filePath);var nameIndex = txtName.LastIndexOf('_');if (nameIndex != -1){txtName = txtName.Substring(nameIndex + 1);txtName = txtName.Substring(0, 1).ToUpper() + txtName.Substring(1);}//創建XXXTable文件File.WriteAllText($"{LuaFolderPath}\\{txtName}{LuaNameEnd}", sb.ToString());Debug.Log($"<color=#00EE00>{fileName}</color>表導入成功");sb.Clear();}//將Excel列表某一行的數據初始化到指定字典中private static void InitSheetDic(ExcelWorksheet sheet, Dictionary<int, string> dic, int rowNum){var columnSum = sheet.Dimension.End.Column;for (var i = 1; i <= columnSum; i++){var value = sheet.GetValue(rowNum, i);if (value == null)break;dic[i] = value.ToString();}}//根據不同類型的數據拼接對應格式的數據內容private static string GetTypeValue(string typeStr, object val){if (val == null)return GetDefaultValue(typeStr);var value = val.ToString();var result = value;switch (typeStr){case "number":case "bool":break;case "string":result = "\"" + value + "\"";break;case "list<number>":case "list<bool>":result = "{" + value + "}";break;case "list<string>":var strArray = value.Split(',');if (strArray.Length > 0){var sb = new StringBuilder();sb.Append("{");foreach (var item in strArray){sb.Append($"\"{item}\",");}sb.Append("}");result = sb.ToString();}break;default:if (typeStr.Contains("dic<")){if (typeStr.Contains("string")){var frontIndex = typeStr.IndexOf("string");var backIndex = typeStr.LastIndexOf("string");var isFront = frontIndex == 5;var isBack = backIndex == (typeStr.Length - 7);var strDic = value.Split(';');if (strDic.Length > 0){var sb = new StringBuilder();sb.Append("{");foreach (var item in strDic){var cell = item.Split("|");var frontStr = isFront ? "\"" + cell[0] + "\"" : cell[0];var backStr = isBack ? "\"" + cell[1] + "\"" : cell[1];sb.Append($"[{frontStr}] = {backStr},");}sb.Append("}");result = sb.ToString();}}else{var sb = new StringBuilder();var strDic = value.Split(';');if (strDic.Length > 0){sb.Append("{");foreach (var item in strDic){var cell = item.Split("|");sb.Append($"[{cell[0]}] = {cell[1]},");}sb.Append("}");result = sb.ToString();}}}else{result = "\"" + value + "\"";}break;}return result;}//如果某個數據未填，返回默認值private static string GetDefaultValue(string typeStr){string result;switch (typeStr){case "number":result = "0";break;case "bool":result = "false";break;case "string":result = "\"\"";break;default:if (typeStr.Contains("dic") || typeStr.Contains("list"))result = "{}";elseresult = "\"\"";break;}return result;}
}

小提示

1、在實際開發工作中，一般會用到導入單個或者所有的表兩種邏輯。對于導出所有的表的邏輯，可以借助多線程來提高效率。

2、對于一些配置錯誤的情況也需要考慮到，增加一些報錯邏輯判斷并提示。