NPOI 导出 excel 性能测试

Intro

网上看到很多人说 NPOI 的性能不行，自己写了一个 NPOI 的扩展库，于是想尝试看看 NPOI 的性能究竟怎么样，道听途说始终不如自己动手一试。

测试环境

测试工具：

• BenchmarkDotNet v0.11.5
• EPPlus.Core.Extensions v2.3.2
• EPPlus v4.5.3.1

测试代码：（Github 源码）

1. 1. 
      
      SimpleJob
      (
      	launchCount
      	:
      	1
      	,
      	warmupCount
      	:
      	1
      	,
      	targetCount
      	:
      	5
      )]
      
      
      [
      	MemoryDiagnoser
      ]
      
      
      [
      	MinColumn
      	,
      	MaxColumn
      	,
      	MeanColumn
      	,
      	MedianColumn
      ]
      
      
      public
      class
      WorkbookBasicTest
      
      
      {
      	private
      	const
      	int
      		ColsCount
      		=
      			10
      		;
      
      
      	[
      		Params
      		(
      			10000
      			,
      			30000
      			,
      			50000
      			,
      			65535
      		)]
      
      
      	public
      	int
      		RowsCount
      	;
      
      
      	[
      		Benchmark
      		(
      			Baseline
      				=
      					true
      		)]
      
      
      	public
      	byte
      	[]
      	NpoiXlsWorkbookInit
      		()
      
      
      	{
      		var
      			workbook
      			=
      				ExcelHelper
      				.
      				PrepareWorkbook
      				(
      					ExcelFormat
      					.
      					Xls
      				);
      
      
      		var
      			sheet
      			=
      				workbook
      				.
      				CreateSheet
      				(
      					"tempSheet"
      				);
      
      
      		for
      		(
      			var
      			i
      				=
      					0
      				;
      			i
      			<
      			RowsCount
      			;
      			i
      			++ )
      
      
      		{
      			var
      				row
      				=
      					sheet
      					.
      					CreateRow
      					(
      						i
      					);
      
      
      			for
      			(
      				var
      				j
      					=
      						0
      					;
      				j
      				<
      				ColsCount
      				;
      				j
      				++ )
      
      
      			{
      				var
      					cell
      					=
      						row
      						.
      						CreateCell
      						(
      							j
      						);
      
      
      				cell
      				.
      				SetCellValue
      				(
      					$
      					"as ({i}, {j}) sa"
      				);
      			}
      		}
      
      
      		return
      			(workbook
      			 .
      			 ToExcelBytes
      				 () );
      	}
      
      
      	[
      		Benchmark
      	]
      
      
      	[
      		MethodImpl
      		(
      			MethodImplOptions
      			.
      			NoInlining
      		)]
      
      
      	public
      	byte
      	[]
      	NpoiXlsxWorkbookInit
      		()
      
      
      	{
      		var
      			workbook
      			=
      				ExcelHelper
      				.
      				PrepareWorkbook
      				(
      					ExcelFormat
      					.
      					Xlsx
      				);
      
      
      		var
      			sheet
      			=
      				workbook
      				.
      				CreateSheet
      				(
      					"tempSheet"
      				);
      
      
      		for
      		(
      			var
      			i
      				=
      					0
      				;
      			i
      			<
      			RowsCount
      			;
      			i
      			++ )
      
      
      		{
      			var
      				row
      				=
      					sheet
      					.
      					CreateRow
      					(
      						i
      					);
      
      
      			for
      			(
      				var
      				j
      					=
      						0
      					;
      				j
      				<
      				ColsCount
      				;
      				j
      				++ )
      
      
      			{
      				var
      					cell
      					=
      						row
      						.
      						CreateCell
      						(
      							j
      						);
      
      
      				cell
      				.
      				SetCellValue
      				(
      					$
      					"as ({i}, {j}) sa"
      				);
      			}
      		}
      
      
      		return
      			(workbook
      			 .
      			 ToExcelBytes
      				 () );
      	}
      
      
      	[
      		Benchmark
      	]
      
      
      	[
      		MethodImpl
      		(
      			MethodImplOptions
      			.
      			NoInlining
      		)]
      
      
      	public
      	byte
      	[]
      	EpplusWorkbookInit
      		()
      
      
      	{
      		var
      			excel
      			=
      				new
      				ExcelPackage
      					();
      
      
      		var
      			sheet
      			=
      				excel
      				.
      				Workbook
      				.
      				Worksheets
      				.
      				Add
      				(
      					"tempSheet"
      				);
      
      
      		for
      		(
      			var
      			i
      				=
      					1
      				;
      			i
      			<=
      			RowsCount
      			;
      			i
      			++ )
      
      
      		{
      			for
      			(
      				var
      				j
      					=
      						1
      					;
      				j
      				<=
      				ColsCount
      				;
      				j
      				++ )
      
      
      			{
      				sheet
      				.
      				Cells
      				[
      					i
      					,
      					j
      				].
      				Value
      					=
      						$
      						"as ({i}, {j}) sa"
      					;
      			}
      		}
      
      
      		return
      			(excel
      			 .
      			 GetAsByteArray
      				 () );
      	}
      }
      
      
      

测试结果

在 Github 上查看结果 https://github.com/WeihanLi/WeihanLi.Npoi/blob/dev/perf/WeihanLi.Npoi.Benchmark/BenchmarkDotNet.Artifacts/results/WeihanLi.Npoi.Benchmark.WorkbookBasicTest-report-github.md

BenchmarkDotNet
	=
		v0
		.
		11.5
	,
OS
	=
		Windows
		10.0
		.
		18362


		Intel
		Core
		i5
		-
		3470
		CPU
		3.20GHz
		(
			Ivy
			Bridge
		),
1
CPU
,
4
logical
and
4
physical cores


.
NET
Core
SDK
	=
		3.0
		.
		100


		[
			Host
		]
		:
		.
		NET
		Core
		2.2
		.
		6
		(
			CoreCLR
			4.6
			.
			27817.03
			,
			CoreFX
			4.6
			.
			27818.02
		),
64bit
RyuJIT


Job
-
CBYTBY
:
.
NET
Core
2.2
.
6
(
	CoreCLR
	4.6
	.
	27817.03
	,
	CoreFX
	4.6
	.
	27818.02
),
64bit
RyuJIT


IterationCount
	=
		5
		LaunchCount
			=
				1
				WarmupCount
					=
						1