NET Excel转换为集合对象

1.仅适用于规则excel：表头和数据一一对应

2.涉及到excel转换为集合对象的部分代码，完整npoi帮助类点击查看

        /// <summary>
/// 默认把excel第一个sheet中的数据转换为对象集合
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="entity"></param>
/// <param name="filepath">文件路径</param>
/// <param name="sheetindex">数据所在sheet索引：默认第一个sheet</param>
/// <param name="originindex">数据开始行：表头行索引</param>
/// <returns></returns>
public static list<t> convertexceltolist<t>(t entity, string filepath, int sheetindex = 0, int originindex = 0)
where t : class, new()
{
var stream = new filestream(filepath, filemode.open, fileaccess.read);
using (stream)
{
return convertexceltolist(entity, filepath, stream, sheetindex, originindex);
}
}
/// <summary>
/// 把excel转换为对象集合
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="entity"></param>
/// <param name="filepath">文件路径</param>
/// <param name="stream">文件流</param>
/// <param name="sheetindex">数据所在sheet索引：默认第一个sheet</param>
/// <param name="originindex">数据开始行：表头行索引</param>
/// <returns></returns>
public static list<t> convertexceltolist<t>(t entity, string filepath, stream stream, int sheetindex = 0, int originindex = 0)
where t : class, new()
{
//  结果集合
var list = new list<t>();
//  获取特性和属性的关系字典
dictionary<string, string> propertydictionary = getpropertydictionary(entity);
var isexcel2007 = filepath.isexcel2007();
var workbook = stream.getworkbook(isexcel2007);
//  获得数据所在sheet对象
var sheet = workbook.getsheetat(sheetindex);
//  获取表头和所在索引的关系字典
dictionary<string, int> headerdictionary = getheaderdictionary(originindex, propertydictionary, sheet);
//  两个字典对象，只有一个为空，则return
if (!propertydictionary.any() || !headerdictionary.any())
{
return list;
}
//  生成结果集合
builderresultlist(originindex, list, propertydictionary, sheet, headerdictionary);
return list;
}
/// <summary>
/// 生成结果集合
/// </summary>
/// <typeparam name="t"></typeparam>
/// <param name="originindex">数据开始行：表头行索引</param>
/// <param name="list">结果集合</param>
/// <param name="propertydictionary">特性和属性的关系字典:属性字典</param>
/// <param name="sheet">数据所在sheet对象</param>
/// <param name="headerdictionary">表头和所在索引的关系字典:表头字典</param>
private static void builderresultlist<t>(int originindex, list<t> list, dictionary<string, string> propertydictionary, isheet sheet, dictionary<string, int> headerdictionary) where t : class, new()
{
#region 通过反射，绑定参数
//  从表头行下一行开始循环，直到最后一行
for (int rowindex = originindex + 1; rowindex <= sheet.lastrownum; rowindex++)
{
t newentity = new t();
var newentitytype = newentity.gettype();
var itemrow = sheet.getrow(rowindex);
//  循环表头字典
foreach (var itemkey in headerdictionary.keys)
{
//  得到先对应的表头列所在列索引
var columnindex = headerdictionary[itemkey];
//  把格式转换为utf-8
var itemcellvalue = itemrow.getvalue(columnindex).formatutf8string();
//  根据表头值，从 属性字典 中获得 属性值 名
var propertyname = propertydictionary[itemkey];
newentitytype.getproperty(propertyname).setvalue(newentity, itemcellvalue);
}
list.add(newentity);
}
#endregion
}
/// <summary>
/// 获取表头和所在索引的关系字典
/// </summary>
/// <param name="originindex">数据开始行：表头行索引</param>
/// <param name="propertydictionary">特性和属性的关系字典:属性字典</param>
/// <param name="sheet">数据所在sheet对象</param>
/// <returns></returns>
private static dictionary<string, int> getheaderdictionary(int originindex, dictionary<string, string> propertydictionary, isheet sheet)
{
var headerdictionary = new dictionary<string, int>();
#region 获取表头和所在索引的关系字典
//  获得表头所在row对象
var itemrow = sheet.getrow(originindex);
//  记录表头行，表头和所在索引的关系，存入字典，暂不考虑表头相同情况
headerdictionary = new dictionary<string, int>();
//  可能会存在无限列情况，设置最大列为200
var celltotal = itemrow.cells.count() > 200 ? 200 : itemrow.cells.count();
for (int columnindex = 0; columnindex < celltotal; columnindex++)
{
//  把格式转换为utf-8
var itemcellvalue = itemrow.getvalue(columnindex).formatutf8string();
//  itemcellvalue补等于空 且 不在headerdictionary中 且 在propertydictionary中
if (!itemcellvalue.isnullorwhitespace() && !headerdictionary.containskey(itemcellvalue) && propertydictionary.containskey(itemcellvalue))
{
headerdictionary.add(itemcellvalue, columnindex);
}
}
#endregion
return headerdictionary;
}
/// <summary>
/// 获取特性和属性的关系字典
/// </summary>
/// <param name="propertyarr"></param>
/// <returns></returns>
private static dictionary<string, string> getpropertydictionary<t>(t entity)
{
//  获取type
var usertype = typeof(t);
//  获取类中所有公共属性集合
var propertyarr = usertype.getproperties();
#region 获取特性和属性的关系字典
//  属性字典，保存别名和属性的对应关系
//  key：别名，特性中的值
//  value：属性名，类中的属性
var propertydictionary = new dictionary<string, string>();
foreach (var itemproperty in propertyarr)
{
//  获取属性上存在aliasattribute的数组
var customattributesarr = itemproperty.getcustomattributes(typeof(aliasattribute), true);
//  存在该特性
if (customattributesarr.any())
{
var first = (aliasattribute)customattributesarr.firstordefault();
if (!propertydictionary.containskey(first.name))
{
propertydictionary.add(first.name, itemproperty.name);
}
}
}
#endregion
return propertydictionary;
}

3.调用测试

            var path = @"c:\导入文件.xlsx";
var result = npoihelper.convertexceltolist(new userdto(), path);
assert.istrue(result.any());