最近项目中需要一个导出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文件的可以用这个