.NET导出Excel的四种方法及评测 - 周杰的代码骚操作
source link: https://www.cnblogs.com/sdflysha/p/20190824-dotnet-excel-compare.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
.NET导出Excel的四种方法及评测
导出Excel
是.NET
的常见需求,开源社区、市场上,都提供了不少各式各样的Excel
操作相关包。本文,我将使用NPOI
、EPPlus
、OpenXML
、Aspose.Cells
四个市面上常见的库,各完成一个导出Excel
示例。然后对其代码风格和性能做一个横向比较。最后我将说出我自己的感想。
文中所有的示例代码可以在这里下载:
https://github.com/sdcb/blog-data/tree/master/2019/20190824-dotnet-excel-compare
NPOI
源自于Java
写的Apache POI,目前最新版本是2.4.1
。NPOI
是开源项目,作者是华人,项目地址是:https://github.com/tonyqus/npoi。
几年前大家导出Excel
都使用COM
,但COM
不方便,这个组件的推出无疑弥补了.NET
在Excel
方面组件的空白,大家都说比COM
好用。
NPOI
还加入了.NET Core Community组织。
EPPlus
EPPlus
是另一个开源的Excel
操作库,目前最新版本是4.5.3.2
。Github
地址如下:https://github.com/JanKallman/EPPlus。
EPPlus
仅依赖基础类库(BCL
),完全没有第三方包依赖,也是.NET
原生库。
EPPlus
只支持导出Office 2007
之后的格式,也就是xlsx
。这已经是存在12年的格式了,但如果有客户想要导出xls
,EPPlus
将不支持。
OpenXML
OpenXML
的NuGet
包全称是DocumentFormat.OpenXml
:是微软推出的较为低层的Excel
操作库,最新稳定版本是2.9.1
。OpenXML
也是开源项目,地址是:https://github.com/OfficeDev/Open-XML-SDK。
从该项目的名字可以看出,OpenXML
比较涉及底层,因此很容易令人浮想联翩,感觉它的性能、速度很可能是最快的,但真的如此吗?
Aspose.Cells
这是Aspose Pty Ltd
公司推出的Excel
操作库。它是众多Aspose File Format API
产品其中之一。目前最新版本是19.8.0
(基于年/月)。Aspose
提供了应有尽有的文件格式支持,除了.NET
外,Aspose
还提供了C++
和Java
的包。
据我所知Aspose
的客户支持服务也不错,客户提出的问题经常可以在下一次发布时解决。
Aspose.Cells
是不开源,付费的库,但提供无限期的试用,据官方网站显示,试用版将:
- 限制打开文件数量100个
- 限制使用Aspose.Cells.GridWeb功能
- 生成的
Excel
将添加如下水印:
但经过我的试用,无论是并行还是串行,都没找到限制打开文件数量100个的限制。因此,“试用版”对我们的物理限制,就只有这个水印了(当然加了这个水印客户肯定也不会有好表情😂)。
Excel-COM
COM是随着Excel安装而自带的库,Excel
的包名叫Microsoft.Office.Interop.Excel
。本文不会深入解析,具体可以看这篇文档。
我想要多说两句的是,COM
的old-fashion
(过时)不是没有原因的,据我所知COM
有以下缺点:
- 调用时会启动一个进程外的
excel.exe
,可能因为它为是专门为Office设计的(不是为.NET
集成设计的) - 要求目标环境安装相关软件,没安装将无法运行
- 显然也没办法跨平台
- 使用了大量动态/多参数接口,对开发不是很友好
- 不像托管内存,
COM
对资源释放也有要求,具体参见这篇文章
NPOI | EPPlus | OpenXML | Aspose.Cells | |
---|---|---|---|---|
包依赖 | 有1个 | 无 | 无 | 无 |
封装程度 | 正常 | 正常 | 低层 | 正常 |
支持格式 | 完善 | 仅xlsx | 仅xlsx | 完善 |
开源协议 | Apache-2.0 | LGPL | MIT | 不开源 |
收费类型 | 开源免费 | 开源免费 | 开源免费 | 试用/付费 |
版本与数据
所有代码的版本号基于上文中提到的最新稳定版本:
NPOI | EPPlus | OpenXML | Aspose.Cells | |
---|---|---|---|---|
最新版本 | 2.4.1 | 4.5.3.2 | 2.9.1 | 19.8.0 |
数据全部基于我上篇文章使用的6万条/10列的数据,总共数据量19,166 KB
。所有数据可以从这里下载:
https://github.com/sdcb/blog-data/tree/master/2019/20190821-generate-lorem-data
项目 | 值 |
---|---|
CPU | E3-1230 v3 @ 3.30GHz |
内存 | 24GB DDR3-1600 MHz (8GBx3) |
操作系统 | Windows 10 1903 64位 |
电源选项 | 已设置为高性能 |
软件 | LINQPad 6.0.18 |
运行时 | .NET Core 3.0-preview8-28405-07 |
注意,LINQPad设置了optimize+
,代码都是优化后执行的;代码都指定了Util.NewProcess = true;
,确保每次运行都会在新进程中运行,不会互相影响。
我的性能测试函数介绍
IEnumerable<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,
};
});
}
除了时间,内存占用实际也是非常非常重要、但容易被人忽略的性能指标。大家都以为“内存不值钱”,但——
- 一旦访问量大,内存就会瞬间上涨,导致频繁
GC
,导致性能下降; - 内存高也会导致服务器分页,这时性能就会急剧下降;
- 吞吐量下降会导致队列排满,此时服务器就会报503等错误,客户就发现服务器“宕机了”。
(提示:除非你的客户真的愿意多花钱再升级一下服务器,否则不要提“内存不值钱”。)
在我的性能测试函数中,使用了如下两个函数来测试内存占用:
- GC.GetTotalAllocatedBytes(true) 获取分配内存大小
- GC.GetTotalMemory(true) 获取占用内存大小
占用内存可能会比分配内存小,因为存在垃圾回收(GC
),但GC
会影响性能。
通过调用Measure
函数,可以测得传入的action
的耗时和内存占用。默认会调用5次,可以从5次测试结果中取出能反映性能的值。
string Export<T>(List<T> data, string path)
{
PropertyInfo[] props = typeof(User).GetProperties();
string noCache = null;
for (var i = 0; i < props.Length; ++i)
{
noCache = props[i].Name;
}
for (var i = 0; i < data.Count; ++i)
{
for (var j = 0; j < props.Length; ++j)
{
noCache = props[j].GetValue(data[i]).ToString();
}
}
return noCache;
}
- 我有意使用了反射,这符合我们导出Excel代码简单、易学、好用、好扩展的愿意;
- 我有意使用了泛型T,而不是实际类型,这也让这些代码容易扩展;
- 里面的
noCache
用来规避编译器优化删除代码的行为
测试结果:
次数 | 分配内存 | 内存提高 | 耗时 |
---|---|---|---|
1 | 9,863,520 | 8,712 | 156 |
2 | 9,852,592 | 0 | 138 |
3 | 9,852,592 | 0 | 147 |
4 | 9,873,096 | 9,240 | 136 |
5 | 9,853,936 | 776 | 133 |
可见,基于反射操作6万/10列数据,每次需要分配约9MB
内存,但这些内存都会被快速GC
,最终内存提高较少。这些使用反射的代码运行耗时在130ms
-150ms
左右。
各个库的使用和性能表现
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);
}
-
里面用到了
XSSFWorkBook
,其中XSSF
这个前缀是从Java
的POI
库传过来的,全称是XML SpreadSheet Format
。这种前缀在
NPOI
包中很常见。 -
XSSFWorkbook
提供了bool Dispose()
方法,但它未实现(因此千万别调用它):
性能测试结果:
次数 | 分配内存 | 内存提高 | 耗时 |
---|---|---|---|
1 | 1,598,586,416 | 537,048 | 6590 |
2 | 1,589,239,728 | 7,712 | 10155 |
3 | 1,589,232,056 | -5,368 | 10309 |
4 | 1,589,237,064 | 7,144 | 10355 |
5 | 1,589,245,000 | 9,560 | 10594 |
分配内存稳定在1.48GB
的样子,首次内存会提高524KB
左右,后面趋于稳定。首次耗时6秒多,后面稳定在10秒多。
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();
}
注意,不同于NPOI
/Aspose.Cells
,EPPlus
的下标是基于1的(而不是0)。
次数 | 分配内存 | 内存提高 | 耗时 |
---|---|---|---|
1 | 534,970,328 | 156,048 | 3248 |
2 | 533,610,232 | 14,896 | 2807 |
3 | 533,595,936 | 7,648 | 2853 |
4 | 533,590,776 | 4,408 | 2742 |
5 | 533,598,440 | 11,280 | 2759 |
分配内存约508MB
,耗时首次稍长,约3.2秒,后面稳定在2.7-2.8秒。
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();
}
注意,因为OpenXML
比较偏低层,东西比较复杂,所以我们慢慢说:
-
对于一些对象,它需要创建相应的
Part
,如WorksheetPart
; -
Excel
可以使用SharedStringTable
来共享变量值,适合相同字符串非常多的场景。但此示例共享变量值收益很低,但会极大地增加代码复杂性(普通用户可能很难写出),因此本示例未使用
SharedStringTable
; -
它基于单元格位置标识,如B3(第三行第二列),因此索引方式比
EPPlus
/NPOI
都要复杂; -
代码示例中使用
('A' + i - 1)
来计算位置标识,因此这个示例不能用于超过26列(字母数)的数据; -
代码使用
LINQ
(而不是循环)来枚举所有行/列,可以让代码更简洁(在已经非常复杂的代码情况下)经测试,将
LINQ
改成for
循环对性能结果变化影响极其微小;
测试结果如下:
次数 | 分配内存 | 内存提高 | 耗时 |
---|---|---|---|
1 | 556,937,896 | 145,832 | 4009 |
2 | 555,981,216 | 312 | 3783 |
3 | 555,985,936 | 2,760 | 3884 |
4 | 555,984,384 | 1,872 | 3869 |
5 | 555,989,120 | 3,880 | 3704 |
内存占用约530MB
左右,第一次比后面多1MB的样子,耗时3.7-4.0秒之间。
Aspose.Cells
void Export<T>(List<T> data, string path)
{
using var excel = new Workbook();
Worksheet sheet = excel.Worksheets["Sheet1"];
PropertyInfo[] props = typeof(User).GetProperties();
for (var i = 0; i < props.Length; ++i)
{
sheet.Cells[0, i].Value = props[i].Name;
}
for (var i = 0; i < data.Count; ++i)
{
for (var j = 0; j < props.Length; ++j)
{
sheet.Cells[i + 1, j].Value = props[j].GetValue(data[i]);
}
}
excel.Save(path);
}
注意,Aspose.Cells
像Excel
软件一样,提供了Sheet1
/Sheet2
/Sheet3
三个默认的工作表,因此取这三个工作表时,不要创建,而是取出来。
性能测试结果如下:
次数 | 分配内存 | 内存提高 | 耗时 |
---|---|---|---|
1 | 404,004,944 | 3,619,520 | 3316 |
2 | 357,931,648 | 6,048 | 2078 |
3 | 357,934,744 | 7,216 | 2007 |
4 | 357,933,376 | 6,280 | 2017 |
5 | 357,933,360 | 6,424 | 2007 |
Aspose.Cells
首次占用内存385MB
,用于3.3秒,后面每次降低为内存341MB
,用时2.0秒。
四种导出Excel库的横向评测数据如下,数据取5次数值的内存消耗中位数,百分比以EPPlus
的测试数据为100%
基准:
分配内存 | 内存占比 | 耗时 | 耗时占比 | |
---|---|---|---|---|
基线(仅反射) | 9,853,936 | 1.85% | 133 | 4.82% |
NPOI | 1,589,237,064 | 297.83% | 10355 | 375.32% |
EPPlus | 533,598,440 | 100% | 2759 | 100% |
OpenXML | 555,985,936 | 104.19% | 3884 | 140.78% |
Aspose.Cells | 357,933,360 | 67% | 2007 | 72.74% |
可以得出以下结论:
- Demo基于反射,但反射总损耗的性能不高,内存、耗时均不超过5%;
- NPOI的性能表现是所有项目中最差的,每次需要分配1.5GB的内存和超过10秒的耗时;
- EPPlus表现不错,内存和耗时在开源组中表现最佳;
- 收费的
Aspose.Cells
表现最佳,内存占用最低,用时也最短; - 较为底层的
OpenXML
表现非常一般,比EPPlus
要差,更不能与收费的Aspose
相提并论;
在真的愿意尝试一下之前,人们很容易相信自己的直觉。底层库,通常能带来更大的可扩展性,能做出上层库很难做的事来。底层库有时性能会更快,就像更底层的C
/C++
比上层的JavaScript
更快一样。但事情也不都如此,如
- 更高层的
React.js
能在性能上将较底层的DOM
操作比下去 - 数据库基于集合的操作也比基于游标的操作要快得多
在导出Excel
这个例子中,我了解到Excel
的xlsx
格式是非常复杂的、多个xml
的集合。如果基于xml
做抽象——也是很正常的做法,拼出6万/10列的数据,需要至少60万个xml
标签做拼接,很显然这需要分配/浪费大量内存,因此性能上不来。
我基于以下几点无责任猜测:Aspose
内部可能没xml
做抽象,而是纯数据做抽象(就像React.js
那样),然后再统一写入到Excel
文件。因此性能可以达到其它库达不到的目标:
Aspose.Cells
对xml
等实现相关技术只字未提(可能因为要支持多种文件格式);Aspose.Cells
是先在内存中创建,再写入文件/流(NPOI也是
);Aspose.Cells
创建Excel
时要求客户直接使用Workbook
类(NPOI
也是);Aspose.Cells
完全隐藏了Excel
的位置(如B3
)信息,下标从0开始(NPOI
也是)
比较这几点,NPOI
也与Aspose.Cells
有几分相似,但导出不到6MB
的Excel
它内存分配居然高达1.5GB
,是后者的444%
!毕竟迭代更新了这么多年了,代码质量我相信应该没问题。因此我再次无责任推测:这可能因为它是从Java
那边移植过来的。
我的选择/推荐
在我做这个性能评测前,我一直使用的是EPPlus
,因为我不喜欢NPOI
有第三方依赖,也不喜欢NPOI
那些“XSSF
”之类的前缀命名,也显然不会去费心思写那么多费力不讨好的OpenXML
代码。
更别提这次评测发现EPPlus
的性能确实不错,唯一的缺点就是它单元格下标从1
开始的设计。即便如此,我还是首选推荐EPPlus
。
近期也经常使用Aspose.Cells
这种商业库,它的功能强大,API
清晰好用,这个评测也证明它的性能卓越。除了高昂的价格,没别的缺点了。乃有钱客户/老板的不二之选!
请关注我的微信公众号:【DotNet骚操作】,
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK