创建execl导入工具类的步骤

1、创建实体属性标记


代码如下:

public class CellAttribute : Attribute
    {
        /// <summary>
        ///
        /// </summary>
        /// <param name="displayName">显示名称</param>
        /// <param name="hander"></param>
        public CellAttribute(string displayName, Type hander = null)
        {
            DisplayName = displayName;

Hander = hander;
        }

/// <summary>
        /// 显示名称
        /// </summary>
        public string DisplayName { get; set; }

/// <summary>
        /// 类型
        /// </summary>
        public Type Hander { get; set; }
    }

2、创建通用处理方法


代码如下:

public class XlsFileHandler<T> where T : new()
    {
        private readonly string _path;
        private readonly Dictionary<string, CellAttribute> _cellAttributes;
        readonly Dictionary<string, string> _propDictionary;

public XlsFileHandler(string path)
        {
            _path = path;
            _cellAttributes = new Dictionary<string, CellAttribute>();
            _propDictionary = new Dictionary<string, string>();
            CreateMappers();
        }

/// <summary>
        /// 创建映射
        /// </summary>
        private void CreateMappers()
        {
            foreach (var prop in typeof(T).GetProperties())
            {
                foreach (CellAttribute cellMapper in prop.GetCustomAttributes(false).OfType<CellAttribute>())
                {
                    _propDictionary.Add(cellMapper.DisplayName, prop.Name);
                    _cellAttributes.Add(cellMapper.DisplayName, cellMapper);
                }
            }
        }

/// <summary>
        /// 获取整个xls文件对应行的T对象
        /// </summary>
        /// <returns></returns>
        public List<T> ToData()
        {
            List<T> dataList = new List<T>();
            using (FileStream stream = GetStream())
            {
                IWorkbook workbook = new HSSFWorkbook(stream);
                ISheet sheet = workbook.GetSheetAt(0);
                var rows = sheet.GetRowEnumerator();
                int lastCell = 0;
                int i = 0;
                IRow headRow = null;
                while (rows.MoveNext())
                {
                    var row = sheet.GetRow(i);
                    if (i == 0)
                    {
                        headRow = sheet.GetRow(0);
                        lastCell = row.LastCellNum;
                    }
                    else
                    {
                        T t = GetData(workbook, headRow, row, lastCell);
                        dataList.Add(t);
                    }
                    i++;
                }
                stream.Close();
            }
            return dataList;
        }

/// <summary>
        /// 获取T对象
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="headRow"></param>
        /// <param name="currentRow"></param>
        /// <param name="lastCell"></param>
        /// <returns></returns>
        private T GetData(IWorkbook workbook, IRow headRow, IRow currentRow, int lastCell)
        {
            T t = new T();
            for (int j = 0; j < lastCell; j++)
            {
                var displayName = headRow.Cells[j].StringCellValue;
                if (!_cellAttributes.ContainsKey(displayName) || !_propDictionary.ContainsKey(displayName))
                {
                    continue;
                }
                var currentAttr = _cellAttributes[displayName];
                var propName = _propDictionary[displayName];

ICell currentCell = currentRow.GetCell(j);
                string value = currentCell != null ? GetCellValue(workbook, currentCell) : "";
                if (currentAttr.Hander != null)
                {
                    SetValue(ref t, propName, InvokeHandler(currentAttr.Hander, value));
                }
                else
                {
                    SetValue(ref t, propName, value);
                }
            }
            return t;
        }

/// <summary>
        /// 动态执行处理方法
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static object InvokeHandler(Type type, object value)
        {
            System.Reflection.ConstructorInfo constructor = type.GetConstructor(Type.EmptyTypes);
            if (constructor == null) throw new ArgumentNullException("type");
            object mgConstructor = constructor.Invoke(null);
            System.Reflection.MethodInfo method = type.GetMethod("GetResults");
            return method.Invoke(mgConstructor, new[] { value });
        }

/// <summary>
        /// 获取文件流
        /// </summary>
        /// <returns></returns>
        private FileStream GetStream()
        {
            if (!File.Exists(_path)) throw new FileNotFoundException("path");
            return new FileStream(_path, FileMode.Open, FileAccess.Read, FileShare.Read);
        }

/// <summary>
        /// 获取xls文件单元格的值
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(IWorkbook workbook, ICell cell)
        {
            string value;
            switch (cell.CellType)
            {
                case CellType.FORMULA:
                    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
                    value = evaluator.Evaluate(cell).FormatAsString();
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }

/// <summary>
        /// 设置T属性值
        /// </summary>
        /// <param name="t"></param>
        /// <param name="propName"></param>
        /// <param name="value"></param>
        private static void SetValue(ref T t, string propName, object value)
        {
            var typeName = t.GetType().GetProperty(propName).PropertyType.Name;
            var property = t.GetType().GetProperty(propName);
            switch (typeName)
            {
                case "Int32":
                    property.SetValue(t, Convert.ToInt32(value), null);
                    break;
                case "DateTime":
                    property.SetValue(t, Convert.ToDateTime(value), null);
                    break;
                case "Decimal":
                    property.SetValue(t, Convert.ToDecimal(value), null);
                    break;
                default:
                    property.SetValue(t, value, null);
                    break;
            }
        }
    }

3、创建Execl文件映射类


代码如下:

public class ReadMapper
    {
        [CellAttribute("测试1")]
        public decimal Code { get; set; }

[CellAttribute("测试2")]
        public int Name { get; set; }

[CellAttribute("测试3", typeof(ClassCellHander))]
        public string Group { get; set; }

[CellAttribute("测试4")]
        public DateTime AddTime { get; set; }
    }

4、指定Execl文件路径，通过通用处理方法导出映射实体

代码如下:

[Test]
        public void Read1()
        {
            const string filePath = @"C:\Users\zk\Desktop\1.xls";
            XlsFileHandler<ReadMapper> handler = new XlsFileHandler<ReadMapper>(filePath);
            List<ReadMapper> readMappers = handler.ToData();
            Assert.AreEqual(readMappers.Count, 3);
        }