unity自定义编辑器使用NPOI实现Excel写入工具

一、用途

用于unity将数据导出到excel，对于一些事件编辑器、剧情编辑器可能用得上。没找到一些比较好用的工具包实现。

此处的实现主要为了不浪费时间去写这些基础工具上。（文中工具类代码仅支持写功能，读功能原本已经实现了相关工具，后面可能会补充这个实现） 编辑器最终效果：

模板示例文件：

第一行首格需要填对应类名，全类名

第二行excel表格字段备注

第三行类型名，可自行修改转换器类添加对应的类型转换实现

第四行字段名，和属性名比较时忽略大小写。表名只识别@开头的表名

二、环境准备：导入包（非手动编译），所有包可在编辑模式下运行，不加入运行时依赖

Rider > Tool > Nuget Tool Window，打开包管理窗口。

下载导入包，NPOI包（NPOIExcel不是NPOI，后者依赖不全）

Package文件夹中找到dll库直接拷贝到自己的Unity项目文件夹中，unity的包管理系统识别不到IDE Nuget管理器安装的，VS同理。（Assets同级目录下）

放到你的项目文件夹中

三、Excel写工具类封装及使用示例

懒得写代码解释了，应该也比较明了（后续完整使用示例）

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using UnityEngine;

namespace Framework
{
    /**
     * 依赖NPOI，支持读写
     */
    public class ExcelUtils2
    {
        public static void WriteAllDataToExcel<T>(List<T> data, ExcelWriteOption option = null)
            where T : new()
        {
            if (option == null)
            {
                option = ExcelWriteOption.CreateDefaultOption();
            }

            ExcelWriter writer = new ExcelWriter(option);
            writer.Write(data);
        }
    }

    public class ExcelWriter
    {
        private ExcelWriteOption _option;

        public ExcelWriter CreateExcelWriter(ExcelWriteOption option)
        {
            return new ExcelWriter(option);
        }

        public ExcelWriter(ExcelWriteOption option = null)
        {
            if (option == null)
            {
                option = ExcelWriteOption.CreateDefaultOption();
            }

            _option = option;
        }

        /**
         * 注意：new Xssfworkbook(file)会直接读完关闭文件流，读写模式下无法使用原本的文件流再写入
         */
        public void Write<T>(List<T> data)
            where T : new()
        {
            IWorkbook workbook;
            byte[] fileBytes;

            // 读取文件内容到字节数组
            using (FileStream file = new FileStream(_option.fileName, FileMode.Open, FileAccess.Read))
            {
                fileBytes = new byte[file.Length];
                file.Read(fileBytes, 0, fileBytes.Length);
            }

            // 使用 MemoryStream 初始化工作簿
            using (MemoryStream memoryStream = new MemoryStream(fileBytes))
            {
                workbook = new XSSFWorkbook(memoryStream);
            }

            UpdateWorkbook(workbook, data);

            // 重新打开文件流进行写入
            using (FileStream file = new FileStream(_option.fileName, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(file);
            }
        }

        private void UpdateWorkbook<TDataType>(IWorkbook workbook, List<TDataType> data)
        {
            IModifier modifier = null;
            if (_option.CustomModifier != null)
            {
                modifier = _option.CustomModifier;
            }
            else
            {
                modifier = new DefaultModifier();
            }

            modifier.Modifier<TDataType>(workbook, data, _option);
        }
    }

    public interface IModifier
    {
        public void Modifier<T>(IWorkbook workbook, List<T> data, ExcelWriteOption option);
    }

    public class DefaultModifier : IModifier
    {
        private Dictionary<string, int> fieldNameToExcelColumnIdxMap = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);

        private string className;
        private string[] remarks, types, fields;
        private ExcelWriteOption _option;

