epplus 速度_.Net下C#针对Excel开发控件汇总(ClosedXML EPPlus NPOI)

最近项目中需要一个导出Excel报告的功能，假期搜了一下，把其中比较主流的列一下，仅供参考。

功能需求：

创建并写入.xlsxExcel+版本的电子表格文件

不需要office组件支持，终端电脑无需安装ms office

简单的format，style,chart和formula支持(不用过于复杂)，并且能够插入图片

速度，保证数据在万行以上表格写入速度

效果图：

一、ClosedXML

需要引用OpenXMLSDK(DocumentFormat.OpenXml.dll)，以简易面向对象的方式操作文件(类似Visual Basic for Applications (VBA))，文档和例子都比较完善

//创建workbook

using (var wb = newXLWorkbook(XLEventTracking.Disabled))

{//设置默认Style

var style =wb.Style;

style.Font.FontName= "Microsoft YaHei";

style.Font.FontSize= 11;//添加Sheets

var ws = wb.Worksheets.Add("Sheet001");

wb.Worksheets.Add("Sheet002");//手动cell赋值

ws.Cell(1, 1).Value = "Project";

ws.Cell(1, 2).Value = "Project001";

ws.Cell("A2").Value = "User";

ws.Cell("B2").Value = "User001";

ws.Cell(3, 1).SetValue("Create Date");

ws.Cell(3, 2).SetValue(DateTime.Now);//加重第一列文字

var rngHeader = ws.Range(1, 1, 3, 1);

rngHeader.Style

.Font.SetBold()

.Font.SetFontColor(XLColor.White)

.Fill.SetBackgroundColor(XLColor.SkyBlue)

.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);//合并cell

ws.Cell(5, 1).Value = "Data List";var rngTitle = ws.Range(5, 1, 5, 5);

rngTitle.Merge();//ws1.Row(5).Merge();

rngTitle.Style

.Font.SetBold()

.Font.SetFontSize(15)

.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);//插入表格或数据,设置Timespan format

var fakeData = Enumerable.Range(1, 5)

.Select(x=> newFakeData

{

Time= TimeSpan.FromSeconds(x * 123.667),

X=x,

Y= -x,

Address= "a" +x,

Distance= x * 100}).ToArray();var table = ws.Cell(6, 1).InsertTable(fakeData);

table.Style.Font.FontSize= 9;var data = ws.Cell(13, 1).InsertData(fakeData);

data.Style.Font.FontSize= 9;

ws.Range(7, 1, 18, 1).Style.DateFormat.Format = "HH:mm:ss.000";//插入图片

var image = ws.AddPicture("1.png");

image.MoveTo(ws.Cell(19, 1).Address);

image.Scale(0.3);//调整列距

ws.Columns().AdjustToContents();//会花费写入数据一倍的时间//保存文件

wb.SaveAs("ClosedXML.xlsx");

}

View Code

二、EPPlus

EPPlus不需要任何别的引用，文档和例子还算比较全

//创建workbook

using (var p = newExcelPackage())

{//添加Sheets

var ws= p.Workbook.Worksheets.Add("Sheet001");

p.Workbook.Worksheets.Add("Sheet002");//手动cell赋值

ws.Cells[1,1].Value = "Project";

ws.Cells[1, 2].Value = "Project001";

ws.Cells["A2"].Value = "User";

ws.Cells["B2"].Value = "User001";

ws.Cells[3,1].Value = "Create Date";

ws.Cells[3,2].Value =DateTime.Now;

ws.Cells[3, 2].Style.Numberformat.Format = "YYYY/MM/DD";//加重第一列文字

var rngHeader = ws.Cells[1, 1, 3, 1];

rngHeader.Style.Font.Bold= true;

rngHeader.Style.Font.Color.SetColor(System.Drawing.Color.White);

rngHeader.Style.Fill.PatternType=OfficeOpenXml.Style.ExcelFillStyle.Solid;

rngHeader.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DodgerBlue);

rngHeader.Style.HorizontalAlignment=OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//合并cell

ws.Cells[5, 1].Value = "Data List";var rngTitle = ws.Cells[5, 1, 5, 5];

rngTitle.Merge= true;

rngTitle.Style.Font.Size= 15;

rngTitle.Style.Font.Bold= true;

rngTitle.Style.HorizontalAlignment=OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//插入表格或数据,设置Timespan format

var fakeData = Enumerable.Range(1, 5)

.Select(x=> newFakeData

{

Time= TimeSpan.FromSeconds(x * 123.667),

X=x,

Y= -x,

Address= "a" +x,

Distance= x * 100}).ToArray();

ws.Cells[6, 1].LoadFromCollection(fakeData, true, OfficeOpenXml.Table.TableStyles.Medium27);

ws.Cells[13, 1].LoadFromArrays(

fakeData.Select(x=> new object[] {x.Time, x.X, x.Y, x.Address, x.Distance}));

