背景和目的
本文介绍了几个常用的电子表格处理库，包括EPPlus、NPOI、Aspose.Cells和DocumentFormat.OpenXml，我们将对这些库进行性能测评，以便为开发人员提供实际的性能指标和数据。
下表将功能/特点、开源/许可证这两列分开，以满足需求：
功能 / 特点
EPPlus
Aspose.Cells
DocumentFormat.OpenXml
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
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）输出结果
分配内存ΞΞ
内存提高ΞΞ
示例代码一：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）输出结果
示例代码二：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）输出结果
经过测试 发现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）输出结果
由此看出 相比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();
作者 => 百宝门瞿佑明
此文章是对此前《.NET骚操作》2019年写的文章的更新和扩展
https://www.cnblogs.com/sdflysha/p/20190824-dotnet-excel-compare.html
原文地址：https://blog.baibaomen.com/2023版：深度比较几种-net-excel导出库的性能差异/
