余小章 @ 大內殿堂

開發環境

• VS 2019
• .NET Core 3.1
• Npoi.Mapper 3.5.0

功能

下圖來自 https://github.com/donnytian/Npoi.Mapper

實作

開一個 .NET Core 測試專案，安裝以下套件

Install-Package Npoi.Mapper -version 3.5.0

 

對應型別

具名型別或是 dynamic  型別

 

對應方式

1. 若工作表的標題跟屬性一樣，即可對應
2. Attribute：在欄位加上 Npoi.Mapper.Attributes.ColumnAttribute 即可對應到 Excel 的標題，或者是對應 Excel 的 ColumnIndex
3. Fluent：Npoi.Mapper.Mapper 靜態方法，定義對應方式

 

Attribute 對應範例如下：

internal class Employee
{
    [Column("LocationID")]
    public string LocationId { get; set; }

    //[Column("DeptID")]
    [Display(Name = "DeptID")]
    public string DepartmentId { get; set; }

    [Column("DeptName")]
    public string DepartmentName { get; set; }

    [Column("EmployeeID")]

    public string EmployeeId { get; set; }

    [Column("Name")]
    public string Name { get; set; }

    [Column("NTDomain")]
    public string DomainName { get; set; }

    [Column("ID")]
    public string Id { get; set; }
}

 

Fluent method 對應範例如下：

var mapper = new Mapper("Input.xlsx");
mapper.Map<Employee>("LocationID", o => o.LocationId)
      .Map<Employee>(1, o => o.DepartmentId)
      .Ignore<Employee>(o => o.ErrorMessage)
      .Format<Employee>("yyyy/MM/dd", o => o.Birthdaty)
    ;

 

匯入

實例化：建立 Mapper 時，傳入將 Excel (xls 或 xlsx)

• mapper.Workbook.NumberOfSheets：取得所有工作表數量
• mapper.Take<Employee>(i).ToList()：將 Excel 轉換成 Model。
  • mapper.Take<Employee>(i)：i 可以是 sheet name 或是 sheet index
  • mapper.Take<Employee>()，回傳 IEnumerable<RowInfo>，巡覽過的資料列，才會被放置到 mapper.Objects
  • mapper.Take<Employee>().ToList()：全部放置到 mapper.Objects
  • RowInfo：記錄轉換失敗的原因
  • 使用 IEnumerable<RowInfo> 進行比對、過濾，效能應該比 ToList 之後還要來的好
• mapper.Objects：Dictionary<string, Dictionary<int, object>> 型別，外層的 Dictionary.Key 存工作表名稱，內層的 Dictionary 存放轉換成功的資料，轉換失敗會得到 null

讀取所有工作表，範例如下：

[TestMethod]
public void 讀取所有工作表()
{
    var mapper         = new Mapper("Input.xlsx");
    var numberOfSheets = mapper.Workbook.NumberOfSheets;
    for (var i = 0; i < numberOfSheets; i++)
    {
        var rowInfos = mapper.Take<Employee>(i).ToList();
        foreach (var rowInfo in rowInfos)
        {
            if (string.IsNullOrWhiteSpace(rowInfo.ErrorMessage) == false)
            {
                Console.WriteLine(rowInfo.ErrorColumnIndex);
                Console.WriteLine(rowInfo.ErrorMessage);
            }
        }
    }

    foreach (var sheetInfo in mapper.Objects)
    {
        var sheetName = sheetInfo.Key;
        foreach (var rowInfo in sheetInfo.Value)
        {
            var rowIndex = rowInfo.Key;
            var rowData  = rowInfo.Value as Employee;
            Console.WriteLine(JsonConvert.SerializeObject(new
            {
                SheetName = sheetName,
                Index     = rowIndex,
                Data      = rowData
            }));
        }
    }
}

 

讀取特定工作表，範例如下：

[TestMethod]
public void 讀取特定工作表()
{
    var mapper   = new Mapper("Input.xlsx");
    var rowInfos = mapper.Take<Employee>("sheet1");
    foreach (var rowInfo in rowInfos)
    {
        if (string.IsNullOrWhiteSpace(rowInfo.ErrorMessage) == false)
        {
            Console.WriteLine(rowInfo.ErrorColumnIndex);
            Console.WriteLine(rowInfo.ErrorMessage);
        }

        Console.WriteLine(JsonConvert.SerializeObject(new
        {
            Index = rowInfo.RowNumber,
            Data  = rowInfo.Value
        }));
    }
}

 

匯出

mapper.Put：先把集合物件 Put 到 mapper

mapper.Save：把 mapper 匯出成 Excel 檔案

[TestMethod]
public void 匯出工作表()
{
    var mapper = new Mapper();
    var employees = new List<Employee>
    {
        new Employee
        {
            Id             = 1,
            LocationId     = "A",
            DepartmentId   = "S000",
            DepartmentName = "廣告部",
            EmployeeId     = "S001",
            Name           = "余小章",
            DomainName     = "TEST",
            Birthdaty      = new DateTime(1988, 9, 11)
        },
        new Employee
        {
            Id             = 2,
            LocationId     = "A",
            DepartmentId   = "A000",
            DepartmentName = "公關部",
            EmployeeId     = "A001",
            Name           = "小章魚",
            DomainName     = "TEST",
            Birthdaty      = new DateTime(1976, 8, 22)
        },
    };
    mapper.Put(employees, "sheet1",overwrite:true);
    mapper.Save("Output.xlsx");
}

 

範例位置

https://github.com/yaochangyu/sample.dotblog/tree/master/Excel/Mapper/Lab.NpoiMapper

若有謬誤，煩請告知，新手發帖請多包涵

Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET