引言
背景和目的
本文介绍了几个常用的电子表格处理库，包括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
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的性能还是一样差不多,只是内存占用减少了
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 | 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
