.NET 中高效 Excel 解決方案 MiniExcel

小码编匠發表於2024-07-24
Excel

前言

MiniExcel 是一個用於 .NET 平臺的輕量級、高效能的庫，專注於提供簡單易用的 API 來處理 Excel 檔案。以下是 MiniExcel 的特點總結：

  • 輕量級與高效：MiniExcel 設計為佔用較少的系統資源，尤其在記憶體使用上表現優秀，適合處理大資料集而不會導致記憶體溢位。

  • 簡單易用：API 設計直觀，易於理解和使用，即使是初學者也能迅速上手，進行 Excel 資料的讀取和寫入操作。

  • 快速讀寫：MiniExcel 提供了快速的資料讀寫機制，能夠有效提高處理 Excel 檔案的效率，特別是在大資料量場景下。

  • 靈活的資料處理：支援多種資料型別，包括但不限於數字、文字、日期等，並提供了資料轉換和格式化功能。

  • 資料填充：MiniExcel 支援資料填充，可以將資料模板與資料集合相結合，快速生成大量格式化的 Excel 報告。

  • 模板支援：利用模板，可以輕鬆建立帶有預設樣式和佈局的複雜 Excel 文件，減少重複工作。

  • 跨平臺相容性：MiniExcel 在 .NET Standard 下執行良好，意味著它可以在多個平臺上使用，包括 Windows、macOS 和 Linux。

  • 易於整合：可以輕鬆地將 MiniExcel 整合到現有的 .NET 專案中，無論是 Web 應用、桌面應用還是服務端應用。

MiniExcel 是處理 Excel 檔案的理想選擇，尤其是對於那些尋求在 .NET 應用中實現快速、低記憶體消耗的 Excel 資料讀寫功能的開發者。無論是用於資料分析、報告生成還是資料匯入匯出，MiniExcel 都能提供強大的支援。

專案介紹

MiniExcel簡單、高效避免OOM的.NET處理Excel查、寫、填充資料工具。

目前主流框架大多需要將資料全載入到記憶體方便操作，但這會導致記憶體消耗問題，MiniExcel 嘗試以 Stream 角度寫底層演算法邏輯，能讓原本1000多MB佔用降低到幾MB，避免記憶體不夠情況。

處理Excel效能對比

1、匯入、查詢 Excel 比較

2、匯出、建立 Excel 比較

安裝 MiniExcel

可以檢視NuGet命令

https://www.nuget.org/packages/MiniExcel

dotnet add package MiniExcel --version 1.34.0

1、Query 查詢 Excel 返回強型別 IEnumerable 資料

public class UserAccount  
{  
    public Guid ID { get; set; }  
    public string Name { get; set; }  
    public DateTime BoD { get; set; }  
    public int Age { get; set; }  
    public bool VIP { get; set; }  
    public decimal Points { get; set; }  
}  
  
var rows = MiniExcel.Query<UserAccount>(path);

2、 Query 查詢 Excel 返回Dynamic IEnumerable 資料

Key 系統預設為 A,B,C,D...Z

MiniExcel 1 
Github
 2 

var rows = MiniExcel.Query(path).ToList();

// or
using (var stream = File.OpenRead(path))
{
    var rows = stream.Query().ToList();

    Assert.Equal("MiniExcel", rows[0].A);
    Assert.Equal(1, rows[0].B);
    Assert.Equal("Github", rows[1].A);
    Assert.Equal(2, rows[1].B);
}

3、查詢資料以第一行資料當Key

注意 : 同名以右邊資料為準

Input Excel :

Column1 Column2
MiniExcel 1
Github 2 

var rows = MiniExcel.Query(useHeaderRow:true).ToList();

// or

using (var stream = File.OpenRead(path))
{
    var rows = stream.Query(useHeaderRow:true).ToList();

    Assert.Equal("MiniExcel", rows[0].Column1);
    Assert.Equal(1, rows[0].Column2);
    Assert.Equal("Github", rows[1].Column1);
    Assert.Equal(2, rows[1].Column2);
}

4、Query 查詢支援延遲載入(Deferred Execution)，能配合LINQ First/Take/Skip辦到低消耗、高效率複雜查詢

舉例 : 查詢第一筆資料

var row = MiniExcel.Query(path).First();
Assert.Equal("HelloWorld", row.A);
// or
using (var stream = File.OpenRead(path))
{
    var row = stream.Query().First();
    Assert.Equal("HelloWorld", row.A);
}

5、查詢指定 Sheet 名稱

MiniExcel.Query(path, sheetName: "SheetName");
//or
stream.Query(sheetName: "SheetName");

6、查詢所有 Sheet 名稱跟資料

var sheetNames = MiniExcel.GetSheetNames(path);
foreach (var sheetName in sheetNames)
{
    var rows = MiniExcel.Query(path, sheetName: sheetName);
}

7、查詢所有欄(列)

var columns = MiniExcel.GetColumns(path); // e.g result : ["A","B"...]

or

var columns = MiniExcel.GetColumns(path, useHeaderRow: true);
// e.g result : ["excel表實際的列名稱","excel表實際的列名稱"...]

var cnt = columns.Count;  // get column count

8、Dynamic Query 轉成 IDictionary<string,object> 資料

