Unity内Excel转Json工具及读取解析方案

你一身傲骨怎能输

已于 2024-12-08 20:10:07 修改

阅读量216

点赞数 1

CC 4.0 BY-SA版权

分类专栏：游戏工具文章标签： Excel转Json

于 2024-11-29 23:00:52 首次发布

本文链接：https://blog.csdn.net/qq_33060405/article/details/144147569

游戏工具专栏收录该内容

30 篇文章

订阅专栏

通过工具能够有效地将Excel数据转换为游戏可用的JSON配置，并自动生成相应的C#类代码。下面是一个简单的实现方案，展示如何使用 Excel.dll 和 Newtonsoft.Json.dll 来完成这个任务。

1. 准备工作

确保你已经在Unity项目中引入了以下两个DLL：

Excel.dll: 用于读取Excel文件。
Newtonsoft.Json.dll: 用于处理JSON序列化和反序列化。

2. 工具的基本结构

你可以创建一个UnityEditor工具，包含以下几个主要功能：

读取Excel文件。
解析特定格式的数据。
生成JSON文件。
自动生成C#类代码。

3. 示例代码

以下是一个简单的示例代码，展示如何实现这些功能。

3.1 Excel 读取和解析

using System.Collections.Generic;
using System.IO;
using ExcelDataReader; // 确保你使用的Excel库
using Newtonsoft.Json;
using UnityEditor;
using UnityEngine;

public class ExcelToJsonConverter : EditorWindow
{
    private string excelFilePath;
    private string jsonOutputPath;

    [MenuItem("Tools/Excel to JSON Converter")]
    public static void ShowWindow()
    {
        GetWindow<ExcelToJsonConverter>("Excel to JSON Converter");
    }

    private void OnGUI()
    {
        GUILayout.Label("Convert Excel to JSON", EditorStyles.boldLabel);
        excelFilePath = EditorGUILayout.TextField("Excel File Path", excelFilePath);
        jsonOutputPath = EditorGUILayout.TextField("JSON Output Path", jsonOutputPath);

        if (GUILayout.Button("Convert"))
        {
            ConvertExcelToJson();
        }
    }

    private void ConvertExcelToJson()
    {
        if (string.IsNullOrEmpty(excelFilePath) || string.IsNullOrEmpty(jsonOutputPath))
        {
            Debug.LogError("Please specify both Excel file path and JSON output path.");
            return;
        }

        var data = ReadExcelFile(excelFilePath);
        var json = JsonConvert.SerializeObject(data, Formatting.Indented);
        File.WriteAllText(jsonOutputPath, json);
        Debug.Log("JSON file created at: " + jsonOutputPath);
        
        GenerateCSharpClass(data);
    }

    private List<Character> ReadExcelFile(string path)
    {
        var characters = new List<Character>();

        using (var stream = File.Open(path, FileMode.Open, FileAccess.Read))
        {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                while (reader.Read())
                {
                    if (reader.Depth == 0) continue; // Skip header row

                    var character = new Character
                    {
                        ID = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        Health = reader.GetInt32(2),
                        Damage = reader.GetInt32(3),
                        Abilities = JsonConvert.DeserializeObject<List<string>>(reader.GetString(4))
                    };
                    characters.Add(character);
                }
            }
        }

        return characters;
    }

    private void GenerateCSharpClass(List<Character> data)
    {
        string classCode = @"
		using System;
		using System.Collections.Generic;
		
		[Serializable]
		public class Character
		{
		    public int ID { get; set; }
		    public string Name { get; set; }
		    public int Health { get; set; }
		    public int Damage { get; set; }
		    public List<string> Abilities { get; set; }
		}";

        string classPath = Path.Combine(Application.dataPath, "Character.cs");
        File.WriteAllText(classPath, classCode);
        Debug.Log("C# class generated at: " + classPath);
    }
}

[Serializable]
public class Character
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int Health { get; set; }
    public int Damage { get; set; }
    public List<string> Abilities { get; set; }
}

4. 使用说明

打开工具: 在Unity中，点击菜单 Tools -> Excel to JSON Converter 打开工具窗口。
输入路径: 在工具窗口中输入Excel文件的路径和JSON输出路径。
转换: 点击“Convert”按钮，工具将读取Excel文件，生成JSON文件，并自动生成C#类代码。

5. 扩展性

