EPPlus 6/7

特点和技术概述

EPPlus

EPPlus是一个.NET Framework/.NET Core库，用于管理 Office Open XML 电子表格，通过 Nuget 分发。有关受支持的 .NET 框架，请参阅以下链接。EPPlus 不依赖于任何其他库，例如 Microsoft Excel。

库的设计考虑到了开发人员。我们的目标一直是，具有 Excel 或任何其他电子表格库知识的开发人员应轻松启动以加快 API 的速度。或者-正如有人说的-说，你告诉胜利的方式！

EPPlus 5/6/7 中的新闻

Worksheets/Ranges

	Split worksheet into panes
	更方便地访问行和列 - Worksheet.Rows, Worksheet.Columns, Range.EntireRow 和 Range.EntireColumn
	新的Fill功能，改进了RangesCopy方法。
	在工作表和表中的行/行中插入/删除范围

导入/导出

	从工作表范围和表格中导出 html/css
	从工作表区域和表中导出 json 数据
	支持将数据从工作表导出到文件和 DataTable
	支持将 dynamic/ExpandoObject 到工作表和一组新的属性，以扩展 LoadFromCollection 功能。

表/数据透视表/Slicers

	数据透视表和表切片器
	自定义表、枢轴表和切片机样式
	枢轴表造型通过枢轴区域
	对数据透视表的扩展支持 - 筛选器、计算列和支持共享缓存

公式计算

动态数组公式，枢轴计算，450+支持的函数，更好的性能和许多其他改进新增功能！

常规改进

	支持比以前更多的图像类型 - svg, ico 和 webp
	消除了对 System.Drawing.Common/GDI 的依赖性 - 可在 non-Windows 环境中无缝工作
	I/O 操作的异步方法
	条件格式 - 改进了对数据栏、自定义图标集和跨工作表引用的支持。新增功能！
	数百个已修复的问题和小改进

其他新功能

	支持所有 Excel 2019 图表类型，使用全新的现代样式
	支持提及的线程注释
	主题, 过滤器, 忽略错误
	支持窗体控件和图纸分组
	外部链接
	增强排序 - 排序状态、从左到右和自定义列表排序以及枢轴表上的自动排序

支持功能

General features

Create, read and modify Office Open XML workbooks (xslx and xlsm)
Supports both .NET Framework and .NET Core. No difference in feature set between the frameworks.
Provides an easy to use, strongly typed, programming interface, which is closely related to the well-known Excel VBA API.
Managed code only, no dependencies to Microsoft Excel
Battle-hardened codebase after many years as the worlds most downloaded .NET library for managing spreadsheets. Quality ensured by thousands of unit-tests.
The source code for EPPlus is openly available on Github , distributed under the Polyform Noncommercial license

Performance (large workbook)

EPPlus 6.2.10 creates the following workbook and writes it to disk in approximately 32 seconds (i9 CPU, 2.50 GHz, 64 GB RAM, SSD, Win11, .NET Core 6). It loads the same workbook from disk into an ExcelPackage instance in approximately 22 seconds.

200 columns
100,000 rows
50% of the data written to the cells are integers, 50% are strings with a length of 36 characters
Workbook size: 65 Mb

Source code for the test

Feature list

