Overview

CliverExcel is a .NET lib for creating and editing excel files.

Its goal is to provide a higher-level API for usual tasks performed in Excel.

It is based on the NPOI lib and allows accessing the NPOI API, too.

Compatibility

CliverExcel is compatible with .NET Standard 2.0.

Licensing

See in the repository.

Source code

Open repository.

Do not download the latest code from a branch because it may be under development. Instead, use the latest (pre-)release code.

Usage

The basic code which frames any custom operations looks like this:

For instance:

Conception

CliverExcel provides a collection of extension methods for the NPOI types: IWorkbook, ISheet, IRow and ICell.

(!)All the CliverExcel extension methods start with '_' to be easily distinguished from the NPOI natives.

Besides the extension methods, CliverExcel exposes its native classes providing routines for certain tasks. The main such class is Cliver.Excel.

(!)In CliverExcel, cell, row and sheet indexes are always 1-based, while in the NPOI API they are mostly 0-based. As both API's coexist, be careful of the arguments and output of the methods you are using.

In the CliverExcel, row and column indexes are always represented by 'y' and 'x'; seeing these symbols you can be sure their values are 1-based.

Cliver.Excel

Any work with CliverExcel must begin with creation of a Cliver.Excel.

Creating a Cliver.Excel means creating both Cliver.Excel and IWorkbook objects at once. These 2 objects remain coupled during their lifetime and reciprocally allow accessing each other by the respective method/property.

Cliver.Excel provides properties and methods that could not be implemented by the mechanism of C# extension methods and thus it can be viewed as a sort of 'extension property' of IWorkbook if such a mechanism existed in C#.

(!)CliverExcel extension methods can access the Cliver.Excel object via IWorkbook, ISheet, IRow or ICell passed in them. Because of that, such objects must be originated within Cliver.Excel scope and not by the NPOI API directly.

Routines

The following routines must give a conception. They are not comprehensive.

Shifting

How to move a range of cells down, up, left and right:

Sizing columns

Commenting

Add a comment:

Append a comment with custom styling:

Copying/moving

CliverExcel facilitates copying/moving cells/rows/columns between sheets. The sheets can belong to different workbooks. How it is done preserving styling, see Excel.StyleMap.

Adding image

Adding dropdown

Styling

It is always commendable when styles are managed in a way that they are not duplicated in the workbook. It is especially important when the same workbook is edited constantly.

CliverExcel provides such a routine of creating styles where you should not worry about creating duplicates. It is shown in the following example:

Cloning styles

If you want to change the style for some cells but not for all cells using it, you need to clone it, configure as you need, register it in the workbook and then apply it to the cells.

Or, if you are creating a sheet from scratch, you can first create an unregistered style that will serve as a base for other styles, and then create real styles by cloning and modifying it. This way you can easily originate styles that have something in common. See the following example:

Highlighting

If you need to add highlighting to a style, use the method Highlight():

Fonts

Getting a font and setting it to a style:

Creating a new style with new font:

Altering styles

See Excel.StyleCache.

Excel.Table

Excel.Table is handy for performing CRUD operations on a sheet that has a predetermined number of columns and whose first row is headers.

It is native to CliverExcel and has no underlying NPOI type.

An example:

Excel.Table.Column

In Excel.Table, only data that belongs to a named column and hence can be referenced by it, is considered valid.

Excel.Table provides various routines to treat columns in preexisting sheets. For instance, the user can be allowed to order the columns as wants by dragging them in Excel.

Inheriting Excel.Table

Often the right way of using Excel.Table is inheriting it and extending the heir with custom methods as shown in the following snippet:

Excel.Range

The class Excel.Range provides routines for ranges.

It is native to CliverExcel and has no underlying NPOI type.

Excel.Column

The class Excel.Column looks to provide routines for sheet columns analogous to those provided for IRow.

It is native to CliverExcel and has no underlying NPOI type.

Excel.StyleCache

StyleCache is appropriate when you need to alter certain parameters of styles in some cells, e.g. set a new color or font, but you do not know in advance which styles you will alter and hence, which new styles must be created.

StyleCache takes care of registering and caching all the styles needed in the workbook during editing.

Profits by StyleCache:
- generalization and simplification of code;
- guarantee of no style duplication;
- performance increase;

A Cliver.Excel always has a StyleCache object which is used by default when operating within the same workbook. One StyleCache must be enough for one IWorkbook.

The default StyleCache is used behind the scene in the following example:

(!)If you want to copy/move cells between 2 workbooks and, at the same time, alter their styles, you need to create a StyleCache for 2 workbooks and pass it explicitly in the respective methods. Without altering, use StyleMap.

Excel.StyleMap

If you want to copy/move cells/rows preserving their styling between 2 workbooks, it can be done with StyleMap which is an inheritor of StyleCache adapted for such operations. The following example gives the idea:

(!)StyleMap is intended only for copying styles from one workbook to another without altering them. If you want to copy and alter at the same time, use StyleCache created for 2 workbooks.