.Net下C#針對Excel開發控制項彙總(ClosedXML,EPPlus,NPOI)

最近項目中需要一個導出Excel報告的功能，假期搜了一下，把其中比較主流的列一下，僅供參考。

功能需求：

1. 創建並寫入.xlsx Excel2007+版本的電子錶格文件
2. 不需要office組件支持，終端電腦無需安裝ms office
3. 簡單的format，style,chart和formula支持（不用過於複雜），並且能夠插入圖片
4. 速度，保證數據在萬行以上表格寫入速度

效果圖：  

一、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

總體上EPPlus在速度和記憶體上都最佳，感覺ClosedXML在API調用上方便一些，文檔寫全面一些。

五、其他

SpreadSheetLight 之前項目使用的，讀寫都可以，需要OpenXMLSDK 2.5

ExcelDataReader Excel 03-07 文件讀取，只需要快速讀取excel文件的可以用這個