.NET 中高效 Excel 解决方案 MiniExcel

来源：cnblogs　　作者：小码编匠　　时间：2024/7/29 9:35:18　　对本文有异议

前言

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