unity直接从excel中读取数据

一、实现背景

唉，每个策划都有不一样的需求，这个策划需要我能够直接从excel读取数据存到全局数据管理类中，能怎么办，只能宠着！！！

同时策划还有一些另外的要求，表示excel表中的数据为"\"或“TODO"或空，则不读取，列首行为中文时整列也不读取。

源码获取：unity直接从excel中读取数据，暂存数据格式为dic<string,Object>资源-CSDN文库

二、实现过程

因为直接从excel读取到的数据没有层次，不像封装好的类，一层一层封装，所以需要一个实体类来暂时保存从excel读取到的数据，从而再转化成封装好的类数据。

本质上是通过类的反射来获取数据，从多个excel文件获取的多个sheet表，只要有表名与实体类名的映射，就能加载，同步加载所有文件并返回Dictionary<String，Object>格式的数据，string是表名，Object为List<表名对应的实体类>的装箱。

核心代码

public class ExcelImporter
{
    /// <summary>
    /// 加载Excel文件
    /// </summary>
    /// <param name="filePaths">多个文件路径</param>
    /// <param name="assembly">程序集</param>
    /// <param name="namespacePrefix">命名空间</param>
    /// <param name="sheetNameShiftDic">映射表</param>
    /// <returns></returns>
    public static Dictionary<string, System.Object> ImportExcelFiles(string[] filePaths, Assembly assembly, string namespacePrefix, Dictionary<string, string> sheetNameShiftDic)
    {
        Dictionary<string, System.Object> excelDatas = new Dictionary<string, System.Object>();
        foreach (var path in filePaths)
        {
            var excelName = Path.GetFileNameWithoutExtension(path);
            if (excelName.StartsWith("~$")) continue;
            IWorkbook book = LoadBook(path);
            Debug.Log("sheetCount:" + book.NumberOfSheets);
            for (int i = 0; i < book.NumberOfSheets; i++)
            {
                ISheet sheet = book.GetSheetAt(i);
                if (sheet == null) continue;
                if (!sheetNameShiftDic.ContainsKey(sheet.SheetName)) continue;
                string className = namespacePrefix + sheetNameShiftDic[sheet.SheetName];
                Type classType = assembly.GetType(className);
                if (classType == null)
                {
                    Debug.LogError("排除表:" + sheet.SheetName);
                    continue;
                }
                Debug.Log("加载表:" + sheet.SheetName);
                excelDatas.Add(sheet.SheetName, GetEntityListFromSheet(sheet, classType));

            }
        }
        return excelDatas;
    }

    public static IWorkbook LoadBook(string excelPath)
    {
        using (FileStream stream = File.Open(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
        {
            if (Path.GetExtension(excelPath) == ".xls") return new HSSFWorkbook(stream);
            else return new XSSFWorkbook(stream);
        }
    }

    private static List<string> GetFieldNamesFromSheetHeader(ISheet sheet)
    {
        IRow headerRow = sheet.GetRow(0);

        var fieldNames = new List<string>();
        for (int i = 0; i < headerRow.LastCellNum; i++)
        {
            var cell = headerRow.GetCell(i);
            if (cell == null || cell.CellType == CellType.Blank) break;
            fieldNames.Add(cell.StringCellValue);
        }
        return fieldNames;
    }

    static object CellToFieldObject(ICell cell, FieldInfo fieldInfo, bool isFormulaEvalute = false)
    {
        var type = isFormulaEvalute ? cell.CachedFormulaResultType : cell.CellType;

        switch (type)
        {
            case CellType.String:
                if (fieldInfo.FieldType.IsEnum) return Enum.Parse(fieldInfo.FieldType, cell.StringCellValue);
                else return cell.StringCellValue;
            case CellType.Boolean:
                return cell.BooleanCellValue;
            case CellType.Numeric:
                return Convert.ChangeType(cell.NumericCellValue, fieldInfo.FieldType);
            case CellType.Formula:
                if (isFormulaEvalute) return null;
                return CellToFieldObject(cell, fieldInfo, true);
            default:
                if (fieldInfo.FieldType.IsValueType)
                {
                    return Activator.CreateInstance(fieldInfo.FieldType);
                }
                return null;
        }
    }

    static object CreateEntityFromRow(IRow row, List<string> columnNames, Type entityType, string sheetName)
    {
        var entity = Activator.CreateInstance(entityType);

        for (int i = 0; i < columnNames.Count; i++)
        {
            FieldInfo entityField = entityType.GetField(
                columnNames[i],
                BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic
            );
            if (entityField == null) continue;
            if (!entityField.IsPublic && entityField.GetCustomAttributes(typeof(SerializeField), false).Length == 0) continue;

            ICell cell = row.GetCell(i);
            if (cell == null) continue;
            if (cell.CellType == CellType.String && cell.StringCellValue == "\\") continue;
            if (cell.CellType == CellType.String && cell.StringCellValue == "TODO") continue;
            try
            {
                object fieldValue = CellToFieldObject(cell, entityField);
                entityField.SetValue(entity, fieldValue);
            }
            catch
            {
                throw new Exception(string.Format("Invalid excel cell type at row {0}, column {1}, {2} sheet.", row.RowNum, cell.ColumnIndex, sheetName));
            }

        }
        return entity;
    }

    static object GetEntityListFromSheet(ISheet sheet, Type entityType)
    {
        List<string> excelColumnNames = GetFieldNamesFromSheetHeader(sheet);

        Type listType = typeof(List<>).MakeGenericType(entityType);
        MethodInfo listAddMethod = listType.GetMethod("Add", new Type[] { entityType });
        object list = Activator.CreateInstance(listType);

        // row of index 0 is header,row of index 1 is Chinese explaination
        for (int i = 2; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            if (row == null) break;

            ICell entryCell = row.GetCell(0);
            if (entryCell == null || entryCell.CellType == CellType.Blank) break;

            // skip comment row
            if (entryCell.CellType == CellType.String && entryCell.StringCellValue.StartsWith("#")) continue;

            var entity = CreateEntityFromRow(row, excelColumnNames, entityType, sheet.SheetName);
            listAddMethod.Invoke(list, new object[] { entity });
        }
        return list;
    }
}

三、测试过程

3.1 excel表

3.2 导入数据查看

3.3 其中一个实体类的数据转换

public class HexCellTypeInfoLoader
{
    public static Dictionary<HexCellTypeID, HexCellTypeInfo> LoadHexCellTypeInfo()
    {
        Dictionary<string, Object> datas = StaticDataLoader.StaticDatas;
        if(datas.ContainsKey("地块"))
        {
            return HexCellTypeInfoShift(datas["地块"]);
        }
        else return null;
    }

    private static Dictionary<HexCellTypeID, HexCellTypeInfo> HexCellTypeInfoShift(Object data)
    {
        List<HexCellTypeInfoEntity> entities = (List<HexCellTypeInfoEntity>)data;
        Dictionary<HexCellTypeID, HexCellTypeInfo> result = new Dictionary<HexCellTypeID, HexCellTypeInfo>();
        foreach(HexCellTypeInfoEntity entity in entities)
        {
            HexCellTypeID id = new HexCellTypeID(entity.UUID, entity.ID);
            HexCellTypeInfo info = new HexCellTypeInfo(id,entity);
            result.Add(id, info);
        }
        return result;
    }
}