支持更多数据类型: 你可以根据需要扩展 Character 类，支持更多的数据类型（如 float, int[], string[] 等）。
自定义映射: 允许用户自定义Excel列与JSON字段之间的映射关系。
错误处理: 增加错误处理机制，确保在读取Excel或生成JSON时能够捕获并报告错误。

总结

这个工具可以大大简化游戏开发中配置文件的管理流程，使得开发者能够更高效地处理数据。通过使用Excel作为输入格式，结合JSON作为输出格式，提供了直观的编辑和维护方式。希望这个示例能够帮助你实现你的工具！

代码

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using UnityEditor;
using UnityEngine;

[Serializable]
public class ExcelConfig
{
    public string name;
    public List<string> sheets;
}

[Serializable]
public class ExcelToJsonTool : MonoBehaviour
{
    public List<ExcelConfig> _Excels;


   



public class UtilTools
{
        public static int ParseInt(string data)
        {
            if (data.Length == 0)
                return 0;
            int val = 0;
            if (int.TryParse(data, out val) == false)
            {
                return 0;
            }
            return val;
        }

        public static float ParseFloat(string data)
        {
            if (data.Length == 0)
                return 0;

            float val = 0;
            if (float.TryParse(data, out val) == false)
            {
                return 0;
            }
            return val;
        }
    }
}

public class StringUtils
{
    private static object m_builderLock = new object();
    private static StringBuilder m_builder = new StringBuilder();
    public static string combine(params object[] texts)
    {
        lock (m_builderLock)
        {
            m_builder.Remove(0, m_builder.Length);
            var len = texts.Length;
            for (int i = 0; i < len; i++)
            {
                m_builder.Append(texts[i]);
            }
            return m_builder.ToString(0, m_builder.Length);
        }
    }

    public static string combine(string main, params object[] texts)
    {
        lock (m_builderLock)
        {
            m_builder.Remove(0, m_builder.Length);
            m_builder.Append(main);
            var len = texts.Length;
            for (int i = 0; i < len; i++)
            {
                m_builder.Append(texts[i]);
            }
            return m_builder.ToString(0, m_builder.Length);
        }
    }

    public static string combine(string main, int intParam)
    {
        lock (m_builderLock)
        {
            m_builder.Remove(0, m_builder.Length);
            m_builder.Append(main);
            m_builder.Append(intParam.ToString());
            return m_builder.ToString(0, m_builder.Length);
        }
    }

    public static string combine(string main, float floatParam)
    {
        lock (m_builderLock)
        {
            m_builder.Remove(0, m_builder.Length);
            m_builder.Append(main);
            m_builder.Append(floatParam.ToString());
            return m_builder.ToString(0, m_builder.Length);
        }
    }
}


#if UNITY_EDITOR
using Excel;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using UnityEditor;
using UnityEngine;


[CustomEditor(typeof(ExcelToJsonTool))]
public class ExcelToJsonToolEditor : Editor
{
    private string _curFinishText = "";
    private string _excelName = "";
    public override void OnInspectorGUI()
    {
        base.OnInspectorGUI();

        _excelName = EditorGUILayout.TextField("Excel名(不填全转):", _excelName);
        if (GUILayout.Button("Excel表转Json"))
        {
            this._curFinishText = "";
            this.DoAllXlsxToJson(this._excelName);
            this._curFinishText += "转表结束...";
        }
        else if (GUILayout.Button("打开Excel文件夹"))
        {
            string path = System.Environment.CurrentDirectory + "/Excel/init.txt";
            EditorUtility.RevealInFinder(path);
        }
        else if (GUILayout.Button("打开Json文件夹"))
        {
            string path = System.Environment.CurrentDirectory + "/Assets/Resources/Config/init.txt";
            EditorUtility.RevealInFinder(path);
        }

        EditorGUILayout.BeginHorizontal();
        GUILayout.Label(_curFinishText);
        EditorGUILayout.EndHorizontal();
    }

    void DoAllXlsxToJson(string singleExcel = "")
    {
        string filepath = Application.dataPath + "/Script/Tools/JsonClass.cs";
        _csharpText = File.ReadAllText(filepath);
        ExcelToJsonTool excels = target as ExcelToJsonTool;
        foreach (ExcelConfig excelName in excels._Excels)
        {
            if (excelName.name.Equals(singleExcel) || singleExcel.Equals(""))
            {
                this._curFinishText += "读取Excel:" + excelName.name + "\n";
                this.DoXlsxToJson(excelName);
                this._curFinishText += "\n";
                if (singleExcel.Equals("") == false)
                    break;
            }
        }

        //将代码写回c#文件
        if (File.Exists(filepath))
        {
            File.SetAttributes(filepath, FileAttributes.Normal);
            File.WriteAllText(filepath, this._csharpText);
        }
    }//end function

