使用特性/泛型导入Excel(NPOI,Aspose)

之前一直在使用Excel导入的方式进行数据的批量录入,之前是将数据转化为DatTable,然后通过遍历Rows获取数据,有时候为了能拿到指定的集合类型,还要新建集合数据存储数据,而且不通用。每次有新的文件导入的时候就要单独处理，代码复用性低，重复开发。

这篇文章是直接通过泛型数据,通过导入特性映射直接将excel文件数据转化出来,读到就是所要的集合数据,很是方便,而且通用性很强,安利安利!!!

上代码

/// <summary>
/// excel读取
/// </summary>
/// <param name="path">excel文件路径</param>
/// <param name="isNPOI">是否为NPOI方式</param>
/// <param name="startRow">从第几行开始读取</param>
/// <param name="loadSheetIndex">所加载的sheet序号</param>
/// <returns></returns>
public static List<T> ReadExcel<T>(string path, bool isNPOI = true, int startRow = 1,int loadSheetIndex = 0) where T : class, new()
        {
            var t = new T();             //实例化泛型类
            var list = new List<T>();    //返回列表接受读取数据;
            ISheet sheet;
            var attrs = AttributeExtension.GetExcelImporterAttribute(t).Where(s => s.Ignore == false || s.IsSummaryTitle == false);  //根据类获取特性集合
            var attrFields = attrs.Select(s => s.Field).ToArray();
            var summarytitleList = AttributeExtension.GetExcelImporterAttribute(t).Where(_ => _.IsSummaryTitle).ToList(); // 标题字段集合
            var extension = Path.GetExtension(path);
            using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                stream.Position = 0;
                if (isNPOI)
                {
                    #region NPOI读取Excel
                    IWorkbook workbook = null;
                    if (extension == ".xls")
                        workbook = new HSSFWorkbook(stream);
                    else
                        workbook = new XSSFWorkbook(stream);

                    sheet = workbook.GetSheetAt(loadSheetIndex);
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue;
                        if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
                        T obj = new T();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            var field = attrFields[j];
                            var cellValue = row.GetCell(j);
                            if (cellValue != null)
                            {
                                SetValue(field, cellValue, obj);
                            }
                        }
                        list.Add(obj);
                    }
                    if (workbook != null)
                    {
                        workbook.Close();
                        workbook = null;
                    }
                    #endregion
                }
                else
                {
                    #region Aspose 读取Excel
                    Workbook workbookAspose = new Workbook(path);
                    Cells cells = workbookAspose.Worksheets[loadSheetIndex].Cells;
                    //从1开始，第一行为标题行
                    try
                    {
                        for (int i = startRow; i < cells.MaxDataRow + 1; i++)
                        {
                            var row = cells.GetRow(i);
                            if (row == null) continue;
                            T obj = new T();
                            for (int j = 0; j < cells.MaxDataColumn + 1; j++)
                            {
                                var field = attrFields[j];
                                var cellValue = row[j].StringValue?.Trim();
                                if (cellValue != null)
                                {
                                    SetValue(field, cellValue.Replace("\n", " "), obj);
                                }
                            }

                            // 给标题行字段赋值
                            foreach (var item in summarytitleList)
                            {
                                var innerRow = cells.GetRow(item.InRow);
                                if (innerRow == null) continue;
                                var cellValue = innerRow[item.InCells].StringValue.Trim();
                                if (cellValue == null) continue;
                                SetValue(item.Field, cellValue, obj);
                            }

                            list.Add(obj);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    #endregion
                }
                if (stream != null)
                {
                    stream.Close();
                    stream.Dispose();
                }
            }
            return list;
        }

这是上面读取excel方法中需要使用的数据赋值的方法