foreach(IDictionary<string,object> row in MiniExcel.Query(path))
{
    //..
}
// or
var rows = MiniExcel.Query(path).Cast<IDictionary<string,object>>();
// or 查詢指定範圍（要大寫才生效哦）
// A2（左上角）代表A列的第二行，C3（右下角）代表C列的第三行
// 如果你不想限制行，就不要包含數字
var rows = MiniExcel.QueryRange(path, startCell: "A2", endCell: "C3").Cast<IDictionary<string, object>>();

9、Query 讀 Excel 返回 DataTable

提醒 : 不建議使用，因為DataTable會將資料全載入記憶體，失去MiniExcel低記憶體消耗功能。

var table = MiniExcel.QueryAsDataTable(path, useHeaderRow: true);

10、指定單元格開始讀取資料

MiniExcel.Query(path,useHeaderRow:true,startCell:"B3")

11、合併的單元格填充

注意 : 效率相對於沒有使用合併填充來說差

底層原因 : OpenXml 標準將 mergeCells 放在檔案最下方，導致需要遍歷兩次 sheetxml

var config = new OpenXmlConfiguration()
{
    FillMergedCells = true
};
var rows = MiniExcel.Query(path, configuration: config);

12、讀取大檔案硬碟快取 (Disk-Base Cache - SharedString)

概念 : MiniExcel 當判斷檔案 SharedString 大小超過 5MB，預設會使用本地快取，如 10x100000.xlsx(一百萬筆資料)，讀取不開啟本地快取需要最高記憶體使用約195MB，開啟後降為65MB。

但要特別注意，此最佳化是以時間換取記憶體減少，所以讀取效率會變慢，此例子讀取時間從 7.4 秒提高到 27.2 秒，假如不需要能用以下程式碼關閉硬碟快取

var config = new OpenXmlConfiguration { EnableSharedStringCache = false };
MiniExcel.Query(path,configuration: config)

也能使用 SharedStringCacheSize 調整 sharedString 檔案大小超過指定大小才做硬碟快取

var config = new OpenXmlConfiguration { SharedStringCacheSize=500*1024*1024 };
MiniExcel.Query(path, configuration: config);

寫/匯出 Excel

必須是非abstract 類別有公開無引數建構函式

MiniExcel SaveAs 支援 IEnumerable引數延遲查詢，除非必要請不要使用 ToList 等方法讀取全部資料到記憶體

是否呼叫 ToList 的記憶體差別,如下圖所示：

1、支援集合<匿名類別>或是<強型別>

var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");  
MiniExcel.SaveAs(path, new[] {  
    new { Column1 = "MiniExcel", Column2 = 1 },  
    new { Column1 = "Github", Column2 = 2}  
});

2、IEnumerable<IDictionary<string, object>>

var values = new List<Dictionary<string, object>>()  
{  
    new Dictionary<string,object>{{ "Column1", "MiniExcel" }, { "Column2", 1 } },  
    new Dictionary<string,object>{{ "Column1", "Github" }, { "Column2", 2 } }  
};  
MiniExcel.SaveAs(path, values);

3、IDataReader

推薦使用，可以避免載入全部資料到記憶體 MiniExcel.SaveAs(path, reader);

推薦 DataReader 多表格匯出方式(建議使用 Dapper ExecuteReader )

using (var cnn = Connection)
{
    cnn.Open();
    var sheets = new Dictionary<string,object>();
    sheets.Add("sheet1", cnn.ExecuteReader("select 1 id"));
    sheets.Add("sheet2", cnn.ExecuteReader("select 2 id"));
    MiniExcel.SaveAs("Demo.xlsx", sheets);
}

4、Datatable

不推薦使用，會將資料全載入記憶體

優先使用 Caption 當欄位名稱

var path = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}.xlsx");
var table = new DataTable();
{
    table.Columns.Add("Column1", typeof(string));
    table.Columns.Add("Column2", typeof(decimal));
    table.Rows.Add("MiniExcel", 1);
    table.Rows.Add("Github", 2);
}

MiniExcel.SaveAs(path, table);

5、Dapper Query

6、SaveAs 支援 Stream，生成檔案不落地

7、建立多個工作表(Sheet)

8、表格樣式選擇

9、AutoFilter 篩選

10、圖片生成

11、Byte Array 檔案匯出

12、垂直合併相同的單元格

13、是否寫入 null values cell

模板填充 Excel

1、基本填充

2、IEnumerable 資料填充

3、複雜資料填充

4、大資料填充效率比較

5、Cell 值自動類別對應

6、Example : 列出 Github 專案

var projects = new[]
{
    new {Name = "MiniExcel",Link="https://github.com/shps951023/MiniExcel",Star=146, CreateTime=new DateTime(2021,03,01)},
    new {Name = "HtmlTableHelper",Link="https://github.com/shps951023/HtmlTableHelper",Star=16, CreateTime=new DateTime(2020,02,01)},
    new {Name = "PocoClassGenerator",Link="https://github.com/shps951023/PocoClassGenerator",Star=16, CreateTime=new DateTime(2019,03,17)}
};
var value = new
{
    User = "ITWeiHan",
    Projects = projects,
    TotalStar = projects.Sum(s => s.Star)
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

8、DataTable 當引數

地址

https://gitee.com/dotnetchina/MiniExcel

如果覺得這篇文章對你有用，歡迎加入微信公眾號 [DotNet技術匠] 社群，與其他熱愛技術的同行交流心得，共同成長。

相關文章