c#读写excel文件使用示例

因为支持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;
}