DOCSEARCHbyAlgolia
Submit Docs

EPPlus Documentation

repository·develop8·Indexed 24 days ago

https://github.com/epplussoftware/epplus

A .NET library for handling Excel files. Version 8 introduces a dual-licensing model (Polyform Noncommercial 1.0.0 and Commercial) requiring explicit configuration via code, appSettings.json, web.config, or environment variables. The library includes support for formula calculation with LAMBDA and REGEX functions, chart styling, external connections, query tables, and specialized components like EPPlus.Interfaces and EPPlus.System.Drawing for imaging and text measurement.

Tokens
7K
Snippets
9
Records
78
Agent score
83%

What's inside EPPlus

  1. Overview of EPPlus

    develop8

    EPPlus is a .NET library (supporting both .NET Framework and .NET Core) used for managing Office Open XML spreadsheets. It is distributed via NuGet and operates as a single DLL with no dependencies on Microsoft Excel or other external libraries.

    Compatibility:

    • .NET Framework: Version 3.5 and later.
    • .NET Core: Version 2.0 and later.

  4. Use LAMBDA-based functions in Excel workbooks

    develop8

    Starting with version 8.2.0, EPPlus supports 8 new LAMBDA-based functions for workbook calculations. These functions allow for advanced functional programming patterns within Excel formulas. Supported functions include:

    • LAMBDA
    • MAKEARRAY
    • SCAN
    • MAP
    • REDUCE
    • BYROW
    • BYCOL
    • ISOMITTED

    You can also define LAMBDA functions via defined names.

  5. Configure conditional formatting in EPPlus 7+

    develop8

    EPPlus 7 provides significant improvements to conditional formatting:

    • Performance: XML is now read and written on load and save for better efficiency.
    • Cross-Worksheet Support: Formulas in conditional formatting can now reference other worksheets.
    • Extended Styling: Enhanced styling options for color scales, data bars, and icon sets.
    • Address Management: A new String constructor can be used to create an ExcelAddress internally for formatting rules.

  6. Configure custom table and slicer styles

    develop8

    Version 5.6.0 added support for custom styling:

    • Custom Table Styles: Create and modify named table styles that can be applied to both tables and pivot tables.
    • Custom Slicer Styles: Create and modify named slicer styles.
    • Pivot Table Styling: Use pivot areas to apply specific styles to pivot tables.

  8. Calculate Pivot Tables in EPPlus

    develop8

    EPPlus 7.2 introduced support for calculating pivot tables, allowing you to compute data fields on column and row fields, including page field filtering, slicers, and 'Show data as' options.

    To access the results of these calculations, use the ExcelPivotTable.CalculatedData property of the ExcelPivotTable.GetPivotData function.

    To optimize performance and ensure shared pivot caches are only refreshed once, use the following methods to calculate multiple pivot tables at once:

    • ExcelWorkbook.CalculateAllPivotTables(refresh)
    • ExcelPivotTableCollection.Calculate(refresh)

  9. Manage external workbook links

    develop8

    Version 5.7.0 introduced support for external links, allowing developers to:

    • Add, remove, and break links to external workbooks.
    • Update external workbook value/defined name caches.
    • Use external workbook caches within the formula parser.
    • Use loaded external packages (workbooks) in the formula parser.

  10. Implement Pivot Table Filters and Slicers

    develop8

    EPPlus 5.4.0 introduced comprehensive Pivot Table features:

    • Filters: Supports Item filters (row/column/page fields), Caption filters (label filters), Date/Numeric/String filters (Equals, NotBetween, etc.), Top 10 filters, and Dynamic filters.
    • Slicers: Support for both Pivot Table slicers and Table slicers.
    • Calculated Fields: Ability to add calculated fields to pivot tables.
    • Shared Caches: Support for pivot table shared caches via specific PivotTables.Add overloads.

  11. EPPlus 8 Licensing Model Overview

    develop8

    EPPlus 8 uses a dual license model:

    • Polyform Noncommercial 1.0.0: Free for personal or noncommercial use.
    • Commercial License: Required for use in a commercial business. Commercial licenses include support and can be purchased from EPPlus Software.

  12. Add external connections and query tables

    develop8

    Version 8.3.0 introduced support for adding external connections and query tables. This includes:

    • Connections: Power query, Database, OLAP, Web, and Text connections.
    • Query Tables: Adding range and table query tables.
    • Pivot Tables: Creating pivot tables that use external connections as a source.

    For detailed implementation details, refer to the External Connections & Query Tables wiki.