【愚公系列】2022年10月 .NET CORE工具案例-.NET Core使用MiniExcel

前言

1.MiniExcel 的介绍

MiniExcel 是一个简单、高效避免 OOM 的 .NET 处理 Excel 查、写、填充数据工具。

目前主流框架大多需要将数据全载入到内存方便操作，但这会导致内存消耗问题，MiniExcel 尝试以 Stream 角度写底层算法逻辑，能让原本 1000 多 MB 占用降低到几 MB，避免内存不够情况。

MiniExcel 所具有的特点有：

• 低内存耗用，避免 OOM (out of memoery)、频繁 Full GC 情况
• 支持即时操作每行数据
• 兼具搭配 LINQ 延迟查询特性，能办到低消耗、快速分页等复杂查询
• 轻量，不需要安装 Microsoft Office、COM+，DLL 小于 150KB
• 简便操作的 API 风格

MiniExcel 的性能测试报告如下：

• 导入、查询 Excel 比较

• 导出、创建 Excel 比较

MiniExcel框架：https://github.com/MiniExcel/MiniExcel

一、.NET Core使用MiniExcel

.NET平台上对Excel进行操作主要有两种方式。

• 第一种，把Excel文件看成一个数据库，通过OleDb的方式进行读取与操作；
• 第二种，调用Excel的COM组件。
• 第三种，插件方式，目前主流框架大多需要将数据全载入到内存方便操作，但这会导致内存消耗问题，MiniExcel 尝试以 Stream 角度写底层算法逻辑，能让原本1000多MB占用降低到几MB，避免内存不够情况。

1.安装Nuget包

使用Nuget搜索Miniexcel安装

2.准备文件

准备一个Excel测试文件

3.准备Model类

public class Test
{public string Status { get; set; }public string OrderNo { get; set; }public string OrderType { get; set; }public string Address { get; set; }public string Num { get; set; }public string Torr { get; set; }public string Ynum { get; set; }public string County { get; set; }public string Level { get; set; }public string Date { get; set; }public string AtrNo { get; set; }
}

4.读/导入 Excel

var rows = MiniExcel.Query<Test>(@"./Test.xlsx").ToList();
// or
using (var stream = File.OpenRead(@"./Test.xlsx"))var rows = stream.Query<Test>();

Query 查询支援延迟加载(Deferred Execution)，能配合LINQ First/Take/Skip办到低消耗、高效率复杂查询

var rows = MiniExcel.Query<Test>(@"./Test.xlsx").First();
//or
using (var stream = File.OpenRead(@"./Test.xlsx"))
{var row = stream.Query().First();
}

MiniExcel 当判断文件 SharedString 大小超过 5MB，预设会使用本地缓存，如 10x100000.xlsx(一百万笔数据)，读取不开启本地缓存需要最高内存使用约195MB，开启后降为65MB。

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

5.写/导出 Excel

var path = $"./{Guid.NewGuid()}.xlsx";
//支持各种类型的导出（匿名类型，强类型，IDataReader等）
MiniExcel.SaveAs(path, new[] {new { Column1 = "MiniExcel", Column2 = 1 },new { Column1 = "Github", Column2 = 2}
});
//or
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);
}

6.模板填充 Excel

var path = $"./{Guid.NewGuid()}.xlsx";
var templatePath = $"./6ed6226c-dfb8-4e0c-95b1-d9b7e4f7f31a.xlsx";
// 1. By POCO
var value = new
{Name = "愚公搬代码",CreateDate = new DateTime(2022, 10, 08),Age = true,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);//or
// 2. By Dictionary
var value = new Dictionary<string, object>()
{["Name"] = "Jack",["CreateDate"] = new DateTime(2021, 01, 01),["Age"] = true,
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

6.复杂数据填充

var path = $"./{Guid.NewGuid()}.xlsx";
var templatePath = $"./6ed6226c-dfb8-4e0c-95b1-d9b7e4f7f31a.xlsx";
// 1. By POCO
var value = new
{title = "嗨欢迎来到我的世界",managers = new[] {new{Name = "愚公搬代码",CreateDate = new DateTime(2022, 10, 08),Age = 18,},new {Name = "愚公搬代码1",CreateDate = new DateTime(2022, 11, 08),Age = 28,}},employees = new[] {new{Name = "愚公搬代码",CreateDate = new DateTime(2022, 10, 08),Age = 18,},new {Name = "愚公搬代码1",CreateDate = new DateTime(2022, 11, 08),Age = 28,},new {Name = "愚公搬代码2",CreateDate = new DateTime(2022, 11, 08),Age = 30,},new {Name = "愚公搬代码3",CreateDate = new DateTime(2022, 11, 08),Age = 31,}}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);//or
var value = new Dictionary<string, object>()
{["title"] = "嗨欢迎来到我的世界",["managers"] = new[] {new{Name = "愚公搬代码",CreateDate = new DateTime(2022, 10, 08),Age = 18,},new {Name = "愚公搬代码1",CreateDate = new DateTime(2022, 11, 08),Age = 28,}},["employees"] = new[] {new{Name = "愚公搬代码",CreateDate = new DateTime(2022, 10, 08),Age = 18,},new {Name = "愚公搬代码1",CreateDate = new DateTime(2022, 11, 08),Age = 28,},new {Name = "愚公搬代码2",CreateDate = new DateTime(2022, 11, 08),Age = 30,},new {Name = "愚公搬代码3",CreateDate = new DateTime(2022, 11, 08),Age = 31,}}
};
MiniExcel.SaveAsByTemplate(path, templatePath, value);

不过wps打开没问题，微软的excel打开报错

7.Excel 列属性 (Excel Column Attribute)

属性指定Column

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")] // 系统会自动转换"I"为第8列public string Test4 { get; set; } public string Test5 { get; } //系统会忽略此列public string Test6 { get; private set; } //set非公开,系统会忽略[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); 

DynamicColumnAttribute 动态设定 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("age"){Index=2,Name="Account Age"},}
};
var path = PathHelper.GetTempPath();
var value = new[] { new { id = 1, name = "愚公搬代码", createdate = new DateTime(2022, 10, 08) ,age= 31} };
MiniExcel.SaveAs(path, value, configuration: config);

8.Excel 类别自动判断

MiniExcel 预设会根据文件扩展名判断是 xlsx 还是 csv，但会有失准时候，请自行指定。

Stream 类别无法判断来源于哪种 excel 请自行指定

stream.SaveAs(excelType:ExcelType.CSV);
//or
stream.SaveAs(excelType:ExcelType.XLSX);
//or
stream.Query(excelType:ExcelType.CSV);
//or
stream.Query(excelType:ExcelType.XLSX);