精华内容
下载资源
问答
  • ClosedXML是一个.NET库,用于读取,操作和写入Excel 2007+(.xlsx,.xlsm)文件。 它旨在提供一个直观且用户友好的界面来处理基础的 API。 通过NuGet安装ClosedXML 如果要在项目中包含ClosedXML,则可以 要安装...
  • Closedxml使用文档.pdf

    2021-01-22 16:12:03
    PDF是closedxml的使用文档,全英文的,插件可以从Nuget下载Closedxml
  • 自己写的写Excel的一个小测试程序,已经测试过可用,使用NPOI和ClosedXML将数据写入Excel表格,电脑无需安装Microsoft Office即可使用,并且在写入速度上有很大的提升。
  • ClosedXML.Extensions.Mvc 用于MVC扩展 通过NuGet安装 要为.NET Core安装ClosedXML.Extensions.Mvc,请在程序包管理器控制台中运行以下命令 PM > Install-Package ClosedXML.Extensions.Mvc 如果您使用的是.NET ...
  • ClosedXML.Report是用于通过使用Microsoft Excel在.NET应用程序中生成报告和进行数据分析的工具。 它是.NET库,用于生成报告Microsoft Excel,而无需在运行代码的计算机上安装Excel。 使用ClosedXML.Report,您可以...
  • LinqToExcel ClosedXml C#

    2018-10-17 16:23:39
    LinqToExcel ClosedXml C#操作Excel Excel C# C#操作Excel
  • ClosedXML.SimpleSheets 一个库,可以轻松地从F#中的数据生成Excel工作表。 建立在之上 安装 dotnet add package ClosedXML.SimpleSheets 现在,您可以在项目中使用它。 以下是如何使用它的示例 根据数据生成工作...
  • 像老板一样导出 Excel 该软件包减轻了将应用程序中的数据导出为 Excel 文件的痛苦。 通过将包装在易于使用的 API 中,您还可以制作出色的 Excel 电子表格,就像您的经理一样! 安装钻头 很快在 NuGet 上,希望如此...
  • 介绍ClosedXML.Report ClosedXML.Report开源报表 支持net4.0+。 github:https://github.com/ClosedXML/ClosedXML.Report github项目下载慢参考:https://blog.csdn.net/czjnoe/article/details/106034095 一个可以...

    一.介绍ClosedXML.Report

    ClosedXML.Report开源报表  支持net4.0+。

    github:https://github.com/ClosedXML/ClosedXML.Report

    github项目下载慢参考:https://blog.csdn.net/czjnoe/article/details/106034095

    一个可以生成Excel报表的工具,创建一份xlsx 模板,设置数据填充规则,然后直接填充数据vb.net教程即可快速转换为 Excel报表。

    在网上没有找到较好ClosedXML.Report的示例,而且官方提供的使用示例实在太坑人了,也许该博主太"懒惰"了,所以今天写下该文章,方便后来者c#教程的使用,以免入坑太久。

    二.如何使用

    1.首先创建xlsx模板

    在这里插入图片描述

    注意:上图红色区域,需要在excel中,设置区域名称为item,否则无法填充数据

    2.c# demo示例

    首先添加ClosedXML(0.95.0.0)、ClosedXML.Report(0.2.0.0),不要在nuget直接下载,最好下载官方开源项目,引用项目下的dll,或者直接使我的demo中的引用,在文章末尾

    创建所需实体:

    public class Student
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public int Age { get; set; }
        }
    
    public class Course
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public double Grade { get; set; }
            public item Items { get; set; }
        }
        public class item
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
    

    代码示例:

     const string outputFile = @".\Output\report.xls";//输出目录
                var template = new XLTemplate(@".\Template\Student.xls");//模板目录
    
                Student stu = new Student
                {
                    Age = 25,
                    Id = 1,
                    Name = "財政局",
                };
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic["Id"] = "1";
                dic["Age"] = "25";
                dic["Name"] = "財政局";
    
    
                //AddVariable(object)方法,可传入实体、字典
                {
                    //template.AddVariable(stu);//传入单个实体
    
                    template.AddVariable(dic);//传入字典
                }
    
                //AddVariable(string alias, object value)方法,可以传入集合、动态类型、DataTable。如果希望输出多个table表,建议传入集合
                //注意key值必须为item
                {
                    IEnumerable<dynamic> CourseItems()
                    {
                        return new List<dynamic>
                    {
                        new { Id=1, Name="数学", Grade=89,Items=new { Id=1,Name="1"} },
                        new { Id=2, Name="英语", Grade=15,Items=new { Id=2,Name="2"} },
                        new { Id=3, Name="语文", Grade=100,Items=new { Id=3,Name="3"} },
                    };
                    }
    
                    var Course = new List<Course>
                     {
                          new Course{ Id=1, Name="数学", Grade=89 , Items=new item{ Id=1,Name="1"}},
                          new Course{ Id=2, Name="英语", Grade=15, Items=new item{ Id=2,Name="2"}},
                          new Course{ Id=3, Name="语文", Grade=100, Items=new item{ Id=3,Name="3"}}
                     };
                    DataTable dt = new DataTable();
                    dt.Columns.Add("Id", typeof(int));
                    dt.Columns.Add("Name", typeof(string));
                    dt.Columns.Add("Grade", typeof(int));
    
                    var newRow = dt.NewRow();
                    newRow["Id"] = 1;
                    newRow["Name"] = "数学";
                    newRow["Grade"] = 89;
                    dt.Rows.Add(newRow);
                    newRow = dt.NewRow();
                    newRow["Id"] = 2;
                    newRow["Name"] = "英语";
                    newRow["Grade"] = 15;
                    dt.Rows.Add(newRow);
                    newRow = dt.NewRow();
                    newRow["Id"] = 3;
                    newRow["Name"] = "语文";
                    newRow["Grade"] = 100;
                    dt.Rows.Add(newRow);
    
    
                    //template.AddVariable("item", CourseItems());//动态类型
                    template.AddVariable("item", Course);//集合 key必须为item
                    //template.AddVariable("item", dt);//DataTable
                }
    
                template.Generate();
    
                template.SaveAs(outputFile);//输出excel文件
    
                //打开excel文件
                Process.Start(new ProcessStartInfo(outputFile) { UseShellExecute = true });
    
     
    

    3.输出excel:

    在这里插入图片描述

    三.总结

    如果你只是处理一些简单的报表输出或只需要输出一张table表,还是可以使用的,毕竟比npoi输出简单多了,否则不建议使用

    ClosedXML.Report缺点:
          1.只能处理简单的数据插入,如果想要输出多个table表的话,则会麻烦一点,这点非常不友好。
          2.输出表格table,需要指定excel一块区域,且把该区域设置名称为item。
          3.不支持xls格式。只支持如下格式。’.xlsx’, ‘.xlsm’, ‘.xltx’ and ‘.xltm’.

    我的demo:https://gitee.com/czjnoe/ClosedXML.ReportDemo

    展开全文
  • ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the h...
    • 什么是Closed Xml

    1. ClosedXML makes it easier for developers to create Excel 2007/2010 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB).

    • 哪里下载

    1. Please refer to https://github.com/ClosedXML/ClosedXML to download the latest code
    • 如何使用

    • How to create a simple excel sheet file
        public void Create(String filePath)
        {
           var workbook = new XLWorkbook();
           var worksheet = workbook.Worksheets.Add("Sample Sheet");
           worksheet.Cell("A1").Value = "Hello World!";
           workbook.SaveAs(filePath);
        }
      
      The sample code demonstrate how to create a very simple excel sheet file. First, you need to create a workbook--XLWorkbook , then add a new sheet, then set value to cell: ‘A1’, then save to a specific path.

    • 2. How to create basic table:
      public void Create(string filePath)
              {
                  // Creating a new workbook
                  var wb = new XLWorkbook();
      
                  //Adding a worksheet
                  var ws = wb.Worksheets.Add("Contacts");
      
                  //Adding text
                  //Title
                  ws.Cell("B2").Value = "Contacts";
                  //First Names
                  ws.Cell("B3").Value = "FName";
                  ws.Cell("B4").Value = "John";
                  ws.Cell("B5").Value = "Hank";
                  ws.Cell("B6").Value = "Dagny";
                  //Last Names
                  ws.Cell("C3").Value = "LName";
                  ws.Cell("C4").Value = "Galt";
                  ws.Cell("C5").Value = "Rearden";
                  ws.Cell("C6").Value = "Taggart";
      
                  //Adding more data types
                  //Is an outcast?
                  ws.Cell("D3").Value = "Outcast";
                  ws.Cell("D4").Value = true;
                  ws.Cell("D5").Value = false;
                  ws.Cell("D6").Value = false;
                  //Date of Birth
                  ws.Cell("E3").Value = "DOB";
                  ws.Cell("E4").Value = new DateTime(1919, 1, 21);
                  ws.Cell("E5").Value = new DateTime(1907, 3, 4);
                  ws.Cell("E6").Value = new DateTime(1921, 12, 15);
                  //Income
                  ws.Cell("F3").Value = "Income";
                  ws.Cell("F4").Value = 2000;
                  ws.Cell("F5").Value = 40000;
                  ws.Cell("F6").Value = 10000;
      
                  //Defining ranges
                  //From worksheet
                  var rngTable = ws.Range("B2:F6");
                  //From another range
                  var rngDates = rngTable.Range("D3:D5"); // The address is relative to rngTable (NOT the worksheet)
                  var rngNumbers = rngTable.Range("E3:E5"); // The address is relative to rngTable (NOT the worksheet)
      
                  //Formatting dates and numbers
                  //Using a OpenXML's predefined formats
                  rngDates.Style.NumberFormat.NumberFormatId = 15;
                  //Using a custom format
                  rngNumbers.Style.NumberFormat.Format = "$ #,##0";
      
                  //Formatting headers
                  var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet)
                  rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                  rngHeaders.Style.Font.Bold = true;
                  rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;
      
                  //Adding grid lines
                  rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
      
                  //Format title cell
                  rngTable.Cell(1, 1).Style.Font.Bold = true;
                  rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
                  rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
      
                  //Merge title cells
                  rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
                  
                  //Add thick borders
                  rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
      
                  // You can also specify the border for each side with:
                  // rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick;
                  // rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick;
                  // rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick;
                  // rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick;
      
                  // Adjust column widths to their content
                  ws.Columns(2, 6).AdjustToContents();
      
                  //Saving the workbook
                  wb.SaveAs(filePath);
              }
      
      The key point is var rngTable = ws.Range("B2:F6"); You need to define a range (table) first, then set styles.
    • How to set a column
          public void Create(String filePath)
              {
                  var workbook = new XLWorkbook();
                  var ws = workbook.Worksheets.Add("Column Settings");
      
                  var col1 = ws.Column("B");
                  col1.Style.Fill.BackgroundColor = XLColor.Red;
                  col1.Width = 20;
      
                  var col2 = ws.Column(4);
                  col2.Style.Fill.BackgroundColor = XLColor.DarkOrange;
                  col2.Width = 5;
      
                  workbook.SaveAs(filePath);
              }
      
    • How to set a row
        public void Create(String filePath)
              {
                  var workbook = new XLWorkbook();
                  var ws = workbook.Worksheets.Add("Row Settings");
      
                  var row1 = ws.Row(2);
                  row1.Style.Fill.BackgroundColor = XLColor.Red;
                  row1.Height = 30;
      
                  var row2 = ws.Row(4);
                  row2.Style.Fill.BackgroundColor = XLColor.DarkOrange;
                  row2.Height = 3;
      
                  workbook.SaveAs(filePath);
              }
      

    • How to merge cells

      public void Create(String filePath)
            {
                var workbook = new XLWorkbook();
                var ws = workbook.Worksheets.Add("Merge Cells");
    
                // Merge a row
                ws.Cell("B2").Value = "Merged Row(1) of Range (B2:D3)";
                ws.Range("B2:D3").Row(1).Merge();
    
                // Merge a column
                ws.Cell("F2").Value = "Merged Column(1) of Range (F2:G8)";
                ws.Cell("F2").Style.Alignment.WrapText = true;
                ws.Range("F2:G8").Column(1).Merge();
    
                // Merge a range
                ws.Cell("B4").Value = "Merged Range (B4:D6)";
                ws.Cell("B4").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                ws.Cell("B4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                ws.Range("B4:D6").Merge();
    
                // Unmerging a range...
                ws.Cell("B8").Value = "Unmerged";
                ws.Range("B8:D8").Merge();
                ws.Range("B8:D8").Unmerge();
    
                workbook.SaveAs(filePath);
            }
    

    • How to insert rows
      public void Create(String filePath)
              {
                  var workbook = new XLWorkbook();
                  var ws = workbook.Worksheets.Add("Inserting Rows");
      
                  // Color the entire spreadsheet using rows
                  ws.Rows().Style.Fill.BackgroundColor = XLColor.LightCyan;
      
                  // Put a value in a few cells
                  foreach (var r in Enumerable.Range(1, 5))
                      foreach (var c in Enumerable.Range(1, 5))
                          ws.Cell(r, c).Value = "X";
      
                  var blueRow = ws.Row(2);
                  var redRow = ws.Row(5);
      
                  blueRow.Style.Fill.BackgroundColor = XLColor.Blue;
                  blueRow.InsertRowsBelow(2);
      
      
                  redRow.Style.Fill.BackgroundColor = XLColor.Red;
                  redRow.InsertRowsAbove(2);
      
                  ws.Columns(3, 4).Style.Fill.BackgroundColor = XLColor.Orange;
                  ws.Range("A2:A4").InsertRowsBelow(2);
                  ws.Range("B2:B4").InsertRowsAbove(2);
                  ws.Range("C2:C4").InsertRowsBelow(2);
                  ws.Range("D2:D4").InsertRowsAbove(2);
      
                  workbook.SaveAs(filePath);
              }
      

    • How to insert columns
      public void Create(String filePath)
              {
                  var workbook = new XLWorkbook();
                  var ws = workbook.Worksheets.Add("Inserting Columns");
      
                  // Color the entire spreadsheet using columns
                  ws.Columns().Style.Fill.BackgroundColor = XLColor.LightCyan;
      
                  // Put a value in a few cells
                  foreach (var r in Enumerable.Range(1, 5))
                      foreach (var c in Enumerable.Range(1, 5))
                          ws.Cell(r, c).Value = "X";
      
      
                  var blueColumn = ws.Column(2);
                  var redColumn = ws.Column(5);
      
                  blueColumn.Style.Fill.BackgroundColor = XLColor.Blue;
                  blueColumn.InsertColumnsAfter(2);
      
      
                  redColumn.Style.Fill.BackgroundColor = XLColor.Red;
                  redColumn.InsertColumnsBefore(2);
      
                  ws.Rows(3, 4).Style.Fill.BackgroundColor = XLColor.Orange;
                  ws.Range("B1:D1").InsertColumnsAfter(2);
                  ws.Range("B2:D2").InsertColumnsBefore(2);
                  ws.Range("B3:D3").InsertColumnsAfter(2);
                  ws.Range("B4:D4").InsertColumnsBefore(2);
      
                  workbook.SaveAs(filePath);
              }
      

     

    展开全文
  • 总体上EPPlus在速度和内存上都最佳,感觉ClosedXML在API调用上方便一些,文档写全面一些。 五、其他 SpreadSheetLight  之前项目使用的,读写都可以,需要OpenXMLSDK 2.5 ExcelDataReader  Excel 03-07 ...

    最近项目中需要一个导出Excel报告的功能,假期搜了一下,把其中比较主流的列一下,仅供参考。

    功能需求:

    1. 创建并写入.xlsx Excel2007+版本的电子表格文件
    2. 不需要office组件支持,终端电脑无需安装ms office
    3. 简单的format,style,chart和formula支持(不用过于复杂),并且能够插入图片
    4. 速度,保证数据在万行以上表格写入速度

    效果图:  

     

    一、ClosedXML

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

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

    //创建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");
    }

    二、EPPlus

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

    EPPlus不需要任何别的引用,文档和例子还算比较全

    //创建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"));
    }

    三、NPOI

    官网:https://github.com/tonyqus/npoi  .netcore version:https://github.com/dotnetcore/NPOI

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

    参考:

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

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

    http://www.cnblogs.com/hanzhaoxin/p/4232572.html 基于NPIO的Report控件

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

    四、Benchmarks

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

    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);
    }
    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
    MethodMeanErrorStdDevGen 0Gen 1Gen 2Allocated
    ClosedXML337.6 msNA2.5647 ms9625.00007062.50002812.500047.26 MB
    EPPlus145.8 msNA0.2533 ms5000.00003250.00002000.000024.68 MB
    NPOI263.4 msNA5.8716 ms10500.00007343.75002375.000055.65 MB

    总体上EPPlus在速度和内存上都最佳,感觉ClosedXML在API调用上方便一些,文档写全面一些。

    五、其他

    SpreadSheetLight 之前项目使用的,读写都可以,需要OpenXMLSDK 2.5

    ExcelDataReader Excel 03-07 文件读取,只需要快速读取excel文件的可以用这个

    展开全文
  • 操作EXCEL三–插件closedXml 新建项目 安装插件 从插件中可以解读出,它就是专门来操作EXCEL的一个小 插件 布局基本窗体 分别为新建:新建带一个表的workbook 写入:写入下面三个textbox指定单元格以及内容 删除:...

    操作EXCEL三–插件closedXml

    新建项目

    在这里插入图片描述

    安装插件

    从插件中可以解读出,它就是专门来操作EXCEL的一个小 插件

    在这里插入图片描述

    布局基本窗体

    分别为新建:新建带一个表的workbook

    写入:写入下面三个textbox指定单元格以及内容

    删除:删除其中一个workSheet

    风格:将指定单元格指定一种分格

    在这里插入图片描述

    新建一个带sheet的workbook

    上代码

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using ClosedXML.Excel;  //引入工作空间
    
    namespace OperateExcel
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            XLWorkbook wb = new XLWorkbook();           //新建一个工作簿实例
            private void button1_Click(object sender, EventArgs e)
            {
                wb.AddWorksheet("mysheet1");        //新建一个sheet
                wb.SaveAs(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");    //另存为123工作簿
            }
        }
    }
    
    

    允许效果:

    在这里插入图片描述

    以及123工作簿打开后效果

    在这里插入图片描述

    写入一些东西

    在这里插入图片描述

    在这里插入图片描述

    对应代码

    private void button2_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                wb.AddWorksheet("mysheet2");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Value = textBox3.Text;
                wb.Save();
            }
    

    删除功能

    在这里插入图片描述

    private void button3_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                wb.AddWorksheet("mysheet3");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Delete();
                wb.Save();
            }
    

    添加风格

    在这里插入图片描述

    private void button4_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Value = textBox3.Text;
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Style.Fill.BackgroundColor = XLColor.Yellow;
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Style.Font.FontColor = XLColor.Blue;
                wb.Save();
            }
    

    完整的全部功能代码

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using ClosedXML.Excel;  //引入工作空间
    
    namespace OperateExcel
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            XLWorkbook wb = new XLWorkbook();           //新建一个工作簿实例
            private void button1_Click(object sender, EventArgs e)
            {
                wb.AddWorksheet("mysheet1");        //新建一个sheet
                wb.SaveAs(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");    //另存为123工作簿
            }
    
            private void button2_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                wb.AddWorksheet("mysheet2");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Value = textBox3.Text;
                wb.Save();
            }
    
            private void button3_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                wb.AddWorksheet("mysheet3");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Delete();
                wb.Save();
            }
    
            private void button4_Click(object sender, EventArgs e)
            {
                wb = new XLWorkbook(@"C:\Users\liuyi\Desktop\新建文件夹\123.xlsx");
                IXLWorksheet sheet = wb.Worksheet(1);
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Value = textBox3.Text;
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Style.Fill.BackgroundColor = XLColor.Yellow;
                sheet.Cell(Convert.ToInt32(textBox1.Text), Convert.ToInt32(textBox2.Text)).Style.Font.FontColor = XLColor.Blue;
                wb.Save();
            }
        }
    }
    
    展开全文
  • 使用说明 github: https://github.com/ClosedXML/ClosedXML API new XLWorkbook(fileUrl); //读取xlsx并获取实例 XLWorksheet.FirstRow();...using ClosedXML.Excel; using System; using System.Collections.
  • closedXML 操作excel

    千次阅读 2018-04-22 22:40:54
    //nuget 搜索closedXML namespace Export_DataTable_Excel { public partial class Form1 : Form { public Form1() { InitializeComponent(); this.BindDataGridView(); } private void BindDataGridView...
  • https://github.com/closedxml/closedxml 这个库存数据为excel简直飞速,很好用。原来用循环方式操作cell行列存,太慢了,代码也多,用这个库很快。 格式也多样,c#推荐此库。 样例程序: System.Data.DataTable...
  • 使用ClosedXML生成excel前, 如果在excel的template中提前设置好样式, 比如单元格颜色, 自动回行等, 之后再使用ClosedXML写入数据的时候会出现样式丢失的情况; 还有一种情况, 在已经设置好的样式excel中写入数据的...
  • 一、ClosedXML介绍 1.简单的说ClosedXML就是一个类库,主要用于开发Office组件。它也是基于OpenXML,但在它的基础上做了很多封装,比OpenXML开发更加容易、方便; 2.ClosedXML是以OpenXML为基础的,所以在实际项目...
  • .NET ClosedXML.Excel 关于转化sub与sup标签为上标下标的操作 上标和下标常见于一些数学公式和化学表达式中,有时候我们需要将这些数学公式和化学式插入到Excel中,因此就涉及到如何在Excel中插入上标和下标。本文...
  • 在使用ClosedXml前需要先下载ClosedXml的库。在VS中从里"工具"--->“NuGet包管理器”--->“管理解决方案的NuGet程序包”,然后在浏览标签下输入ClosedXml,然后下载。下载好后,相应的文件会出现在工程的...
  • 最近项目中使用了ClosedXML.dll来处理Excel,在读取Excel的时候,用workSheet.Rows()获取Excel行数,默认读取Excel最大行数1048576 所以为了读取到不是空的Row,那就要利用LastRowUsed().RowNumber()判断出有内容的最后...
  • ClosedXML 读写Excel文件

    千次阅读 2017-05-10 17:04:11
    ClosedXML 读写Excel文件
  • :floppy_disk:下载不稳定的CI版本ClosedXML是一个.NET库,用于读取,操作和写入Excel 2007+(.xlsx,.xlsm)文件。 它旨在提供一个直观且用户友好的界面来处理基础内容:floppy_disk:下载不稳定的CI版本ClosedXML是一...
  • ClosedXML读取Excel文件

    千次阅读 2015-11-27 18:45:43
    因项目需要,需要把Excel里面的数据整合处理一下,文件多,而且文件内容较大,所以希望...问了下师兄,他介绍说ClosedXML比较好用。安装过程就不细说了,使用Visual Studio的NuGet一键安装即可,相当方便,贴个示意图。
  • 整理了一个简单关于ClosedXML的小demo: 1. 首先要在项目中引入两个library:ClosedXML.dll 和 DocumentFormat.OpenXml.dll2. 在项目代码中加入引用:usingClosedXML.Excel;3. demo方法(在代码的最后部分使用了...
  • ClosedXML 内存泄漏或OutOfMemory错误
  • 在很多系统中都用到导出,使用过多种导出方式,觉得ClosedXML插件的导出简单又方便。 并且ClosedXML、DocumentFormat.OpenXml都是MIT开源。 首先通过 Nuget 安装 ClosedXML 插件,同时会自动安装 DocumentFormat....
  • ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML ...
  • 使用ClosedXML开发Office之--Excel的开发

    千次阅读 2013-11-22 11:30:32
    一、ClosedXML介绍 1.简单的说ClosedXML就是一个类库,主要用于开发Office组件。它也是基于OpenXML,但在它的基础上做了很多封装,比OpenXML开发更加容易、方便; 2.ClosedXML是以OpenXML为基础的,所以在实际项目...

空空如也

空空如也

1 2 3 4 5 ... 14
收藏数 265
精华内容 106
关键字:

closedxml