思路介绍
借助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.xls
sb.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 = "{}";
else
result = "\"\"";
break;
}
return result;
}
}
小提示
1、在实际开发工作中，一般会用到导入单个或者所有的表两种逻辑。对于导出所有的表的逻辑，可以借助多线程来提高效率。
2、对于一些配置错误的情况也需要考虑到，增加一些报错逻辑判断并提示。