        public void Modifier<T>(IWorkbook workbook, List<T> data, ExcelWriteOption option)
        {
            _option = option;
            fieldNameToExcelColumnIdxMap.Clear();
            TypeToExcelHandlerBinder.LoadDefaultHandler();
            ISheet sheet = workbook.GetSheet(option.sheetName);
            className = sheet.GetRow(0)?.GetCell(0)?.StringCellValue;
            if (className == null)
            {
                throw new Exception("空类型名称");
            }

            Type type = Type.GetType(className);
            if (type == null)
            {
                throw new Exception("无法识别的类名:" + className);
            }

            remarks = sheet.GetRow(1)?.Cells?.Select(c => c.StringCellValue).ToArray();
            types = sheet.GetRow(2)?.Cells?.Select(c => c.StringCellValue).ToArray();
            fields = sheet.GetRow(3)?.Cells?.Select(c => c.StringCellValue).ToArray();
            if (fields == null)
            {
                throw new Exception("excel表格未配置字段类型");
            }

            for (int idx = 0; idx < fields.Length; idx++)
            {
                fieldNameToExcelColumnIdxMap[fields[idx]] = idx;
            }

            // 读取已有数据
            ExcelToTypeHandlerBinder.LoadDefaultHandler();
            List<T> allSheetRow = ReadAllSheetRow<T>(sheet);

            // 写入数据
            int e = -1;
            for (var i = 0; i < data.Count; i++)
            {
                if ((e = allSheetRow.FindIndex(item => option.EqualsFunc(item, data[i]))) != -1)
                {
                    Debug.Log("已存在，更新: " + e);
                    WriteRow(data[i], sheet, option, e);
                }
                else
                {
                    WriteRow(data[i], sheet, option, i + option.startRow);
                }
            }
        }

        private List<T> ReadAllSheetRow<T>(ISheet sheet)
        {
            List<T> result = new List<T>();
            IEnumerator rowEnumerator = sheet.GetRowEnumerator();
            int idx = 0;
            while (idx < _option.startRow)
            {
                idx++;
            }

            while (rowEnumerator.MoveNext())
            {
                idx++;
                T row = ReadRow<T>(sheet, idx);
                if (row != null)
                {
                    result.Add(row);
                }
            }

            return result;
        }

        private T ReadRow<T>(ISheet sheet, int i)
        {
            IRow row = sheet.GetRow(i);
            if (row == null)
            {
                return default;
            }

            T instance = Activator.CreateInstance<T>();
            List<PropertyInfo> propertyInfos = typeof(T)?.GetProperties().ToList();
            for (var i1 = 0; i1 < row.Cells.Count; i1++)
            {
                string fieldName = fields[i1];
                string fieldType = types[i1];
                PropertyInfo info = propertyInfos
                    .First(item => item.Name.Equals(fieldName, StringComparison.OrdinalIgnoreCase));
                ICell cell = row.GetCell(i1);
                if (cell == null)
                {
                    continue;
                }

                info.SetValue(instance,
                    ExcelToTypeHandlerBinder.GetValueHandleByConfigType(fieldType).Invoke(cell));
            }

            return instance;
        }

        private void WriteRow<T>(T r, ISheet sheet, ExcelWriteOption option, int row)
        {
            PropertyInfo[] properties = typeof(T).GetProperties();
            IRow tRow = sheet.GetRow(row) ?? sheet.CreateRow(row);
            for (int i = 0; i < properties.Length; i++)
            {
                PropertyInfo propertyInfo = properties[i];
                if (!fieldNameToExcelColumnIdxMap.TryGetValue(propertyInfo.Name.ToLowerInvariant(), out var value))
                {
                    continue;
                }

                ICell cell = tRow.GetCell(value) ?? tRow.CreateCell(value);
                // 对应属性类型处理
                Action<object, ICell> handler = TypeToExcelHandlerBinder.GetHandler(propertyInfo.PropertyType);
                handler?.Invoke(propertyInfo.GetValue(r), cell);
            }
        }
    }


    [Serializable]
    public class ExcelWriteOption
    {
        public int startRow = 4;
        public string fileName = "TempWriteExcel.xlsx";
        public string sheetName = "TempSheet";
        private IModifier customModifier = null;
        public Func<object, object, bool> EqualsFunc = ((o, o1) => o == o1);

        public IModifier CustomModifier
        {
            get => customModifier;
            set => customModifier = value;
        }

        public static Builder NewBuilder()
        {
            return new Builder();
        }

        public class Builder
        {
            private ExcelWriteOption _option = new ExcelWriteOption();

            public Builder StartRow(int v)
            {
                _option.startRow = v;
                return this;
            }

            public ExcelWriteOption Bulld()
            {
                return _option;
            }
        }

        private ExcelWriteOption()
        {
        }

        public static ExcelWriteOption CreateDefaultOption()
        {
            return NewBuilder().StartRow(4).Bulld();
        }
    }

    /**
     * c#类型写入到excel的转换器
     */
    public class TypeToExcelHandlerBinder
    {
        private static Dictionary<Type, Action<object, ICell>> typeWriteToExcelHandlerMap =
            new Dictionary<Type, Action<object, ICell>>();

        public static void Register(Type t, Action<object, ICell> handler)
        {
            typeWriteToExcelHandlerMap[t] = handler;
        }

