winform 读取excel 数据显示到窗体中_在.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    {        ///         /// 读取excel转换成list集合        ///         /// 对象        /// 文件流        /// 从第几行开始读取        /// 读取第几个sheet        ///         public static IList GetList(Stream stream, int startIndex, int sheetIndex = 0)            where T : class        {            IList ts = new List();            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();                            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(fs, 3);            var json1 = Newtonsoft.Json.JsonConvert.SerializeObject(temp.Where(x => !string.IsNullOrWhiteSpace(x.Name)).ToList());            Console.WriteLine(json1);            Console.WriteLine("ok");            Console.ReadKey();        }

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