我是一名 ASP.NET 程序员,专注于 B/S 项目开发。累计文章阅读量超过一千万,我的博客主页地址:https://www.itsvse.com/blog_xzz.html
网上有很多关于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();
- }
最后,就出现了文章最开始的效果图。
转载于:https://down.itsvse.com/k/0tmhog.html