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

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

目录

效果

Excel结构

需要插件 

读表算法 

代码

效果

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);
                }

                // 生成JSON
                string 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}");
        }
    }
}