您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:批量拼脚本神器,针对Excel开发控件

新葡亰496net:批量拼脚本神器,针对Excel开发控件

发布时间:2019-06-21 13:01编辑:网络数据库浏览(87)

    办事中要给产品老总写各类脚本拉数据、修改数据。这种批量是拼sql,Excel当然是最合适的。可是苦于Excel玩不转,此前一向用Visual Studio Code 的多主旨编辑功用,即使如此,那在同事眼中已经是神器了。可是当遇到NimberText今后,VS Code也要以后排了。
    如何也不说了,直接上海体育场地:
    新葡亰496net 1

    系统中偶然会接纳导出Excel的作用。

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

    近年项目中需求三个导出Excel报告的效率,假日搜了须臾间,把在那之中比较主流的列一下,仅供仿照效法。

    意义须要:

    效果图:  

     新葡亰496net 2

    近来项目中必要叁个导出Excel报告的效率,假期搜了瞬间,把里面比较主流的列一下,仅供参照他事他说加以调查。

       这两天刚刚用到上传Excel,并依照Excel中的数据做相应的拍卖,故整理以备用。

    越多效益见官网
    唤醒:官方网址无需付费版已经去掉了Where条件部分,已经满意超过54%常常操作。若是必要,可应用dnspy实行破解,笔者曾经打响破解达成,但不便于提供破解版。

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

    一、ClosedXML

    主页:

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

    新葡亰496net 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

     

    效益供给:

       借使需求将Excel中的数据对应写到数据库表中请参见后学用SqlBulkCopy一次性将多条数据赋值到表中

    不久前观望EPPlus能够用来导出Excel,就和睦测了下两个导出上的差距。

    二、EPPlus

    主页:

    EPPlus不须求任何其他引用,文书档案和例子还算相比较全

    新葡亰496net 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 Excel二零零五 版本的手表格文件
    2. 无需office组件协理,终端计算机没有须要安装ms office
    3. 简轻易单的format,style,chart和formula援助(不用过于复杂),并且能够插入图片
    4. 速度,保险数据在万行以上表格写入速度

          用到的财富:

    NPIO官方网址地址:

    三、NPOI

    官网:  .netcore version:

    急需引用SharpZipLib,能够读写Word和Excel,例子比较全,系统点的文书档案未有找到,可是是同胞的开源项目,百度应该能找到诸多

    参考:

       

     

     基于NPIO的Report控件

    新葡亰496net 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

    效果图:  

      (1)NOPI 2.2.0.0 可自个儿官方网站下载,也可点击:

    EPPlus官方网址地址:

    四、Benchmarks

    上述四个控件的回顾测试,一千0条数据写入

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

     新葡亰496net 7

      (2)用到某些周围管理公事的公家方法类,能够增多到花色中: 

    累加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)),文档和例子都相比较完善

    新葡亰496net 8新葡亰496net 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不要求别的其余引用,文书档案和例子还算相比较全

    新葡亰496net 10新葡亰496net 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控件

    新葡亰496net 12新葡亰496net 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

           后台的唤醒方法ShowMsgHelper,依照本身的改写就可以。

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

    四、Benchmarks

    如上八个控件的总结测试,一千0条数据写入

    新葡亰496net 14新葡亰496net 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

    新葡亰496net:批量拼脚本神器,针对Excel开发控件汇总。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文件的能够用那一个

     

    新葡亰496net 16新葡亰496net 17

    NPOI导出:

     1 <!DOCTYPE html>
     2 <html xmlns="http://www.w3.org/1999/xhtml">
     3 <head runat="server">
     4 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
     5     <title>导入EXCEL,生成DataTable</title>
     6     <script src="../../Themes/Scripts/jquery-1.8.2.min.js"></script>
     7     <link href="/Themes/Styles/Site.css" rel="stylesheet" type="text/css" />
     8     <script src="/Themes/Scripts/FunctionJS.js" type="text/javascript"></script>
     9     
    10     <script type="text/javascript">
    11         $(document).ready(function () {
    12             $("#Import").click(function () {
    13                 var filename = $("#FileUpload1").val();
    14                 if (filename == '') {
    15                     alert('请选择上传的EXCEL文件');
    16                     return false;
    17                 }
    18                 else {
    19                     var exec = (/[.]/.exec(filename)) ? /[^.] $/.exec(filename.toLowerCase()) : '';
    20                     if (!(exec == "xlsx" || exec == "xls")) {
    21                         alert("文件格式不对,请上传Excel文件!");
    22                         return false;
    23                     }
    24                 }
    25                 return true;
    26             });
    27         });
    28     </script>
    29 </head>
    30 <body>
    31     <form id="form1" runat="server">
    32     <div>
    33         <asp:FileUpload ID="FileUpload1" runat="server" /><asp:Button ID="Import" runat="server" Text="导入" OnClick="ImpClick" />
    34     </div>
    35     </form>
    36 </body>
    37 </html>
    

    新葡亰496net 18新葡亰496net 19

    View Code

    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导出:

    新葡亰496net 20新葡亰496net 21

    新葡亰496net 22新葡亰496net 23

      1  protected void ImpClick(object sender, EventArgs e)
      2         {
      3             try
      4             {
      5                 #region 校验
      6                 var fileName = this.FileUpload1.FileName;
      7                 if (string.IsNullOrWhiteSpace(fileName))
      8                 {
      9                     //提示信息
     10                     ShowMsgHelper.Alert("请选择上传Excel文件");
     11                     return;
     12                 }
     13 
     14                 //获取上传文件扩展名称
     15                 if (!(fileName.IndexOf(".xlsx") > 0 || fileName.IndexOf(".xls") > 0))
     16                 {
     17                     ShowMsgHelper.Alert("上传文件格式不正确,请核对!");
     18                     return;
     19                 }
     20                 
     21 
     22                 #endregion
     23 
     24                 #region 将Excel文件上传到服务器上临时文件夹中
     25                 //临时文件夹,根目录下/Upload/tmp/,根据自己配置选择
     26                 string path = Server.MapPath("~/")   "Upload\tmp\";
     27                 string retStr=UploadHelper.FileUpload(path, this.FileUpload1);
     28                 if (!retStr.Equals("上传成功")) {
     29                     ShowMsgHelper.Alert(retStr);
     30                     return;
     31                 }
     32                 #endregion
     33 
     34                 #region 读取Excel文件第一个表获取内容并转换成DataTable,删除临时文件,也可以自己加时间戳,维护处理
     35                 DataTable dt = this.ExcelToDataTable(path   this.FileUpload1.FileName, true);
     36                 if (dt == null) {
     37                     ShowMsgHelper.Alert_Error("获取失败");
     38                     return;
     39                 }
     40 
     41                 //示例:获取dt中的值
     42                 string test = dt.Rows[0]["name"].ToString();
     43                 string test2 = dt.Rows[1]["class"].ToString();
     44 
     45                 //删除临时文件
     46                 DirFileHelper.DeleteFile("Upload\tmp\"   fileName);
     47                 #endregion 
     48         
     49             }
     50             catch (Exception ex) {
     51                 throw ex;
     52             }
     53 
     54         }
     55 
     56         /// <summary>
     57         /// 将excel导入到datatable
     58         /// </summary>
     59         /// <param name="filePath">excel路径</param>
     60         /// <param name="isColumnName">第一行是否是列名</param>
     61         /// <returns>返回datatable</returns>
     62         public DataTable ExcelToDataTable(string filePath, bool isColumnName)
     63         {
     64             DataTable dataTable = null;
     65             FileStream fs = null;
     66             DataColumn column = null;
     67             DataRow dataRow = null;
     68             IWorkbook workbook = null;
     69             ISheet sheet = null;
     70             IRow row = null;
     71             ICell cell = null;
     72             int startRow = 0;
     73             try
     74             {
     75                 using (fs = File.OpenRead(filePath))
     76                 {
     77                     // 2007版本
     78                     if (filePath.IndexOf(".xlsx") > 0)
     79                         workbook = new XSSFWorkbook(fs);
     80                     // 2003版本
     81                     else if (filePath.IndexOf(".xls") > 0)
     82                         workbook = new HSSFWorkbook(fs);
     83 
     84                     if (workbook != null)
     85                     {
     86                         sheet = workbook.GetSheetAt(0);//读取第一个sheet,当然也可以循环读取每个sheet
     87                         dataTable = new DataTable();
     88                         if (sheet != null)
     89                         {
     90                             int rowCount = sheet.LastRowNum;//总行数
     91                             if (rowCount > 0)
     92                             {
     93                                 IRow firstRow = sheet.GetRow(0);//第一行
     94                                 int cellCount = firstRow.LastCellNum;//列数
     95 
     96                                 //构建datatable的列
     97                                 if (isColumnName)
     98                                 {
     99                                     startRow = 1;//如果第一行是列名,则从第二行开始读取
    100                                     for (int i = firstRow.FirstCellNum; i < cellCount;   i)
    101                                     {
    102                                         cell = firstRow.GetCell(i);
    103                                         if (cell != null)
    104                                         {
    105                                             if (cell.StringCellValue != null)
    106                                             {
    107                                                 column = new DataColumn(cell.StringCellValue);
    108                                                 dataTable.Columns.Add(column);
    109                                             }
    110                                         }
    111                                     }
    112                                 }
    113                                 else
    114                                 {
    115                                     for (int i = firstRow.FirstCellNum; i < cellCount;   i)
    116                                     {
    117                                         column = new DataColumn("column"   (i   1));
    118                                         dataTable.Columns.Add(column);
    119                                     }
    120                                 }
    121 
    122                                 //填充行
    123                                 for (int i = startRow; i <= rowCount;   i)
    124                                 {
    125                                     row = sheet.GetRow(i);
    126                                     if (row == null) continue;
    127 
    128                                     dataRow = dataTable.NewRow();
    129                                     for (int j = row.FirstCellNum; j < cellCount;   j)
    130                                     {
    131                                         cell = row.GetCell(j);
    132                                         if (cell == null)
    133                                         {
    134                                             dataRow[j] = "";
    135                                         }
    136                                         else
    137                                         {
    138                                             //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
    139                                             switch (cell.CellType)
    140                                             {
    141                                                 case CellType.Blank:
    142                                                     dataRow[j] = "";
    143                                                     break;
    144                                                 case CellType.Numeric:
    145                                                     short format = cell.CellStyle.DataFormat;
    146                                                     //对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理
    147                                                     if (format == 14 || format == 31 || format == 57 || format == 58)
    148                                                         dataRow[j] = cell.DateCellValue;
    149                                                     else
    150                                                         dataRow[j] = cell.NumericCellValue;
    151                                                     break;
    152                                                 case CellType.String:
    153                                                     dataRow[j] = cell.StringCellValue;
    154                                                     break;
    155                                             }
    156                                         }
    157                                     }
    158                                     dataTable.Rows.Add(dataRow);
    159                                 }
    160                             }
    161                         }
    162                     }
    163                 }
    164                 return dataTable;
    165             }
    166             catch (Exception)
    167             {
    168                 if (fs != null)
    169                 {
    170                     fs.Close();
    171                 }
    172                 return null;
    173             }
    174         }
    
    /// <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

    View Code

      ===========后台截至===============

    测试结果:

      

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

     

     

    引用地址:

     

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:批量拼脚本神器,针对Excel开发控件

    关键词: