.Net下C#针对Excel开发控件汇总(ClosedXML,EPPlus,NPOI)
最近项目中需要一个导出Excel报告的功能，假期搜了一下，把其中比较主流的列一下，仅供参考。
功能需求：
- 创建并写入.xlsx Excel2007+版本的电子表格文件
- 不需要office组件支持，终端电脑无需安装ms office
- 简单的format，style,chart和formula支持（不用过于复杂），并且能够插入图片
- 速度，保证数据在万行以上表格写入速度
效果图：
一、ClosedXML
主页：https://github.com/ClosedXML/ClosedXML
需要引用OpenXMLSDK（DocumentFormat.OpenXml.dll），以简易面向对象的方式操作文件（类似Visual Basic for Applications (VBA)），文档和例子都比较完善
//创建workbook using (var wb = new XLWorkbook(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 => new FakeData { 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"); }
二、EPPlus
主页：https://github.com/JanKallman/EPPlus/
EPPlus不需要任何别的引用，文档和例子还算比较全
//创建workbook using (var p = new ExcelPackage()) { //添加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 => new FakeData { 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")); }
三、NPOI
官网：https://github.com/tonyqus/npoi .netcore version:https://github.com/dotnetcore/NPOI
需要引用SharpZipLib，可以读写Word和Excel，例子比较全，系统点的文档没有找到，不过是国人的开源项目，百度应该能找到很多
参考：
http://blog.csdn.net/pan_junbiao/article/details/39717443
http://www.cnblogs.com/yinrq/p/5590970.html
http://www.cnblogs.com/hanzhaoxin/p/4232572.html 基于NPIO的Report控件
using (var fs = new FileStream("NPOI.xlsx", FileMode.Create, FileAccess.Write)) { //创建workbook IWorkbook wb = new XSSFWorkbook(); //添加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); }
四、Benchmarks
以上三个控件的简单测试，10000条数据写入
using (var wb = new XLWorkbook(XLEventTracking.Disabled)) { var ws = wb.AddWorksheet("1"); ws.Column(1).Style.DateFormat.Format = "HH:mm:ss.000"; int rowCount = 1; foreach (var fakeData in data) { 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 = new ExcelPackage()) { 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, new System.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 = new XSSFWorkbook(); var ws = wb.CreateSheet("1"); int rowCount = 0; IRow row; foreach (var fakeData in data) { 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); }
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 | Mean | Error | StdDev | Gen 0 | Gen 1 | Gen 2 | Allocated |
|---|---|---|---|---|---|---|---|
| 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
ExcelDataReader Excel 03-07 文件读取，只需要快速读取excel文件的可以用这个
文章来自：http://www.cnblogs.com/ryantocode/p/excelwriter.html