NPOI读取Excel到集合对象

之前做过的项目中有个需要读取Excel文件内容的需求，因此使用NPOI实现，写下以下代码，这个只是一个代码段，还有很多地方需要优化，希望能对大家有所帮助

        public static IList<T> ReadListFromStream<T>(string fileName, Stream stream, bool ignoreFirstLine)
            where T : new()
        {
            string extendsion = Path.GetExtension(fileName).TrimStart('.');

            IWorkbook workBook = null;
            switch (extendsion)
            {
                case "xls":
                    workBook = new HSSFWorkbook(stream);
                    break;
                case "xlsx":
                    workBook = new XSSFWorkbook(stream);
                    break;
            }

            if (workBook == null || workBook.Count <= 0) { throw new NPOIException("Excel表格工作簿为空"); }

            IList<T> list = new List<T>();
            for (int i = 0; i < workBook.Count; i++)
            {
                ISheet sheet = workBook.GetSheetAt(i);

                if (sheet.PhysicalNumberOfRows > 0)
                {
                    if (!ignoreFirstLine)
                    {
                        //检查列是否与ExcelAttribute定义的一致
                        ValidTableHeader<T>(sheet);
                    }

                    for (int j = ignoreFirstLine ? 0 : 1; j < sheet.PhysicalNumberOfRows; j++)
                    {
                        var row = sheet.GetRow(j);

                        T entity = new T();

                        var propertys = typeof(T).GetProperties();

                        foreach (var p in propertys)
                        {
                            var excel = Attribute.GetCustomAttribute(p, typeof(ExcelAttribute)) as ExcelAttribute;

                            if (excel != null)
                            {
                                var cellValue = row.GetCell(excel.ColumnIndex);

                                if (cellValue == null || string.IsNullOrEmpty(cellValue.ToString()))
                                    throw new NPOIException(string.Format("第{0}行“{1}”不能为空", j + 1, excel.ColumnName));

                                string cellValueStr = cellValue.ToString();
                                if (p.PropertyType == typeof(int))
                                {
                                    int temp;
                                    if (!int.TryParse(cellValueStr, out temp))
                                        throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "整数"));
                                    p.SetValue(entity, temp, null);
                                }
                                else if (p.PropertyType == typeof(DateTime))
                                {
                                    DateTime temp;
                                    if (!DateTime.TryParse(cellValueStr, out temp))
                                        throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "时间"));
                                    p.SetValue(entity, temp, null);
                                }
                                else if (p.PropertyType == typeof(bool))
                                {
                                    bool temp;
                                    if (!bool.TryParse(cellValueStr, out temp))
                                        throw new NPOIException(string.Format("第{0}行“{1}”应为{2}类型", j + 1, excel.ColumnName, "布尔"));
                                    p.SetValue(entity, cellValueStr, null);
                                }
                                else if (p.PropertyType == typeof(string))
                                {
                                    p.SetValue(entity, cellValueStr, null);
                                }
                                else
                                {
                                    throw new NPOIException(string.Format("第{0}行“{1}”类型未知，请联系开发人员", j + 1, excel.ColumnName));
                                }
                            }
                        }
                        list.Add(entity);
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 检查表头与定义是否匹配
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="firstRow"></param>
        /// <returns></returns>
        private static void ValidTableHeader<T>(ISheet sheet) where T : new()
        {
            var firstRow = sheet.GetRow(0);

            var propertys = typeof(T).GetProperties();

            foreach (var p in propertys)
            {
                var excel = Attribute.GetCustomAttribute(p, typeof(ExcelAttribute)) as ExcelAttribute;

                if (excel != null)
                {
                    if (!firstRow.GetCell(excel.ColumnIndex).StringCellValue.Trim().Equals(excel.ColumnName))
                    {
                        throw new NPOIException(string.Format("Excel表格第{0}列标题应为{1}", excel.ColumnIndex + 1, excel.ColumnName));
                    }
                }
            }
        }

ExcelAttribute是自定义的一个特性类，主要在实体属性上标记，以确定该属性对应于Excel中的列名，列的索引

[AttributeUsage(AttributeTargets.Property)]
    public class ExcelAttribute : Attribute
    {
        private string _columnName;

        public string ColumnName
        {
            get { return _columnName; }
            set { _columnName = value; }
        }
        private int _columnIndex;

        public int ColumnIndex
        {
            get { return _columnIndex; }
            set { _columnIndex = value; }
        }

        public ExcelAttribute(string columnName)
        {
            this._columnName = columnName;
        }

        public ExcelAttribute(string columnName, int columnIndex)
        {
            this._columnName = columnName;
            this._columnIndex = columnIndex;
        }
    }

 

转载于:https://www.cnblogs.com/efenghuo/p/4089607.html