C # Summary of Excel Development controls under. Net (ClosedXML, EPPlus, NPOI ),

C # Summary of Excel Development controls under. Net (ClosedXML, EPPlus, NPOI ),

Recently, an Excel report export function is required in the project. I searched for the Excel report during the holidays and listed the mainstream reports for reference only.

Functional requirements:

:

 

I. ClosedXML

Home: https://github.com/ClosedXML/ClosedXML

OpenXMLSDK (DocumentFormat. OpenXml. dll) must be referenced to operate files in a simple object-oriented manner (similar to Visual Basic for Applications (VBA). The documentation and examples are complete.

// Create a workbook using (var wb = new XLWorkbook (XLEventTracking. disabled) {// set the default Style var style = wb. style; style. font. fontName = "Microsoft YaHei"; style. font. fontSize = 11; // Add Sheets var ws = wb. worksheets. add ("Sheet001"); wb. worksheets. add ("Sheet002"); // manually assign ws values to cells. 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); // increase the first column of text var rngHeader = ws. range (1, 1, 3, 1); rngHeader. style. font. setBold (). font. setFontColor (XLColor. white ). fill. setBackgroundColor (XLColor. skyBlue ). alignment. setHorizontal (XLAlignmentHorizontalValues. center); // merge 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); // Insert table or data and set 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"; // Insert the image var image = ws. addPicture ("1.png"); image. moveTo (ws. cell (19, 1 ). address); image. scale (0.3); // adjust the column distance to ws. columns (). adjustToContents (); // It Takes twice the time to write data. // save the file wb. saveAs ("ClosedXML.xlsx ");}

View Code

 

Ii. EPPlus

Home: https://github.com/JanKallman/EPPlus/

EPPlus does not require any reference, and the documentation and examples are fairly complete.

// Create workbook using (var p = new ExcelPackage () {// Add Sheets var ws = p. workbook. worksheets. add ("Sheet001"); p. workbook. worksheets. add ("Sheet002"); // manually assign ws values to cells. 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"; // adds the first column of text 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; // merge 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; // Insert table or data and set 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"; // Insert the image var image = ws. drawings. addPicture ("picture", new FileInfo ("1.png"); image. from. row = 19; image. from. column = 0; image. setSize (30); // set the default Style ws. cells [ws. dimension. address]. style. font. name = "Microsoft YaHei"; // adjust the column distance to ws. cells. autoFitColumns (0); // It Takes twice the time to write data // save the file p. saveAs (new FileInfo ("EPPlus.xlsx "));}

View Code

 

Iii. NPOI

Official Website: https://github.com/tonyqus/npoi. netcore version: https://github.com/dotnetcore/NPOI

SharpZipLib needs to be referenced to read and write Word and Excel. Examples are comprehensive, and documents of the system points are not found. However, baidu should be able to find many open-source projects in China.

Refer:

Http://blog.csdn.net/pan_junbiao/article/details/39717443

Http://www.cnblogs.com/yinrq/p/5590970.html

Report Control Based on NPIO for http://www.cnblogs.com/hanzhaoxin/p/4232572.html

Using (var fs = new FileStream ("NPOI.xlsx", FileMode. create, FileAccess. write) {// create a workbook IWorkbook wb = new XSSFWorkbook (); // Add Sheets var ws = wb. createSheet ("Sheet001"); wb. createSheet ("Sheet002"); // manually assign ws values to cells. 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 4. Benchmarks

A simple test of the above three controls, with 10000 data records written

            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=8Frequency=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

 

 

 

 

In general, EPPlus is the best in terms of speed and memory. It feels that ClosedXML is more convenient to call APIs, and the documentation is more comprehensive.

V. Others

Before SpreadSheetLight, the project can read and write data. OpenXMLSDK 2.5 is required.

ExcelDataReader Excel 03-07 file read, you only need to quickly read the excel file can use this