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<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 |
Email |
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
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)输出结果
次数 |
分配内存 |
内存提高 |
耗时 |
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的性能还是一样差不多,只是内存占用减少了
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();
}
输出结果
次数ΞΞ |
分配内存 |
内存提高 |
耗时ΞΞ |
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 |
次数ΞΞ |
分配内存 |
内存提高 |
耗时ΞΞ |
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导出库的性能差异/