0%

[DotnetCore]Reader系列-Excel檔案

前提情要

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

內容

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

安裝ExcelMapper套件

1
dotnet add package ExcelDataReader.Mapping

撰寫ExcelExtension

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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撰寫

1
2
3
4
5
6
7
8
9
10
11
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來跑出結果,結果會發生以下錯誤

1
2
3
4
5
6
7
8
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來避免這種問題發生

1
2
3
4
5
6
7
8
9
10
11
12
13
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的靜態方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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

1
2
3
4
5
6
7
8
9
10
11
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就是寫的快速又安全。