在.NET Core 使用NPOI读取Excel数据映射到List集合中

网上有很多关于npoi读取excel表格的例子，很多都是返回一个Datatable的对象，但是我需要的是一个list集合，这里就需要把Datatable转成自己需要的List集合，所以，我封装了一个方法，传入class对象就能返回相应的list对象。

首先先看效果图，如下：

模板

一共有4列，有很多行，其中只有2行有数据，如下图：

特性

首先，定义一个特性，意义是对象的属性对应表格的哪一列，代码如下：

public class ColumnAttribute: Attribute

    {

        public ColumnAttribute(int index)

        {

            Index = index;

        }

        public int Index { get; set; }

    }

　　

对象模型

将表格数据读取出来，转换成相应的对象集合，在对象的属性标注上面定义的特性，代码如下：

public class TestModel

    {

        [Column(0)]

        public string Name { get; set; }

        [Column(1)]

        public string Url { get; set; }

        [Column(2)]

        public string Date { get; set; }

        [Column(3)]

        public string Remark { get; set; }

    }

封装的方法

nuget安装npoi：

Install-Package DotNetCore.NPOI -Version 1.2.2

代码如下：

public class ExcelHelper

    {

        /// <summary>

        /// 读取excel转换成list集合

        /// </summary>

        /// <typeparam name="T">对象</typeparam>

        /// <param name="stream">文件流</param>

        /// <param name="startIndex">从第几行开始读取</param>

        /// <param name="sheetIndex">读取第几个sheet</param>

        /// <returns></returns>

        public static IList<T> GetList<T>(Stream stream, int startIndex, int sheetIndex = 0)

            where T : class

        {

            IList<T> ts = new List<T>();

            try

            {

                IWorkbook workbook = WorkbookFactory.Create(stream);

                var sheet = workbook.GetSheetAt(sheetIndex);

                if (sheet != null)

                {

                    IRow firstRow = sheet.GetRow(0);

                    //一行最后一个cell的编号 即总的列数

                    int cellCount = firstRow.LastCellNum;

                    //最后一列的标号

                    int rowCount = sheet.LastRowNum;

                    for (int i = startIndex; i <= rowCount; ++i)

                    {

                        //获取行的数据

                        IRow row = sheet.GetRow(i);

                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        {

                            T model = Activator.CreateInstance<T>();

                            for (int j = row.FirstCellNum; j < cellCount; ++j)

                            {

                                if (row.GetCell(j) != null)

                                {

                                    var rowTemp = row.GetCell(j);

                                    string value = null;

                                    if (rowTemp.CellType == CellType.Numeric)

                                    {

                                        short format = rowTemp.CellStyle.DataFormat;

                                        if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20)

                                            value = rowTemp.DateCellValue.ToString("yyyy-MM-dd");

                                        else

                                            value = rowTemp.NumericCellValue.ToString();

                                    }

                                    else

                                        value = rowTemp.ToString();

                                    //赋值

                                    foreach (System.Reflection.PropertyInfo item in typeof(T).GetProperties())

                                    {

                                        var column = item.GetCustomAttributes(true).First(x => x is ColumnAttribute) as ColumnAttribute;

                                        if (column.Index == j)

                                        {

                                            item.SetValue(model, value);

                                            break;

                                        }

                                    }

                                }

                            }

                            ts.Add(model);

                        }

                    }

                }

            }

            catch (Exception)

            {

                throw;

            }

            finally

            {

                if (stream != null) stream.Close();

            }

            return ts;

        }

    }

调用代码：

static void Main(string[] args)

        {

 

            FileStream fs = new FileStream(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + "/test.xlsx", FileMode.Open, FileAccess.Read);

            var temp = ExcelHelper.GetList<TestModel>(fs, 3);

            var json1 = Newtonsoft.Json.JsonConvert.SerializeObject(temp.Where(x => !string.IsNullOrWhiteSpace(x.Name)).ToList());

            Console.WriteLine(json1);

            Console.WriteLine("ok");

            Console.ReadKey();

        }

　　最后，就出现了文章最开始的效果图。