创建execl导入工具类的步骤
1、创建实体属性标记
public class cellattribute : attribute
{
/// <summary>
///
/// </summary>
/// <param name="displayname">显示名称</param>
/// <param name="hander"></param>
public cellattribute(string displayname, type hander = null)
{
displayname = displayname;
hander = hander;
}
/// <summary>
/// 显示名称
/// </summary>
public string displayname { get; set; }
/// <summary>
/// 类型
/// </summary>
public type hander { get; set; }
}
2、创建通用处理方法
public class xlsfilehandler<t> where t : new()
{
private readonly string _path;
private readonly dictionary<string, cellattribute> _cellattributes;
readonly dictionary<string, string> _propdictionary;
public xlsfilehandler(string path)
{
_path = path;
_cellattributes = new dictionary<string, cellattribute>();
_propdictionary = new dictionary<string, string>();
createmappers();
}
/// <summary>
/// 创建映射
/// </summary>
private void createmappers()
{
foreach (var prop in typeof(t).getproperties())
{
foreach (cellattribute cellmapper in prop.getcustomattributes(false).oftype<cellattribute>())
{
_propdictionary.add(cellmapper.displayname, prop.name);
_cellattributes.add(cellmapper.displayname, cellmapper);
}
}
}
/// <summary>
/// 获取整个xls文件对应行的t对象
/// </summary>
/// <returns></returns>
public list<t> todata()
{
list<t> datalist = new list<t>();
using (filestream stream = getstream())
{
iworkbook workbook = new hssfworkbook(stream);
isheet sheet = workbook.getsheetat(0);
var rows = sheet.getrowenumerator();
int lastcell = 0;
int i = 0;
irow headrow = null;
while (rows.movenext())
{
var row = sheet.getrow(i);
if (i == 0)
{
headrow = sheet.getrow(0);
lastcell = row.lastcellnum;
}
else
{
t t = getdata(workbook, headrow, row, lastcell);
datalist.add(t);
}
i++;
}
stream.close();
}
return datalist;
}
/// <summary>
/// 获取t对象
/// </summary>
/// <param name="workbook"></param>
/// <param name="headrow"></param>
/// <param name="currentrow"></param>
/// <param name="lastcell"></param>
/// <returns></returns>
private t getdata(iworkbook workbook, irow headrow, irow currentrow, int lastcell)
{
t t = new t();
for (int j = 0; j < lastcell; j++)
{
var displayname = headrow.cells[j].stringcellvalue;
if (!_cellattributes.containskey(displayname) || !_propdictionary.containskey(displayname))
{
continue;
}
var currentattr = _cellattributes[displayname];
var propname = _propdictionary[displayname];
icell currentcell = currentrow.getcell(j);
string value = currentcell != null ? getcellvalue(workbook, currentcell) : "";
if (currentattr.hander != null)
{
setvalue(ref t, propname, invokehandler(currentattr.hander, value));
}
else
{
setvalue(ref t, propname, value);
}
}
return t;
}
/// <summary>
/// 动态执行处理方法
/// </summary>
/// <param name="type"></param>
/// <param name="value"></param>
/// <returns></returns>
private static object invokehandler(type type, object value)
{
system.reflection.constructorinfo constructor = type.getconstructor(type.emptytypes);
if (constructor == null) throw new argumentnullexception("type");
object mgconstructor = constructor.invoke(null);
system.reflection.methodinfo method = type.getmethod("getresults");
return method.invoke(mgconstructor, new[] { value });
}
/// <summary>
/// 获取文件流
/// </summary>
/// <returns></returns>
private filestream getstream()
{
if (!file.exists(_path)) throw new filenotfoundexception("path");
return new filestream(_path, filemode.open, fileaccess.read, fileshare.read);
}
/// <summary>
/// 获取xls文件单元格的值
/// </summary>
/// <param name="workbook"></param>
/// <param name="cell"></param>
/// <returns></returns>
private static string getcellvalue(iworkbook workbook, icell cell)
{
string value;
switch (cell.celltype)
{
case celltype.formula:
hssfformulaevaluator evaluator = new hssfformulaevaluator(workbook);
value = evaluator.evaluate(cell).formatasstring();
break;
default:
value = cell.tostring();
break;
}
return value;
}
/// <summary>
/// 设置t属性值
/// </summary>
/// <param name="t"></param>
/// <param name="propname"></param>
/// <param name="value"></param>
private static void setvalue(ref t t, string propname, object value)
{
var typename = t.gettype().getproperty(propname).propertytype.name;
var property = t.gettype().getproperty(propname);
switch (typename)
{
case "int32":
property.setvalue(t, convert.toint32(value), null);
break;
case "datetime":
property.setvalue(t, convert.todatetime(value), null);
break;
case "decimal":
property.setvalue(t, convert.todecimal(value), null);
break;
default:
property.setvalue(t, value, null);
break;
}
}
}
3、创建execl文件映射类
public class readmapper
{
[cellattribute("测试1")]
public decimal code { get; set; }
[cellattribute("测试2")]
public int name { get; set; }
[cellattribute("测试3", typeof(classcellhander))]
public string group { get; set; }
[cellattribute("测试4")]
public datetime addtime { get; set; }
}
4、指定execl文件路径，通过通用处理方法导出映射实体
[test]
public void read1()
{
const string filepath = @"c:\users\zk\desktop\1.xls";
xlsfilehandler<readmapper> handler = new xlsfilehandler<readmapper>(filepath);
list<readmapper> readmappers = handler.todata();
assert.areequal(readmappers.count, 3);
}