ws.Cells[6, 1, 18, 1].Style.Numberformat.Format = "HH:mm:ss.000";//插入图片

var image = ws.Drawings.AddPicture("picture", new FileInfo("1.png"));

image.From.Row= 19;

image.From.Column= 0;

image.SetSize(30);//设置默认Style

ws.Cells[ws.Dimension.Address].Style.Font.Name = "Microsoft YaHei";//调整列距

ws.Cells.AutoFitColumns(0);//会花费写入数据一倍的时间//保存文件

p.SaveAs(new FileInfo("EPPlus.xlsx"));

}

View Code

三、NPOI

需要引用SharpZipLib，可以读写Word和Excel，例子比较全，系统点的文档没有找到，不过是国人的开源项目，百度应该能找到很多

参考：

using (var fs = new FileStream("NPOI.xlsx", FileMode.Create, FileAccess.Write))

{//创建workbook

IWorkbook wb = newXSSFWorkbook();//添加Sheets

var ws = wb.CreateSheet("Sheet001");

wb.CreateSheet("Sheet002");//手动cell赋值

ws.CreateRow(0).CreateCell(0).SetCellValue("Project");

ws.CreateRow(0).CreateCell(1).SetCellValue("Project001");

ws.CreateRow(1).CreateCell(0).SetCellValue("User");

ws.CreateRow(1).CreateCell(1).SetCellValue("User001");

ws.CreateRow(2).CreateCell(0).SetCellValue("Create Date");

ws.CreateRow(2).CreateCell(1).SetCellValue(DateTime.Now);

wb.Write(fs);

}

View Code

四、Benchmarks

以上三个控件的简单测试，10000条数据写入

using (var wb = newXLWorkbook(XLEventTracking.Disabled))

{var ws = wb.AddWorksheet("1");

ws.Column(1).Style.DateFormat.Format = "HH:mm:ss.000";int rowCount = 1;foreach (var fakeData indata)

{

rowCount++;

ws.Cell(rowCount,1).Value =fakeData.Time;

ws.Cell(rowCount,2).Value =fakeData.X;

ws.Cell(rowCount,3).Value =fakeData.Distance;

ws.Cell(rowCount,4).Value =fakeData.Address;

}

wb.SaveAs("ClosedXML.xlsx");

}using (var wb = newExcelPackage())

{var ws = wb.Workbook.Worksheets.Add("1");

ws.Column(1).Style.Numberformat.Format = "HH:mm:ss.000";

ws.Cells[1, 1].LoadFromCollection(data,true,

OfficeOpenXml.Table.TableStyles.Medium2,

System.Reflection.BindingFlags.Public|System.Reflection.BindingFlags.Instance,newSystem.Reflection.MemberInfo[]

{typeof(FakeData).GetProperty("Time"),typeof(FakeData).GetProperty("X"),typeof(FakeData).GetProperty("Distance"),typeof(FakeData).GetProperty("Address")

});

wb.SaveAs(new FileInfo("EPPlus.xlsx"));

}using (var fs = new FileStream("NPOI.xlsx", FileMode.Create, FileAccess.Write))

{var wb = newXSSFWorkbook();var ws = wb.CreateSheet("1");int rowCount = 0;

IRow row;foreach (var fakeData indata)

{

row= ws.CreateRow(rowCount++);

row.CreateCell(0).SetCellValue(fakeData.Time.ToString(@"hh\:mm\:ss\.fff"));

row.CreateCell(1).SetCellValue(fakeData.X);

row.CreateCell(2).SetCellValue(fakeData.Distance);

row.CreateCell(3).SetCellValue(fakeData.Address);

}

wb.Write(fs);

}

View Code

BenchmarkDotNet=v0.10.9, OS=Windows 10 Redstone 2 (10.0.15063)

Processor=Intel Core i7-6700K CPU 4.00GHz (Skylake), ProcessorCount=8

Frequency=3914068 Hz, Resolution=255.4887 ns, Timer=TSC

[Host] : .NET Framework 4.7 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.2110.0

Job-EJASFH : .NET Framework 4.7 (CLR 4.0.30319.42000), 32bit LegacyJIT-v4.7.2110.0

Method

MeanErrorStdDevGen 0Gen 1Gen 2Allocated

ClosedXML

337.6 ms

NA

2.5647 ms

9625.0000

7062.5000

2812.5000

47.26 MB

EPPlus

145.8 ms

NA

0.2533 ms

5000.0000

3250.0000

2000.0000

24.68 MB

NPOI

263.4 ms

NA

5.8716 ms

10500.0000

7343.7500

2375.0000

55.65 MB

总体上EPPlus在速度和内存上都最佳，感觉ClosedXML在API调用上方便一些，文档写全面一些。

五、其他

SpreadSheetLight之前项目使用的，读写都可以，需要OpenXMLSDK 2.5

ExcelDataReaderExcel 03-07文件读取，只需要快速读取excel文件的可以用这个