澳门金沙vip 1

澳门金沙vipC# NPOI导出Excel和EPPlus导出Excel比较

工作中要给产品经理写各种脚本拉数据、修改数据。这种批量是拼sql,Excel当然是最合适的。但是苦于Excel玩不转,之前一直用Visual
Studio Code
的多焦点编辑功能,即便如此,这在同事眼中已经是神器了。但是当遇到NimberText以后,VS
Code也要往后排了。
什么也不说了,直接上图:
澳门金沙vip 1

系统中经常会使用导出Excel的功能。

.Net下C#针对Excel开发控件汇总(ClosedXML,EPPlus,NPOI),

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

功能需求:

效果图:  

 澳门金沙vip 2

更多功能见官网
提示:官网免费版已经去掉了Where条件部分,已经满足大部分日常操作。如果需要,可使用dnspy进行破解,我已经成功破解完毕,但不方便提供破解版。

之前使用的是NPOI,但是导出数据行数多就报内存溢出。

一、ClosedXML

主页:

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

澳门金沙vip 3

            //创建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");
            }

View Code

 

最近看到EPPlus可以用来导出Excel,就自己测了下两者导出上的差异。

二、EPPlus

主页:

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

澳门金沙vip 4

            //创建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"));
            }

View Code

 

NPIO官网地址:

三、NPOI

官网:  .netcore
version:

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

参考:

   

 

 基于NPIO的Report控件

澳门金沙vip 5

            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);
            }

View Code

EPPlus官网地址:

四、Benchmarks

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

澳门金沙vip 6

            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);
            }

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

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调用上方便一些,文档写全面一些。

添加NPOI、EPPlus类库dll使用的是NuGet添加。

五、其他

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

ExcelDataReader Excel
03-07 文件读取,只需要快速读取excel文件的可以用这个

 

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

在类库References右键Manage NuGet Packages…,之后选择添加对应的dll。

测试结果显示,相同数据结构的数据,EPPlus的导出能力比NPOI强。