        public static Action<object, ICell> GetHandler(Type t)
        {
            if (!typeWriteToExcelHandlerMap.ContainsKey(t))
            {
                Debug.Log("类型不存在对应处理器: " + t.Name);
                return (o, cell) => { };
            }

            return typeWriteToExcelHandlerMap[t];
        }

        public static void LoadDefaultHandler()
        {
            // string
            Register(typeof(string), (o, cell) =>
            {
                cell.SetCellValue(o.ToString());
                cell.SetCellType(CellType.String);
            });
            // int
            Register(typeof(int), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue((int)o);
            });
            // bool
            Register(typeof(bool), (o, cell) =>
            {
                cell.SetCellType(CellType.Boolean);
                cell.SetCellValue((bool)o);
            });
            // long
            Register(typeof(long), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue((long)o);
            });
            // float
            Register(typeof(float), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue((float)o);
            });
            // double
            Register(typeof(double), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue((double)o);
            });
            // list<string>
            Register(typeof(List<string>), (o, cell) =>
            {
                cell.SetCellType(CellType.String);
                string s = string.Join(",", (List<string>)o);
                cell.SetCellValue(s);
            });
            // list<int>
            Register(typeof(List<int>), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                string s = string.Join(",", (List<int>)o);
                cell.SetCellValue(s);
            });
            // list<float>
            Register(typeof(List<float>), (o, cell) =>
            {
                cell.SetCellType(CellType.Numeric);
                string s = string.Join(",", (List<float>)o);
                cell.SetCellValue(s);
            });
        }
    }

    public class ExcelToTypeHandlerBinder
    {
        private static Dictionary<string, Type> excelConfigTypeToTypeMap = new Dictionary<string, Type>(StringComparer.OrdinalIgnoreCase);

        private static Dictionary<Type, Func<ICell, object>> excelValueToObjectHandlerMap =
            new Dictionary<Type, Func<ICell, object>>();

        public static void RegisterConfigType(string excelConfigType, Type t)
        {
            excelConfigTypeToTypeMap[excelConfigType.ToLowerInvariant()] = t;
        }

        public static void RegisterExcelValueToValue(Type t, Func<ICell, object> func)
        {
            excelValueToObjectHandlerMap[t] = func;
        }

        public static Func<ICell, object> GetValueHandleByConfigType(string configType)
        {
            if (excelConfigTypeToTypeMap.TryGetValue(configType.ToLowerInvariant(), out Type t))
            {
                if (excelValueToObjectHandlerMap.TryGetValue(t, out Func<ICell, object> f))
                {
                    return f;
                }
            }

            Debug.Log("类型不存在:" + configType);
            return o => o;
        }

        public static void LoadDefaultHandler()
        {
            // string
            RegisterConfigType("string", typeof(string));
            RegisterExcelValueToValue(typeof(string), (o) => { return (string)o.StringCellValue; });
            // int
            RegisterConfigType("int", typeof(int));
            RegisterExcelValueToValue(typeof(int), (o) => { return (int)o.NumericCellValue; });
            // long
            RegisterConfigType("long", typeof(long));
            RegisterExcelValueToValue(typeof(long), (o) => { return (long)o.NumericCellValue; });
            // bool
            RegisterConfigType("bool", typeof(bool));
            RegisterExcelValueToValue(typeof(bool), (o) => { return o.BooleanCellValue; });
            // float
            RegisterConfigType("float", typeof(float));
            RegisterExcelValueToValue(typeof(float), (o) => { return (float)o.NumericCellValue; });
            // double
            RegisterConfigType("double", typeof(double));
            RegisterExcelValueToValue(typeof(double), (o) => { return (double)o.NumericCellValue; });
            // list<string>
            RegisterConfigType("list<string>", typeof(List<string>));
            RegisterExcelValueToValue(typeof(List<string>), (o) =>
            {
                string s = o.StringCellValue;
                return s.Split(",").ToList();
            });
            // list<int>
            RegisterConfigType("list<int>", typeof(List<int>));
            RegisterExcelValueToValue(typeof(List<int>), (o) =>
            {
                string s = (string)o.StringCellValue;
                return s.Split(",").Select(item =>
                {
                    if (int.TryParse(item, out int v))
                    {
                        return v;
                    }

                    return 0;
                }).ToList();
            });
            // list<float>
            RegisterConfigType("list<float>", typeof(List<float>));
            RegisterExcelValueToValue(typeof(List<float>), (o) =>
            {
                string s = (string)o.StringCellValue;
                return s.Split(",").Select(item =>
                {
                    if (float.TryParse(item, out float v))
                    {
                        return v;
                    }

                    return 0;
                }).ToList();
            });
        }
    }
}

