ExcelMapper - 基于NPOI封装的读取、导出类库

1、Nuget引用

源码：https://github.com/mganss/ExcelMapper

NPOI

ExcelMapper

 

2、读取操作，Excel转List<T>

2.1 映射规则

 根据特定列名称进行映射

public class EMOrderDetails
{
    [Column("订单日期")]
    public DateTime? Date { get; set; }
    [Column("地区")]
    public string? Region { get; set; }
    [Column("代销人")]
    public string? Representative { get; set; }
    [Column("项目")]
    public string? Item { get; set; }
    [Column("单价")]
    public decimal? UnitCost { get; set; }
    [Column("总数")]
    public decimal? Total { get; set; }
}

根据索引进行映射

public class EMOrderDetails
{
    [Column(1)]
    public DateTime? Date { get; set; }
    [Column(2)]
    public string? Region { get; set; }
    [Column(3)]
    public string? Representative { get; set; }
    [Column(4)]
    public string? Item { get; set; }
    [Column(5)]
    public decimal? UnitCost { get; set; }
    [Column(6)]
    public decimal? Total { get; set; }
}

2.2 获取数据

private void button2_Click(object sender, EventArgs e)
{
    IEnumerable<EMOrderDetails> list = new ExcelMapper("./测试.xlsx") {
        //其他配置
    }
    .Fetch<EMOrderDetails>();
    dataGridView1.DataSource = list.ToList();
}

 

3、导出操作，List<T>转Excel

private void button3_Click(object sender, EventArgs e)
{
    List<EMOrderDetails> list = new List<EMOrderDetails>() { 
        new EMOrderDetails(){ Date = new DateTime(2022,1,3),Region = "广东-汕头", Item = "手表", Representative = "小白", UnitCost = 150.5M, Total = 30 },
        new EMOrderDetails(){ Date = new DateTime(2021,1,3),Region = "广东-佛山", Item = "耳机", Representative = "小李", UnitCost = 700, Total = 1 }
    };
    new ExcelMapper().Save($"./{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx", list, "sheetName");
}