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


引言

背景和目的

本文介绍了几个常用的电子表格处理库,包括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万条标准化的测试数据。

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输出结果

IdGenderFirstNameLastNameEmailBirthDateCompanyPhoneWebsiteSSN
1MaleAntonioPaucekAntonio.Paucek@gmail.com1987/10/31 5:46:50Moen, Willms and Maggio(898) 283-1583 x88626pamela.name850-06-4706
2MaleKurtGerholdKurt.Gerhold40@yahoo.com1985/11/1 18:41:01Wilkinson and Sons(698) 637-0181 x49124cordelia.net014-86-1757
3MaleHowardHegmannHoward2@hotmail.com1979/7/20 22:35:40Kassulke, Murphy and Volkman(544) 464-9818 x98381kari.com360-23-1669
4FemaleRosemariePowlowskiRosemarie.Powlowski48@hotmail.com1964/5/18 1:35:45Will Group1-740-705-6482laurence.net236-10-9925
5FemaleEuniceRogahnEunice84@gmail.com1979/11/25 11:53:14Rippin - Rowe(691) 491-2282 x3466yvette.net219-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)输出结果

次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ
1454,869,176970,1602447
2440,353,4881761776
3440,062,26401716
4440,283,58401750
5440,653,26401813

EPPlus (4.5.3.2)(2019/6/16)输出结果

次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ
1963,850,944192,0482765
2509,450,7926001897
3509,872,1604241920
4509,858,5764241989
5509,651,5124242076

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

次数ΞΞ分配内存内存提高耗时ΞΞ
11,589,285,792567,2725549
21,577,028,664967043
31,577,398,488488107
41,576,360,696-90,5129336
51,576,226,688-3,1208289

NPOI (2.4.1)(2018/12/18)输出结果

次数ΞΞ分配内存内存提高耗时ΞΞ
11,648,548,696526,8246947
21,633,685,1361207921
31,634,033,296248864
41,634,660,176-90,2008945
51,634,205,368-2,5848078

示例代码二: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)输出结果

次数分配内存内存提高耗时
1571,769,14411,495,4882542
2482,573,584965106
3481,139,296241463
4481,524,384481510
5481,466,616481493

NPOI (2.4.1)(2018/12/18)输出结果

次数分配内存内存提高耗时
1660,709,472537,5127808
2650,060,3768,1288649
3649,006,9524,1367064
4649,267,920-89,7766973
5649,955,024486538

经过测试 发现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)输出结果

次数分配内存内存提高耗时
1443,025,1123,471,9842889
2392,090,30430,2081863
3391,419,072-81716
4392,041,144241797
5392,078,992241689

Aspose.Cells (19.8.0)(2019/8/20)输出结果

次数分配内存内存提高耗时
1552,862,0562,987,0002913
2508,337,87249,7761750
3507,922,728241933
4507,949,584241781
5508,368,208241773

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

次数ΞΞ分配内存内存提高耗时ΞΞ
1614,013,080421,5523909
2613,007,112963487
3613,831,6721043465
4613,058,344243650
5613,161,096243521

DocumentFormat.OpenXml (2.9.1)(2019/3/14)输出结果

次数ΞΞ分配内存内存提高耗时ΞΞ
1542,724,752139,0803504
2542,478,208962897
3543,030,904242826
4542,247,544242957
5542,763,312242941

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

结论和总结

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

次数ΞΞ分配内存ΞΞ内存提高ΞΞ耗时ΞΞ
1454,869,176970,1602447
2440,353,4881761776
3440,062,26401716
4440,283,58401750
5440,653,26401813

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

次数分配内存内存提高耗时
1443,025,1123,471,9842889
2392,090,30430,2081863
3391,419,072-81716
4392,041,144241797
5392,078,992241689

总结:
1、EPPlus表现不错,内存和耗时在开源组中表现最佳
2、收费的Aspose.Cells表现最佳,内存占用最低,用时也最短

作者 => 百宝门瞿佑明

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

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值