使用代码：

// LevelEvent换成你自己的数据类，工具类的写实现基于属性实现读写，不基于字段，属性需要有get/set(IDE Rider直接生成需要属性的get/set)
ExcelUtils2.WriteAllDataToExcel(new List<LevelEvent>() {m.curLevenEvent}, option);

四、自定义编辑器实际使用示例

编辑器类(相关依赖工具类替换为自己项目的工具类)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Framework;
using UnityEditor;
using UnityEngine;

namespace BikexsGame
{
    [CustomEditor(typeof(LevelEventEditManager))]
    public class LevelEventEditor : Editor
    {
        public static string PersistenceWritePathKey = "LevelEventEditorSaveFile";
        public static string LastOpenFolderPathKey = "LastOpenFolderPathKey";
        public static string lastSheetNameKey = "lastSheetNameKey";
        public static string lastStartRowKey = "lastStartRowKey";
        public static string lastSheetListKey = "lastSheetListKey";
        public static string lastSheetIdxKey = "lastSheetIdxKey";
        public static string lastLevelActionEventTypeKey = "lastLevelActionEventTypeKey";
        
        private string path = "";
        private string sheetName = "";
        private string startRow = "5";
        private string lastOpenFolderPath = "";
        private string log = "";
        private int sheetIdx = 0;
        private LevelActionEventType _levelActionEventType;

        private string[] sheetList = new []{"无"};
        public override void OnInspectorGUI()
        {
            base.OnInspectorGUI();
            LevelEventEditManager m = (LevelEventEditManager)target;
            GUILayout.Box ("===============编辑器面板=================", GUILayout.ExpandWidth(true));
            if (string.IsNullOrEmpty(path) && PlayerPrefs.HasKey(PersistenceWritePathKey))
            {
                path = PlayerPrefs.GetString(PersistenceWritePathKey);
            }

            if (PlayerPrefs.HasKey(LastOpenFolderPathKey))
            {
                lastOpenFolderPath = PlayerPrefs.GetString(LastOpenFolderPathKey);
            }
            if(string.IsNullOrEmpty(lastSheetNameKey) && PlayerPrefs.HasKey(lastSheetNameKey))
            {
                lastSheetNameKey = PlayerPrefs.GetString(lastSheetNameKey);
            }
            if(PlayerPrefs.HasKey(lastStartRowKey))
            {
                startRow = PlayerPrefs.GetString(lastStartRowKey);
            }

            if (PlayerPrefs.HasKey(lastSheetListKey))
            {
                sheetList = JsonUtils.ReadFromJson<string[]>(PlayerPrefs.GetString(lastSheetListKey));
            }

            if (PlayerPrefs.HasKey(lastLevelActionEventTypeKey))
            {
                if (Enum.TryParse(PlayerPrefs.GetString(lastLevelActionEventTypeKey), out LevelActionEventType t))
                {
                    _levelActionEventType = t;
                }
            }

            List<LevelEvent> data = new List<LevelEvent>();

            GUILayout.BeginHorizontal();
            GUILayout.Label("文件路径:");
            if (GUILayout.Button("选择"))
            {
                path = EditorUtility.OpenFilePanel("选择文件", lastOpenFolderPath, "xlsx");
                // Debug.Log("path: " + path + "; " + Path.GetDirectoryName(path));
                if (!string.IsNullOrEmpty(path))
                {
                    PlayerPrefs.SetString(LastOpenFolderPathKey, Path.GetDirectoryName(path));
                    PlayerPrefs.SetString(PersistenceWritePathKey, path);
                }

                GUIUtility.ExitGUI();
            }

            GUILayout.EndHorizontal();
            GUILayout.BeginVertical();
            GUILayout.TextField(path);
            GUILayout.EndVertical();
            // 筛选
            GUILayout.Box("加载数据筛选", GUILayout.Width(200));
            _levelActionEventType = (LevelActionEventType)EditorGUILayout.EnumPopup("LevelEventType", _levelActionEventType);
            PlayerPrefs.SetString(lastLevelActionEventTypeKey, _levelActionEventType.ToString());
            GUILayout.BeginHorizontal();
            GUILayout.Box("写入参数");
            if (GUILayout.Button("重置", GUILayout.Width(50)))
            {
                startRow = "5";
                sheetName = "";
            }
            if (GUILayout.Button("加载表名", GUILayout.Width(100)))
            {
                List<string> list = ExcelLoader.LoadAllSheetName(path);
                sheetList = list.Where(item => item.StartsWith("@")).ToArray();
                PlayerPrefs.SetString(lastSheetListKey, JsonUtils.WriteToJson(sheetList));
            }
            GUILayout.EndHorizontal();
            
            GUILayout.BeginVertical();
            GUILayout.BeginHorizontal();
            GUILayout.Label("表：");
            sheetIdx = EditorGUILayout.Popup(sheetIdx, sheetList);
            if (sheetIdx < sheetList.Length)
            {
                PlayerPrefs.SetInt(lastSheetIdxKey, sheetIdx);
                sheetName = sheetList[sheetIdx];
            }
            // sheetName = GUILayout.TextField(sheetName);
            GUILayout.EndHorizontal();
            GUILayout.BeginHorizontal();
            GUILayout.Label("开始行：");
            startRow = GUILayout.TextField(startRow);
            PlayerPrefs.SetString(lastStartRowKey, startRow);
            GUILayout.EndHorizontal();
            GUILayout.EndVertical();
            
            
            GUILayout.BeginHorizontal();
            if (GUILayout.Button("加载"))
            {
            }

            if (GUILayout.Button("保存"))
            {
                ExcelWriteOption option = ExcelWriteOption.CreateDefaultOption();
                option.sheetName = sheetList[sheetIdx];
                option.fileName = path;
                option.startRow = int.Parse(startRow);
                ExcelUtils2.WriteAllDataToExcel(new List<LevelEvent>() {m.curLevenEvent}, option);
                log = "保存完成";
            }

            if (GUILayout.Button("更新文件路径"))
            {
                PlayerPrefs.SetString(PersistenceWritePathKey, path);
            }

            GUILayout.EndHorizontal();
            
            // 日志
            GUILayout.TextArea(log);
            serializedObject.ApplyModifiedProperties();
        }
    }
}