    private string _csharpText = "";
    private bool DoXlsxToJson(ExcelConfig config)
    {
        string dataPath = System.Environment.CurrentDirectory;
        // xlsx路径
        string xlsxPath = dataPath + "/Excel/" + config.name + ".xlsx";
        string savePath = Application.dataPath + "/Resources/Config/";
        FileStream stream = null;
        try
        {
            stream = File.Open(xlsxPath, FileMode.Open, FileAccess.Read);
        }
        catch (IOException e)
        {
            this._curFinishText = "请关闭Excel后再进行";
            stream.Close();
            return false;
        }

        if (stream == null)
            return false;
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        excelReader.Read();//需要调用一下Read，否则下面AsDataSet 方法会返回null，不知为何，困惑
        DataSet result = excelReader.AsDataSet();
        if (result == null)
        {
            Debug.LogError(config.name + " Has Empty Line!");
            return false;
        }

        // 读取
        foreach (string exSheet in config.sheets)
        {
            List<TypeValue> kvList = new List<TypeValue>();
            JsonKeyType idType = ReadSingleSheet(result.Tables[exSheet], savePath + exSheet + ".json", ref kvList);
            this.GenerateSheetCSharpCode(exSheet, kvList, idType);
            this._curFinishText += "----转表:" + exSheet + ".json完成\n";
        }

        stream.Close();

        return true;
    }//end function

    public class UICodeConfig
    {
        public const string classDefine = "\tpublic static string {0} = \"{1}\";\n";
        public const string variable = "\tpublic {0} {1};";
        public const string construct = "public {0}() : base(ConfigDefine.{1},{2})";
    }

    private void GenerateSheetCSharpCode(string sheetName, List<TypeValue> memberList, JsonKeyType keyType)
    {
        int startIndex = this._csharpText.IndexOf("ConfigDefine");
        int endIndex = this._csharpText.IndexOf("}", startIndex) + 1;
        string defineString = _csharpText.Substring(startIndex, endIndex - startIndex);
        string strHas = sheetName + " = ";
        if (defineString.Contains(strHas))
            return;//已经有定义了
        string classDefine = EditorUtil.format(UICodeConfig.classDefine, sheetName, sheetName);
        //插入Config定义到代码里
        this._csharpText = this._csharpText.Insert(endIndex - 1, classDefine);

        //类定义代码
        startIndex = this._csharpText.IndexOf("ConfigDefine");
        endIndex = this._csharpText.IndexOf("}", startIndex) + 1;

        int classStartIndex = endIndex + 1;
        string configStr = StringUtils.combine(sheetName, "Config");
        string inhiertStr = "Config<" + configStr + ">";

        string className = "\npublic class " + configStr + " : " + inhiertStr;
        List<string> memberStrList = new List<string>();
        int count = memberList.Count;
        for (int i = 0; i < count; ++i)
        {
            string memberStr = EditorUtil.format(UICodeConfig.variable, memberList[i].memberType, memberList[i].memberName);
            memberStrList.Add(memberStr);
        }
        string finalMemberStr = string.Join("\n", memberStrList);//finalMemberStr
        className = className + "\n{\n" + finalMemberStr;


        //构造函数代码
        string KeyTypeStr = "JsonKeyType.STRING";
        if (keyType == JsonKeyType.INT)
            KeyTypeStr = "JsonKeyType.INT";
        string constructStr = EditorUtil.format(UICodeConfig.construct, configStr, sheetName, KeyTypeStr) +" { }";//construct
        className = className + "\n\t" + constructStr+"\n}\n";

        this._csharpText = this._csharpText.Insert(endIndex + 1, className);
    }

