&

C#.NET操作Excel高效低內存的開源框架 - MiniExcel

當前位置：點晴教程→知識管理交流 →『技術文檔交流』

admin

2023年9月7日 17:16 本文熱度 1610

.Net平臺上對Excel進行操作主要有兩種方式。第一種，把Excel文件看成一個數據庫，通過OleDb的方式進行讀取與操作；第二種，調用Excel的COM組件。兩種方式各有特點。

今天給大家介紹第三種方式：插件方式，目前主流框架大多需要將數據全載入到內存方便操作，但這會導致內存消耗問題，MiniExcel 嘗試以 Stream 角度寫底層算法邏輯，能讓原本1000多MB占用降低到幾MB，避免內存不夠情況。

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

特點

低內存耗用，避免OOM、頻繁 Full GC 情況
支持即時操作每行數據
兼具搭配 LINQ 延遲查詢特性，能辦到低消耗、快速分頁等復雜查詢
輕量，不需要安裝 Microsoft Office、COM+，DLL小于150KB
簡便操作的 API 風格

性能比較、測試

導入、查詢 Excel 比較

邏輯 : 以 Test1,000,000x10.xlsx 做基準與主流框架做性能測試，總共 1,000,000 行 * 10 列筆 "HelloWorld"，文件大小 23 MB。

導出、創(chuàng)建 Excel 比較

邏輯 : 創(chuàng)建1千萬筆 "HelloWorld"

使用示例

1、讀/導入 Excel

1.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);
  // or
  using (var stream = File.OpenRead(path))
  var rows = stream.Query<UserAccount>();

1.2 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);
}

與其他框架效率比較

1.3 讀取大文件硬盤緩存 (Disk-Base Cache - SharedString)

概念 : MiniExcel 當判斷文件 SharedString 大小超過 5MB，預設會使用本地緩存，如 10x100000.xlsx(一百萬筆數據)，讀取不開啟本地緩存需要最高內存使用約195MB，開啟后降為65MB。但要特別注意，此優(yōu)化是以時間換取內存減少，所以讀取效率會變慢，此例子讀取時間從 7.4 秒提高到 27.2 秒，假如不需要能用以下代碼關閉硬盤緩存

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

也能使用 SharedStringCacheSize 調整 sharedString 文件大小超過指定大小才做硬盤緩存

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

2、寫/導出 Excel

必須是非abstract 類別有公開無參數構造函數
MiniExcel SaveAs 支援 IEnumerable參數延遲查詢，除非必要請不要使用 ToList 等方法讀取全部數據到內存

2.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.2 IDataReader

推薦使用，可以避免載入全部數據到內存

推薦 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);
·         }

3、模板填充 Excel

宣告方式類似 Vue 模板 {{變量名稱}}, 或是集合渲染 {{集合名稱.欄位名稱}}
集合渲染支持 IEnumerable/DataTable/DapperRow

3.1 基本填充

  // 1. By POCO
  var value = new
  {
      Name = "Jack",
      createDate = new DateTime(2021, 01, 01),
      VIP = true,
      Points = 123
  };
  MiniExcel.SaveAsByTemplate(path, templatePath, value);

  // 2. By Dictionary
  var value = new Dictionary<string, object>()
  {
      ["Name"] = "Jack",
      ["createDate"] = new DateTime(2021, 01, 01),
      ["VIP"] = true,
      ["Points"] = 123
  };
  MiniExcel.SaveAsByTemplate(path, templatePath, value);

3.2 復雜數據填充

    // 1. By POCO
    var value = new
    {
    title = "FooCompany",
    managers = new[] {
    new {name="Jack",department="HR"},
    new {name="Loan",department="IT"}
    },
    employees = new[] {
  new {name="Wade",department="HR"},
  new {name="Felix",department="HR"},
  new {name="Eric",department="IT"},
  new {name="Keaton",department="IT"}
  }
  };
  MiniExcel.SaveAsByTemplate(path, templatePath, value);

  // 2. By Dictionary
  var value = new Dictionary<string, object>()
  {
  ["title"] = "FooCompany",
  ["managers"] = new[] {
  new {name="Jack",department="HR"},
  new {name="Loan",department="IT"}
  },
  ["employees"] = new[] {
new {name="Wade",department="HR"},
new {name="Felix",department="HR"},
new {name="Eric",department="IT"},
new {name="Keaton",department="IT"}
}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

4、Excel 列屬性 (Excel Column Attribute)

4.1 指定列名稱、指定第幾列、是否忽略該列

    public class ExcelAttributeDemo
    {
    [ExcelColumnName("Column1")]
    public string Test1 { get; set; }
    [ExcelColumnName("Column2")]
    public string Test2 { get; set; }
    [ExcelIgnore]
    public string Test3 { get; set; }
    [ExcelColumnIndex("I")] // 系統(tǒng)會自動轉換"I"為第8列
  public string Test4 { get; set; }
  public string Test5 { get; } //系統(tǒng)會忽略此列
  public string Test6 { get; private set; } //set非公開,系統(tǒng)會忽略
  [ExcelColumnIndex(3)] // 從0開始索引
  public string Test7 { get; set; }
  }

var rows = MiniExcel.Query<ExcelAttributeDemo>(path).ToList();
Assert.Equal("Column1", rows[0].Test1);
Assert.Equal("Column2", rows[0].Test2);
Assert.Null(rows[0].Test3);
Assert.Equal("Test7", rows[0].Test4);
Assert.Null(rows[0].Test5);
Assert.Null(rows[0].Test6);
Assert.Equal("Test4", rows[0].Test7);

4.2 DynamicColumnAttribute 動態(tài)設定 Column

    var config = new OpenXmlConfiguration
    {
    DynamicColumns = new DynamicExcelColumn[] {
    new DynamicExcelColumn("id"){Ignore=true},
    new DynamicExcelColumn("name"){Index=1,Width=10},
    new DynamicExcelColumn("createdate"){Index=0,Format="yyyy-MM-dd",Width=15},
    new DynamicExcelColumn("point"){Index=2,Name="Account Point"},
    }
    };
  var path = PathHelper.GetTempPath();
  var value = new[] { new { id = 1, name = "Jack", createdate = new DateTime(2022, 04, 12) ,point = 123.456} };
  MiniExcel.SaveAs(path, value, configuration: config);

Excel 類別自動判斷

MiniExcel 預設會根據文件擴展名判斷是 xlsx 還是 csv，但會有失準時候，請自行指定。
Stream 類別無法判斷來源于哪種 excel 請自行指定
1. stream.SaveAs(excelType:ExcelType.CSV);
2. //or
3. stream.SaveAs(excelType:ExcelType.XLSX);
4. //or
5. stream.Query(excelType:ExcelType.CSV);
6. //or
7. stream.Query(excelType:ExcelType.XLSX);

該文章在 2023/9/7 17:26:42 編輯過

關鍵字查詢

正在查詢...

點晴ERP是一款針對中小制造業(yè)的專業(yè)生產管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內大量中小企業(yè)的青睞。

點晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運作、調度、堆場、車隊、財務費用、相關報表等業(yè)務管理，結合碼頭的業(yè)務特點，圍繞調度、堆場作業(yè)而開發(fā)的。集技術的先進性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。

點晴WMS倉儲管理系統(tǒng)提供了貨物產品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質期管理,貨位管理,庫位管理,生產管理,WMS管理系統(tǒng),標簽打印,條形碼,二維碼管理,批號管理軟件。

點晴免費OA是一款軟件和通用服務都免費，不限功能、不限時間、不限用戶的免費OA協(xié)同辦公管理系統(tǒng)。