Workbook
- Document/Printer settings
Worksheets
- Supports 1,048,576 rows and 16,384 columns
- Add, remove, re-order, rename, hide/unhide worksheets in a workbook
- Copy worksheets within a workbook or to another workbook with maintained styling and shifting of addresses/references.
- Print areas
- Group/ungroup/collapse/expand rows and columns
- Header/Footer
- Page breaks
- Split/Freeze/unfreeze panes
- Merge cells
- Autofit columns
- Altering worksheet properties such as gridlines, tab colors, etc.
Cell Ranges
- Create, modify, delete, hide/unhide cells or entire ranges
- Access to Row and Column properties via the EntireRow and EntireColumn properties
- Sort ranges
- Insert/delete ranges - depending addresses will be shifted.
- Copy ranges to a new address with maintained styling and addresses/reference shifting within a workbook or to another workbook. Exclude different cell properties in the copy operation.
- Copy styles only.
- Fill ranges
- Named ranges
- The cell store supports IEnumerable, so Cells/Ranges can be queried with Linq
Cell Styling
- Number
- Alignment
- Font
- Fill
- Border
- Protection
- Wrap text, text rotations and more
- Named Styles
Formula calculation
- Built-in calculation engine with no dependency to Excel or any other library
- Calculate entire workbook, a worksheet or a single range
- Supports 452 functions
- Supports dynamic array formulas and legacy arrayformulas
- Supports Precision as Displayed in calculations (numeric cell values will be rounded according to the cell's number format).
- Calculates External links
- Support for named ranges and the R1C1 format.
- Add custom functions in runtime
Import/export data to and from worksheets
- Import data into a formatted table from enumerables of objects, arrays, DataSets, DataReaders, text-files, etc., with just one line of code.
- Export data to csv files, strings, json, html/css, data tables, IEnumerable<T> or streams with many configuration parameters
Supports load and save from/to both files and streams
- Can create workbooks in a web application and write to the response stream without file access.
- Get workbook as byte-array
- Supports async/await
Templates
Auto filters/Table Filters
- Create, read, modify, apply and delete filters of the types below:
- - Value filters
  - Date/Time filters
  - Custom filters
  - Top 10 filters
  - Dynamic filters
Data validation
- Create, read, modify, delete Data validations
- Types of validations supported: Integer (whole in Excel), Decimal, List, Date, Time, Any and Custom.
- Strongly typed interface for each validation type
Conditional Formatting
- Create, read, modify Conditional formatting
- 45 types of conditional formatting supported
- Strongly typed interface for each conditional formatting type
Tables
- 60 predefined styles
- Create and modify custom table styles from scratch or using a built-in style as template.
- Sort table with updated sort state
- Support for adding and deleting rows/columns (with shifting)
- Total row with predefined functions (supported by the built-in Formula calculation)
- Adding, modifying and applying filters
- Adding, modifying or removing table slicers
- Export table to a System.Data.DataTable
- Export table to a JSON
- Export table to html/css
- Export table to an IEnumerable<T>
Pivot Tables
- 84 predefined styles
- Support for the GETPIVOTDATA function in calculations.
- Support for Pivot table calculations
- Create and modify custom pivot table styles from scratch or using a built-in style as template.
- Style your pivot table using pivot areas
- Add, modify or remove row, column, page and data fields.
- Support for 12 different data field functions.
- Support for numeric and date grouping
- Adding calculated fields
- Support for adding, modifying filters.
- Adding, modifying or removing pivot table slicers
Slicers
- Adding, modifying or removing table- and pivot table- slicers
- 14 predefined styles
- Create and modify custom slicer styles from scratch or using a built-in style as template.
Hyperlinks
Comments
- Create, read, modify and delete comments (in later Excel versions renamed to Notes)
- Rich-text supported
- Threaded comments with support for mentions, resolve/re-open threads
Shapes
- 187 different types of shapes supported
- Position, size, border, text, styling, color/gradient/effect/shadow, etc
Pictures
- Supports adding images from file or stream/byte array
- Formats: jpg, gif, png, tiff, bmp, emf, wmf, svg, ico, webp
Form Controls
- Supports adding, modifying and removing nine different types of form controls
- Support to connect controls to VBA macros, link to cells and more.
Charts
- Support for all Excel 2019 chart types
- Full support for modern chart styling
- Load charts using crtx-files
- Strongly typed classes for the different chart types
Sparklines
- Create, read, modify and delete Sparklines
- Line
- Column
- Win/loss
Themes
- Load themes from thmx-files
- Modify current theme
External Links
- Updating the external workbook cache (xlsx, xlsm and xlst).
- Adding references to external workbooks.
- Breaking links to external workbooks.
- Retaining OLE and DDE links (available read only).
- Formula calculation via the external workbook cache or via loading and calculating the external package directly.
Protection
- Protect workbooks with password
- Open protected workbooks with password
- Protect workbook, worksheets or ranges
- Set workbooks to read-only with a password
Encryption
- Encrypt/decrypt workbooks
- Supports both Standard (Older office versions) and Agile encryption (Office 2010 and later)
VBA
- Create, read, delete, modify VBA code/modules
- Sign VBA code with certificates, supports Legacy, Agile and V3 signature. Supports 5 different hash algorithms for the signature.
- Password protect VBA projects
Supported formats
- Reads and writes xlsx, xlsm. Please note that EPPlus does not support the xlsx Strict format.
- Reads xltx, crtx, thmx
- Reads and writes txt, csv via the LoadFromText and SaveToText methods

Code for the performance test:

                
using OfficeOpenXml;
using System.Diagnostics;

var fileInfo = new FileInfo("c:\\Temp\\EPPlus_PerfTest.xlsx");
if (File.Exists(fileInfo.FullName)) File.Delete(fileInfo.FullName);

ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
Console.WriteLine("Creating package");

var sw = new Stopwatch();
sw.Start();
// since we are in a sync context, wait for the async task to finish...
WriteToDisk(sw, fileInfo).Wait();
sw.Stop();
Console.WriteLine(" Total seconds writing data: " + sw.Elapsed.TotalSeconds);
Console.WriteLine("Loading package...");
sw.Reset();
sw.Start();
LoadFromDisk(fileInfo).Wait();
sw.Stop();
Console.WriteLine(" Total seconds loading data: " + sw.Elapsed.TotalSeconds);

static async Task LoadFromDisk(FileInfo fileInfo)
{
    using var package = new ExcelPackage();
    await package.LoadAsync(fileInfo);
    Console.WriteLine("Cell GR100000: " + package.Workbook.Worksheets[0].Cells["GR100000"].Value);
}

static async Task WriteToDisk(Stopwatch sw, FileInfo fileInfo)
{
    using var package = new ExcelPackage(fileInfo);
    var sheet = package.Workbook.Worksheets.Add("Test");
    var range = sheet.Cells[1, 1, 100000, 200];
    var toCol = range.End.Column;
    var toRow = range.End.Row;
    for (var row = 1; row <= toRow; row++)
    {
        for (var col = 1; col <= toCol; col++)
        {
            if (col % 2 == 0)
            {
                range[row, col].Value = row + col;
            }
            else
            {
                range[row, col].Value = "abc123abc123abc123abc123abc123abc123";
            }
        }
    }
    Console.WriteLine(" Data written to cell store: " + sw.Elapsed.TotalSeconds);
    await package.SaveAsync();
}

EPPlus 6/7

特点和技术概述

特点和技术概述

EPPlus

EPPlus 5/6/7 中的新闻

Worksheets/Ranges

导入/导出

表/数据透视表/Slicers

公式计算

常规改进

其他新功能

支持功能

General features

Performance (large workbook)

Feature list

Conditional formatting

Performance test

Modal Heading