在Unity环境中读取Excel配置文件(进阶)

        阅读了基础篇后,我们使用ExcelReader插件配合ScriptObject类和UItoolkit,拓展编辑器(如果你不了解uitoolkit,参阅我的另一篇关于编辑器开发的文章),一键生成ScriptObject配置资产.

        首先我们创建一个脚本,能生成一个ScriptObject资产持久化保存路径.

图一

using System;
using System.Collections;
using System.Collections.Generic;
using UnityEngine;

[CreateAssetMenu(fileName = "ExcelPathScriptObj", menuName ="excel")]
public class ExcelPathScriptObj : ScriptableObject
{
    public string excelFilePath;//Excel文件路径
    //数据类,我会在表中声明字段类型,最后生成一个类
    public string dataClassPath;  
    public string dataScriptObjClassPath;
    public string dataScriptObjPath;
    private void Awake()
    {
        excelFilePath= "Assets/ArtRes/Excel";
        dataClassPath="Assets/Scripts/ExcelData/DataClass";
        dataScriptObjClassPath="Assets/Scripts/ExcelData/DataScriptObjClass";
        dataScriptObjPath = "Assets/Resources/DataScriptObj";
    }
}

图二

图三

        上面的数据类路径你可能不理解,请看图二,第一行你可以将其作为注释,第二行作为字段名,第三行作为字段类型,表名就是类名,表和类是能对应上的,开发的编辑器能自动匹配你写的表生成这个类(图三).

那么dataScriptObjClassPath这个是什么路径?这个是存放生成ScriptObject资产的类的路径.

图四

        上一篇我说到每一行就是一个对象,每个表就是一个List,所以开发的编辑器也可以自动生成这个类,每张表就会多一个List字段.

        最后dataScriptObjPath就是存放根据上面的这个类生成的ScriptObject资产的地方.

        但是我考虑你可能想自定义路径,所以我写了图一,图一的类可以持久化路径.编辑器会加载图一生成的资产,使用其路径.

        图五是我开发的编辑器先点第一个按钮,会生成所有的数据类,等待编译然后点击第二个按钮,会生成一个ScriptObject资产(图六),这个资产中会有多个List字段,每个List字段代表一张表,这个资产默认放在Resources文件夹下,你可以加载这个资产拿到这些配置.

图五

图六

编辑器源码图七

图七

using System;
using System.CodeDom.Compiler;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using ExcelDataReader;
using UnityEditor;
using UnityEngine;
using UnityEngine.UIElements;

public class AutoGenerateExcel : EditorWindow
{
    #region Path

    /// <summary>
    /// Excel文件存储路径
    /// </summary>
    private string pathConfig = "Assets/Editor/AutoGenerateExcelConfig/Config/ExcelPathScriptObj.asset";

    private ExcelPathScriptObj pathScriptObj;

    #endregion


    #region Component

    private Button creatDataClass;
    private Button creatScriptObj;
    private Label hintLable;

    #endregion


    [MenuItem("Window/Tools/GenerateexcelConfig")]
    private static void ShowWin()
    {
        var win = GetWindow<AutoGenerateExcel>();
        win.titleContent = new GUIContent("Auto Generate Tool");
    }

    private void CreateGUI()
    {
        pathScriptObj = AssetDatabase.LoadAssetAtPath<ExcelPathScriptObj>(pathConfig);
        Directory.CreateDirectory(pathScriptObj.excelFilePath);
        Directory.CreateDirectory(pathScriptObj.dataClassPath);
        Directory.CreateDirectory(pathScriptObj.dataScriptObjClassPath);
        Directory.CreateDirectory(pathScriptObj.dataScriptObjPath);
        #region InitComponent

        hintLable = new Label();
        hintLable.style.fontSize = 18;  // 设置字体大小
        hintLable.style.color = Color.red;  // 设置字体颜色
        hintLable.style.width = 300;
        hintLable.style.height = 50;
        creatDataClass = new Button(CreatDataClass);
        creatDataClass.text = "Auto Generate Data Class";
        creatScriptObj = new Button(CreatScriptObj);
        creatScriptObj.text = "Auto Generate ScriptObject";

        #endregion


        #region AddComponent

        rootVisualElement.Add(hintLable);
        rootVisualElement.Add(creatDataClass);
        rootVisualElement.Add(creatScriptObj);

        #endregion

       
    }

    #region ButtonFunc

    private void CreatDataClass()
    {
        if (!File.Exists(pathScriptObj.excelFilePath + "/Excel.xlsx"))
        {
            DelayClearHintLable("Excel File Can't Find,Do Nothing!");
            return;
        }

        var dataTableCollection = ReadExcel("Excel.xlsx");
        GenerateExcelDataClass(dataTableCollection, pathScriptObj.dataClassPath);
        GenerateScriptObjClass(dataTableCollection, pathScriptObj.dataScriptObjClassPath);
        AssetDatabase.Refresh();
    }

