针对Excel开发控件汇总,批量拼脚本神器

做事中要给产品经营写各类脚本拉数据、修改数据。那种批量是拼sql,Excel当然是最合适的。不过苦于Excel玩不转,从前一向用Visual
Studio Code
的多要点编辑成效,即使如此,那在同事眼中已经是神器了。可是当遇到NimberText未来,VS
Code也要将来排了。
怎么着也不说了,直接上图:
997755.com澳门葡京 1

系统中平时会选取导出Excel的意义。

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

目前项目中须要一个导出Excel报告的效用,假日搜了弹指间,把其中相比较主流的列一下,仅供参考。

针对Excel开发控件汇总,批量拼脚本神器。效能须求:

效果图:  

 997755.com澳门葡京 2

近来项目中必要一个导出Excel报告的机能,沐日搜了一下,把里面比较主流的列一下,仅供参考。

愈多效益见官网
提醒:官网免费版已经去掉了Where条件部分,已经满意半数以上常常操作。如若急需,可利用dnspy进行破解,我早已成功破解完结,但不便于提供破解版。

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

一、ClosedXML

主页:

须求引用OpenXMLSDK(DocumentFormat.OpenXml.dll),以简练面向对象的不二法门操作文件(类似Visual
Basic for Applications (VBA)),文档和例子都比较完善

997755.com澳门葡京 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不需求任何其余引用,文档和例子还算相比全

997755.com澳门葡京 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

 

  1. 创造并写入.xlsx Excel2007+版本的电子表格文件
  2. 不要求office组件帮衬,终端电脑无需安装ms office
  3. 简单的说的format,style,chart和formula支持(不用过分复杂),并且可以插入图片
  4. 进程,有限支撑数据在万行以上表格写入速度

NPIO官网地址:

三、NPOI

官网:  .netcore
version:

内需引用夏普ZipLib,可以读写Word和Excel,例子比较全,系统点的文档没有找到,但是是同胞的开源项目,百度应该能找到很多

参考:

   

 

 基于NPIO的Report控件

997755.com澳门葡京 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条数据写入

997755.com澳门葡京 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

997755.com澳门葡京,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调用上方便一些,文档写周到一些。

 997755.com澳门葡京 7

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

五、其他

SpreadSheetLight 此前项目利用的,读写都可以,须求OpenXMLSDK 2.5

ExcelDataReader Excel
03-07 文件读取,只须要飞快读取excel文件的可以用这些

 

近期项目中要求一个导出Excel报告的效果,假日搜了刹那间,把其中比较主流的列一下,仅供…

一、ClosedXML

主页:https://github.com/ClosedXML/ClosedXML

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

997755.com澳门葡京 8997755.com澳门葡京 9

            //创建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

 

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

二、EPPlus

主页:https://github.com/JanKallman/EPPlus/

EPPlus不必要其余其他引用,文档和例子还算相比较全

997755.com澳门葡京 10997755.com澳门葡京 11

            //创建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

 

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

三、NPOI

官网:  .netcore
version:

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

参考:

   

 

 基于NPIO的Report控件

997755.com澳门葡京 12997755.com澳门葡京 13

            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

20列,NPOI能导出4万数目,导出5万数目时报内存溢出。

四、Benchmarks

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

997755.com澳门葡京 14997755.com澳门葡京 15

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

         EPPlus能导出20万上述数据,导出23万测试时内存溢出。

五、其他

SpreadSheetLight 此前项目应用的,读写都可以,必要OpenXMLSDK
2.5

ExcelDataReader Excel
03-07 文件读取,只要求急迅读取excel文件的可以用这一个

 

NPOI导出:

997755.com澳门葡京 16997755.com澳门葡京 17

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

        }

View Code

EPPlus导出:

997755.com澳门葡京 18997755.com澳门葡京 19

/// <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
40000 成功生成 成功生成
50000 失败 成功生成
230000 失败 失败
     
     
     
     
     

 

 

引用地址:

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website