    public class TypeValue
    {
        public string memberType;
        public string memberName;
    }
    private JsonKeyType ReadSingleSheet(DataTable dataTable, string jsonPath,ref List<TypeValue> kvList)
    {
        JsonKeyType type = JsonKeyType.INT;
        kvList = new List<TypeValue>();//返回成员变量名字和类型
        int rows = dataTable.Rows.Count;
        int Columns = dataTable.Columns.Count;
        DataRowCollection collect = dataTable.Rows;
        // xlsx对应的数据字段，规定是第二行
        // xlsx对应的数据字段類型，规定是第3行
        string[] jsonFileds = new string[Columns];
        string[] jclassTypes = new string[Columns];
        for (int i = 0; i < Columns; i++)
        {
            jsonFileds[i] = collect[1][i].ToString();
            jclassTypes[i] = collect[2][i].ToString();

            string memberName = jsonFileds[i];
            if (memberName.Equals("ID") || memberName.Equals("IDs"))
            {
                continue;
            }

            TypeValue kv = new TypeValue();
            kv.memberName = jsonFileds[i];
            switch (jclassTypes[i])
            {
                case "String":
                    kv.memberType = "string";
                    break;
                case "Int":
                    kv.memberType = "int";
                    break;
                case "ArrayInt":
                case "ArrayIntLine":
                    kv.memberType = "int[]";
                    break;
                case "ArrayString":
                case "StringArrayLine":
                    kv.memberType = "string[]";
                    break;
                case "Float":
                    kv.memberType = "float";
                    break;
                case "ArrayFloat":
                    kv.memberType = " float[]";
                    break;
                default:
                    kv.memberType = "string";
                    break;
            }//end switch
            kvList.Add(kv);
        }//end for

        List<object> objsToSave = new List<object>();
        // 数据从第4行开始
        for (int i = 3; i < rows; i++)
        {
            JObject postedJObject = new JObject();
            bool isEnd = false;
            for (int j = 0; j < Columns; j++)
            {
                string memberName = jsonFileds[j];
                object value = null;
                string classType = jclassTypes[j];
                string str = collect[i][j].ToString();
                switch (classType)
                {
                    case "String":
                        value = Convert.ChangeType(collect[i][j], typeof(string));
                        break;
                    case "Int":
                        int valueint = EditorUtil.ParseInt(str);
                        value = Convert.ChangeType(valueint, typeof(int));
                        break;
                    case "ArrayInt":
                        if (str.Length > 0)
                        {
                            string[] strs = str.Split(',');
                            int[] ints = new int[strs.Length];
                            for (int k = 0; k < strs.Length; k++)
                            {
                                ints[k] = EditorUtil.ParseInt(strs[k]);
                            }
                            value = ints;
                        }
                        else
                        {
                            value = new int[0];
                        }
                        break;
                    case "ArrayIntLine":
                        if (str.Length > 0)
                        {
                            string[] strs = str.Split('|');
                            int[] ints = new int[strs.Length];
                            for (int k = 0; k < strs.Length; k++)
                            {
                                ints[k] = EditorUtil.ParseInt(strs[k]);
                            }
                            value = ints;
                        }
                        else
                        {
                            value = new int[0];
                        }
                        break;
                    case "ArrayString":
                        if (str.Length > 0)
                        {
                            value = str.Split(',');
                        }
                        else
                        {
                            value = new string[0];
                        }
                        break;
                    case "StringArrayLine":
                        if (str.Length > 0)
                        {
                            value = str.Split('|');
                        }
                        else
                        {
                            value = new string[0];
                        }
                        break;
                    case "Float":
                        float valuefloat = EditorUtil.ParseFloat(str);
                        value = Convert.ChangeType(valuefloat, typeof(float));
                        break;
                    case "ArrayFloat":
                        if (str.Length > 0)
                        {
                            string[] strs = str.Split(',');
                            float[] floats = new float[strs.Length];
                            for (int k = 0; k < strs.Length; k++)
                            {
                                floats[k] = EditorUtil.ParseFloat(strs[k]);
                            }
                            value = floats;
                        }
                        else
                        {
                            value = new float[0];
                        }
                        break;
                    default:
                        Convert.ChangeType(collect[i][j], typeof(string));
                        break;
                }//end switch

                if (memberName.Equals("IDs"))
                    type = JsonKeyType.STRING;
                if (memberName.Equals("ID") || memberName.Equals("IDs"))
                {
                    if (str.Equals(""))
                    {
                        isEnd = true;
                        break;
                    }
                }

                if (value == null)
                {
                    Debug.Log("value is Null");
                }

                postedJObject.Add(memberName, JToken.FromObject(value));
            }//end for j

            if (isEnd)
                break;
            objsToSave.Add(postedJObject);
        }//end for i
        // 保存为Json
        string content = Newtonsoft.Json.JsonConvert.SerializeObject(objsToSave, Formatting.Indented);
        SaveFile(content, jsonPath);
        return type;
    }//end func

