2023版：深度比较几种.NET Excel导出库的性能差异

2024-07-11 18:50| 来源: 网络整理| 查看: 265

2023版：深度比较几种.NET Excel导出库的性能差异引言背景和目的

本文介绍了几个常用的电子表格处理库，包括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() .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 Email BirthDate Company Phone Website SSN 1 Male Antonio Paucek [email protected] 1987/10/31 5:46:50 Moen, Willms and Maggio (898) 283-1583 x88626 pamela.name 850-06-4706 2 Male Kurt Gerhold [email protected] 1985/11/1 18:41:01 Wilkinson and Sons (698) 637-0181 x49124 cordelia.net 014-86-1757 3 Male Howard Hegmann [email protected] 1979/7/20 22:35:40 Kassulke, Murphy and Volkman (544) 464-9818 x98381 kari.com 360-23-1669 4 Female Rosemarie Powlowski [email protected] 1964/5/18 1:35:45 Will Group 1-740-705-6482 laurence.net 236-10-9925 5 Female Eunice Rogahn [email protected] 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 LoadUsers(string jsonfile) { string path = jsonfile; byte[] bytes = File.ReadAllBytes(path); return JsonSerializer.Deserialize(bytes); } IObservable 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 users = LoadUsers(path); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\export.epplus.xlsx"); }).Dump("EPPlus"); void Export(List 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 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(List 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 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(List 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 Util.NewProcess = true; List users = LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\test-data.json"); SetLicense(); Measure(() => { Export(users, Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"\export.aspose2.xlsx"); }, 5).Dump("Aspose"); void Export(List data, string path) { using var excel = new Workbook(); excel.Settings.MemorySetting = MemorySetting.Normal; excel.Settings.CheckExcelRestriction = false; Worksheet sheet = excel.Worksheets["Sheet1"]; sheet.Cells.ImportCustomObjects(data, 0, 0, new ImportTableOptions { IsFieldNameShown = true, DateFormat = "MM/DD/YYYY hh:mm:ss AM/PM", ConvertNumericData = false, }); excel.Save(path); } void SetLicense() { Stream stream = new MemoryStream(Convert.FromBase64String(@"密钥")); stream.Seek(0, SeekOrigin.Begin); new Aspose.Cells.License().SetLicense(stream); }

输出结果

Aspose.Cells （23.8.0）（2023/8/9）输出结果次数分配内存内存提高耗时 1 443,025,112 3,471,984 2889 2 392,090,304 30,208 1863 3 391,419,072 -8 1716 4 392,041,144 24 1797 5 392,078,992 24 1689 Aspose.Cells （19.8.0）（2019/8/20）输出结果次数分配内存内存提高耗时 1 552,862,056 2,987,000 2913 2 508,337,872 49,776 1750 3 507,922,728 24 1933 4 507,949,584 24 1781 5 508,368,208 24 1773

由此看出相比2019,到了2023年Aspose.Cells的性能还是一样差不多,只是内存占用减少了

DocumentFormat.OpenXml List 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(List data, string path) { using SpreadsheetDocument excel = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook); WorkbookPart workbookPart = excel.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = excel.WorkbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet { Id = excel.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild(); 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.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.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 443,025,112 3,471,984 2889 2 392,090,304 30,208 1863 3 391,419,072 -8 1716 4 392,041,144 24 1797 5 392,078,992 24 1689

总结: 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导出库的性能差异/

【本文地址】

公司简介

联系我们