/// <summary>
/// 类型转换
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="field">列名</param>
/// <param name="cellValue">读取到的单元格内容</param>
/// <param name="t"></param>
 public static void SetValue<T>(string field, object cellValue, T t)
        {
            var prop = t.GetType().GetProperty(field).PropertyType;
            var typeName = prop.Name;

            switch (typeName)
            {
                case nameof(System.String):                       //string
                    var stringValue = ConvertExtension.ToString(cellValue).Trim(new char[2] { '\'', '‘' });
                    t.GetType().GetProperty(field)?.SetValue(t, stringValue);
                    break;
                case "Nullable`1":                       //int
                    SetNullableValue(prop.GenericTypeArguments[0].Name, field, cellValue, t);
                    break;
                case nameof(System.Guid):                       //int
                    var gValue = ConvertExtension.ToGuid(cellValue);
                    t.GetType().GetProperty(field)?.SetValue(t, gValue);
                    break;
                case nameof(System.Int32):                       //int
                    var intValue = ConvertExtension.ToInt(cellValue);
                    t.GetType().GetProperty(field)?.SetValue(t, intValue);
                    break;
                case nameof(System.Decimal):                     //int
                    var decimalValue = ConvertExtension.ToDecimal(cellValue);
                    t.GetType().GetProperty(field)?.SetValue(t, decimalValue);
                    break;
                case nameof(System.DateTime):                   //int
                    var datetimelValue = ConvertExtension.ToDateTime(cellValue);
                    t.GetType().GetProperty(field)?.SetValue(t, datetimelValue);
                    break;
                
                default:
                    break;
            }
        }

ToolEquExcelModel是作为导入的一个演示的实体类，这个类里面需要使用 [ExcelProperity]特性，关于这个特性的定义后面会有代码

 public class ToolEquExcelModel
    {
        /// <summary>
        /// 描    述:资产编号
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string tool_no { get; set; }


        /// <summary>
        /// 描    述:工装类型
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public ToolTypeEnum tool_type { get; set; }

        /// <summary>
        /// 描    述:物料号
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string item_code { get; set; }

        /// <summary>
        /// 描    述:描述
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string item_desc { get; set; }

        /// <summary>
        /// 描    述:工装状态
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public ToolStatusEnum status { get; set; }

        /// <summary>
        /// 描    述:涉及产品
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string production { get; set; }

        /// <summary>
        /// 描    述:存储地点
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string storage { get; set; }

        /// <summary>
        /// 描    述:货架位置
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string shelf { get; set; }

        /// <summary>
        /// 描    述:入库时间
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public DateTime? in_date { get; set; }

        /// <summary>
        /// 描    述:工装所属公司
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public string belong { get; set; }


        /// <summary>
        /// 描    述:是否启用领用校验
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public bool is_use_check { get; set; }

        /// <summary>
        /// 描    述:是否启用归还校验
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public bool is_back_check { get; set; }

        /// <summary>
        /// 描    述:是否删除
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public bool is_delete { get; set; }

        /// <summary>
        /// 描    述:是否生效
        /// 默 认 值:
        /// 是否空值:True
        /// </summary>
        [ExcelProperity]
        public bool? is_valid { get; set; }


    }

这是实体类需要使用的特性定义

[AttributeUsage(AttributeTargets.Property)]
    public class ExcelProperityAttribute : Attribute
    {
        /// <summary>
        /// 数据行，非Excelindex
        /// </summary>
        public int InRow { get; set; }

        /// <summary>
        /// 数据列，非Excelindex
        /// </summary>
        public int InCells { get; set; }

        /// <summary>
        /// 是否为主标题数据
        /// </summary>
        public bool IsSummaryTitle { get; set; }

        /// <summary>
        /// 是否忽略
        /// </summary>
        public bool Ignore { get; set; } = false;

        /// <summary>
        /// 字段
        /// </summary>
        public string Field { get; set; }

        /// <summary>
        /// Excel 表头名
        /// </summary>
        public string Title { get; set; }
    }

最后就是方法的调用了,这里是使用了上面定义的ToolEquExcelModel实体作为验证,最后返回的就是这个类的集合,很是方便

这里直接通过泛型调用,得到的即为所需要的实体,实体的顺序和表格中列的书序请保持一直

调用
var dt = ReadExcel<ToolEquExcelModel>(path, false, 1);