    private  void SaveFile(string content, string jsonPath)
    {
        StreamWriter streamWriter;
        FileStream fileStream;
        if (File.Exists(jsonPath))
        {
            File.Delete(jsonPath);
        }
        fileStream = new FileStream(jsonPath, FileMode.Create);
        streamWriter = new StreamWriter(fileStream);
        streamWriter.Write(content);
        streamWriter.Close();
    }

}//end class


public class EditorUtil
{
    public static int ParseInt(string data)
    {
        if (data.Length == 0)
            return 0;
        int val = 0;
        if (int.TryParse(data, out val) == false)
        {
            return 0;
        }
        return val;
    }

    public static float ParseFloat(string data)
    {
        if (data.Length == 0)
            return 0;

        float val = 0;
        if (float.TryParse(data, out val) == false)
        {
            return 0;
        }
        return val;
    }

    public static string format(string valuestr, params object[] paramStrs)
    {
        string afterStr = "";
        try
        {
            afterStr = string.Format(valuestr, paramStrs);
        }
        catch (Exception ex)
        {
#if UNITY_EDITOR
            UnityEngine.Debug.LogError(string.Format(": {0} 参数数量不匹配", valuestr));
#endif
        }
        return afterStr;
    }
}

#endif


using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using UnityEngine;

public enum JsonKeyType
{
    INT,
    STRING,
}

public class ConfigDefine
{
	public static string ItemInfo = "ItemInfo";
}


public class ItemInfoConfig : Config<ItemInfoConfig>
{
	public string Name;
	public int Type;
	public int[] Funs;
	public string[] Values;
	public ItemInfoConfig() : base(ConfigDefine.ItemInfo,JsonKeyType.STRING) { }
}

using Newtonsoft.Json.Linq;
using System.Collections.Generic;
using UnityEngine;

public class Config<T> where T : new()
{
    public string jName;
    public int ID;
    public string IDs;
    public Dictionary<int, T> _configDic;
    public Dictionary<string, T> _configStringDic;

    private JsonKeyType keyType;

    private static readonly object sycObj = new object();
    private static T t;
    private bool _isInit = false;
    public static T Instance
    {
        get
        {
            if (t == null)
            {
                lock (sycObj)
                {
                    if (t == null)
                    {
                        t = new T();
                    }
                }
            }
            return t;
        }
    }

    public  JArray JsonRead(string name)
    {
        string json = "";
        string path = "Config/" + name;
        TextAsset text = Resources.Load<TextAsset>(path);
        if (text == null)
            return null;
        json = text.text;
        JArray jArray = JArray.Parse(json);
        return jArray;
    }

    public Config(string jName, JsonKeyType tt = JsonKeyType.INT)
    {
        this.jName = jName;
        this.keyType = tt;
        if (tt == JsonKeyType.INT)
            this._configDic = new Dictionary<int, T>();
        else if (tt == JsonKeyType.STRING)
            this._configStringDic = new Dictionary<string, T>();
    }
    private void Init()
    {
        if (this._isInit)
            return;
        this._isInit = true;
        //读取Json
        JArray list = this.JsonRead(this.jName);
        foreach (var item in list)
        {
            T oneJson = item.ToObject<T>();
            if (this.keyType == JsonKeyType.INT)
            {
                int ID = item.Value<int>("ID");
                this._configDic[ID] = oneJson;
            }
            else if (this.keyType == JsonKeyType.STRING)
            {
                string IDs = item.Value<string>("IDs");
                this._configStringDic[IDs] = oneJson;
            }
        }//end for
    }


    public Dictionary<int, T> getDataArray()
    {
        this.Init();
        return this._configDic;
    }

    public Dictionary<string, T> getStrDataArray()
    {
        this.Init();
        return this._configStringDic;
    }

    public T GetData(int id)
    {
        this.Init();
        T config;
        if (this._configDic.TryGetValue(id, out config))
            return config;
        return default(T);
    }

    public T GetData(string ids)
    {
        this.Init();
        T config;
        if (this._configStringDic.TryGetValue(ids, out config))
            return config;
        return default(T);
    }
}