引言背景和目的本文介绍了几个常用的电子表格处理库，包括EPPlus、NPOI、Aspose.Cells和DocumentFormat.OpenXml，我们将对这些库进行性能测评，以便为开发人员提供实际的性能指标和数据。
下表将功能/特点、开源/许可证这两列分开，以满足需求：功能 / 特点EPPlusNPOIAspose.CellsDocumentFormat.OpenXml开源是是否是许可证MITApache商业MIT
支持的 Excel 版本Excel 2007 及更高版本Excel 97-2003Excel 2003 及更高版本Excel 2007 及更高版本测评电脑配置组件规格CPU11th 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万条标准化的测试数据。
voidMain(){string path =Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop),"test-data.json"
);usingvar file =File.Create(path);usingvar writer =newUtf8JsonWriter(file,newJsonWriterOptions{Indented
=true});var data =newBogus.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输出结果IdGenderFirstNameLastName
EmailBirthDateCompanyPhoneWebsiteSSN1MaleAntonioPaucekAntonio.Paucek@gmail.com1987/10/31 5:46:50Moen, Willms and Maggio
(898) 283-1583 x88626pamela.name850-06-47062MaleKurtGerholdKurt.Gerhold40@yahoo.com1985/11/1 18:41:01
Wilkinson and Sons(698) 637-0181 x49124cordelia.net014-86-17573MaleHowardHegmannHoward2@hotmail.com1979/7/20 22:35:40
Kassulke, Murphy and Volkman(544) 464-9818 x98381kari.com360-23-16694FemaleRosemariePowlowskiRosemarie.Powlowski48@hotmail.com
1964/5/18 1:35:45Will Group1-740-705-6482laurence.net236-10-99255FemaleEuniceRogahnEunice84@gmail.com
1979/11/25 11:53:14Rippin - Rowe(691) 491-2282 x3466yvette.net219-75-6886……创建公共类方便正式测评使用voidMain(){string
path =Environment.GetFolderPath(Environment.SpecialFolder.Desktop)+@"\test-data.json";LoadUsers(path
).Dump();}ListLoadUsers(string jsonfile){string path = jsonfile;byte[] bytes =File.ReadAllBytes
(path);returnJsonSerializer.Deserialize(bytes);}IObservableMeasure(Action action,
int times =5){returnEnumerable.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();
returnnew{次数= i,分配内存=(allocate2 - allocate1).ToString("N0"),内存提高=(memory2 - memory1).ToString("N0"),耗时
= sw.ElapsedMilliseconds,};}).ToObservable();}classUser{publicintId{get;set;}publicintGender{get;set;
}publicstringFirstName{get;set;}publicstringLastName{get;set;}publicstringEmail{get;set;}publicDateTime
BirthDate{get;set;}publicstringCompany{get;set;}publicstringPhone{get;set;}publicstringWebsite{get;set
;}publicstring SSN {get;set;}}代码解释1、上面的代码单位是字节 (bytes)2 、其中IObservable（System.IObservable）是用于处理事件流的接口，它实现了观察者模式。
它表示一个可观察的序列，可以产生一系列的事件，并允许其他对象（观察者）来订阅和接收这些事件IObservable 适用于动态的、实时的事件流处理，允许观察者以异步方式接收事件，可以用于响应式编程、事件驱动的编程模型等。
3、GC.GetTotalAllocatedBytes(true) 获取分配内存大小GC.GetTotalMemory(true) 获取占用内存大小性能测评EPPlusstring 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");voidExport(List data,string path){usingvar stream =File.Create(path);usingvar
excel =newExcelPackage(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）输出结果
次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ1454,869,176970,16024472440,353,48817617763440,062,264017164440,283,584017505440,653,264
01813EPPlus （4.5.3.2）（2019/6/16）输出结果次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ1963,850,944192,04827652509,450,79260018973509,872,160
42419204509,858,57642419895509,651,5124242076由此看出 相比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");voidExport(List data,string path){IWorkbook workbook =newXSSFWorkbook();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）输出结果
次数ΞΞ分配内存内存提高耗时ΞΞ11,589,285,792567,272554921,577,028,66496704331,577,398,48848810741,576,360,696-90,512
933651,576,226,688-3,1208289NPOI （2.4.1）（2018/12/18）输出结果次数ΞΞ分配内存内存提高耗时ΞΞ11,648,548,696526,824694721,633,685,136
120792131,634,033,29624886441,634,660,176-90,200894551,634,205,368-2,5848078示例代码二：SXSSFWorkbookList
> users =LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder.Desktop)+@"\test-data.json");Measure
(()=>{Export(users,Environment.GetFolderPath(Environment.SpecialFolder.Desktop)+@"\export.npoi.xlsx");
}).Dump("NPOI");voidExport(List data,string path){IWorkbook workbook =newSXSSFWorkbook();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）输出结果
次数分配内存内存提高耗时1571,769,14411,495,48825422482,573,5849651063481,139,2962414634481,524,3844815105481,466,616
481493NPOI （2.4.1）（2018/12/18）输出结果次数分配内存内存提高耗时1660,709,472537,51278082650,060,3768,12886493649,006,952
4,13670644649,267,920-89,77669735649,955,024486538经过测试 发现SXSSFWorkbook 确实比XSSFWorkbook 性能好，有显著提升由此看出 相比2018,到了2023年NPOI的性能得到了略微的提升
Aspose.CellsUtil.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");voidExport(List data,string
path){usingvar excel =newWorkbook(); excel.Settings.MemorySetting=MemorySetting.Normal; excel.
Settings.CheckExcelRestriction=false;Worksheet sheet = excel.Worksheets["Sheet1"]; sheet.Cells.ImportCustomObjects
(data,0,0,newImportTableOptions{IsFieldNameShown=true,DateFormat="MM/DD/YYYY hh:mm:ss AM/PM",ConvertNumericData
=false,}); excel.Save(path);}voidSetLicense(){Stream stream =newMemoryStream(Convert.FromBase64String
(@"密钥")); stream.Seek(0,SeekOrigin.Begin);newAspose.Cells.License().SetLicense(stream);}输出结果Aspose.Cells （23.8.0）（2023/8/9）输出结果
次数分配内存内存提高耗时1443,025,1123,471,98428892392,090,30430,20818633391,419,072-817164392,041,1442417975392,078,992
241689Aspose.Cells （19.8.0）（2019/8/20）输出结果次数分配内存内存提高耗时1552,862,0562,987,00029132508,337,87249,7761750
3507,922,7282419334507,949,5842417815508,368,208241773由此看出 相比2019,到了2023年Aspose.Cells的性能还是一样差不多,只是内存占用减少了
DocumentFormat.OpenXmlList users =LoadUsers(Environment.GetFolderPath(Environment.SpecialFolder
.Desktop)+@"\test-data.json");Measure(()=>{Export(users,Environment.GetFolderPath(Environment.SpecialFolder
.Desktop)+@"\export.openXml.xlsx");}).Dump("OpenXML");voidExport(List data,string path){usingSpreadsheetDocument
excel =SpreadsheetDocument.Create(path,SpreadsheetDocumentType.Workbook);WorkbookPart workbookPart =
excel.AddWorkbookPart(); workbookPart.Workbook=newWorkbook();WorksheetPart worksheetPart = workbookPart
.AddNewPart(); worksheetPart.Worksheet=newWorksheet(newSheetData());Sheets sheets =
excel.WorkbookPart.Workbook.AppendChild(newSheets());Sheet sheet =newSheet{Id= excel.WorkbookPart
.GetIdOfPart(worksheetPart),SheetId=1,Name="Sheet1"}; sheets.Append(sheet);SheetData sheetData = worksheetPart
.Worksheet.GetFirstChild();PropertyInfo[] props =typeof(User).GetProperties();{// headervar
row =newRow(){RowIndex=1}; sheetData.Append(row); row.Append(props.Select((prop, i)=>new
Cell{CellReference=(A+ i -1)+ row.RowIndex.Value.ToString(),CellValue=newCellValue(props[i].Name),DataType
=newEnumValue(CellValues.String),}));} sheetData.Append(data.Select((item, i)=>{var row
=newRow{RowIndex=(uint)(i +2)}; row.Append(props.Select((prop, j)=>newCell{CellReference=(A+ j
-1)+ row.RowIndex.Value.ToString(),CellValue=newCellValue(props[j].GetValue(data[i]).ToString()),DataType
=newEnumValue(CellValues.String),}));return row;})); excel.Save();}输出结果DocumentFormat.OpenXml （2.20.0）（2023/4/7）输出结果
次数ΞΞ分配内存内存提高耗时ΞΞ1614,013,080421,55239092613,007,1129634873613,831,67210434654613,058,3442436505613,161,096
243521DocumentFormat.OpenXml （2.9.1）（2019/3/14）输出结果次数ΞΞ分配内存内存提高耗时ΞΞ1542,724,752139,08035042542,478,208
9628973543,030,9042428264542,247,5442429575542,763,312242941由此看出 相比2019,到了2023年DocumentFormat.OpenXml的性能反而越差啦
结论和总结结论一：如果你想找开源，(旧版本免费)，（最新版收费）EPPlus 依旧是最佳选择次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ1454,869,176970,16024472440,353,488
17617763440,062,264017164440,283,584017505440,653,26401813结论二：如果你想找速度快，很稳定，但收费的，Aspose.Cells 依旧是最佳选择次数
分配内存内存提高耗时1443,025,1123,471,98428892392,090,30430,20818633391,419,072-817164392,041,1442417975392,078,992
241689总结:1、EPPlus表现不错，内存和耗时在开源组中表现最佳2、收费的Aspose.Cells表现最佳，内存占用最低，用时也最短作者 => 百宝门瞿佑明此文章是对此前《.NET骚操作》2019年写的文章的更新和扩展
https://www.cnblogs.com/sdflysha/p/20190824-dotnet-excel-compare.html
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。