数据类

using System;
using System.Collections.Generic;
using AppFramework;
using Framework.Resource;
using LitJson.Extensions;
using UnityEngine;
using UnityEngine.Serialization;

namespace BikexsGame
{
    /**
     * 关卡事件
     */
    [Serializable]
    public class LevelEvent
    {
        
        public int id;
        public string title;
        public string content;
        [JsonIgnore] public Sprite imageSprite;
        public string image;
        public string levelEventType;
        [JsonIgnore] public LevelActionEventType levelEventTypeEnum;
        public bool isBlockEvent = false;
        public bool isBossEvent = false;
        public int weight = 100;
        
        // 事件参数，事件管理器根据
        [JsonIgnore] private object _param;
        // 所属章节,节，层（用于构建对应节点时进行抽取）
        public int chapter = 1;
        public int section = 1;
        public int layer = 1;

        public List<string> eventActionTypeList = new List<string>();
        [JsonIgnore]
        public List<EventActionType> eventActionTypeEnumList = new List<EventActionType>();
        
        public object Param
        {
            get { return _param; }
            set { _param = value; }
        }

        public int ID
        {
            get => id;
            set => id = value;
        }

        public string Title
        {
            get => title;
            set => title = value;
        }

        public string Content
        {
            get => content;
            set => content = value;
        }

        public Sprite ImageSprite
        {
            get => imageSprite;
            set => imageSprite = value;
        }

        public string Image
        {
            get { return image; }
            set => image = value;
        }

        public string LevelEventType
        {
            get => levelEventType;
            set => levelEventType = value;
        }

        public LevelActionEventType LevelEventTypeEnum
        {
            get => levelEventTypeEnum;
            set => levelEventTypeEnum = value;
        }

        public bool IsBlockEvent
        {
            get => isBlockEvent;
            set => isBlockEvent = value;
        }

        public bool IsBossEvent
        {
            get => isBossEvent;
            set => isBossEvent = value;
        }

        public int Weight
        {
            get => weight;
            set => weight = value;
        }

        public int Chapter
        {
            get => chapter;
            set => chapter = value;
        }

        public int Section
        {
            get => section;
            set => section = value;
        }

        public int Layer
        {
            get => layer;
            set => layer = value;
        }

        public List<string> EventActionTypeList
        {
            get => eventActionTypeList;
            set => eventActionTypeList = value;
        }

        public List<EventActionType> EventActionTypeEnumList
        {
            get => eventActionTypeEnumList;
            set => eventActionTypeEnumList = value;
        }
    }

    public enum LevelActionEventType
    {
        None,
        // 战斗，进入战斗（战斗系统的功能）
        Battle,
        // ...部分具体内容省略
        // 自定义事件，比如
        Custom,
    }
}