Unity导出Excel表

发表于2019-03-22
评论0 3.5k浏览
一般在游戏开发中策划都会把数据配置在excel中。所以我们需要从excel中导出数据,并且把数据保存在本地。

有很多种方式可以把数据导出成我们想要的格式,比如说导出为json,cs,或者xml。还有的喜欢直接把数据序列化为二进制文件,然后在游戏中加载的时候直接反序列化,这样方便引用数据。

代码如下:
using System.Collections.Generic;
using System.Reflection;
using Microsoft.CSharp;
using System.CodeDom.Compiler;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.IO;
using System;
using UnityEngine;
using UnityEditor;
using Excel;
namespace WJExcelDataManager
{
    public class ExcelDataTool
    {
        private static string INPUT_PATH;
        public const string CODE_NAMESPACE = "WJExcelDataClass";//由表生成的数据类型均在此命名空间内
        public const string BinDataFolder = "BinConfigData";//序列化的数据文件都会放在此文件夹内,此文件夹位于Resources文件夹下用于读取数据
        private static List<string> codeList;//存放所有生成的类的代码
        private static Dictionary<string, List<ConfigData[]>> dataDict;//存放所有数据表内的数据,key:类名  value:数据
        [UnityEditor.MenuItem("开发工具/导表工具/导入全部数据")]
        public static void LoadAllExcelData()
        {
            //if (!EditorUtility.DisplayDialog("注意!!!", "导表前要关闭打开的数据表,否则会失败,是否继续?", "继续", "取消")) return;
            INPUT_PATH = PlayerPrefs.GetString(System.Environment.CurrentDirectory + "ExcelDataInputPath", "");
            if (string.IsNullOrEmpty(INPUT_PATH))
            {
                ProgressBar.HideBarWithFailInfo("\n请先设置数据表路径!");
                throw new Exception("请先设置数据表路径!");
            }
            string[] files = Directory.GetFiles(INPUT_PATH, "*.xls");
            if (files == null || files.Length == 0)
            {
                EditorUtility.DisplayDialog("注意!!!", "\n暂无可以导入的数据表!", "确定");
                EditorUtility.ClearProgressBar();
                throw new Exception("暂无可以导入的数据表!");
            }
            if (codeList == null)
            {
                codeList = new List<string>();
            }
            else
            {
                codeList.Clear();
            }
            if (dataDict == null)
            {
                dataDict = new Dictionary<string, List<ConfigData[]>>();
            }
            else
            {
                dataDict.Clear();
            }
            float step = 1f;
            foreach (string item in files)
            {
                ProgressBar.UpdataBar("正在加载 " + item, step / files.Length * 0.4f);
                step++;
                GetExcelData(item);
            }
            if (codeList.Count == 0)
            {
                EditorUtility.DisplayDialog("注意!!!", "\n暂无可以导入的数据表!", "确定");
                EditorUtility.ClearProgressBar();
                throw new Exception("暂无可以导入的数据表!");
            }
            //编译代码,生成包含所有数据表内数据类型的dll
            Assembly assembly = CompileCode(codeList.ToArray());
            //准备序列化数据
            string BinDataPath = System.Environment.CurrentDirectory + "/Assets/Resources/" + BinDataFolder;//序列化后的数据存放路径
            if (Directory.Exists(BinDataPath)) Directory.Delete(BinDataPath, true);//删除旧的数据文件
            Directory.CreateDirectory(BinDataPath);
            step = 1;
            foreach (KeyValuePair<string, List<ConfigData[]>> each in dataDict)
            {
                ProgressBar.UpdataBar("序列化数据: " + each.Key, step / dataDict.Count * 0.6f + 0.399f);//0.399是为了进度条在生成所有代码以前不会走完显示完成弹窗
                step++;
                //Assembly.CreateInstance 方法 (String) 使用区分大小写的搜索,从此程序集中查找指定的类型,然后使用系统激活器创建它的实例化对象
                object container = assembly.CreateInstance(CODE_NAMESPACE + "." + each.Key);
                Type temp = assembly.GetType(CODE_NAMESPACE + "." + each.Key + "Item");
                //序列化数据
                Serialize(container, temp, each.Value, BinDataPath);
            }
            ProgressBar.UpdataBar("创建数据管理类: DataManager", 0.999f);
            ScriptGenerator.CreateDataManager(assembly);
            ProgressBar.UpdataBar("\n导表成功!", 1);
            AssetDatabase.Refresh();
            Debug.Log("<color=yellow>导表成功!</color>");
        }
        //数据表内每一格数据
        class ConfigData
        {
            public string Type;//数据类型
            public string Name;//字段名
            public string Data;//数据值
        }
        private static void GetExcelData(string inputPath)
        {
            FileStream stream = null;
            try
            {
                stream = File.Open(inputPath, FileMode.Open, FileAccess.Read);
            }
            catch
            {
                EditorUtility.DisplayDialog("注意!!!", "\n请关闭 " + inputPath + " 后再导表!", "确定");
                EditorUtility.ClearProgressBar();
                throw new Exception("请关闭 " + inputPath + " 后再导表!");
            }
            IExcelDataReader excelReader = null;
            if (inputPath.EndsWith(".xls")) excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
            else if (inputPath.EndsWith(".xlsx")) excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            if (!excelReader.IsValid)
            {
                ProgressBar.HideBarWithFailInfo("\n无法读取的文件:  " + inputPath);
                EditorUtility.ClearProgressBar();
                throw new Exception("无法读取的文件:  " + inputPath);
            }
            else
            {
                do
                {
                    // sheet name
                    string className = excelReader.Name;
                    string[] types = null;//数据类型
                    string[] names = null;//字段名
                    List<ConfigData[]> dataList = new List<ConfigData[]>();
                    int index = 1;
                    //开始读取
                    while (excelReader.Read())
                    {
                        //这里读取的是每一行的数据
                        string[] datas = new string[excelReader.FieldCount];
                        for (int j = 0; j < excelReader.FieldCount; ++j)
                        {
                            datas[j] = excelReader.GetString(j);
                        }
                        //空行不处理
                        if (datas.Length == 0 || string.IsNullOrEmpty(datas[0]))
                        {
                            ++index;
                            continue;
                        }
                        //第4行表示类型
                        if (index == 4) types = datas;
                        //第5行表示变量名
                        else if (index == 5) names = datas;
                        //后面的表示数据
                        else if (index > 5)
                        {
                            //把读取的数据和数据类型,名称保存起来,后面用来动态生成类
                            List<ConfigData> configDataList = new List<ConfigData>();
                            for (int j = 0; j < datas.Length; ++j)
                            {
                                ConfigData data = new ConfigData();
                                data.Type = types[j];
                                data.Name = names[j];
                                data.Data = datas[j];
                                if (string.IsNullOrEmpty(data.Type) || string.IsNullOrEmpty(data.Data)) continue;//空的数据不处理
                                configDataList.Add(data);
                            }
                            dataList.Add(configDataList.ToArray());
                        }
                        ++index;
                    }
                    if (string.IsNullOrEmpty(className))
                    {
                        ProgressBar.HideBarWithFailInfo("\n空的类名(excel页签名), 路径:  " + inputPath);
                        throw new Exception("空的类名(excel页签名), 路径:  " + inputPath);
                    }
                    if (names != null && types != null)
                    {
                        //根据刚才的数据来生成C#脚本
                        ScriptGenerator generator = new ScriptGenerator(inputPath, className, names, types);
                        //所有生成的类的代码最终保存在这个链表中
                        codeList.Add(generator.Generate());
                        if (dataDict.ContainsKey(className))
                        {
                            ProgressBar.HideBarWithFailInfo("\n类名重复:" + className + " ,路径: " + inputPath);
                            throw new Exception("类名重复: " + className + " ,路径:  " + inputPath);
                        }
                        else dataDict.Add(className, dataList);
                    }
                }
                while (excelReader.NextResult());//excelReader.NextResult() Excel表下一个sheet页有没有数据
            }
            stream.Dispose();
            stream.Close();
        }
        //编译代码
        private static Assembly CompileCode(string[] scripts)
        {
            string path = System.Environment.CurrentDirectory + "/Assets/Plugins/" + CODE_NAMESPACE;
            if (Directory.Exists(path)) Directory.Delete(path, true);//删除旧dll
            Directory.CreateDirectory(path);
            //编译器实例对象
            CSharpCodeProvider codeProvider = new CSharpCodeProvider();
            //编译器参数实例对象
            CompilerParameters objCompilerParameters = new CompilerParameters();
            objCompilerParameters.ReferencedAssemblies.AddRange(new string[] { "System.dll" });//添加程序集引用
            objCompilerParameters.OutputAssembly = path + "/" + CODE_NAMESPACE + ".dll";//设置输出的程序集名
            objCompilerParameters.GenerateExecutable = false;
            objCompilerParameters.GenerateInMemory = true;
            //开始编译脚本
            CompilerResults cr = codeProvider.CompileAssemblyFromSource(objCompilerParameters, scripts);
            if (cr.Errors.HasErrors)
            {
                ProgressBar.HideBarWithFailInfo("\n编译dll出错(详情见控制台)!");
                foreach (CompilerError err in cr.Errors)
                {
                    Debug.LogError(err.ErrorText);
                }
                throw new Exception("编译dll出错!");
            }
            Debug.Log("已生成 " + path + "/<color=#FFFF00>" + CODE_NAMESPACE + ".dll</color>");
            return cr.CompiledAssembly;
        }
        //序列化对象
        private static void Serialize(object container, Type temp, List<ConfigData[]> dataList,string BinDataPath)
        {
            //设置数据
            foreach (ConfigData[] datas in dataList)
            {
                //Type.FullName 获取该类型的完全限定名称,包括其命名空间,但不包括程序集。
                object t = temp.Assembly.CreateInstance(temp.FullName);
                foreach (ConfigData data in datas)
                {
                    //Type.GetField(String) 搜索Type内指定名称的公共字段。
                    FieldInfo info = temp.GetField(data.Name);
                    // FieldInfo.SetValue 设置对象内指定名称的字段的值
                    info.SetValue(t, ParseValue(data.Type, data.Data, temp.Name));
                }
                // FieldInfo.GetValue 获取对象内指定名称的字段的值
                object id = temp.GetField("id").GetValue(t);//获取id
                FieldInfo dictInfo = container.GetType().GetField("Dict");
                object dict = dictInfo.GetValue(container);
                bool isExist = (bool)dict.GetType().GetMethod("ContainsKey").Invoke(dict, new System.Object[] { id });
                if (isExist)
                {
                    EditorUtility.DisplayDialog("注意!!!", "ID重复:" + id + ",类型: " + container.GetType().Name, "确定");
                    throw new Exception("ID重复:" + id + ",类型: " + container.GetType().Name);
                }
                dict.GetType().GetMethod("Add").Invoke(dict, new System.Object[] { id, t });
            }
            IFormatter f = new BinaryFormatter();
            Stream s = new FileStream(BinDataPath + "/" + container.GetType().Name + ".bytes", FileMode.OpenOrCreate, FileAccess.Write, FileShare.Write);
            f.Serialize(s, container);
            Debug.Log("已生成 " + BinDataPath + "/<color=#FFFF00>" + container.GetType().Name + ".bytes</color>");
            s.Close();
        }
        public static object ParseValue(string type, string data, string classname)
        {
            object o = null;
            try
            {
                switch (type)
                {
                    case SupportType.INT:
                        o = int.Parse(data);
                        break;
                    case SupportType.LONG:
                        o = long.Parse(data);
                        break;
                    case SupportType.FLOAT:
                        o = float.Parse(data);
                        break;
                    case SupportType.STRING:
                        o = data;
                        break;
                    case SupportType.LIST_INT:
                        data = data.Substring(1, data.Length - 2);//移除 '['   ']'
                        string[] ints = data.Split(',');//逗号分隔
                        List<int> list = new List<int>();
                        foreach (var item in ints)
                        {
                            list.Add(int.Parse(item));
                        }
                        o = list;
                        break;
                    case SupportType.LIST_FLOAT:
                        data = data.Substring(1, data.Length - 2);//移除 '['   ']'
                        string[] floats = data.Split(',');//逗号分隔
                        List<float> list2 = new List<float>();
                        foreach (var item in floats)
                        {
                            list2.Add(float.Parse(item));
                        }
                        o = list2;
                        break;
                    case SupportType.LIST_STRING:
                        data = data.Substring(1, data.Length - 2);//移除 '['   ']'
                        string[] strs = data.Split(',');//逗号分隔
                        o = strs;
                        break;
                    case SupportType.LIST_LIST_INT:
                        break;
                    case SupportType.LIST_LIST_FLOAT:
                        break;
                    case SupportType.LIST_LIST_STRING:
                        break;
                    case SupportType.DICTIONARY_INT_INT:
                        break;
                    case SupportType.DICTIONARY_INT_FLOAT:
                        break;
                    case SupportType.DICTIONARY_INT_STRING:
                        break;
                    case SupportType.DICTIONARY_INT_LIST_INT:
                        break;
                    case SupportType.DICTIONARY_INT_LIST_FLOAT:
                        break;
                }
            }
            catch (Exception ex)
            {
                ProgressBar.HideBarWithFailInfo("\n" + "错误的数据值:" + data + "\n位于:" + classname);
                throw new Exception("\n错误的数据值:" + data + "\n位于:" + classname, ex);
            }
            return o;
        }
    }
}

如社区发表内容存在侵权行为,您可以点击这里查看侵权投诉指引