引言
背景和目的
本文介绍了几个常用的电子表格处理库，包括EPPlus、NPOI、Aspose.Cells和DocumentFormat.OpenXml，我们将对这些库进行性能测评，以便为开发人员提供实际的性能指标和数据。
下表将功能/特点、开源/许可证这两列分开，以满足需求：
| 功能 / 特点 | EPPlus | NPOI | Aspose.Cells | DocumentFormat.OpenXml |
|---|---|---|---|---|
| 开源 | 是 | 是 | 否 | 是 |
| 许可证 | MIT | Apache | 商业 | MIT |
| 支持的 Excel 版本 | Excel 2007 及更高版本 | Excel 97-2003 | Excel 2003 及更高版本 | Excel 2007 及更高版本 |
测评电脑配置
| 组件 | 规格 |
|---|---|
| CPU | 11th Gen Intel(R) Core(TM) i5-11320H @ 3.20GHz,2496 Mhz，4 个内核，8 个逻辑处理器 |
| 内存 | 40 GB DDR4 3200MHz |
| 操作系统 | Microsoft Windows 10 专业版 |
| 电源选项 | 已设置为高性能 |
| 软件 | LINQPad 7.8.5 Beta |
| 运行时 | .NET 6.0.21 |
准备工作
使用Bogus库生成6万条标准化的测试数据。
void Main() { string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "test-data.json"); using var file = File.Create(path); using var writer = new Utf8JsonWriter(file, new JsonWriterOptions { Indented = true }); var data = new Bogus.Faker<Data>() .RuleFor(x => x.Id, x => x.IndexFaker + 1) .RuleFor(x => x.Gender, x => x.Person.Gender) .RuleFor(x => x.FirstName, (x, u) => x.Name.FirstName(u.Gender)) .RuleFor(x => x.LastName, (x, u) => x.Name.LastName(u.Gender)) .RuleFor(x => x.Email, (x, u) => x.Internet.Email(u.FirstName, u.LastName)) .RuleFor(x => x.BirthDate, x => x.Person.DateOfBirth) .RuleFor(x => x.Company, x => x.Person.Company.Name) .RuleFor(x => x.Phone, x => x.Person.Phone) .RuleFor(x => x.Website, x => x.Person.Website) .RuleFor(x => x.SSN, x => x.Person.Ssn()) .GenerateForever().Take(6_0000) .Dump(); JsonSerializer.Serialize(writer, data); Process.Start("explorer", @$"/select, ""{path}""".Dump()); } Bogus输出结果
| Id | Gender | FirstName | LastName | BirthDate | Company | Phone | Website | SSN | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Male | Antonio | Paucek | Antonio.Paucek@gmail.com | 1987/10/31 5:46:50 | Moen, Willms and Maggio | (898) 283-1583 x88626 | pamela.name | 850-06-4706 |
| 2 | Male | Kurt | Gerhold | Kurt.Gerhold40@yahoo.com | 1985/11/1 18:41:01 | Wilkinson and Sons | (698) 637-0181 x49124 | cordelia.net | 014-86-1757 |
| 3 | Male | Howard | Hegmann | Howard2@hotmail.com | 1979/7/20 22:35:40 | Kassulke, Murphy and Volkman | (544) 464-9818 x98381 | kari.com | 360-23-1669 |
| 4 | Female | Rosemarie | Powlowski | Rosemarie.Powlowski48@hotmail.com | 1964/5/18 1:35:45 | Will Group | 1-740-705-6482 | laurence.net | 236-10-9925 |
| 5 | Female | Eunice | Rogahn | Eunice84@gmail.com | 1979/11/25 11:53:14 | Rippin - Rowe | (691) 491-2282 x3466 | yvette.net | 219-75-6886 |
| …… |
创建公共类方便正式测评使用
void Main() { string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"; LoadUsers(path).Dump(); } List<User> LoadUsers(string jsonfile) { string path = jsonfile; byte[] bytes = File.ReadAllBytes(path); return JsonSerializer.Deserialize<List<User>>(bytes); } IObservable<object> Measure(Action action, int times = 5) { return Enumerable.Range(1, times).Select(i => { var sw = Stopwatch.StartNew(); long memory1 = GC.GetTotalMemory(true); long allocate1 = GC.GetTotalAllocatedBytes(true); { action(); } long allocate2 = GC.GetTotalAllocatedBytes(true); long memory2 = GC.GetTotalMemory(true); sw.Stop(); return new { 次数 = i, 分配内存 = (allocate2 - allocate1).ToString("N0"), 内存提高 = (memory2 - memory1).ToString("N0"), 耗时 = sw.ElapsedMilliseconds, }; }).ToObservable(); } class User { public int Id { get; set; } public int Gender { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Email { get; set; } public DateTime BirthDate { get; set; } public string Company { get; set; } public string Phone { get; set; } public string Website { get; set; } public string SSN { get; set; } } 代码解释
1、上面的代码单位是字节 (bytes)
2 、其中IObservable（System.IObservable）是用于处理事件流的接口，它实现了观察者模式。它表示一个可观察的序列，可以产生一系列的事件，并允许其他对象（观察者）来订阅和接收这些事件。IObservable 适用于动态的、实时的事件流处理，允许观察者以异步方式接收事件，可以用于响应式编程、事件驱动的编程模型等。
3、GC.GetTotalAllocatedBytes(true) 获取分配内存大小
GC.GetTotalMemory(true) 获取占用内存大小
性能测评
EPPlus
string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"; List<User> users = LoadUsers(path); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"export.epplus.xlsx"); }).Dump("EPPlus"); void Export<T>(List<T> data, string path) { using var stream = File.Create(path); using var excel = new ExcelPackage(stream); ExcelWorksheet sheet = excel.Workbook.Worksheets.Add("Sheet1"); PropertyInfo[] props = typeof(User).GetProperties(); for (var i = 0; i < props.Length; ++i) { sheet.Cells[1, i + 1].Value = props[i].Name; } for (var i = 0; i < data.Count; ++i) { for (var j = 0; j < props.Length; ++j) { sheet.Cells[i + 2, j + 1].Value = props[j].GetValue(data[i]); } } excel.Save(); } 输出结果
EPPlus (6.2.8) （2023/8/15）输出结果
| 次数ΞΞ | 分配内存ΞΞ | 内存提高ΞΞ | 耗时ΞΞ |
|---|---|---|---|
| 1 | 454,869,176 | 970,160 | 2447 |
| 2 | 440,353,488 | 176 | 1776 |
| 3 | 440,062,264 | 0 | 1716 |
| 4 | 440,283,584 | 0 | 1750 |
| 5 | 440,653,264 | 0 | 1813 |
EPPlus （4.5.3.2）（2019/6/16）输出结果
| 次数ΞΞ | 分配内存ΞΞ | 内存提高ΞΞ | 耗时ΞΞ |
|---|---|---|---|
| 1 | 963,850,944 | 192,048 | 2765 |
| 2 | 509,450,792 | 600 | 1897 |
| 3 | 509,872,160 | 424 | 1920 |
| 4 | 509,858,576 | 424 | 1989 |
| 5 | 509,651,512 | 424 | 2076 |
由此看出 相比2019,到了2023年EPPlus的性能得到了略微的提升
NPOI
示例代码一：XSSFWorkbook
List<User> users = LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"export.npoi.xlsx"); }).Dump("NPOI"); void Export<T>(List<T> data, string path) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); var headRow = sheet.CreateRow(0); PropertyInfo[] props = typeof(User).GetProperties(); for (var i = 0; i < props.Length; ++i) { headRow.CreateCell(i).SetCellValue(props[i].Name); } for (var i = 0; i < data.Count; ++i) { var row = sheet.CreateRow(i + 1); for (var j = 0; j < props.Length; ++j) { row.CreateCell(j).SetCellValue(props[j].GetValue(data[i]).ToString()); } } using var file = File.Create(path); workbook.Write(file); workbook.Close(); } 输出结果
NPOI （2.6.1）（2023/7/12）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 1,589,285,792 | 567,272 | 5549 |
| 2 | 1,577,028,664 | 96 | 7043 |
| 3 | 1,577,398,488 | 48 | 8107 |
| 4 | 1,576,360,696 | -90,512 | 9336 |
| 5 | 1,576,226,688 | -3,120 | 8289 |
NPOI （2.4.1）（2018/12/18）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 1,648,548,696 | 526,824 | 6947 |
| 2 | 1,633,685,136 | 120 | 7921 |
| 3 | 1,634,033,296 | 24 | 8864 |
| 4 | 1,634,660,176 | -90,200 | 8945 |
| 5 | 1,634,205,368 | -2,584 | 8078 |
示例代码二：SXSSFWorkbook
List<User> users = LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"export.npoi.xlsx"); }).Dump("NPOI"); void Export<T>(List<T> data, string path) { IWorkbook workbook = new SXSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); var headRow = sheet.CreateRow(0); PropertyInfo[] props = typeof(User).GetProperties(); for (var i = 0; i < props.Length; ++i) { headRow.CreateCell(i).SetCellValue(props[i].Name); } for (var i = 0; i < data.Count; ++i) { var row = sheet.CreateRow(i + 1); for (var j = 0; j < props.Length; ++j) { row.CreateCell(j).SetCellValue(props[j].GetValue(data[i]).ToString()); } } using var file = File.Create(path); workbook.Write(file); workbook.Close(); } 输出结果
NPOI （2.6.1）（2023/7/12）输出结果
| 次数 | 分配内存 | 内存提高 | 耗时 |
|---|---|---|---|
| 1 | 571,769,144 | 11,495,488 | 2542 |
| 2 | 482,573,584 | 96 | 5106 |
| 3 | 481,139,296 | 24 | 1463 |
| 4 | 481,524,384 | 48 | 1510 |
| 5 | 481,466,616 | 48 | 1493 |
NPOI （2.4.1）（2018/12/18）输出结果
| 次数 | 分配内存 | 内存提高 | 耗时 |
|---|---|---|---|
| 1 | 660,709,472 | 537,512 | 7808 |
| 2 | 650,060,376 | 8,128 | 8649 |
| 3 | 649,006,952 | 4,136 | 7064 |
| 4 | 649,267,920 | -89,776 | 6973 |
| 5 | 649,955,024 | 48 | 6538 |
经过测试 发现SXSSFWorkbook 确实比XSSFWorkbook 性能好，有显著提升
由此看出 相比2018,到了2023年NPOI的性能得到了略微的提升
Aspose.Cells
List<User> users = LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"); Measure(() => { Export(users, null); }).Dump("Baseline"); string Export<T>(List<T> data, string path) { PropertyInfo[] props = typeof(User).GetProperties(); string noCache = null; for (var i = 0; i < props.Length; ++i) { noCache = props[i].Name; } for (var i = 0; i < data.Count; ++i) { for (var j = 0; j < props.Length; ++j) { noCache = props[j].GetValue(data[i]).ToString(); } } return noCache; } 输出结果
Aspose.Cells （23.8.0）（2023/8/9）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 9,878,784 | 19,248 | 82 |
| 2 | 9,854,688 | 96 | 63 |
| 3 | 9,854,688 | 24 | 59 |
| 4 | 9,854,688 | 24 | 62 |
| 5 | 10,106,352 | 24 | 72 |
Aspose.Cells （19.8.0）（2019/8/20）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 9,878,784 | 19,248 | 89 |
| 2 | 9,854,688 | 96 | 64 |
| 3 | 9,854,688 | 24 | 62 |
| 4 | 9,854,688 | 24 | 58 |
| 5 | 10,101,632 | 24 | 63 |
由此看出 相比2019,到了2023年Aspose.Cells的性能还是一样差不多
DocumentFormat.OpenXml
List<User> users = LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"test-data.json"); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"export.openXml.xlsx"); }).Dump("OpenXML"); void Export<T>(List<T> data, string path) { using SpreadsheetDocument excel = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = excel.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = excel.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); Sheet sheet = new Sheet { Id = excel.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); PropertyInfo[] props = typeof(User).GetProperties(); { // header var row = new Row() { RowIndex = 1 }; sheetData.Append(row); row.Append(props.Select((prop, i) => new Cell { CellReference = ('A' + i - 1) + row.RowIndex.Value.ToString(), CellValue = new CellValue(props[i].Name), DataType = new EnumValue<CellValues>(CellValues.String), })); } sheetData.Append(data.Select((item, i) => { var row = new Row { RowIndex = (uint)(i + 2) }; row.Append(props.Select((prop, j) => new Cell { CellReference = ('A' + j - 1) + row.RowIndex.Value.ToString(), CellValue = new CellValue(props[j].GetValue(data[i]).ToString()), DataType = new EnumValue<CellValues>(CellValues.String), })); return row; })); excel.Save(); } 输出结果
DocumentFormat.OpenXml （2.20.0）（2023/4/7）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 614,013,080 | 421,552 | 3909 |
| 2 | 613,007,112 | 96 | 3487 |
| 3 | 613,831,672 | 104 | 3465 |
| 4 | 613,058,344 | 24 | 3650 |
| 5 | 613,161,096 | 24 | 3521 |
DocumentFormat.OpenXml （2.9.1）（2019/3/14）输出结果
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 542,724,752 | 139,080 | 3504 |
| 2 | 542,478,208 | 96 | 2897 |
| 3 | 543,030,904 | 24 | 2826 |
| 4 | 542,247,544 | 24 | 2957 |
| 5 | 542,763,312 | 24 | 2941 |
由此看出 相比2019,到了2023年DocumentFormat.OpenXml的性能反而越差啦
结论和总结
结论一：如果你想找开源，(旧版本免费)，（最新版收费）EPPlus 依旧是最佳选择
| 次数ΞΞ | 分配内存ΞΞ | 内存提高ΞΞ | 耗时ΞΞ |
|---|---|---|---|
| 1 | 454,869,176 | 970,160 | 2447 |
| 2 | 440,353,488 | 176 | 1776 |
| 3 | 440,062,264 | 0 | 1716 |
| 4 | 440,283,584 | 0 | 1750 |
| 5 | 440,653,264 | 0 | 1813 |
结论二：如果你想找速度快，很稳定，但收费的，Aspose.Cells 依旧是最佳选择
| 次数ΞΞ | 分配内存 | 内存提高 | 耗时ΞΞ |
|---|---|---|---|
| 1 | 9,878,784 | 19,248 | 82 |
| 2 | 9,854,688 | 96 | 63 |
| 3 | 9,854,688 | 24 | 59 |
| 4 | 9,854,688 | 24 | 62 |
| 5 | 10,106,352 | 24 | 72 |
总结:
1、EPPlus表现不错，内存和耗时在开源组中表现最佳
2、收费的Aspose.Cells表现最佳，内存占用最低，用时也最短
作者 => 百宝门瞿佑明
此文章是对此前《.NET骚操作》2019年写的文章的更新和扩展
https://www.cnblogs.com/sdflysha/p/20190824-dotnet-excel-compare.html
原文地址：https://blog.baibaomen.com/2023版：深度比较几种-net-excel导出库的性能差异/
