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

相关文章推荐

霸气的蘑菇 · 手动部署 ClickOnce ...· 7 月前 ·

不羁的风衣 · WPF中Button点击后闪烁的解决办法_b ...· 10 月前 ·

安静的匕首 · 我院学生获得Google ...· 1 年前 ·

光明磊落的烤面包 · Nuxt.js实践篇 - 狗尾草的博客 - 博客园· 1 年前 ·

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

引言背景和目的

本文介绍了几个常用的电子表格处理库，包括EPPlus、NPOI、Aspose.Cells和DocumentFormat.OpenXml，我们将对这些库进行性能测评，以便为开发人员提供实际的性能指标和数据。

下表将功能/特点、开源/许可证这两列分开，以满足需求：

功能 / 特点 EPPlus Aspose.Cells DocumentFormat.OpenXml Apache 支持的 Excel 版本 Excel 2007 及更高版本 Excel 97-2003 Excel 2003 及更高版本 Excel 2007 及更高版本测评电脑配置 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输出结果

Gender FirstName LastName Email BirthDate Company Phone Website 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 Gerhold Kurt.Gerhold40@yahoo.com 1985/11/1 18:41:01 Wilkinson and Sons (698) 637-0181 x49124 cordelia.net 014-86-1757 Howard Hegmann Howard2@hotmail.com 1979/7/20 22:35:40 Kassulke, Murphy and Volkman (544) 464-9818 x98381 kari.com 360-23-1669 Female Rosemarie Powlowski Rosemarie.Powlowski48@hotmail.com 1964/5/18 1:35:45 Will Group 1-740-705-6482 laurence.net 236-10-9925 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）输出结果

分配内存ΞΞ 内存提高ΞΞ 454,869,176 970,160 440,353,488 440,062,264 440,283,584 440,653,264 EPPlus （4.5.3.2）（2019/6/16）输出结果分配内存ΞΞ 内存提高ΞΞ 963,850,944 192,048 509,450,792 509,872,160 509,858,576 509,651,512

由此看出相比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,589,285,792 567,272 1,577,028,664 1,577,398,488 1,576,360,696 -90,512 1,576,226,688 -3,120 NPOI （2.4.1）（2018/12/18）输出结果 1,648,548,696 526,824 1,633,685,136 1,634,033,296 1,634,660,176 -90,200 1,634,205,368 -2,584 示例代码二：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）输出结果

571,769,144 11,495,488 482,573,584 481,139,296 481,524,384 481,466,616 NPOI （2.4.1）（2018/12/18）输出结果 660,709,472 537,512 650,060,376 8,128 649,006,952 4,136 649,267,920 -89,776 649,955,024

经过测试发现SXSSFWorkbook 确实比XSSFWorkbook 性能好，有显著提升

由此看出相比2018,到了2023年NPOI的性能得到了略微的提升

Aspose.Cells

Util.NewProcess = true;

List<User> 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<T>(List<T> 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）输出结果

443,025,112 3,471,984 392,090,304 30,208 391,419,072 392,041,144 392,078,992 Aspose.Cells （19.8.0）（2019/8/20）输出结果 552,862,056 2,987,000 508,337,872 49,776 507,922,728 507,949,584 508,368,208

由此看出相比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.<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）输出结果

614,013,080 421,552 613,007,112 613,831,672 613,058,344 613,161,096 DocumentFormat.OpenXml （2.9.1）（2019/3/14）输出结果 542,724,752 139,080 542,478,208 543,030,904 542,247,544 542,763,312

由此看出相比2019,到了2023年DocumentFormat.OpenXml的性能反而越差啦

结论和总结

结论一：如果你想找开源，(旧版本免费)，（最新版收费） EPPlus 依旧是最佳选择

分配内存ΞΞ 内存提高ΞΞ 454,869,176 970,160 440,353,488 440,062,264 440,283,584 440,653,264

结论二：如果你想找速度快，很稳定，但收费的， Aspose.Cells 依旧是最佳选择

443,025,112 3,471,984 392,090,304 30,208 391,419,072 392,041,144 392,078,992

1、EPPlus表现不错，内存和耗时在开源组中表现最佳

2、收费的Aspose.Cells表现最佳，内存占用最低，用时也最短

作者 => 百宝门瞿佑明

此文章是对此前《.NET骚操作》2019年写的文章的更新和扩展 https://www.cnblogs.com/sdflysha/p/20190824-dotnet-excel-compare.html 返回搜狐，查看更多

责任编辑：

平台声明：该文观点仅代表作者本人，搜狐号系信息发布平台，搜狐仅提供信息存储空间服务。

阅读 ( )