近年看到EPPlus能够用来导出Excel,NPIO官网地址

系统中时时会动用导出Excel的功能。

系统中时常会利用导出Excel的职能。

前边运用的是NPOI,可是导出数据行数多就报内部存款和储蓄器溢出。

事先使用的是NPOI,可是导出数据行数多就报内部存款和储蓄器溢出。

多年来观察EPPlus能够用来导出Excel,就本人测了下两者导出上的差异。

新近看到EPPlus能够用来导出Excel,就本人测了下两者导出上的分化。

NPIO官网地址:http://npoi.codeplex.com/

NPIO官网地址:http://npoi.codeplex.com/

EPPlus官网地址:http://epplus.codeplex.com/

EPPlus官网地址:http://epplus.codeplex.com/

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

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

在类库References右键Manage NuGet Packages…,之后选用丰硕对应的dll。

在类库References右键Manage NuGet Packages…,之后选用丰富对应的dll。

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

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

20列,NPOI能导出4万数据,导出陆仟0数量时报内部存款和储蓄器溢出。

20列,NPOI能导出四万数额,导出四万数额时报内部存款和储蓄器溢出。

         EPPlus能导出20万之上数据,导出二三万测试时内部存款和储蓄器溢出。

         EPPlus能导出20万之上数量,导出二20000测试时内部存款和储蓄器溢出。

NPOI导出:

NPOI导出:

 1 private static MemoryStream ExportXlsx(DataTable dt)
 2         {
 3             XSSFWorkbook workbook = new XSSFWorkbook();
 4             ISheet sheet = null;
 5 
 6             int headRowIndex = 0;
 7             string sheetName = "Sheet1";
 8             if (!string.IsNullOrEmpty(dt.TableName))
 9             {
10                 sheetName = dt.TableName;
11             }
12             sheet = workbook.CreateSheet(sheetName);
13             int rowIndex = 0;
14 
15             #region 列头及样式
16             {
17                 XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
18 
19                 ICellStyle headStyle = workbook.CreateCellStyle();
20                 headStyle.Alignment = HorizontalAlignment.Center;
21                 IFont font = workbook.CreateFont();
22                 font.FontHeightInPoints = 10;
23                 font.Boldweight = 700;
24                 headStyle.SetFont(font);
25 
26                 foreach (DataColumn column in dt.Columns)
27                 {
28                     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
29                     headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
30                 }
31             }
32             #endregion
33 
34             #region 填充内容
35 
36             foreach (DataRow row in dt.Rows)
37             {
38                 rowIndex++;
39                 XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
40                 foreach (DataColumn column in dt.Columns)
41                 {
42                     string drValue = row[column].ToString();
43                     dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
44                 }
45             }
46             #endregion
47 
48 
49             MemoryStream ms = new MemoryStream();
50 
51             workbook.Write(ms);
52             ms.Flush();
53 
54             return ms;
55         }
56 
57         public static void ExportXlsxByWeb(DataTable dt, string strFileName)
58         {
59 
60             HttpContext curContext = HttpContext.Current;
61 
62             MemoryStream ms = ExportXlsx(dt);
63 
64             curContext.Response.AppendHeader("Content-Disposition",
65                 "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
66             curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
67             curContext.Response.ContentEncoding = Encoding.UTF8;
68 
69             curContext.Response.BinaryWrite(ms.ToArray());
70             ms.Close();
71             ms.Dispose();
72             curContext.Response.End();
73 
74         }

图片 1图片 2

EPPlus导出:

private static MemoryStream ExportXlsx(DataTable dt)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = null;

            int headRowIndex = 0;
            string sheetName = "Sheet1";
            if (!string.IsNullOrEmpty(dt.TableName))
            {
                sheetName = dt.TableName;
            }
            sheet = workbook.CreateSheet(sheetName);
            int rowIndex = 0;

            #region 列头及样式
            {
                XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);

                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);

                foreach (DataColumn column in dt.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                }
            }
            #endregion

            #region 填充内容

            foreach (DataRow row in dt.Rows)
            {
                rowIndex++;
                XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dt.Columns)
                {
                    string drValue = row[column].ToString();
                    dataRow.CreateCell(column.Ordinal).SetCellValue(drValue);
                }
            }
            #endregion


            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);
            ms.Flush();

            return ms;
        }

        public static void ExportXlsxByWeb(DataTable dt, string strFileName)
        {

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

        }
 1 /// <summary>
 2         /// 使用EPPlus导出Excel(xlsx)
 3         /// </summary>
 4         /// <param name="sourceTable">数据源</param>
 5         /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
 6         public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
 7         {
 8             using (ExcelPackage pck = new ExcelPackage())
 9             {
10                 //Create the worksheet
11                 string sheetName = string.IsNullOrEmpty(sourceTable.TableName) ? "Sheet1" : sourceTable.TableName;
12                 ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
13 
14                 //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
15                 ws.Cells["A1"].LoadFromDataTable(sourceTable, true);
16 
17                 //Format the row
18                 ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
19                 Color borderColor = Color.FromArgb(155, 155, 155);
20 
21                 using (ExcelRange rng = ws.Cells[1, 1, sourceTable.Rows.Count + 1, sourceTable.Columns.Count])
22                 {
23                     rng.Style.Font.Name = "宋体";
24                     rng.Style.Font.Size = 10;
25                     rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
26                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
27 
28                     rng.Style.Border.Top.Style = borderStyle;
29                     rng.Style.Border.Top.Color.SetColor(borderColor);
30 
31                     rng.Style.Border.Bottom.Style = borderStyle;
32                     rng.Style.Border.Bottom.Color.SetColor(borderColor);
33 
34                     rng.Style.Border.Right.Style = borderStyle;
35                     rng.Style.Border.Right.Color.SetColor(borderColor);
36                 }
37 
38                 //Format the header row
39                 using (ExcelRange rng = ws.Cells[1, 1, 1, sourceTable.Columns.Count])
40                 {
41                     rng.Style.Font.Bold = true;
42                     rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
43                     rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
44                     rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
45                 }
46 
47                 //Write it back to the client
48                 HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
49                 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, Encoding.UTF8)));
50                 HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
51 
52                 HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray());
53                 HttpContext.Current.Response.End();
54             }
55         }

View Code

程序生成DataTable,20列,内容如下图
图片 3

EPPlus导出:

电脑配置:

图片 4图片 5

图片 6

/// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">xlsx文件名(不含后缀名)</param>
        public static void ExportByEPPlus(DataTable sourceTable, string strFileName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                string sheetName = 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 1
                ws.Cells["A1"].LoadFromDataTable(sourceTable, true);

                //Format the row
                ExcelBorderStyle 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 = "宋体";
                    rng.Style.Font.Size = 10;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.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 blue
                    rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                }

                //Write it back to the client
                HttpContext.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

条数 NPOI EPPlus
10000 成功生成 成功生成
20000 成功生成 成功生成
30000 成功生成 成功生成
40000 成功生成 成功生成
50000 失败 成功生成
100000 失败 成功生成
200000 失败 成功生成
230000 失败 失败

测试结果:

条数 NPOI EPPlus
40000 成功生成 成功生成
50000 失败 成功生成
230000 失败 失败
     
     
     
     
     

 

 

引用地址:http://www.cnblogs.com/tanpeng/p/6155749.html

 

相关文章