C # Npoi Export Excel and Epplus export Excel comparison

The ability to export Excel is often used in the system.

Previously, Npoi was used, but the number of exported data rows reported a memory overflow.

Recently saw Epplus can be used to export Excel, you have measured the difference between the two export.

Npio website Address: http://npoi.codeplex.com/

Epplus website Address: http://epplus.codeplex.com/

Adding the Npoi, Epplus class library DLL is using NuGet Add.

In the class library references right-manage NuGet Packages ..., then choose to add the corresponding DLL.

The test results show that the data of the same data structure, the Epplus export ability is stronger than Npoi.

20 columns, Npoi can export 40,000 data, export 50,000 data times memory overflow.

Epplus can export more than 200,000 data and export 230,000 test memory overflow.

Npoi Export:

Private StaticMemoryStream exportxlsx (DataTable dt) {Xssfworkbook workbook=NewXssfworkbook (); Isheet sheet=NULL; intHeadrowindex =0; stringSheetName ="Sheet1"; if(!string. IsNullOrEmpty (dt. TableName)) {SheetName=dt.            TableName; } sheet=workbook.            Createsheet (SheetName); intRowIndex =0; #regioncolumn headers and styles{xssfrow HeaderRow=(xssfrow) sheet.                CreateRow (Headrowindex); Icellstyle Headstyle=workbook.                Createcellstyle (); Headstyle.alignment=HorizontalAlignment.Center; IFont Font=workbook.                CreateFont (); Font. Fontheightinpoints=Ten; Font. Boldweight= the;                Headstyle.setfont (font); foreach(DataColumn columninchdt. Columns) {Headerrow.createcell (column. Ordinal). Setcellvalue (column.                    ColumnName); Headerrow.getcell (column. Ordinal). CellStyle=Headstyle; }            }            #endregion            #regionPopulating contentforeach(DataRow rowinchdt. Rows) {RowIndex++; Xssfrow DataRow=(xssfrow) sheet.                CreateRow (RowIndex); foreach(DataColumn columninchdt. Columns) {stringDrvalue =Row[column].                    ToString (); Datarow.createcell (column. Ordinal).                Setcellvalue (Drvalue); }            }            #endregionMemoryStream Ms=NewMemoryStream (); Workbook.            Write (MS); Ms.            Flush (); returnMS; }         Public Static voidExportxlsxbyweb (DataTable DT,stringstrFileName) {HttpContext Curcontext=HttpContext.Current; MemoryStream Ms=exportxlsx (DT); CurContext.Response.AppendHeader ("content-disposition",                "attachment;filename="+ Httputility.urlencode (strFileName, Encoding.UTF8) +". xlsx"); CurContext.Response.AddHeader ("Content-length", Ms. ToArray ().            Length.tostring ()); CurContext.Response.ContentEncoding=Encoding.UTF8; CurContext.Response.BinaryWrite (Ms.            ToArray ()); Ms.            Close (); Ms.            Dispose ();        CurContext.Response.End (); }

View Code

Epplus Export:

/// <summary>        ///export Excel (xlsx) using Epplus/// </summary>        /// <param name= "SourceTable" >Data Source</param>        /// <param name= "strFileName" >xlsx file name (without the suffix name)</param>         Public Static voidExportbyepplus (DataTable SourceTable,stringstrFileName) {            using(Excelpackage PCK =NewExcelpackage ()) {                //Create the worksheet                stringSheetName =string. IsNullOrEmpty (sourcetable.tablename)?"Sheet1": Sourcetable.tablename; Excelworksheet ws=PCK.                WORKBOOK.WORKSHEETS.ADD (SheetName); //Load the DataTable into the sheet, starting from cell A1. Print the column names on row 1Ws. cells["A1"]. Loadfromdatatable (SourceTable,true); //Format the rowExcelborderstyle BorderStyle =Excelborderstyle.thin; Color bordercolor= Color.FromArgb (155,155,155); using(Excelrange rng = ws.) cells[1,1, SourceTable.Rows.Count +1, SourceTable.Columns.Count]) {rng. Style.Font.Name="Song Body"; Rng. Style.Font.Size=Ten; Rng. Style.Fill.PatternType= Excelfillstyle.solid;//Set Pattern for the background to SolidRng. Style.Fill.BackgroundColor.SetColor (Color.FromArgb (255,255,255)); Rng. Style.Border.Top.Style=BorderStyle; Rng.                    Style.Border.Top.Color.SetColor (bordercolor); Rng. Style.Border.Bottom.Style=BorderStyle; Rng.                    Style.Border.Bottom.Color.SetColor (bordercolor); Rng. Style.Border.Right.Style=BorderStyle; Rng.                Style.Border.Right.Color.SetColor (bordercolor); }                //Format the header row                using(Excelrange rng = ws.) cells[1,1,1, SourceTable.Columns.Count]) {rng. Style.Font.Bold=true; Rng. Style.horizontalalignment=Excelhorizontalalignment.center; Rng. Style.Fill.BackgroundColor.SetColor (Color.FromArgb (234,241,246));//Set color to dark blueRng. Style.Font.Color.SetColor (Color.FromArgb (Wuyi,Wuyi,Wuyi)); }                //Write It back to the clientHttpContext.Current.Response.ContentType ="Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; HttpContext.Current.Response.AddHeader ("content-disposition",string. Format ("attachment; Filename={0}.xlsx", Httputility.urlencode (strFileName, Encoding.UTF8)); HttpContext.Current.Response.ContentEncoding=Encoding.UTF8; HttpContext.Current.Response.BinaryWrite (PCK.                Getasbytearray ());            HttpContext.Current.Response.End (); }        }

View Code

Test results:

Article number	Npoi	Epplus
40000	Successful build	Successful build
50000	Failed	Successful build
230000	Failed	Failed

Reference Address: http://www.cnblogs.com/tanpeng/p/6155749.html

C # Npoi Export Excel and Epplus export Excel comparison