Unity 从零开始的框架搭建1-6 读Excel配置表自动生成json和数据结构类

本人水平有限如有不足还请斧正,本文仅作学习交流使用不做任何商业用途

效果

Excel结构

需要插件

读表算法

代码

效果

Unity Excel一键读表转json和数据结构类

Excel结构

需要插件

Epplus的dll即可可以网上搜索或者用Vs工具下载另外我已上传资源

读表算法

看上面这个表的结构

1.先找到工作簿下某一工作表的索引默认为1

2.从第二行获取类型到List

从第三行获取id以及名称到另一个List

3.获取工作表名做裁剪

4.核心算法

行号 | 列1（类型） | 列2（类型） | 列3（类型） | 列4（类型）
--- | --- | --- | --- | ---
2    | int        | string      | float       | int[]
3    | id         | 角色名      | 生命值      | 技能等级数组
4    | 1001       | 战士        | 80.2        | 1,2,3
5    | 1002       | 刺客        | 60          | 2,3,4

是横着读的,声明一个字典

for (int row = 4; row <= worksheet.Dimension.Rows; row++)
{// 处理当前行（如第4行、第5行...）var dataItem = new Dictionary<string, object>();// 内层循环遍历当前行的所有列for (int col = 1; col <= worksheet.Dimension.Columns; col++){// 获取当前列的值（如第4行第1列的值是"1001"）string value = worksheet.Cells[row, col].Text;// 根据列索引获取预读取的类型和字段名string type = variableTypes[col - 1]; // 第1列对应variableTypes[0] = "int"string name = variableNames[col - 1]; // 第1列对应variableNames[0] = "id"// 转换值并存储到字典dataItem[name] = ConvertValue(type, value);}dataList.Add(dataItem); // 将当前行数据存入总列表
}

从4,1开始匹配之前的两个List表[col -1] 然后做类型转换最后存入字典

然后内层循环是4,2---------4,3-------4,4

之后外层循环跳跃到5,1-----5,2-----5,3......以此类推

               var worksheet = package.Workbook.Worksheets[1];if (worksheet.Dimension.Rows < 3){Debug.LogError("配置表格式错误：至少需要3行配置行（类型行和名称行）");return;}var variableTypes = new List<string>();var variableNames = new List<string>();// 读取类型和字段名for (int col = 1; col <= worksheet.Dimension.Columns; col++){variableTypes.Add(worksheet.Cells[2, col].Text.Trim());variableNames.Add(worksheet.Cells[3, col].Text.Trim());}// 获取工作表名称string sheetName = worksheet.Name;int commentIndex = sheetName.IndexOf('#');string className;if (commentIndex > 0){className = sheetName.Substring(0, commentIndex).Trim();}else{className = Path.GetFileNameWithoutExtension(excelFilePath).Replace(" ", "");}var dataList = new List<Dictionary<string, object>>();// 读取数据行for (int row = 4; row <= worksheet.Dimension.Rows; row++){var dataItem = new Dictionary<string, object>();for (int col = 1; col <= worksheet.Dimension.Columns; col++){string value = worksheet.Cells[row, col].Text;dataItem[variableNames[col - 1]] = ConvertValue(variableTypes[col - 1], value);}dataList.Add(dataItem);}

全部代码