    private void CreatScriptObj()
    {
        AssetDatabase.DeleteAsset(pathScriptObj.dataScriptObjPath);
        if (!File.Exists(pathScriptObj.excelFilePath + "/Excel.xlsx"))
        {
            DelayClearHintLable("Excel File Can't Find,Do Nothing!");
            return;
        }

        var dataTableCollection = ReadExcel("Excel.xlsx");
        var instance = ScriptableObject.CreateInstance("ExcelDataScriptObject");
        Type t = instance.GetType();
        foreach (DataTable dataTable in dataTableCollection)
        {
            //获取ScriptObjec脚本的链表字段
            string fieldName = char.ToLower(dataTable.TableName[0]) + dataTable.TableName.Substring(1) + "List";

            var fieldInfo = t.GetField(fieldName);
            if (fieldInfo == null)
            {
                Debug.LogWarning($"Field {fieldName} not found in {t.Name}");
                continue;
            }

            //获取链表类型
            Type listType = fieldInfo.FieldType;
            if (!listType.IsGenericType || listType.GetGenericTypeDefinition() != typeof(List<>))
            {
                Debug.LogWarning($"{fieldName} is not a generic List.");
                continue;
            }


            Type[] genericArgs = listType.GetGenericArguments();
            Type listGenericType = genericArgs[0];

            // 通过反射获取实例
            var list = fieldInfo.GetValue(instance);
            if (list == null)
            {
                Debug.LogWarning(
                    $"List {fieldName} is null. Ensure the field name is correct and the list is properly initialized.");
            }

            for (int i = 2; i < dataTable.Rows.Count; i++)
            {
                var row = dataTable.Rows[i];
                object value = CreateValueForList(row, listGenericType);

                listType.GetMethod("Add").Invoke(list, new object[] { value });
            }
        }

        Directory.CreateDirectory(pathScriptObj.dataScriptObjPath);
        EditorUtility.SetDirty(instance);
        AssetDatabase.CreateAsset(instance, pathScriptObj.dataScriptObjPath+"/ExcelDataScriptObject.asset");
        AssetDatabase.SaveAssets();
        AssetDatabase.Refresh();
    }

    #endregion

    #region CoreFunc

    //excel 第一行作为列标题(被忽略),第二行作为变量名,第三行作为变量类型,第一列为ID
    private DataTableCollection ReadExcel(string excelName)
    {
        string path = Path.Combine(pathScriptObj.excelFilePath, excelName);
        using (var fileStream = File.Open(path, FileMode.Open, FileAccess.Read))
        {
            using (var reader = ExcelReaderFactory.CreateReader(fileStream))
            {
                var conf = new ExcelDataSetConfiguration
                {
                    ConfigureDataTable = _ => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = true //第一行作为列标题
                    }
                };
                var dataset = reader.AsDataSet(conf);
                return dataset.Tables;
            }
        }
    }


    private void GenerateExcelDataClass(DataTableCollection dataTableCollection, string path)
    {
        AssetDatabase.DeleteAsset(path);
        StringBuilder fileContent = new StringBuilder();
        fileContent.Append("using System;\n");
        foreach (DataTable dataTable in dataTableCollection)
        {
            var nameRow = dataTable.Rows[0];
            var typeRow = dataTable.Rows[1];
            fileContent.Append("[Serializable]\n").Append("public class ").Append(dataTable.TableName).Append("\n{\n");
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                fileContent.Append("\tpublic ").Append(typeRow[i].ToString()).Append(" ").Append(nameRow[i].ToString())
                    .Append(";\n");
            }

            fileContent.Append("}\r\n");
        }

        Directory.CreateDirectory(path);
        File.WriteAllText(path + "/ExcelDataClass.cs", fileContent.ToString());
    }

    private void GenerateScriptObjClass(DataTableCollection dataTableCollection, string path)
    {
        AssetDatabase.DeleteAsset(path);
        StringBuilder fileContent = new StringBuilder();
        fileContent.Append("using UnityEngine;\n");
        fileContent.Append("using System.Collections.Generic;\n");
        fileContent.Append("public class ExcelDataScriptObject : ScriptableObject \n{\n");
        foreach (DataTable dataTable in dataTableCollection)
        {
            fileContent
               // .Append("[SerializeField]\n")
                .Append("public  ").Append($"List<{dataTable.TableName}> ")
                .Append(char.ToLower(dataTable.TableName[0]) + dataTable.TableName.Substring(1) + "List")
                .Append($" = new List<{dataTable.TableName}>();\n");
        }

        fileContent.Append("}");
        Directory.CreateDirectory(path);
        File.WriteAllText(path + "/ExcelDataScriptObject.cs", fileContent.ToString());
    }

    private object CreateValueForList(DataRow row, Type listGenericType)
    {
        var instance = Activator.CreateInstance(listGenericType);
        //访问所有字段
        var fields = listGenericType.GetFields();
        foreach (var field in fields)
        {
            string fieldName = field.Name;

            for (int i = 0; i < row.Table.Rows[0].ItemArray.Length; i++)
            {
                if (row.Table.Rows[0].ItemArray[i].ToString() == fieldName)
                {
                    object value = Convert.ChangeType(row[row.Table.Columns[i]], field.FieldType);
                    field.SetValue(instance, value);
                }
            }
        }

        return instance;
    }

    #endregion


    #region UtilityFunc

    private void DelayClearHintLable(string content)
    {
        hintLable.text = content;
        double timer = EditorApplication.timeSinceStartup + 2;
        EditorApplication.CallbackFunction callbackFunction = null;
        callbackFunction = () =>
        {
            if (EditorApplication.timeSinceStartup >= timer)
            {
                hintLable.text = string.Empty;
                EditorApplication.update -= callbackFunction; // 移除回调函数
            }
        };
        EditorApplication.update += callbackFunction;
    }

    #endregion
}

这个是我的文件结构,对应编辑器脚本中的路径