Unity Excel导表工具转Lua文件

思路介绍

借助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、对于一些配置错误的情况也需要考虑到，增加一些报错逻辑判断并提示。