前情提要

筆者這篇就延續Reader系列，這次介紹Excel檔案讀取的實作，Excel讀寫套件百百種，有名的就那幾個，ExcelDataReader、NPOI、CloseXML等等。筆者這篇主要使用ExcelDataReader上面再包一層的ExcelMapper，主要是簡單易用，有強型別的Binding，一般讀取來說已足夠。

內容

筆者繼續沿用Reader系列的專案檔案，想要看建立專案的請參考[DotnetCore]Reader系列-CSV檔案

安裝ExcelMapper套件

1	dotnet add package ExcelDataReader.Mapping

撰寫ExcelExtension

public static class ExcelExtension
{
    public static List<T> ReadData<T>(string filePath)
    {
        var dataList = new List<T>();
        using var stream = File.OpenRead(filePath);
        using (var importer = new ExcelImporter(stream))
        {
            ExcelSheet sheet = importer.ReadSheet();
            dataList = sheet.ReadRows<T>().ToList();
        }

        return dataList;
    }
}

製作Model

讓筆者偷懶一下，直接用ExcelMapper的github上提供的範例Model

public enum EventCause
{
    Profit,
    Charity
}

public class Event
{
    public string Name { get; set; }
    public string Location { get; set; }
    public int Attendance { get; set; }
    public DateTime Date { get; set; }
    public Uri Link { get; set; }
    public EventCause Cause { get; set; }
}

製作範例檔案

範例資料如下

Name	Location	Attendance	Date	Link	Revenue	Successful	Cause
Pub Quiz	The Blue Anchor	20	18/07/2017	http://eventbrite.com	100.2	TRUE	Charity
Live Music	The Raven	15	17/07/2017	http://ticketmaster.com	105.6	FALSE	Profit
Live Football	The Rutland Arms	45	16/07/2017	http://facebook.com	263.9	TRUE	Profit

Client端使用

筆者就在Program.cs中的main撰寫

class Program
{
    static void Main(string[] args)
    {
        var _folder = AppDomain.CurrentDomain.BaseDirectory;        
        var _filePath = Path.Combine(_folder, "Upload", "Event.xlsx");
        var events = ExcelExtension.ReadData<Event>(_filePath);
        System.Console.WriteLine($"[Events] {JsonConvert.SerializeObject(events)}");
        Console.ReadLine();
    }
}

Encoding 1252問題處裡

筆者就以簡單的dotnet run來跑出結果，結果會發生以下錯誤

dotnet run
# Error
Unhandled exception. System.NotSupportedException: No data is available for encoding 1252. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.
   at System.Text.Encoding.GetEncoding(Int32 codepage)
   at ExcelDataReader.ExcelReaderConfiguration..ctor()
   at ExcelDataReader.ExcelReaderFactory.CreateReader(Stream fileStream, ExcelReaderConfiguration configuration)
   at ExcelMapper.ExcelImporter..ctor(Stream stream)
   at reader.demo.console.Extensions.ExcelExtension.ReadData[T](String filePath) in ExcelExtension.cs:line 14

解決方式是，多註冊宣告一個Encoding.CodePages

1	System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

Mapping問題

接著執行dotnet run來跑出結果，結果會發生以下錯誤

1
2
3

dotnet run
# Error
Invalid assigning "18/07/2017" to member "Date" of type "System.DateTime" in column "Date" on row 0 in sheet "工作表1".

ClassMap宣告客製化Mapping邏輯

實際上是套件本身不知道將18/07/2017如何轉換成DateTime的值，因此這邊筆者要直接宣告ClassMap來避免這種問題發生

public class EventMap : ExcelClassMap<Event>
{
	public EventMap()
	{
		Map(e => e.Name);
		Map(e => e.Location);
		Map(e => e.Attendance);
		Map(e => e.Date)
		.WithDateFormats("dd/MM/yyyy", "g");
		Map(e => e.Link);
		Map(e => e.Cause);
	}
}

擴充程式:註冊ExcelClassMap

最後要來改造一下ExcelExtension，筆者就不動原有的，再增加一個可以註冊ExcelClassMap的靜態方法

public static class ExcelExtension
{
    public static List<T> ReadData<T>(string filePath)
    {
        var dataList = new List<T>();
        using var stream = File.OpenRead(filePath);
        using (var importer = new ExcelImporter(stream))
        {
            ExcelSheet sheet = importer.ReadSheet();
            dataList = sheet.ReadRows<T>().ToList();
        }

        return dataList;
    }

		 public static List<T> ReadData<T, TMap>(string filePath)
			// 這一個限制很重要，這樣宣告RegisterClassMap才可以編譯過
			where TMap: ExcelClassMap<T>, new() 
    {
        var dataList = new List<T>();
        using var stream = File.OpenRead(filePath);
        using (var importer = new ExcelImporter(stream))
        {
            importer.Configuration.RegisterClassMap<TMap>();
            ExcelSheet sheet = importer.ReadSheet();
            dataList = sheet.ReadRows<T>().ToList();
        }

        return dataList;
    }
}

執行地方換成使用ExcelClassMap

class Program
{
    static void Main(string[] args)
    {
        var _folder = AppDomain.CurrentDomain.BaseDirectory;        
        var _filePath = Path.Combine(_folder, "Upload", "Event.xlsx");
        var events = ExcelExtension.ReadData<Event, EventMap>(_filePath);
        System.Console.WriteLine($"[Events] {JsonConvert.SerializeObject(events)}");
        Console.ReadLine();
    }
}

最後成功執行

1
2
3

dotnet run
# 執行結果
[Events] [{"Name":"Pub Quiz","Location":"The Blue Anchor","Attendance":20,"Date":"2017-07-18T00:00:00","Link":"http://eventbrite.com","Cause":1},{"Name":"Live Music","Location":"The Raven","Attendance":15,"Date":"2017-07-17T00:00:00","Link":"http://ticketmaster.com","Cause":0},{"Name":"Live Football","Location":"The Rutland Arms","Attendance":45,"Date":"2017-07-16T00:00:00","Link":"http://facebook.com","Cause":0}]

結論

這篇就以ExcelMapper為主，再找篇來寫ExcelDataReader的讀取方式吧，不過有強型別就大大降低存取方式的困難度，有Intellisense就是寫的快速又安全。

Buy me a coffee

Eugene's Blog

[DotnetCore]Reader系列-Excel檔案