using System;
using System.Collections.Generic;
using System.IO;
using UnityEditor;
using UnityEngine;
using Newtonsoft.Json;
using OfficeOpenXml;public class ConfigTableTool : EditorWindow
{private string configTablePath = "D:/unitygames/GreyBox/Tools/Excel";private string exportJsonPath = "D:/unitygames/GreyBox/Assets/Model/Json";private string generateClassPath = "D:/unitygames/GreyBox/Assets/Scripts/Data/SaveJsonClass";private string[] excelFiles;private int selectedIndex = 0;private Texture2D excelIcon;private Vector2 scrollPos;private const string ConfigTablePathKey = "ConfigTableTool_ConfigTablePath";private const string ExportJsonPathKey = "ConfigTableTool_ExportJsonPath";private const string GenerateClassPathKey = "ConfigTableTool_GenerateClassPath";[MenuItem("Tools/配置表工具")]public static void ShowWindow(){GetWindow<ConfigTableTool>("配置表工具");}private void OnEnable(){configTablePath = EditorPrefs.GetString(ConfigTablePathKey, "");exportJsonPath = EditorPrefs.GetString(ExportJsonPathKey, "");generateClassPath = EditorPrefs.GetString(GenerateClassPathKey, "");// 加载Excel图标（确保图标文件存在，且设置为EditorGUI类型）excelIcon = AssetDatabase.LoadAssetAtPath<Texture2D>("Assets/JKFrame/Editor/ConfigeTableTool/icon.png");if (excelIcon == null){Debug.LogWarning("Excel图标未找到，使用默认图标");}if (!string.IsNullOrEmpty(configTablePath)){LoadExcelFiles();}}private void OnDisable(){EditorPrefs.SetString(ConfigTablePathKey, configTablePath);EditorPrefs.SetString(ExportJsonPathKey, exportJsonPath);EditorPrefs.SetString(GenerateClassPathKey, generateClassPath);}private void OnGUI(){// 路径配置区域DrawPathSetting();// Excel文件列表显示DrawExcelList();// 打开选中Excel按钮DrawOpenButton();// 底部操作按钮DrawActionButtons();EditorGUILayout.LabelField("版本: dev0.2", EditorStyles.miniLabel);}void DrawPathSetting(){configTablePath = EditorGUILayout.TextField("配置表路径", configTablePath);if (GUILayout.Button("选择配置表文件夹", GUILayout.Width(150))){var path = EditorUtility.OpenFolderPanel("选择配置表文件夹", configTablePath, "");if (!string.IsNullOrEmpty(path)){configTablePath = path;LoadExcelFiles();}Repaint();}EditorGUILayout.Space(10);exportJsonPath = EditorGUILayout.TextField("JSON导出路径", exportJsonPath);if (GUILayout.Button("选择JSON导出文件夹", GUILayout.Width(150))){var path = EditorUtility.OpenFolderPanel("选择JSON导出文件夹", exportJsonPath, "");if (!string.IsNullOrEmpty(path)){exportJsonPath = path;}Repaint();}EditorGUILayout.Space(10);generateClassPath = EditorGUILayout.TextField("类文件路径", generateClassPath);if (GUILayout.Button("选择类文件文件夹", GUILayout.Width(150))){var path = EditorUtility.OpenFolderPanel("选择类文件文件夹", generateClassPath, "");if (!string.IsNullOrEmpty(path)){generateClassPath = path;}Repaint();}EditorGUILayout.Space(20);}void DrawExcelList(){if (!Directory.Exists(configTablePath)) return;EditorGUILayout.LabelField("Excel文件列表", EditorStyles.boldLabel);using (var scrollScope = new EditorGUILayout.ScrollViewScope(scrollPos)){scrollPos = scrollScope.scrollPosition;int itemsPerRow = Mathf.FloorToInt(EditorGUIUtility.currentViewWidth / 110);int rowCount = 0;for (int i = 0; i < excelFiles.Length; i++){if (i % itemsPerRow == 0){EditorGUILayout.BeginHorizontal();rowCount++;}DrawExcelItem(i);if ((i + 1) % itemsPerRow == 0 || i == excelFiles.Length - 1){EditorGUILayout.EndHorizontal();}}}}void DrawExcelItem(int index){bool isSelected = index == selectedIndex;string fileName = Path.GetFileNameWithoutExtension(excelFiles[index]);EditorGUILayout.BeginVertical(GUI.skin.box, GUILayout.Width(100), GUILayout.Height(90));// 绘制选中背景if (isSelected){GUI.color = new Color(0.5f, 0.8f, 1f, 0.3f);GUI.DrawTexture(GUILayoutUtility.GetRect(100, 90), Texture2D.whiteTexture);GUI.color = Color.white;}// 绘制图标if (excelIcon != null && GUILayout.Button(excelIcon, GUILayout.Width(50), GUILayout.Height(50))){selectedIndex = index;}// 绘制文件名GUIStyle labelStyle = new GUIStyle(EditorStyles.miniLabel){alignment = TextAnchor.MiddleCenter,wordWrap = true};EditorGUILayout.LabelField(fileName, labelStyle);EditorGUILayout.EndVertical();}void DrawOpenButton(){EditorGUILayout.BeginHorizontal();if (GUILayout.Button("打开选中Excel", GUILayout.Height(30))){if (excelFiles != null && excelFiles.Length > 0 && selectedIndex >= 0 && selectedIndex < excelFiles.Length){OpenExcelFile(excelFiles[selectedIndex]);}}EditorGUILayout.EndHorizontal();}void DrawActionButtons(){EditorGUILayout.BeginHorizontal();if (GUILayout.Button("导出选中配置表", GUILayout.Height(30))){if (excelFiles != null && excelFiles.Length > 0){ExportSingleExcel(excelFiles[selectedIndex]);}}if (GUILayout.Button("导出全部配置表", GUILayout.Height(30))){if (excelFiles != null && excelFiles.Length > 0){foreach (var file in excelFiles){ExportSingleExcel(file);}}}EditorGUILayout.EndHorizontal();}void LoadExcelFiles(){if (!string.IsNullOrEmpty(configTablePath)){excelFiles = Directory.GetFiles(configTablePath, "*.xlsx");selectedIndex = 0;}}void ExportSingleExcel(string excelFilePath){try{if (!File.Exists(excelFilePath)){Debug.LogError($"配置表文件不存在: {excelFilePath}");return;}using (var package = new ExcelPackage(new FileInfo(excelFilePath))){var worksheet = package.Workbook.Worksheets[1];if (worksheet.Dimension.Rows < 3){Debug.LogError("配置表格式错误：至少需要3行配置行（类型行和名称行）");return;}var variableTypes = new List<string>();var variableNames = new List<string>();// 读取类型和字段名for (int col = 1; col <= worksheet.Dimension.Columns; col++){variableTypes.Add(worksheet.Cells[2, col].Text.Trim());variableNames.Add(worksheet.Cells[3, col].Text.Trim());}// 获取工作表名称string sheetName = worksheet.Name;int commentIndex = sheetName.IndexOf('#');string className;if (commentIndex > 0){className = sheetName.Substring(0, commentIndex).Trim();}else{className = Path.GetFileNameWithoutExtension(excelFilePath).Replace(" ", "");}var dataList = new List<Dictionary<string, object>>();// 读取数据行for (int row = 4; row <= worksheet.Dimension.Rows; row++){var dataItem = new Dictionary<string, object>();for (int col = 1; col <= worksheet.Dimension.Columns; col++){string value = worksheet.Cells[row, col].Text;dataItem[variableNames[col - 1]] = ConvertValue(variableTypes[col - 1], value);}dataList.Add(dataItem);}// 生成JSONstring jsonFileName = Path.GetFileNameWithoutExtension(excelFilePath) + ".json";string jsonPath = Path.Combine(exportJsonPath, jsonFileName);// 添加类名元数据var jsonWithMetadata = new Dictionary<string, object>{{ "className", className },{ "data", dataList }};File.WriteAllText(jsonPath, JsonConvert.SerializeObject(jsonWithMetadata, Formatting.Indented));Debug.Log($"✅ JSON 导出成功: {jsonPath}");// 生成C#类if (!string.IsNullOrEmpty(generateClassPath)){string classPath = Path.Combine(generateClassPath, className + ".cs");string classCode = GenerateClassCode(className, variableTypes, variableNames);File.WriteAllText(classPath, classCode);Debug.Log($"✅ C#类生成成功: {classPath}");}}AssetDatabase.Refresh();}catch (Exception e){Debug.LogError($"❌ 导出失败: {excelFilePath}\n{e.Message}");}}string GenerateClassCode(string className, List<string> types, List<string> names){string code = $"// Auto Generated\n[System.Serializable]\npublic class {className} : ConfigBase\n{{\n";for (int i = 0; i < names.Count; i++){code += $"    public {ConvertTypeName(types[i])} {names[i]};\n";}code += "}";return code;}string ConvertTypeName(string typeName){return typeName switch{"float[]" => "float[]","int[]" => "int[]","string[]" => "string[]",_ => typeName};}object ConvertValue(string type, string value){if (string.IsNullOrWhiteSpace(value)) return null;try{return type switch{"int" => Convert.ToInt32(value),"float" => Convert.ToSingle(value),"bool" => Convert.ToBoolean(value),"double" => Convert.ToDouble(value),"string" => value,"int[]" => Array.ConvertAll(value.Split(','), int.Parse),"float[]" => Array.ConvertAll(value.Split(','), float.Parse),"string[]" => value.Split(','),_ => value};}catch{Debug.LogError($"类型转换失败：{type} <- {value}");return null;}}void OpenExcelFile(string filePath){try{UnityEngine.Debug.Log($"尝试打开文件: {filePath}");System.Diagnostics.Process.Start(filePath);}catch (Exception ex){UnityEngine.Debug.LogError($"无法打开Excel文件: {filePath} - {ex.Message}");}}
}