我要评论因为支持csv,所以就一块写上了
workbook,worksheet using aspose.cells(第三方)
把excel读取到属性对象列表,需要传入对象类型和文件路径.
例:
list<propsetcurrency> currencylist = this.getobjectlist<propsetcurrency>(filepath);
注:excel的表头需要和对象名对应(可无序),且第一列不能为空
把属性对象列表保存到excel,需要传入对象列表和保存的文件完整路径.
例:
this.setexcellist(currencylist,"c://currencylist.excel");
/// <summary>
/// 从excel获取数据
/// </summary>
/// <typeparam name="t">对象</typeparam>
/// <param name="filepath">文件完整路径</param>
/// <returns>对象列表</returns>
public list<t> getobjectlist<t>(string filepath) where t : new()
{
list<t> list = new list<t>();
if (!filepath.trim().endswith("csv") && !filepath.trim().endswith("xlsx"))
{
return list;
}
type type = typeof(t);
workbook workbook = new workbook(filepath);
worksheet sheet = workbook.worksheets[0];
// 获取标题列表
var titledic = this.gettitledic(sheet);
// 循环每行数据
for (int i = 1; i < int.maxvalue; i++)
{
// 行为空时结束
if (string.isnullorempty(sheet.cells[i, 0].stringvalue))
{
break;
}
t instance = new t();
// 循环赋值每个属性
foreach (var item in type.getproperties())
{
if (titledic.containskey(item.name))
{
string str = sheet.cells[i, titledic[item.name]].stringvalue;
if (!string.isnullorempty(str))
{
try
{
// 根据类型进行转换赋值
if (item.propertytype == typeof(string))
{
item.setvalue(instance, str);
}
else if (item.propertytype.isenum)
{
item.setvalue(instance, int.parse(str));
}
else
{
methodinfo method = item.propertytype.getmethod("parse", new type[] { typeof(string) });
object obj = null;
if (method != null)
{
obj = method.invoke(null, new object[] { str });
item.setvalue(instance, obj);
}
}
}
catch (exception)
{
// 获取错误
}
}
}
}
list.add(instance);
}
return list;
}
/// <summary>
/// 把对象list保存到excel
/// </summary>
/// <typeparam name="t">对象</typeparam>
/// <param name="objlist">对象列表</param>
/// <param name="savefilepath">保存文件的完整路径,包括文件类型</param>
public void setexcellist<t>(list<t> objlist, string savefilepath)
{
if (!savefilepath.trim().endswith("csv") && !savefilepath.trim().endswith("xlsx"))
{
return;
}
workbook workbook = new workbook();
worksheet sheet = workbook.worksheets[0];
// 冻结第一行
sheet.freezepanes(1, 1, 1, 0);
// 循环插入每行
int row = 0;
foreach (var obj in objlist)
{
int column = 0;
var properties = obj.gettype().getproperties(bindingflags.instance | bindingflags.public | bindingflags.ignorecase | bindingflags.declaredonly);
if (row == 0)
{
foreach (var titname in properties)
{
sheet.cells[0, column].putvalue(titname.name);
column++;
}
row++;
}
// 循环插入当前行的每列
column = 0;
foreach (var property in properties)
{
var itemvalue = property.getvalue(obj);
sheet.cells[row, column].putvalue(itemvalue.tostring());
column++;
}
row++;
}
workbook.save(savefilepath);
}
/// <summary>
/// 获取标题行数据
/// </summary>
/// <param name="sheet">sheet</param>
/// <returns>标题行数据</returns>
private dictionary<string, int> gettitledic(worksheet sheet)
{
dictionary<string, int> titlist = new dictionary<string, int>();
for (int i = 0; i < int.maxvalue; i++)
{
if (sheet.cells[0, i].stringvalue == string.empty)
{
return titlist;
}
titlist.add(sheet.cells[0, i].stringvalue, i);
}
return titlist;
}
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论