API Index

namespace ClosedXML.Excel

Enums

enum XLFilterDynamicType

Values:

AboveAverage

BelowAverage

enum XLFilterType

Values:

None

Regular

Custom

TopBottom

Dynamic

enum XLTopBottomPart

Values:

Top

Bottom

enum XLTopBottomType

Type of a XLFilterType.TopBottom filter that is used to determine number of visible top/bottom values.

Values:

Items: Filter should display requested number of items.

Percent: Number of displayed items is determined as a percentage data rows of auto filter.

enum XLDateTimeGrouping

Values:

Year

Month

Day

Hour

Minute

Second

enum XLConnector

Values:

And

Or

enum XLFilterOperator

Values:

Equal

NotEqual

GreaterThan

LessThan

EqualOrGreaterThan

EqualOrLessThan

enum XLError

A formula error.

Keep order of errors in same order as value returned by ERROR.TYPE, because it is used for comparison in some case (e.g. AutoFilter). Values are off by 1, so default produces a valid error.

Values:

NullValue

#NULL! - Intended to indicate when two areas are required to intersect, but do not.

The space is an intersection operator.

SUM(B1 C1) tries to intersect B1:B1 area and C1:C1 area, but since there are no intersecting cells, the result is #NULL.

DivisionByZero: #DIV/0! - Intended to indicate when any number (including zero) or any error code is divided by zero.

IncompatibleValue

#VALUE! - Intended to indicate when an incompatible type argument is passed to a function, or an incompatible type operand is used with an operator.

Passing a non-number text to a function that requires a number, trying to get an area from non-contiguous reference. Creating an area from different sheets Sheet1!A1:Sheet2!A2

CellReference

#REF! - a formula refers to a cell that’s not valid.

When unable to find a sheet or a cell.

NameNotRecognized

#NAME? - Intended to indicate when what looks like a name is used, but no such name has been defined.

Only for named ranges, not sheets.

TestRange*10 when the named range doesn’t exist will result in an error.

NumberInvalid

#NUM! - Intended to indicate when an argument to a function has a compatible type, but has a value that is outside the domain over which that function is defined.

This is known as a domain error.

ASIN(10) - the ASIN accepts only argument -1..1 (an output of SIN), so the resulting value is #NUM!.

NoValueAvailable

#N/A - Intended to indicate when a designated value is not available.

The value is used for extra cells of an array formula that is applied on an array of a smaller size that the array formula.

enum XLDataType

A value that is in the cell.

Values:

Blank

The value is a blank (either blank cells or the omitted optional argument of a function, e.g. IF(TRUE,,).

Keep as the first, so the default values are blank.

Boolean: The value is a logical value.

Number: The value is a double-precision floating points number, excluding Double.NaN, Double.PositiveInfinity or double.NegativeInfinity.

Text: A text or a rich text. Can’t be null and can be at most 32767 characters long.

Error: The value is one of XLError.

DateTime

The value is a DateTime, represented as a serial date time number.

Serial date time 60 is a 1900-02-29, nonexistent day kept for compatibility, but unrepresentable by DateTime. Don’t use.

TimeSpan: The value is a TimeSpan, represented in a serial date time (24 hours is 1, 36 hours is 1.5 ect.).

enum XLTableCellType

Values:

None

Header

Data

Total

enum XLCellCopyOptions

Values:

None

Values

Styles

ConditionalFormats

DataValidations

Sparklines

All

enum FormulaType

Values:

Normal

Array

DataTable

Shared

enum FormulaConversionType

Values:

A1ToR1C1

R1C1ToA1

enum XLChartType

Values:

Area

Area3D

AreaStacked

AreaStacked100Percent

AreaStacked100Percent3D

AreaStacked3D

BarClustered

BarClustered3D

BarStacked

BarStacked100Percent

BarStacked100Percent3D

BarStacked3D

Bubble

Bubble3D

Column3D

ColumnClustered

ColumnClustered3D

ColumnStacked

ColumnStacked100Percent

ColumnStacked100Percent3D

ColumnStacked3D

Cone

ConeClustered

ConeHorizontalClustered

ConeHorizontalStacked

ConeHorizontalStacked100Percent

ConeStacked

ConeStacked100Percent

Cylinder

CylinderClustered

CylinderHorizontalClustered

CylinderHorizontalStacked

CylinderHorizontalStacked100Percent

CylinderStacked

CylinderStacked100Percent

Doughnut

DoughnutExploded

Line

Line3D

LineStacked

LineStacked100Percent

LineWithMarkers

LineWithMarkersStacked

LineWithMarkersStacked100Percent

Pie

Pie3D

PieExploded

PieExploded3D

PieToBar

PieToPie

Pyramid

PyramidClustered

PyramidHorizontalClustered

PyramidHorizontalStacked

PyramidHorizontalStacked100Percent

PyramidStacked

PyramidStacked100Percent

Radar

RadarFilled

RadarWithMarkers

StockHighLowClose

StockOpenHighLowClose

StockVolumeHighLowClose

StockVolumeOpenHighLowClose

Surface

SurfaceContour

SurfaceContourWireframe

SurfaceWireframe

XYScatterMarkers

XYScatterSmoothLinesNoMarkers

XYScatterSmoothLinesWithMarkers

XYScatterStraightLinesNoMarkers

XYScatterStraightLinesWithMarkers

enum XLChartTypeCategory

Values:

Bar3D

enum XLBarOrientation

Values:

Vertical

Horizontal

enum XLBarGrouping

Values:

Clustered

Percent

Stacked

Standard

enum XLCFContentType

Values:

Number

Percent

Formula

Percentile

Minimum

Maximum

enum XLCFIconSetOperator

Values:

GreaterThan

EqualOrGreaterThan

enum XLTimePeriod

Values:

Yesterday

Today

Tomorrow

InTheLast7Days

LastWeek

ThisWeek

NextWeek

LastMonth

ThisMonth

NextMonth

enum XLIconSetStyle

Values:

ThreeArrows

ThreeArrowsGray

ThreeFlags

ThreeTrafficLights1

ThreeTrafficLights2

ThreeSigns

ThreeSymbols

ThreeSymbols2

FourArrows

FourArrowsGray

FourRedToBlack

FourRating

FourTrafficLights

FiveArrows

FiveArrowsGray

FiveRating

FiveQuarters

enum XLConditionalFormatType

Values:

Expression

CellIs

ColorScale

DataBar

IconSet

Top10

IsUnique

IsDuplicate

ContainsText

NotContainsText

StartsWith

EndsWith

IsBlank

NotBlank

IsError

NotError

TimePeriod

AboveAverage

enum XLCFOperator

Values:

Equal

NotEqual

GreaterThan

LessThan

EqualOrGreaterThan

EqualOrLessThan

Between

NotBetween

Contains

NotContains

StartsWith

EndsWith

enum XLCustomPropertyType

Values:

Text

Number

Date

Boolean

enum XLAllowedValues

Values:

AnyValue

WholeNumber

Decimal

Date

Time

TextLength

List

Custom

enum XLErrorStyle

Values:

Stop

Warning

Information

enum XLOperator

Values:

EqualTo

NotEqualTo

GreaterThan

LessThan

EqualOrGreaterThan

EqualOrLessThan

Between

NotBetween

enum XLNamedRangeScope

A scope of IXLDefinedName. It determines where can be defined name resolved.

Values:

Worksheet: Name is defined at the sheet level and is available only at the sheet it is defined or IXLWorksheet.DefinedNames collection or when referred with sheet specifier (e.g. Sheet5!Name when name is scoped to Sheet5).

Workbook: Name is defined at the workbook and is available everywhere.

enum XLDrawingAnchor

Values:

MoveAndSizeWithCells

MoveWithCells

Absolute

enum XLDrawingTextDirection

Values:

Context

LeftToRight

RightToLeft

enum XLDrawingTextOrientation

Values:

LeftToRight

Vertical

BottomToTop

TopToBottom

enum XLDrawingHorizontalAlignment

Values:

Left

Justify

Center

Right

Distributed

enum XLDrawingVerticalAlignment

Values:

Top

Justify

Center

Bottom

Distributed

enum XLDashStyle

Values:

Solid

RoundDot

SquareDot

Dash

DashDot

LongDash

LongDashDot

LongDashDotDot

enum XLLineStyle

Values:

Single

ThinThin

ThinThick

ThickThin

ThickBetweenThin

enum XLOutlineSummaryVLocation

Values:

Top

Bottom

enum XLOutlineSummaryHLocation

Values:

Left

Right

enum XLSheetViewOptions

Values:

Normal

PageBreakPreview

PageLayout

enum XLWorksheetVisibility

Values:

Visible

Hidden

VeryHidden

enum XLHFMode

Values:

OddPagesOnly

OddAndEvenPages

Odd

enum XLHFPredefinedText

Values:

PageNumber

NumberOfPages

Date

Time

FullPath

Path

File

SheetName

enum XLHFOccurrence

Values:

AllPages

OddPages

EvenPages

FirstPage

enum XLPageOrientation

Values:

Default

Portrait

Landscape

enum XLPaperSize

Values:

LetterPaper

LetterSmallPaper

TabloidPaper

LedgerPaper

LegalPaper

StatementPaper

ExecutivePaper

A3Paper

A4Paper

A4SmallPaper

A5Paper

B4Paper

B5Paper

FolioPaper

QuartoPaper

StandardPaper

StandardPaper1

NotePaper

No9Envelope

No10Envelope

No11Envelope

No12Envelope

No14Envelope

CPaper

DPaper

EPaper

DlEnvelope

C5Envelope

C3Envelope

C4Envelope

C6Envelope

C65Envelope

B4Envelope

B5Envelope

B6Envelope

ItalyEnvelope

MonarchEnvelope

No634Envelope

UsStandardFanfold

GermanStandardFanfold

GermanLegalFanfold

IsoB4

JapaneseDoublePostcard

StandardPaper2

StandardPaper3

StandardPaper4

InviteEnvelope

LetterExtraPaper

LegalExtraPaper

TabloidExtraPaper

A4ExtraPaper

LetterTransversePaper

A4TransversePaper

LetterExtraTransversePaper

SuperaSuperaA4Paper

SuperbSuperbA3Paper

LetterPlusPaper

A4PlusPaper

A5TransversePaper

JisB5TransversePaper

A3ExtraPaper

A5ExtraPaper

IsoB5ExtraPaper

A2Paper

A3TransversePaper

A3ExtraTransversePaper

enum XLPageOrderValues

Values:

DownThenOver

OverThenDown

enum XLShowCommentsValues

Values:

None

AtEnd

AsDisplayed

enum XLPrintErrorValues

Values:

Blank

Dash

Displayed

NA

enum XLSubtotalFunction

Values:

Automatic

None

Sum

Count

Average

Minimum

Maximum

Product

CountNumbers

StandardDeviation

PopulationStandardDeviation

Variance

PopulationVariance

enum XLPivotLayout

Values:

Outline

Tabular

Compact

enum XLPivotStyleFormatElement

Values:

None

Label

Data

All

enum XLPivotStyleFormatTarget

Values:

PivotTable

GrandTotal

Subtotal

Header

Label

Data

enum XLPivotCalculation

Enum describing how is a pivot field values (i.e. in data area) displayed.

[ISO-29500] 18.18.70 ST_ShowDataAs

Values:

Normal: Field values are displayed normally.

DifferenceFrom

PercentageOf

PercentageDifferenceFrom

RunningTotal

PercentageOfRow

PercentageOfColumn

PercentageOfTotal

Index: Basically a relative importance of a value. Closer the value to 1.0 is, the less important it is. Calculated as (value-in-cell * grand-total-of-grand-totals) / (grand-total-row * grand-total-column).

enum XLPivotCalculationItem

Some calculation from XLPivotCalculation need a value as another an argument of a calculation (e.g. difference from). This enum specifies how to find the reference value.

Values:

Value

Previous

Next

enum XLPivotSummary

An enum that specifies how are grouped pivot field values summed up in a single cell of a pivot table.

[ISO-29500] 18.18.17 ST_DataConsolidateFunction

Values:

Sum: Values are summed up.

Count

Average

Minimum

Maximum

Product

CountNumbers

StandardDeviation

PopulationStandardDeviation

Variance

PopulationVariance

enum XLPivotAreaType

An area of aspect of pivot table that is part of the XLPivotArea.Type.

[ISO-29500] 18.18.58 ST_PivotAreaType

Values:

None

Normal

Data

All

Origin

Button

TopRight

TopEnd

enum XLPivotCacheValueType

An enum that represents types of values in pivot cache records. It represents values under CT_Record type.

Values:

Missing: A blank value. Keep at 0 so newly allocated arrays of values have a value of missing.

Number: Double precision number, not NaN or infinity.

Boolean: Bool value.

Error: XLError value.

String: Cache value is a string. Because references can’t be converted to number (GC would not accept it), the value is an index into a table of strings in the cache.

DateTime: Value is a date time. Although the value can be in theory csd:dateTime (i.e. with offsets and zulu), the time offsets are not permitted (Excel refused to load cache data) and zulu is ignored.

Index: Value is a reference to the shared item. The index value is an index into the shared items array of the field.

enum XLFilterAreaOrder

Values:

DownThenOver

OverThenDown

enum XLItemsToRetain

Specifies the number of unused items to allow in a IXLPivotCache before discarding unused items.

Values:

Automatic

The threshold is set automatically based on the number of items.

Default behavior.

None: When even one item is unused.

Max: When all shared items of a filed are unused.

enum XLPivotSortType

An enum describing how are values of a pivot field are sorted.

[ISO-29500] 18.18.28 ST_FieldSortType.

Values:

Default: Field values are sorted manually.

Ascending: Field values are sorted in ascending order.

Descending: Field values are sorted in descending order.

enum XLPivotSubtotals

Values:

DoNotShow

AtTop

AtBottom

enum XLPivotTableTheme

Values:

None

PivotStyleDark1

PivotStyleDark10

PivotStyleDark11

PivotStyleDark12

PivotStyleDark13

PivotStyleDark14

PivotStyleDark15

PivotStyleDark16

PivotStyleDark17

PivotStyleDark18

PivotStyleDark19

PivotStyleDark2

PivotStyleDark20

PivotStyleDark21

PivotStyleDark22

PivotStyleDark23

PivotStyleDark24

PivotStyleDark25

PivotStyleDark26

PivotStyleDark27

PivotStyleDark28

PivotStyleDark3

PivotStyleDark4

PivotStyleDark5

PivotStyleDark6

PivotStyleDark7

PivotStyleDark8

PivotStyleDark9

PivotStyleLight1

PivotStyleLight10

PivotStyleLight11

PivotStyleLight12

PivotStyleLight13

PivotStyleLight14

PivotStyleLight15

PivotStyleLight16

PivotStyleLight17

PivotStyleLight18

PivotStyleLight19

PivotStyleLight2

PivotStyleLight20

PivotStyleLight21

PivotStyleLight22

PivotStyleLight23

PivotStyleLight24

PivotStyleLight25

PivotStyleLight26

PivotStyleLight27

PivotStyleLight28

PivotStyleLight3

PivotStyleLight4

PivotStyleLight5

PivotStyleLight6

PivotStyleLight7

PivotStyleLight8

PivotStyleLight9

PivotStyleMedium1

PivotStyleMedium10

PivotStyleMedium11

PivotStyleMedium12

PivotStyleMedium13

PivotStyleMedium14

PivotStyleMedium15

PivotStyleMedium16

PivotStyleMedium17

PivotStyleMedium18

PivotStyleMedium19

PivotStyleMedium2

PivotStyleMedium20

PivotStyleMedium21

PivotStyleMedium22

PivotStyleMedium23

PivotStyleMedium24

PivotStyleMedium25

PivotStyleMedium26

PivotStyleMedium27

PivotStyleMedium28

PivotStyleMedium3

PivotStyleMedium4

PivotStyleMedium5

PivotStyleMedium6

PivotStyleMedium7

PivotStyleMedium8

PivotStyleMedium9

enum XLPivotTableSourceType

Values:

Area: A range in a sheet of the workbook.

Named: Book-scoped named range or a table.

enum XLSheetProtectionElements

Values:

None

AutoFilter

DeleteColumns

DeleteRows

EditObjects

EditScenarios

FormatCells

FormatColumns

FormatRows

InsertColumns

InsertHyperlinks

InsertRows

PivotTables

SelectLockedCells

SelectUnlockedCells

Sort

DeleteEverything

FormatEverything

InsertEverything

SelectEverything

Everything

enum XLWorkbookProtectionElements

Values:

None

Structure

Windows: The Windows option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac. Select the Windows option if you want to prevent users from moving, resizing, or closing the workbook window, or hide/unhide windows.

Everything

enum XLShiftDeletedCells

Values:

ShiftCellsUp

ShiftCellsLeft

enum XLTransposeOptions

A behavior of extra outside cells for transpose operation. The option is meaningful only for transposition of non-squared ranges, because squared ranges can always be transposed without effecting outside cells.

Values:

MoveCells: Shift cells of the smaller side to its direction so there is a space to transpose other side (e.g. if A1:C5 range is transposed, move D1:XFD5 are moved 2 columns to the right).

ReplaceCells: Data of the cells are replaced by the transposed cells.

enum XLSearchContents

Values:

Values

Formulas

ValuesAndFormulas

enum XLScope

Values:

Workbook

Worksheet

enum XLSortOrder

Values:

Ascending

Descending

enum XLSortOrientation

Values:

TopToBottom

LeftToRight

enum XLRangeType

Values:

Range

Column

Row

RangeColumn

RangeRow

Table

Worksheet

enum XLPhoneticAlignment

Values:

Center

Distributed

Left

NoControl

enum XLPhoneticType

Values:

FullWidthKatakana

HalfWidthKatakana

Hiragana

NoConversion

enum XLDisplayBlanksAsValues

Values:

Interpolate

NotPlotted

Zero

enum XLSparklineAxisMinMax

Values:

Automatic

SameForAll

Custom

enum XLSparklineMarkers

Values:

None

HighPoint

LowPoint

FirstPoint

LastPoint

NegativePoints

Markers

All

enum XLSparklineType

Values:

Line

Column

Stacked

enum XLColorType

Values:

Color

Theme

Indexed

enum XLThemeColor

Values:

Background1

Text1

Background2

Text2

Accent1

Accent2

Accent3

Accent4

Accent5

Accent6

Hyperlink

FollowedHyperlink

enum XLAlignmentReadingOrderValues

Values:

ContextDependent

LeftToRight

RightToLeft

enum XLAlignmentHorizontalValues

Values:

Center

CenterContinuous

Distributed

Fill

General

Justify

Left

Right

enum XLAlignmentVerticalValues

Values:

Bottom

Center

Distributed

Justify

Top

enum XLBorderStyleValues

Values:

DashDot

DashDotDot

Dashed

Dotted

Double

Hair

Medium

MediumDashDot

MediumDashDotDot

MediumDashed

None

SlantDashDot

Thick

Thin

enum XLFillPatternValues

Values:

DarkDown

DarkGray

DarkGrid

DarkHorizontal

DarkTrellis

DarkUp

DarkVertical

Gray0625

Gray125

LightDown

LightGray

LightGrid

LightHorizontal

LightTrellis

LightUp

LightVertical

MediumGray

None

Solid

enum XLFontUnderlineValues

Values:

Double

DoubleAccounting

None

Single

SingleAccounting

enum XLFontVerticalTextAlignmentValues

Values:

Baseline

Subscript

Superscript

enum XLFontFamilyNumberingValues

Values:

NotApplicable

Roman

Swiss

Modern

Script

Decorative

enum XLFontCharSet

Values:

Ansi: ASCII character set.

Default: System default character set.

Symbol: Symbol character set.

Mac: Characters used by Macintosh.

ShiftJIS: Japanese character set.

Hangul: Korean character set.

Hangeul: Another common spelling of the Korean character set.

Johab: Korean character set.

GB2312: Chinese character set used in mainland China.

ChineseBig5: Chinese character set used mostly in Hong Kong SAR and Taiwan.

Greek: Greek character set.

Turkish: Turkish character set.

Vietnamese: Vietnamese character set.

Hebrew: Hebrew character set.

Arabic: Arabic character set.

Baltic: Baltic character set.

Russian: Russian character set.

Thai: Thai character set.

EastEurope: Eastern European character set.

Oem: Extended ASCII character set used with disk operating system (DOS) and some Microsoft Windows fonts.

enum XLFontScheme

A font theme scheme. Theme has categories of fonts and when the theme changes, texts that are associated with the particular theme scheme are switched to a font of a new theme.

Values:

None: Not a part of theme scheme.

Major: A major font of a theme, generally used for headings.

Minor: A minor font of a theme, generally used to body and paragraphs.

enum XLTotalsRowFunction

Values:

None

Sum

Minimum

Maximum

Average

Count

CountNumbers

StandardDeviation

Variance

Custom

enum XLCellsUsedOptions

Values:

None

NoConstraints

Contents

DataType

NormalFormats

ConditionalFormats

Comments

DataValidation

MergedRanges

Sparklines

AllFormats

AllContents

All

enum XLClearOptions

Values:

Contents

NormalFormats

ConditionalFormats

Comments

DataValidation

MergedRanges

Sparklines

AllFormats

AllContents

All

enum XLCalculateMode

Values:

Auto

AutoNoTable

Manual

Default

enum XLReferenceStyle

Values:

R1C1

A1

Default

enum XLCellSetValueBehavior

Values:

Smart: Analyze input string and convert value. For avoid analyzing use escape symbol ‘

Simple: Direct set value. If value has unsupported type - value will be stored as string returned by object.ToString()

class AbstractPivotFieldReference: Subclassed by PivotLabelFieldReference, PivotValueFieldReference

class Blank

A blank value. Used as a value of blank cells or as an optional argument for function calls.

Public Static Attributes

readonly Blank Value = new(): Represents the sole instance of the Blank class.

class ColorExtensions

class DateTimeExtensions

class ><TKey, TValue> DictionaryComparer : public IEqualityComparer< Dictionary< TKey, TValue>

class DictionaryExtensions

class DoubleExtensions

Public Static Functions

Int32 RoundToInt (this Double value)

Round the number to the integer.

A helper method to avoid need to specify the midpoint rounding and casting each time.

Double Round (this Double value, int digits)

Round the number to specified number of digits.

A helper method to avoid need to specify the midpoint rounding each time.

class DoubleValueExtensions

class EnumConverter

class ><T> EnumerableComparer : public IEqualityComparer< IEnumerable< T>

class EnumerableExtensions

Public Static Functions

IEnumerable<T> SkipLast<T> (this IEnumerable<T> source): Skip last element of a sequence.

class FontBaseExtensions

class FormulaSlice : public ISlice 

class IntegerExtensions

interface ISheetListener

An interface for components reacting on changes in a worksheet.

Subclassed by XLCalcEngine

Public Functions

void OnInsertAreaAndShiftDown (XLWorksheet sheet, XLSheetRange area)

A handler called after the area was put into the sheet and cells shifted down.

Param sheet: Sheet where change happened.
Param area: Area that has been inserted. The original cells were shifted down.

void OnInsertAreaAndShiftRight (XLWorksheet sheet, XLSheetRange area)

A handler called after the area was put into the sheet and cells shifted right.

Param sheet: Sheet where change happened.
Param area: Area that has been inserted. The original cells were shifted right.

void OnDeleteAreaAndShiftLeft (XLWorksheet sheet, XLSheetRange deletedRange)

A handler called after the area was deleted from the sheet and cells shifted left.

Param sheet: Sheet where change happened.
Param deletedRange: Range that has been deleted and cells to the right were shifted left.

void OnDeleteAreaAndShiftUp (XLWorksheet sheet, XLSheetRange deletedRange)

A handler called after the area was deleted from the sheet and cells shifted up.

Param sheet: Sheet where change happened.
Param deletedRange: Range that has been deleted and cells below were shifted up.

interface ISlice

An interface for methods of Slice<TElement> without specified type of an element.

Subclassed by FormulaSlice, Slice< TElement >, ValueSlice

Public Functions

void Clear (XLSheetRange range): Clear all values in the range and mark them as unused.

void DeleteAreaAndShiftLeft (XLSheetRange rangeToDelete): Clear all values in the rangeToDelete and shift all values right of the deleted area to the deleted place.

void DeleteAreaAndShiftUp (XLSheetRange rangeToDelete): Clear all values in the rangeToDelete and shift all values below the deleted area to the deleted place.

IEnumerator<XLSheetPoint> GetEnumerator (XLSheetRange range, bool reverse = false)

Get all used points in a slice.

Param range: Range to iterate over.
Param reverse: false = left to right, top to bottom. true = right to left, bottom to top.

void InsertAreaAndShiftDown (XLSheetRange range): Shift all values at the range and all cells below it down by XLSheetRange.Height of the range . The insert area is cleared.

void InsertAreaAndShiftRight (XLSheetRange range): Shift all values at the range and all cells right of it to the right by XLSheetRange.Width of the range . The insert area is cleared.

bool IsUsed (XLSheetPoint address): Does slice contains a non-default value at specified point?

void Swap (XLSheetPoint sp1, XLSheetPoint sp2): Swap content of two points.

Properties

bool IsEmpty { get; set; }: Is at least one cell in the slice used?

int MaxColumn { get; set; }: Get maximum used column in the slice or 0, if no column is used.

int MaxRow { get; set; }: Get maximum used row in the slice or 0, if no row is used.

int >.KeyCollection UsedColumns { get; set; }: A set of columns that have at least one used cell. Order of columns is non-deterministic.

int > UsedRows { get; set; }: A set of rows that have at least one used cell. Order of rows is non-deterministic.

interface IXLAddress : public IEqualityComparer<IXLAddress>, public IEquatable<IXLAddress>

Reference to a single cell in a workbook. Reference can be absolute, relative or mixed. Reference can be with or without a worksheet.

Subclassed by XLAddress

Properties

IXLWorksheet Worksheet { get; set; }: Worksheet of the reference. Value is null for address without a worksheet.

interface IXLAddressable

A very lightweight interface for entities that have an address as a rectangular range.

Subclassed by IXLRangeBase

Properties

IXLRangeAddress RangeAddress { get; set; }: Gets an object with the boundaries of this range.

interface IXLAlignment : public IEquatable<IXLAlignment>

Subclassed by XLAlignment

Properties

XLAlignmentHorizontalValues Horizontal { get; set; }: Gets or sets the cell’s horizontal alignment.

XLAlignmentVerticalValues Vertical { get; set; }: Gets or sets the cell’s vertical alignment.

Int32 Indent { get; set; }: Gets or sets the cell’s text indentation.

Boolean JustifyLastLine { get; set; }: Gets or sets whether the cell’s last line is justified or not.

XLAlignmentReadingOrderValues ReadingOrder { get; set; }: Gets or sets the cell’s reading order.

Int32 RelativeIndent { get; set; }: Gets or sets the cell’s relative indent.

Boolean ShrinkToFit { get; set; }: Gets or sets whether the cell’s font size should decrease to fit the contents.

Int32 TextRotation { get; set; }: Gets or sets the cell’s text rotation in degrees. Allowed values are -90 (text is rotated clockwise) to 90 (text is rotated counterclockwise) and 255 for vertical layout of a text.

Boolean WrapText { get; set; }: Gets or sets whether the cell’s text should wrap if it doesn’t fit.

Boolean TopToBottom { get; set; }

Gets or sets whether the cell’s text should be displayed from to to bottom

(as opposed to the normal left to right).

interface IXLAutoFilter

Autofilter can sort and filter (hide) values in a non-empty area of a sheet. Each table can have autofilter and each worksheet can have at most one range with an autofilter. First row of the area contains headers, remaining rows contain sorted and filtered data.

Sorting of rows is done Sort method, using the passed parameters. The sort properties (SortColumn and SortOrder) are updated from properties passed to the Sort method. Sorting can be done only on values of one column.

Autofilter can filter rows through Reapply method. The filter evaluates conditions of the autofilter and leaves visible only rows that satisfy the conditions. Rows that don’t satisfy filter conditions are marked as hidden. Filter conditions can be specified for each column (accessible through Column(string) methods), e.g. sheet.AutoFilter.Column(1).Top(10, XLTopBottomType.Percent) creates a filter that displays only rows with values in top 10% percentile.

Subclassed by XLAutoFilter

Public Functions

IXLAutoFilter Clear (): Disable autofilter, remove all filters and unhide all rows of the Range.

IXLFilterColumn Column (String columnLetter)

Get filter configuration for a column.

Param columnLetter: Column letter that determines number in the range, from A as the first column of a Range.
Throws ArgumentOutOfRangeException: Invalid column.
Return: Filter configuration for the column.

IXLFilterColumn Column (Int32 columnNumber)

Get filter configuration for a column.

Param columnNumber: Column number in the range, from 1 as the first column of a Range.
Return: Filter configuration for the column.

IXLAutoFilter Reapply ()

Apply autofilter filters to the range and show every row that satisfies the conditions and hide the ones that don’t satisfy conditions.

Filter is generally automatically applied on a filter change. This method could be called after a cell value change or row deletion.

IXLAutoFilter Sort (Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)

Sort rows of the range using data of one column.

This method sets Sorted, SortColumn and SortOrder properties.

Param columnToSortBy: Column number in the range, from 1 to width of the Range.
Param sortOrder: Should rows be sorted in ascending or descending order?
Param matchCase: Should XLDataType.Text values on the column be matched case sensitive.
Param ignoreBlanks: true - rows with blank value in the column will always at the end, regardless of sorting order. false - blank will be treated as empty string and sorted accordingly.

Properties

IXLRangeRow > HiddenRows { get; set; }

Get rows of Range that were hidden because they didn’t satisfy filter conditions during last filtering.

Visibility is automatically updated on filter change.

Boolean IsEnabled { get; set; }: Is autofilter enabled? When autofilter is enabled, it shows the arrow buttons and might contain some filter that hide some rows. Disabled autofilter doesn’t show arrow buttons and all rows are visible.

IXLRange Range { get; set; }: Range of the autofilter. It consists of a header in first row, followed by data rows. It doesn’t include totals row for tables.

Int32 SortColumn { get; set; }: What column was used during last Sort. Contains undefined value for not yet Sorted autofilter.

Boolean Sorted { get; set; }

Are values in the autofilter range sorted? I.e. the values were either already loaded sorted or Sort has been called at least once.

If true, SortColumn and SortOrder contain valid values.

XLSortOrder SortOrder { get; set; }: What sorting order was used during last Sort. Contains undefined value for not yet Sorted autofilter.

IXLRangeRow > VisibleRows { get; set; }

Get rows of Range that are visible because they satisfied filter conditions during last filtering.

Visibility is not updated on filter change.

interface IXLBaseCollection<TSingle, TMultiple> : public IEnumerable<TSingle>

Public Functions

TMultiple AddToNamed (String rangeName)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

The default scope for the named range is Workbook.

Param rangeName: Name of the range.

TMultiple AddToNamed (String rangeName, XLScope scope)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.

TMultiple AddToNamed (String rangeName, XLScope scope, String comment)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.
Param comment: The comments for the named range.

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (Boolean includeFormats)

Returns the collection of cells that have a value.

Param includeFormats: if set to true will return all cells with a value or a style different than the default.

TMultiple Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these ranges.

Param clearOptions: Specify what you want to clear.

Properties

Object Value { get; set; }

Sets the cells’ value.

If the object is an IEnumerable ClosedXML will copy the collection’s data into a table starting from each cell.

If the object is a range ClosedXML will copy the range starting from each cell.

Setting the value to an object (not IEnumerable/range) will call the object’s ToString() method.

ClosedXML will try to translate it to the corresponding type, if it can’t then the value will be left as a string.

The object containing the value(s) to set.

interface IXLBorder : public IEquatable<IXLBorder>: Subclassed by XLBorder

interface IXLCell

Subclassed by XLCell

Public Functions

IXLCell AddToNamed (String rangeName)

Creates a named range out of this cell.

If the named range exists, it will add this range to that named range.

The default scope for the named range is Workbook.

Param rangeName: Name of the range.

IXLCell AddToNamed (String rangeName, XLScope scope)

Creates a named range out of this cell.

If the named range exists, it will add this range to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.

IXLCell AddToNamed (String rangeName, XLScope scope, String comment)

Creates a named range out of this cell.

If the named range exists, it will add this range to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.
Param comment: The comments for the named range.

IXLRange AsRange (): Returns this cell as an IXLRange.

IXLCell Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this cell.

Param clearOptions: Specify what you want to clear.

IXLCell CopyFrom (IXLRangeBase rangeBase)

Copy range content to an area of same size starting at the cell. Original content of cells is overwritten.

Param rangeBase: Range whose content to copy.
Return: This cell.

IXLComment CreateComment (): Creates a new comment for the cell, replacing the existing one.

IXLDataValidation CreateDataValidation (): Creates a new data validation rule for the cell, replacing the existing one.

XLHyperlink CreateHyperlink (): Creates a new hyperlink replacing the existing one.

IXLRichText CreateRichText (): Replaces a value of the cell with a newly created rich text object.

void Delete (XLShiftDeletedCells shiftDeleteCells)

Deletes the current cell and shifts the surrounding cells according to the shiftDeleteCells parameter.

Param shiftDeleteCells: How to shift the surrounding cells.

IXLComment GetComment (): Returns the comment for the cell or create a new instance if there is no comment on the cell.

IXLDataValidation GetDataValidation (): Returns a data validation rule assigned to the cell, if any, or creates a new instance of data validation rule if no rule exists.

Boolean GetBoolean ()

Gets the cell’s value as a Boolean.

Shortcut for Value.GetBoolean()

Throws InvalidCastException: If the value of the cell is not a logical.

Double GetDouble ()

Gets the cell’s value as a Double.

Shortcut for Value.GetNumber()

Throws InvalidCastException: If the value of the cell is not a number.

String GetText ()

Gets the cell’s value as a String.

Shortcut for Value.GetText(). Returned value is never null.

Throws InvalidCastException: If the value of the cell is not a text.

XLError GetError ()

Gets the cell’s value as a XLError.

Shortcut for Value.GetError()

Throws InvalidCastException: If the value of the cell is not an error.

DateTime GetDateTime ()

Gets the cell’s value as a DateTime.

Shortcut for Value.GetDateTime()

Throws InvalidCastException: If the value of the cell is not a DateTime.

TimeSpan GetTimeSpan ()

Gets the cell’s value as a TimeSpan.

Shortcut for Value.GetTimeSpan()

Throws InvalidCastException: If the value of the cell is not a TimeSpan.

Boolean TryGetValue<T> (out T value)

Try to get cell’s value converted to the T type.

Supported T types:

Boolean - uses a logic of XLCellValue.TryConvert(out Boolean)
Number (s/byte, u/short, u/int, u/long, float, double, or decimal)
- uses a logic of XLCellValue.TryConvert(out Double, System.Globalization.CultureInfo) and succeeds, if the value fits into the target type.
String - sets the result to a text representation of a cell value (using current culture).
DateTime - uses a logic of XLCellValue.TryConvert(out DateTime)
TimeSpan - uses a logic of XLCellValue.TryConvert(out TimeSpan, System.Globalization.CultureInfo)
XLError - if the value is of type XLDataType.Error, it will return the value.
Enum - tries to parse a value to a member by comparing the text of a cell value and a member name.

If the T is a nullable value type and the value of cell is blank or empty string, return null value.

If the cell value can’t be determined because formula function is not implemented, the method always returns false.

Tparam T: The requested type into which will the value be converted.
Param value: Value to store the value.
Return: true if the value was converted and the result is in the value , false otherwise.

T GetValue<T> ()

Conversion logic is identical with TryGetValue<T>.

Tparam T: The requested type into which will the value be converted.
Throws InvalidCastException: If the value can’t be converted to the type of T

String GetString (): Return cell’s value represented as a string. Doesn’t use cell’s formatting or style.

String GetFormattedString (CultureInfo culture = null)

Gets the cell’s value formatted depending on the cell’s data type and style.

Param culture: Culture used to format the string. If null (default value), use current culture.

XLHyperlink GetHyperlink (): Returns a hyperlink for the cell, if any, or creates a new instance is there is no hyperlink.

IXLRichText GetRichText (): Returns the value of the cell if it formatted as a rich text.

IXLRange InsertData (IEnumerable data)

Inserts the IEnumerable data elements and returns the range it occupies.

Param data: The IEnumerable data.

IXLRange InsertData (IEnumerable data, Boolean transpose)

Inserts the IEnumerable data elements and returns the range it occupies.

Param data: The IEnumerable data.
Param transpose: if set to true the data will be transposed before inserting.

IXLRange InsertData (DataTable dataTable)

Inserts the data of a data table.

Param dataTable: The data table.
Return: The range occupied by the inserted data

IXLTable InsertTable<T> (IEnumerable<T> data)

Inserts the IEnumerable data elements as a table and returns it.

The new table will receive a generic name: Table#

Param data: The table data.

IXLTable InsertTable<T> (IEnumerable<T> data, Boolean createTable)

Inserts the IEnumerable data elements as a table and returns it.

The new table will receive a generic name: Table#

if set to false the table will be created in memory.

Param data: The table data.
Param createTable: if set to true it will create an Excel table.

IXLTable InsertTable<T> (IEnumerable<T> data, String tableName)

Creates an Excel table from the given IEnumerable data elements.

Param data: The table data.
Param tableName: Name of the table.

IXLTable InsertTable<T> (IEnumerable<T> data, String tableName, Boolean createTable)

Inserts the IEnumerable data elements as a table and returns it.

if set to false the table will be created in memory.

Param data: The table data.
Param tableName: Name of the table.
Param createTable: if set to true it will create an Excel table.

IXLTable InsertTable (DataTable data)

Inserts the DataTable data elements as a table and returns it.

The new table will receive a generic name: Table#

Param data: The table data.

IXLTable InsertTable (DataTable data, Boolean createTable)

Inserts the DataTable data elements as a table and returns it.

The new table will receive a generic name: Table#

if set to false the table will be created in memory.

Param data: The table data.
Param createTable: if set to true it will create an Excel table.

IXLTable InsertTable (DataTable data, String tableName)

Creates an Excel table from the given DataTable data elements.

Param data: The table data.
Param tableName: Name of the table.

IXLTable InsertTable (DataTable data, String tableName, Boolean createTable)

Inserts the DataTable data elements as a table and returns it.

if set to false the table will be created in memory.

Param data: The table data.
Param tableName: Name of the table.
Param createTable: if set to true it will create an Excel table.

void InvalidateFormula (): Invalidate CachedValue so the formula will be re-evaluated next time Value is accessed. If cell does not contain formula nothing happens.

IXLCell SetValue (XLCellValue value)

Return: This cell.

string ToString (string format)

Returns a string that represents the current state of the cell according to the format.

Param format: A: address, F: formula, NF: number format, BG: background color, FG: foreground color, V: formatted value

Properties

Boolean Active { get; set; }: Is this cell the active cell of the worksheet? Setting false deactivates cell only when the cell is currently active.

IXLAddress Address { get; set; }

Gets this cell’s address, relative to the worksheet.

The cell’s address.

XLCellValue CachedValue { get; set; }

Get the value of a cell without evaluation of a formula. If the cell contains a formula, it returns the last calculated value or a blank value. If the cell doesn’t contain a formula, it returns same value as Value. May hold invalid value when NeedsRecalculation flag is True.

Can be useful to decrease a number of formula evaluations.

IXLRange CurrentRegion { get; set; }

Returns the current region. The current region is a range bounded by any combination of blank rows and blank columns

The current region.

XLDataType DataType { get; set; }

Gets the type of this cell’s data.

The type of the cell’s data.

String FormulaA1 { get; set; }

Gets or sets the cell’s formula with A1 references.

The formula with A1 references.

String FormulaR1C1 { get; set; }

Gets or sets the cell’s formula with R1C1 references.

The formula with R1C1 references.

IXLRangeAddress FormulaReference { get; set; }: An indication that value of this cell is calculated by a array formula that calculates values for cells in the referenced address. Null if not part of such formula.

Boolean NeedsRecalculation { get; set; }: Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated. Only cells with formula may return true, value cells always return false.

Boolean ShareString { get; set; }

Gets or sets a value indicating whether this cell’s text should be shared or not.

If false the cell’s text will not be shared and stored as an inline value.

IXLStyle Style { get; set; }: Gets or sets the cell’s style.

XLCellValue Value { get; set; }

Gets or sets the cell’s value.

Getter will return value of a cell or value of formula. Getter will evaluate a formula, if the cell NeedsRecalculation, before returning up-to-date value.

Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.

Boolean ShowPhonetic { get; set; }: Should the cell show phonetic (i.e. furigana) above the rich text of the cell? It shows phonetic runs in the rich text, it is not autogenerated. Default is false.

interface IXLCells : public IEnumerable<IXLCell>

Subclassed by XLCells

Public Functions

IXLCells Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these cells.

Param clearOptions: Specify what you want to clear.

void DeleteComments (): Delete the comments of these cells.

void DeleteSparklines (): Delete the sparklines of these cells.

Properties

XLCellValue Value { get; set; }

Sets the cells’ value.

Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.

String FormulaA1 { get; set; }

Sets the cells’ formula with A1 references.

The formula with A1 references.

String FormulaR1C1 { get; set; }

Sets the cells’ formula with R1C1 references.

The formula with R1C1 references.

interface IXLCFColorScaleMax: Subclassed by XLCFColorScaleMax

interface IXLCFColorScaleMid: Subclassed by XLCFColorScaleMid

interface IXLCFColorScaleMin: Subclassed by XLCFColorScaleMin

interface IXLCFConverter: Subclassed by XLCFCellIsConverter, XLCFColorScaleConverter, XLCFContainsConverter, XLCFDataBarConverter, XLCFDatesOccurringConverter, XLCFEndsWithConverter, XLCFIconSetConverter, XLCFIsBlankConverter, XLCFIsErrorConverter, XLCFNotBlankConverter, XLCFNotContainsConverter, XLCFNotErrorConverter, XLCFStartsWithConverter, XLCFTopConverter, XLCFUniqueConverter

interface IXLCFConverterExtension: Subclassed by XLCFDataBarConverterExtension

interface IXLCFDataBarMax: Subclassed by XLCFDataBarMax

interface IXLCFDataBarMin: Subclassed by XLCFDataBarMin

interface IXLCFIconSet: Subclassed by XLCFIconSet

interface IXLChart : public IXLDrawing<IXLChart>: Subclassed by XLChart

interface IXLCharts : public IEnumerable<IXLChart>: Subclassed by XLCharts

interface IXLColumn : public IXLRangeBase 

Subclassed by XLColumn

Public Functions

void Delete ()

Deletes this column and shifts the columns at the right of this one accordingly.

Don’t use in a loop due to poor performance. Use IXLRange.Delete(XLShiftDeletedCells) instead.

Int32 ColumnNumber (): Gets this column’s number

String ColumnLetter (): Gets this column’s letter

IXLColumns InsertColumnsAfter (Int32 numberOfColumns)

Inserts X number of columns at the right of this one.

All columns at the right will be shifted accordingly.

Param numberOfColumns: The number of columns to insert.

IXLColumns InsertColumnsBefore (Int32 numberOfColumns)

Inserts X number of columns at the left of this one.

This column and all at the right will be shifted accordingly.

Param numberOfColumns: The number of columns to insert.

IXLCell Cell (Int32 rowNumber)

Gets the cell in the specified row.

Param rowNumber: The cell’s row.

new IXLCells Cells (String cellsInColumn)

Returns the specified group of cells, separated by commas.

e.g. Cells(“1”), Cells(“1:5”), Cells(“1,3:5”)

Param cellsInColumn: The column cells to return.

IXLCells Cells (Int32 firstRow, Int32 lastRow)

Returns the specified group of cells.

Param firstRow: The first row in the group of cells to return.
Param lastRow: The last row in the group of cells to return.

IXLColumn AdjustToContents (): Adjusts the width of the column based on its contents.

IXLColumn AdjustToContents (Int32 startRow)

Adjusts the width of the column based on its contents, starting from the startRow.

Param startRow: The row to start calculating the column width.

IXLColumn AdjustToContents (Int32 startRow, Int32 endRow)

Adjusts the width of the column based on its contents, starting from the startRow and ending at endRow.

Param startRow: The row to start calculating the column width.
Param endRow: The row to end calculating the column width.

IXLColumn AdjustToContents (Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth)

Adjust width of the column according to the content of the cells.

Param startRow: Number of a first row whose content is considered.
Param endRow: Number of a last row whose content is considered.
Param minWidth: Minimum width of adjusted column, in NoC.
Param maxWidth: Maximum width of adjusted column, in NoC.

IXLColumn Hide (): Hides this column.

IXLColumn Unhide (): Unhides this column.

IXLColumn Group (): Adds this column to the next outline level (Increments the outline level for this column by 1).

IXLColumn Group (Boolean collapse)

Adds this column to the next outline level (Increments the outline level for this column by 1).

Param collapse: If set to true the column will be shown collapsed.

IXLColumn Group (Int32 outlineLevel)

Sets outline level for this column.

Param outlineLevel: The outline level.

IXLColumn Group (Int32 outlineLevel, Boolean collapse)

Sets outline level for this column.

Param outlineLevel: The outline level.
Param collapse: If set to true the column will be shown collapsed.

IXLColumn Ungroup (): Adds this column to the previous outline level (decrements the outline level for this column by 1).

IXLColumn Ungroup (Boolean fromAll)

Adds this column to the previous outline level (decrements the outline level for this column by 1).

Param fromAll: If set to true it will remove this column from all outline levels.

IXLColumn Collapse (): Show this column as collapsed.

IXLColumn Expand (): Expands this column (if it’s collapsed).

IXLColumn AddVerticalPageBreak (): Adds a vertical page break after this column.

new IXLColumn Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this column.

Param clearOptions: Specify what you want to clear.

Properties

Double Width { get; set; }

Gets or sets the width of this column in number of characters (NoC).

NoC are a non-linear units displayed as a column width in Excel, next to pixels. NoC combined with default font of the workbook can express width of the column in pixels and other units.

Boolean IsHidden { get; set; }

Gets a value indicating whether this column is hidden or not.

true if this column is hidden; otherwise, false.

Int32 OutlineLevel { get; set; }

Gets or sets the outline level of this column.

The outline level of this column.

interface IXLColumns : public IEnumerable<IXLColumn>

Subclassed by XLColumns

Public Functions

void Delete (): Deletes all columns and shifts the columns at the right of them accordingly.

IXLColumns AdjustToContents (): Adjusts the width of all columns based on its contents.

IXLColumns AdjustToContents (Int32 startRow)

Adjusts the width of all columns based on its contents, starting from the startRow.

Param startRow: The row to start calculating the column width.

IXLColumns AdjustToContents (Int32 startRow, Int32 endRow)

Adjusts the width of all columns based on its contents, starting from the startRow and ending at endRow.

Param startRow: The row to start calculating the column width.
Param endRow: The row to end calculating the column width.

void Hide (): Hides all columns.

void Unhide (): Unhides all columns.

void Group (): Increments the outline level of all columns by 1.

void Group (Boolean collapse)

Increments the outline level of all columns by 1.

Param collapse: If set to true the columns will be shown collapsed.

void Group (Int32 outlineLevel)

Sets outline level for all columns.

Param outlineLevel: The outline level.

void Group (Int32 outlineLevel, Boolean collapse)

Sets outline level for all columns.

Param outlineLevel: The outline level.
Param collapse: If set to true the columns will be shown collapsed.

void Ungroup (): Decrements the outline level of all columns by 1.

void Ungroup (Boolean fromAll)

Decrements the outline level of all columns by 1.

Param fromAll: If set to true it will remove the columns from all outline levels.

void Collapse (): Show all columns as collapsed.

void Expand (): Expands all columns (if they’re collapsed).

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (Boolean includeFormats)

Returns the collection of cells that have a value.

Param includeFormats: if set to true will return all cells with a value or a style different than the default.

IXLColumns AddVerticalPageBreaks (): Adds a vertical page break after these columns.

IXLColumns Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these columns.

Param clearOptions: Specify what you want to clear.

Properties

Double Width { get; set; }

Sets the width of all columns.

The width of all columns.

interface IXLComment : public IXLFormattedText<IXLComment>, public IXLDrawing<IXLComment>

Subclassed by XLComment

Public Functions

IXLComment SetAuthor (String value)

Sets the name of the comment’s author

Param value: Author’s name

IXLRichString AddSignature (): Adds a bolded line with the author’s name

Properties

String Author { get; set; }: Gets or sets this comment’s author’s name

interface IXLConditionalFormat

Subclassed by XLConditionalFormat

Properties

IXLRange Range { get; set; }: The first of the Ranges.

interface IXLConditionalFormats : public IEnumerable<IXLConditionalFormat>: Subclassed by XLConditionalFormats

interface IXLCustomFilteredColumn

interface IXLCustomProperties : public IEnumerable<IXLCustomProperty>: Subclassed by XLCustomProperties

interface IXLCustomProperty: Subclassed by XLCustomProperty

interface IXLDataValidation

Subclassed by XLDataValidation

Public Functions

void AddRange (IXLRange range)

Add a range to the collection of ranges this rule applies to. If the specified range does not belong to the worksheet of the data validation rule it is transferred to the target worksheet.

Param range: A range to add.

void AddRanges (IEnumerable<IXLRange> ranges)

Add a collection of ranges to the collection of ranges this rule applies to. Ranges that do not belong to the worksheet of the data validation rule are transferred to the target worksheet.

Param ranges: Ranges to add.

void ClearRanges (): Detach data validation rule of all ranges it applies to.

bool RemoveRange (IXLRange range)

Remove the specified range from the collection of range this rule applies to.

Param range: A range to remove.

Properties

IXLRange > Ranges { get; set; }: A collection of ranges the data validation rule applies too.

interface IXLDataValidations : public IEnumerable<IXLDataValidation>

Subclassed by XLDataValidations

Public Functions

IXLDataValidation Add (IXLDataValidation dataValidation)

Add data validation rule to the collection. If the specified rule refers to another worksheet than the collection, the copy will be created and its ranges will refer to the worksheet of the collection. Otherwise the original instance will be placed in the collection.

Param dataValidation: A data validation rule to add.
Return: The instance that has actually been added in the collection (may be a copy of the specified one).

IEnumerable<IXLDataValidation> GetAllInRange (IXLRangeAddress rangeAddress): Get all data validation rules applied to ranges that intersect the specified range.

bool TryGet (IXLRangeAddress rangeAddress, out IXLDataValidation dataValidation)

Get the data validation rule for the range with the specified address if it exists.

Param rangeAddress: A range address.
Param dataValidation: Data validation rule which ranges collection includes the specified address. The specified range should be fully covered with the data validation rule. For example, if the rule is applied to ranges A1:A3,C1:C3 then this method will return True for ranges A1:A3, C1:C2, A2:A3, and False for ranges A1:C3, A1:C1, etc.
Return: True is the data validation rule was found, false otherwise.

interface IXLDefinedName

Public Functions

IXLDefinedName CopyTo (IXLWorksheet targetSheet)

Copy sheet-scoped defined name to a different sheet. The references to the original sheet are changed to refer to the targetSheet :

Cell ranges (Org!A1 will be New!A1).
Tables - if the target sheet contains a table of same size at same place as the original sheet.
Sheet-specified names (Org!Name will be New!Name, but the actual name won’t be created).

Param targetSheet: Target sheet where to copy the defined name.
Throws InvalidOperationException: Defined name is workbook-scoped
Throws InvalidOperationException: Trying to copy defined name to the same sheet.

void Delete (): Deletes this named range (not the cells).

Properties

String? Comment { get; set; }

Gets or sets the comment for this named range.

The comment for this named range.

bool IsValid { get; set; }

Checks if the named range contains invalid references (#REF!).

Defined name with a formula SUM(#REF!A1, Sheet7!B4) would return true, because #REF!A1 is an invalid reference.

String Name { get; set; }

Gets or sets the name of the range.

The name of the range.

Throws ArgumentException: Set value is not a valid name.
Throws InvalidOperationException: The name is colliding with a different name that is already defined in the collection.

IXLRanges Ranges { get; set; }

Gets the ranges associated with this named range.

Note: A named range can point to multiple ranges.

String RefersTo { get; set; }: A formula of the named range. In most cases, name is just a range (e.g. Sheet5!$A$4), but it can be a constant, lambda or other values. The name formula can contain a bang reference (e.g. reference without a sheet, but with exclamation mark !$A$5), but can’t contain plain local cell references (i.e. references without a sheet like A5).

XLNamedRangeScope Scope { get; set; }: Gets the scope of this named range.

Boolean Visible { get; set; }

Gets or sets the visibility of this named range.

true if visible; otherwise, false.

interface IXLDefinedNames : public IEnumerable<IXLDefinedName>

Subclassed by XLDefinedNames

Public Functions

IXLDefinedName DefinedName (String name)

Gets the specified defined name.

Param name: Name identifier.
Throws KeyNotFoundException: Name wasn’t found.

IXLDefinedName Add (String name, String rangeAddress)

Adds a new defined name.

Param name: Name identifier to add.
Param rangeAddress: The range address to add.
Throws ArgumentException: The name or address is invalid.

IXLDefinedName Add (String name, IXLRange range)

Adds a new defined name.

Param name: Name identifier to add.
Param range: The range to add.
Throws ArgumentException: The name is invalid.

IXLDefinedName Add (String name, IXLRanges ranges)

Adds a new defined name.

Param name: Name identifier to add.
Param ranges: The ranges to add.
Throws ArgumentException: The name is invalid.

IXLDefinedName Add (String name, String rangeAddress, String? comment)

Adds a new defined name.

Param name: Name identifier to add.
Param rangeAddress: The range address to add.
Param comment: The comment for the new named range.
Throws ArgumentException: The range name or address is invalid.

IXLDefinedName Add (String name, IXLRange range, String? comment)

Adds a new defined name.

Param name: Name identifier to add.
Param range: The range to add.
Param comment: The comment for the new named range.
Throws ArgumentException: The range name is invalid.

IXLDefinedName Add (String name, IXLRanges ranges, String? comment)

Adds a new defined name.

Param name: Name identifier to add.
Param ranges: The ranges to add.
Param comment: The comment for the new named range.
Throws ArgumentException: The range name is invalid.

void Delete (String name)

Deletes the specified defined name. Deleting defined name doesn’t delete referenced cells.

Param name: Name identifier to delete.

void Delete (Int32 index)

Deletes the specified defined name’s index. Deleting defined name doesn’t delete referenced cells.

Param index: Index of the defined name to delete.
Throws ArgumentOutOfRangeException: The index is outside of named ranges array.

void DeleteAll (): Deletes all defined names of this collection, i.e. a workbook or a sheet. Deleting defined name doesn’t delete referenced cells.

IEnumerable<IXLDefinedName> ValidNamedRanges (): Returns a subset of defined names that do not have invalid references.

IEnumerable<IXLDefinedName> InvalidNamedRanges (): Returns a subset of defined names that do have invalid references.

interface IXLDrawing<T>: Subclassed by XLDrawing< T >

interface IXLDrawingAlignment: Subclassed by XLDrawingAlignment

interface IXLDrawingColorsAndLines: Subclassed by XLDrawingColorsAndLines

interface IXLDrawingFont : public IXLFontBase : Subclassed by XLDrawingFont

interface IXLDrawingMargins: Subclassed by XLDrawingMargins

interface IXLDrawingPosition: Subclassed by XLDrawingPosition

interface IXLDrawingProperties: Subclassed by XLDrawingProperties

interface IXLDrawingProtection: Subclassed by XLDrawingProtection

interface IXLDrawingSize: Subclassed by XLDrawingSize

interface IXLDrawingStyle: Subclassed by XLDrawingStyle

interface IXLDrawingWeb: Subclassed by XLDrawingWeb

interface IXLElementProtection<T> : public IXLElementProtection, public ICloneable 

Public Functions

IXLElementProtection<T> AllowElement (T element, Boolean allowed = true)

Adds the specified element to the list of allowed elements. Beware that if you pass through “None”, this will have no effect.

Param element: The element to add
Param allowed: Set to true to allow the element or false to disallow the element
Return: The current protection instance

IXLElementProtection<T> AllowEverything (): Allows all elements to be edited.

IXLElementProtection<T> AllowNone (): Allows no elements to be edited. Protects all elements.

IXLElementProtection<T> CopyFrom (IXLElementProtection<T> protectable)

Copies all the protection settings from a different instance.

Param protectable: The protectable.

IXLElementProtection<T> DisallowElement (T element)

Removes the element to the list of allowed elements. Beware that if you pass through “None”, this will have no effect.

Param element: The element to remove
Return: The current protection instance

IXLElementProtection<T> Protect (Algorithm algorithm = DefaultProtectionAlgorithm)

Protects this instance without a password.

Param algorithm: The algorithm.

IXLElementProtection<T> Protect (String password, Algorithm algorithm = DefaultProtectionAlgorithm)

Protects this instance using the specified password and password hash algorithm.

Param password: The password.
Param algorithm: The algorithm.

IXLElementProtection<T> Unprotect (): Unprotects this instance without a password.

IXLElementProtection<T> Unprotect (String password)

Unprotects this instance using the specified password.

Param password: The password.

Properties

T AllowedElements { get; set; }

Gets or sets the elements that are allowed to be edited by the user, i.e. those that are not protected.

The allowed elements.

Algorithm Algorithm { get; set; }

Gets the algorithm used to hash the password.

The algorithm.

Boolean IsPasswordProtected { get; set; }

Gets a value indicating whether this instance is protected with a password.

true if this instance is password protected; otherwise, false.

Boolean IsProtected { get; set; }

Gets a value indicating whether this instance is protected, either with or without a password.

true if this instance is protected; otherwise, false.

interface IXLFileSharing: Subclassed by XLFileSharing

interface IXLFill : public IEquatable<IXLFill>: Subclassed by XLFill

interface IXLFilterColumn

AutoFilter filter configuration for one column in an autofilter area. Filters determine visibility of rows in the autofilter area. Value in the row must satisfy all filters in all columns in order for row to be visible, otherwise it is IXLRow.IsHidden.

Column can have only one type of filter, so it’s not possible to combine several different filter types on one column. Methods for adding filters clear other types or remove previously set filters when needed. Some types of filters can have multiple conditions (e.g. XLFilterType.Regular can have many values while XLFilterType.Dynamic can be only one).

Whenever filter configuration changes, the filters are immediately reapplied.

Subclassed by XLFilterColumn

Public Functions

void Clear (bool reapply = true)

Remove all filters from the column.

Param reapply: Should the autofilter be immediately reapplied?

IXLFilteredColumn AddFilter (XLCellValue value, bool reapply = true)

Switch to the XLFilterType.Regular filter if filter column has a different type (for current type FilterType) and add value to a set of allowed values. Excel displays regular filter as a list of possible values in a column with checkbox next to it and user can check which one should be displayed.

From technical perspective, the passed value is converted to a localized string (using current locale) and the column values satisfy the filter condition, when the formatted string of a cell matches any filter string.

Examples of less intuitive behavior: filter value is 2.5 in locale cs-CZ that uses “<em>,</em>” as a decimal separator. The passed value is number 2.5, converted immediately to a string 2,5. The string is used for comparison with values of cells in the column:

Number 2.5 formatted with two decimal places as 2,50 will not match.
Number 2.5 with default formatting will be matched, because its string is 2,5 in cs-CZ locale (but not in others, e.g. en-US locale).
Text 2,5 will be matched.

This behavior of course highly depends on locale and working with same file on two different locales might lead to different results.

Param value: Value of the filter. The type is XLCellValue, but that’s for convenience sake. The value is converted to a string and filter works with string.
Param reapply: Should the autofilter be immediately reapplied?
Return: Fluent API allowing to add additional filter value.

IXLFilteredColumn AddDateGroupFilter (DateTime date, XLDateTimeGrouping dateTimeGrouping, bool reapply = true)

Enable autofilter (if needed), switch to the XLFilterType.Regular filter if filter column has a different type (for current type FilterType) and add a filter that is satisfied when cell value is a XLDataType.DateTime and the tested date has same components from dateTimeGrouping component up to the XLDateTimeGrouping.Year component with same value as the dateTimeGrouping .

The condition basically defines a date range (based on the dateTimeGrouping ) and all dates in the range satisfy the filter. If condition is a day, all date-times in the day satisfy the filter. If condition is a month, all date-times in the month satisfy the filter.

Example:

// Filter will be satisfied if the cell value is a XLDataType.DateTime and the month,
// and year are same as the passed date. The day component in the <c>DateTime</c>
// is ignored
AddDateGroupFilter(new DateTime(2023, 7, 15), XLDateTimeGrouping.Month)

There can be multiple date group filters and they are XLFilterType.Regular filter types, i.e. they don’t delete filters from AddFilter. The cell value is satisfied, if it matches any of the text values from AddFilter or any date group filter.

Param date: Date which components are compared with date values of the column.
Param dateTimeGrouping: Starting component of the grouping. Tested date must match all date components of the date from this one to the XLDateTimeGrouping.Year.
Param reapply: Should the autofilter be immediately reapplied?
Return: Fluent API allowing to add additional date time group value.

void Top (Int32 value, XLTopBottomType type = XLTopBottomType.Items, bool reapply = true)

throws ArgumentOutOfRangeException: If value is out of range 1..500.

void Bottom (Int32 value, XLTopBottomType type = XLTopBottomType.Items, bool reapply = true)

throws ArgumentOutOfRangeException: If value is out of range 1..500.

Properties

XLFilterType FilterType { get; set; }: Current filter type used by the filter columns.

Int32 TopBottomValue { get; set; }

Configuration of a XLFilterType.TopBottom filter. It contains how many items/percent (depends on TopBottomType) should filter accept.

Returns undefined value, if FilterType is not XLFilterType.TopBottom.

XLTopBottomType TopBottomType { get; set; }

Configuration of a XLFilterType.TopBottom filter. It contains the content interpretation of a TopBottomValue property, i.e. does it mean how many percents or how many items?

Returns undefined value, if FilterType is not XLFilterType.TopBottom.

XLTopBottomPart TopBottomPart { get; set; }

Configuration of a XLFilterType.TopBottom filter. It determines if filter should accept items from top or bottom.

Returns undefined value, if FilterType is not XLFilterType.TopBottom.

XLFilterDynamicType DynamicType { get; set; }

Configuration of a XLFilterType.Dynamic filter. It determines the type of dynamic filter.

Returns undefined value, if FilterType is not XLFilterType.Dynamic.

Double DynamicValue { get; set; }

Configuration of a XLFilterType.Dynamic filter. It contains the dynamic value used by the filter, e.g. average. The interpretation depends on DynamicType.

Returns undefined value, if FilterType is not XLFilterType.Dynamic.

interface IXLFilterConnector

interface IXLFont : public IXLFontBase, public IEquatable<IXLFont>: Subclassed by XLFont

interface IXLFontBase

Subclassed by IXLDrawingFont, IXLFont, IXLPhonetics, IXLRichString

Properties

XLFontCharSet FontCharSet { get; set; }

Defines an expected character set used by the text of this font. It helps Excel to choose a font face, either because requested one isn’t present or is unsuitable. Each font file contains a list of charsets it is capable of rendering and this property is used to detect whether the charset of a text matches the rendering capabilities of a font face and is thus suitable.

Example: The FontCharSet is XLFontCharSet.Default, but the selected font name is B Mitra that contains only arabic alphabet and declares so in its file. Excel will detect this discrepancy and choose a different font to display the text. The outcome is that text is not displayed with the B Mitra font, but with a different one and user doesn’t see persian numbers. To use the B Mitra font, this property must be set to XLFontCharSet.Arabic that would match the font declared capabilities.

Due to prevalence of unicode fonts, this property is rarely used.

XLFontScheme FontScheme { get; set; }: Determines a theme font scheme a text belongs to. If the text belongs to a scheme and user changes theme in Excel, the font of the text will switch to the new theme font. Scheme font has precedence and will be used instead of a set font.

interface IXLFormattedText<T> : public IEnumerable<IXLRichString>, public IEquatable<IXLFormattedText<T>>, public IXLWithRichString 

Subclassed by XLFormattedText< T >

Public Functions

IXLFormattedText<T> CopyFrom (IXLFormattedText<T> original)

Replace the text and formatting of this text by texts and formatting from the original text.

Param original: Original to copy from.
Return: This text.

Properties

Int32 Count { get; set; }: How many rich strings is the formatted text composed of.

Int32 Length { get; set; }: Length of the whole formatted text.

String Text { get; set; }: Get text of the whole formatted text.

Boolean HasPhonetics { get; set; }: Does this text has phonetics? Unlike accessing the Phonetics property, this method doesn’t create a new instance on access.

IXLPhonetics Phonetics { get; set; }: Get or create phonetics for the text. Use HasPhonetics to check for existence to avoid unnecessary creation.

interface IXLHeaderFooter

Subclassed by XLHeaderFooter

Public Functions

String GetText (XLHFOccurrence occurrence)

Gets the text of the specified header/footer occurrence.

Param occurrence: The occurrence.

Properties

IXLHFItem Left { get; set; }: Gets the left header/footer item.

IXLHFItem Center { get; set; }: Gets the middle header/footer item.

IXLHFItem Right { get; set; }: Gets the right header/footer item.

interface IXLHFItem : public IXLWithRichString 

Subclassed by XLHFItem

Public Functions

String GetText (XLHFOccurrence occurrence)

Gets the text of the specified header/footer occurrence.

Param occurrence: The occurrence.

IXLRichString AddText (XLHFPredefinedText predefinedText)

Adds the given predefined text to this header/footer item.

Param predefinedText: The predefined text to add to this header/footer item.

IXLRichString AddText (String text, XLHFOccurrence occurrence)

Adds the given text to this header/footer item.

Param text: The text to add to this header/footer item.
Param occurrence: The occurrence for the text.

IXLRichString AddText (XLHFPredefinedText predefinedText, XLHFOccurrence occurrence)

Adds the given predefined text to this header/footer item.

Param predefinedText: The predefined text to add to this header/footer item.
Param occurrence: The occurrence for the predefined text.

void Clear (XLHFOccurrence occurrence = XLHFOccurrence.AllPages)

Clears the text/formats of this header/footer item.

Param occurrence: The occurrence to clear.

interface IXLHyperlinks : public IEnumerable<XLHyperlink>: Subclassed by XLHyperlinks

interface IXLMargins

Subclassed by XLMargins

Properties

Double Left { get; set; }

Gets or sets the Left margin.

The Left margin.

Double Right { get; set; }

Gets or sets the Right margin.

The Right margin.

Double Top { get; set; }

Gets or sets the Top margin.

The Top margin.

Double Bottom { get; set; }

Gets or sets the Bottom margin.

The Bottom margin.

Double Header { get; set; }

Gets or sets the Header margin.

The Header margin.

Double Footer { get; set; }

Gets or sets the Footer margin.

The Footer margin.

interface IXLNumberFormat : public IXLNumberFormatBase, public IEquatable<IXLNumberFormatBase>: Subclassed by XLNumberFormat

interface IXLNumberFormatBase: Subclassed by IXLNumberFormat, IXLPivotValueFormat

interface IXLOutline: Subclassed by XLOutline

interface IXLPageSetup

Subclassed by XLPageSetup

Public Functions

void SetRowsToRepeatAtTop (String range)

Sets the rows to repeat on the top of the printed pages.

Param range: The range of rows to repeat on the top of the printed pages.

void SetRowsToRepeatAtTop (Int32 firstRowToRepeatAtTop, Int32 lastRowToRepeatAtTop)

Sets the rows to repeat on the top of the printed pages.

Param firstRowToRepeatAtTop: The first row to repeat at top.
Param lastRowToRepeatAtTop: The last row to repeat at top.

void SetColumnsToRepeatAtLeft (Int32 firstColumnToRepeatAtLeft, Int32 lastColumnToRepeatAtLeft)

Sets the rows to repeat on the left of the printed pages.

Param firstColumnToRepeatAtLeft: The first column to repeat at left.
Param lastColumnToRepeatAtLeft: The last column to repeat at left.

void SetColumnsToRepeatAtLeft (String range)

Sets the rows to repeat on the left of the printed pages.

Param range: The range of rows to repeat on the left of the printed pages.

void AdjustTo (Int32 percentageOfNormalSize)

Sets the scale at which the worksheet will be printed. This is equivalent to setting the Scale property.

The worksheet will be printed on as many pages as necessary to print at the given scale.

Setting this value will override the PagesWide and PagesTall values.

Param percentageOfNormalSize: The scale at which the worksheet will be printed.

void FitToPages (Int32 pagesWide, Int32 pagesTall)

Gets or sets the number of pages the worksheet will be printed on.

This is equivalent to setting both PagesWide and PagesTall properties.

Setting this value will override the Scale value.

Param pagesWide: The pages wide.
Param pagesTall: The pages tall.

void AddHorizontalPageBreak (Int32 row)

Adds a horizontal page break after the given row.

Param row: The row to insert the break.

void AddVerticalPageBreak (Int32 column)

Adds a vertical page break after the given column.

Param column: The column to insert the break.

IXLPageSetup SetFirstPageNumber (Int32? value)

>

Param value: First page number or null for auto/default page numbering.

Properties

IXLPrintAreas PrintAreas { get; set; }: Gets an object to manage the print areas of the worksheet.

Int32 FirstRowToRepeatAtTop { get; set; }

Gets the first row that will repeat on the top of the printed pages.

Use SetRowsToRepeatAtTop() to set the rows that will be repeated on the top of the printed pages.

Int32 LastRowToRepeatAtTop { get; set; }

Gets the last row that will repeat on the top of the printed pages.

Use SetRowsToRepeatAtTop() to set the rows that will be repeated on the top of the printed pages.

Int32 FirstColumnToRepeatAtLeft { get; set; }

Gets the first column to repeat on the left of the printed pages.

The first column to repeat on the left of the printed pages.

Int32 LastColumnToRepeatAtLeft { get; set; }

Gets the last column to repeat on the left of the printed pages.

The last column to repeat on the left of the printed pages.

XLPageOrientation PageOrientation { get; set; }

Gets or sets the page orientation for printing.

The page orientation.

Int32 PagesWide { get; set; }

Gets or sets the number of pages wide (horizontal) the worksheet will be printed on.

If you don’t specify the PagesTall, Excel will adjust that value

based on the contents of the worksheet and the PagesWide number.

Setting this value will override the Scale value.

Int32 PagesTall { get; set; }

Gets or sets the number of pages tall (vertical) the worksheet will be printed on.

If you don’t specify the PagesWide, Excel will adjust that value

based on the contents of the worksheet and the PagesTall number.

Setting this value will override the Scale value.

Int32 Scale { get; set; }

Gets or sets the scale at which the worksheet will be printed.

The worksheet will be printed on as many pages as necessary to print at the given scale.

Setting this value will override the PagesWide and PagesTall values.

Int32 HorizontalDpi { get; set; }: Gets or sets the horizontal dpi for printing the worksheet.

Int32 VerticalDpi { get; set; }: Gets or sets the vertical dpi for printing the worksheet.

Int32? FirstPageNumber { get; set; }

Gets or sets the page number that will begin the printout.

For example, the first page of your printout could be numbered page 5.

First page number can be negative, e.g. -2.

Boolean CenterHorizontally { get; set; }

Gets or sets a value indicating whether the worksheet will be centered on the page horizontally.

true if the worksheet will be centered on the page horizontally; otherwise, false.

Boolean CenterVertically { get; set; }

Gets or sets a value indicating whether the worksheet will be centered on the page vertically.

true if the worksheet will be centered on the page vertically; otherwise, false.

XLPaperSize PaperSize { get; set; }: Gets or sets the size of the paper to print the worksheet.

IXLMargins Margins { get; set; }: Gets an object to work with the page margins.

IXLHeaderFooter Header { get; set; }: Gets an object to work with the page headers.

IXLHeaderFooter Footer { get; set; }: Gets an object to work with the page footers.

Boolean ScaleHFWithDocument { get; set; }

Gets or sets a value indicating whether Excel will automatically adjust the font size to the scale of the worksheet.

true if Excel will automatically adjust the font size to the scale of the worksheet; otherwise, false.

Boolean AlignHFWithMargins { get; set; }

Gets or sets a value indicating whether the header and footer margins are aligned with the left and right margins of the worksheet.

true if the header and footer margins are aligned with the left and right margins of the worksheet; otherwise, false.

Boolean ShowGridlines { get; set; }

Gets or sets a value indicating whether the gridlines will be printed.

true if the gridlines will be printed; otherwise, false.

Boolean ShowRowAndColumnHeadings { get; set; }

Gets or sets a value indicating whether to show row numbers and column letters/numbers.

true to show row numbers and column letters/numbers; otherwise, false.

Boolean BlackAndWhite { get; set; }

Gets or sets a value indicating whether the worksheet will be printed in black and white.

true if the worksheet will be printed in black and white; otherwise, false.

Boolean DraftQuality { get; set; }

Gets or sets a value indicating whether the worksheet will be printed in draft quality.

true if the worksheet will be printed in draft quality; otherwise, false.

XLPageOrderValues PageOrder { get; set; }: Gets or sets the page order for printing.

XLShowCommentsValues ShowComments { get; set; }: Gets or sets how the comments will be printed.

Int32 > RowBreaks { get; set; }: Gets a list with the row breaks (for printing).

Int32 > ColumnBreaks { get; set; }: Gets a list with the column breaks (for printing).

XLPrintErrorValues PrintErrorValue { get; set; }: Gets or sets how error values will be printed.

interface IXLPhonetic : public IEquatable<IXLPhonetic>: Subclassed by XLPhonetic

interface IXLPhonetics : public IXLFontBase, public IEnumerable<IXLPhonetic>, public IEquatable<IXLPhonetics>

Subclassed by XLPhonetics

Public Functions

IXLPhonetics Add (String text, Int32 start, Int32 end)

Add a phonetic run above a base text. Phonetic runs can’t overlap.

Param text: Text to display above a section of a base text. Can’t be empty.
Param start: Index of a first character of a base text above which should text be displayed. Valid values are 0..length-1.
Param end: The excluded ending index in a base text (the hint is not displayed above the end). Must be > start . Valid values are 1..length.

IXLPhonetics ClearText (): Remove all phonetic runs. Keeps font properties.

IXLPhonetics ClearFont (): Reset font properties to the default font of a container (likely IXLCell). Keeps phonetic runs, Type and Alignment.

Properties

Int32 Count { get; set; }: Number of phonetic runs above the base text.

interface IXLPivotCache

A cache of pivot data - essentially a collection of fields and their values that can be displayed by a IXLPivotTable. Data for the cache are retrieved from an area (a table or a range). The pivot cache data are cached, i.e. the data in the source are not immediately updated once the data in a worksheet change.

Subclassed by XLPivotCache

Public Functions

IXLPivotCache Refresh ()

Refresh data in the pivot source from the source reference data.

Throws InvalidReferenceException: The data source for the pivot table can’t be found.

IXLPivotCache SetRefreshDataOnOpen (): Sets the value to true.

IXLPivotCache SetSaveSourceData (): Sets the value to true.

Properties

String > FieldNames { get; set; }

Get names of all fields in the source, in left to right order. Every field name is unique.

The field names are case insensitive. The field names of the cached source might differ from actual names of the columns in the data cells.

XLItemsToRetain ItemsToRetainPerField { get; set; }

Gets the number of unused items in shared items to allow before discarding unused items.

Shared items are distinct values of a source field values. Updating them can be expensive and this controls, when should the cache be updated. Application-dependent attribute.

Default value is XLItemsToRetain.Automatic.

Boolean RefreshDataOnOpen { get; set; }

Will Excel refresh the cache when it opens the workbook.

Default value is false.

Boolean SaveSourceData { get; set; }

Should the cached values of the pivot source be saved into the workbook file? If source data are not saved, they will have to be refreshed from the source reference which might cause a change in the table values.

Default value is true.

interface IXLPivotCaches : public IEnumerable<IXLPivotCache>

A collection of pivot caches. Pivot cache can be added from a IXLRange or a IXLTable.

Subclassed by XLPivotCaches

Public Functions

IXLPivotCache Add (IXLRange range)

Add a new pivot cache for the range. If the range area is same as an area of a table, the created cache will reference the table as source of data instead of a range of cells.

Param range: Range for which to create the pivot cache.
Return: The pivot cache for the range.

interface IXLPivotField: Subclassed by XLPivotField

interface IXLPivotFields : public IEnumerable<IXLPivotField>: Subclassed by XLPivotFields

interface IXLPivotFieldStyleFormats: Subclassed by XLPivotFieldStyleFormats

interface IXLPivotStyleFormat: Subclassed by IXLPivotValueStyleFormat, XLPivotStyleFormat

interface IXLPivotStyleFormats : public IEnumerable<IXLPivotStyleFormat>: Subclassed by XLPivotStyleFormats

interface IXLPivotTable

Subclassed by XLPivotTable

Properties

IXLPivotCache PivotCache { get; set; }: The cache of data for the pivot table. The pivot table is created from cached data, not up-to-date data in a worksheet.

interface IXLPivotTables : public IEnumerable<IXLPivotTable>

Subclassed by XLPivotTables

Public Functions

IXLPivotTable Add (String name, IXLCell targetCell, IXLPivotCache pivotCache)

Add a pivot table that will use the pivot cache.

Param name: Name of new pivot table.
Param targetCell: A cell where will the pivot table be have it’s left top corner.
Param pivotCache: Pivot cache to use for the pivot table.
Throws ArgumentException: There already is a pivot table with the same name.
Return: Added pivot table.

IXLPivotTable Add (String name, IXLCell targetCell, IXLRange range)

Add a pivot table from source data of range . If workbook already contains a cache for same range as the range , the matching pivot cache is used.

Param name: Name of new pivot table
Param targetCell: A cell where will the pivot table be have it’s left top corner.
Param range: A range to add/find pivot cache.
Throws ArgumentException: There already is a pivot table with the same name.

IXLPivotTable Add (String name, IXLCell targetCell, IXLTable table)

Add a pivot table from source data of table . If workbook already contains a cache for same range as the table , the matching pivot cache is used.

Param name: Name of new pivot table
Param targetCell: A cell where will the pivot table be have it’s left top corner.
Param table: A table to add/find pivot cache.
Throws ArgumentException: There already is a pivot table with the same name.

IXLPivotTable PivotTable (String name)

Get pivot table with the specified name (case insensitive).

Param name: Name of a pivot table to return.
Throws KeyNotFoundException: No such pivot table found.

interface IXLPivotTableStyleFormats: Subclassed by XLPivotTableStyleFormats

interface IXLPivotValue

A pivot value field, it is basically a specification of how to determine and format values from source to display in the pivot table.

Subclassed by XLPivotValue

Properties

String BaseFieldName { get; set; }

Specifies the index to the base field when the ShowDataAs calculation is in use. Instead of base item, previous or next value can be used through CalculationItem

Used only if the value should be showed Show Values As in the value field settings.

The name of the column of the relevant base field.

Show values as a percent of a specific value of a different field, e.g. as a % of units sold from Q1 (quarts is a base field and Q1 is a base item).

XLCellValue BaseItemValue { get; set; }

The value of a base item to calculate a value to show in the pivot table. The base item is selected from values of a base field.

Used only if the value should be showed Show Values As in the value field settings.

The value of the referenced base field item.

Show values as a percent of a specific value of a different field, e.g. as a % of units sold from Q1 (quarts is a base field and Q1 is a base item).

interface IXLPivotValueCombination

An interface for fluent configuration of how to show IXLPivotValue, when the value should be displayed not as a value itself, but in relation to another value (e.g. percentage difference in relation to different value).

Subclassed by XLPivotValueCombination

Public Functions

IXLPivotValue AndPrevious ()

The base item value for calculation will be the value of the previous row of base field, depending on the order of base field values in a row/column. If there isn’t a previous value, the same value will be used.

This only affects display how are values displayed, not the values themselves.

Example: We have a table of sales and a pivot table, where sales are summed per month. The months are sorted from Jan to Dec. To display a percentage increase of sales per month (the base value is previous month): IXLPivotValue sales; sales.SetSummaryFormula(XLPivotSummary.Sum).ShowAsPercentageDifferenceFrom("Month").AndPrevious();

interface IXLPivotValueFormat : public IXLNumberFormatBase, public IEquatable<IXLNumberFormatBase>: Subclassed by XLPivotValueFormat

interface IXLPivotValues : public IEnumerable<IXLPivotValue>: Subclassed by XLPivotValues

interface IXLPivotValueStyleFormat : public IXLPivotStyleFormat 

Subclassed by XLPivotValueStyleFormat

Public Functions

IXLPivotValueStyleFormat AndWith (IXLPivotField field, Predicate<XLCellValue> predicate)

Adds a further limitation so the IXLPivotStyleFormat.Style is only applied to cells in a pivot table that are are within the field that has some values.

The pivot style is bound by the field index in a pivot table, not field value. E.g. if field values are Jan, Feb and the predicate marks Feb (offset 1) = second field (Feb) will be highlighted. If user later reverses order in Excel to Feb, Jan, the style would still apply to the second value - Jan.

Param field: Only cells in a pivot table under this field will be styled.
Param predicate: A predicate to determine which index of the field should be styled.

interface IXLPrintAreas : public IEnumerable<IXLRange>

Subclassed by XLPrintAreas

Public Functions

void Clear (): Removes the print areas from the worksheet.

void Add (Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn)

Adds a range to the print areas.

Param firstCellRow: The first cell row.
Param firstCellColumn: The first cell column.
Param lastCellRow: The last cell row.
Param lastCellColumn: The last cell column.

void Add (String rangeAddress)

Adds a range to the print areas.

Param rangeAddress: The range address to add.

void Add (String firstCellAddress, String lastCellAddress)

Adds a range to the print areas.

Param firstCellAddress: The first cell address.
Param lastCellAddress: The last cell address.

void Add (IXLAddress firstCellAddress, IXLAddress lastCellAddress)

Adds a range to the print areas.

Param firstCellAddress: The first cell address.
Param lastCellAddress: The last cell address.

interface IXLProtectable<TProtection, in TElement> : public IXLProtectable 

Public Functions

TProtection Protect (TElement allowedElements): Protects this instance without a password.

new TProtection Protect (Algorithm algorithm = DefaultProtectionAlgorithm): Protects this instance without a password.

TProtection Protect (Algorithm algorithm, TElement allowedElements)

Protects this instance with the specified password, password hash algorithm and set elements that the user is allowed to change.

Param algorithm: The algorithm.
Param allowedElements: The allowed elements.

new TProtection Protect (String password, Algorithm algorithm = DefaultProtectionAlgorithm)

Protects this instance using the specified password and password hash algorithm.

Param password: The password.
Param algorithm: The algorithm.

TProtection Protect (String password, Algorithm algorithm, TElement allowedElements)

Protects this instance with the specified password, password hash algorithm and set elements that the user is allowed to change.

Param password: The password.
Param algorithm: The algorithm.
Param allowedElements: The allowed elements.

new TProtection Unprotect (): Unprotects this instance without a password.

new TProtection Unprotect (String password)

Unprotects this instance using the specified password.

Param password: The password.

IXLElementProtection Protect (Algorithm algorithm = DefaultProtectionAlgorithm): Protects this instance without a password.

IXLElementProtection Protect (String password, Algorithm algorithm = DefaultProtectionAlgorithm)

Protects this instance using the specified password and password hash algorithm.

Param password: The password.
Param algorithm: The algorithm.

IXLElementProtection Unprotect (): Unprotects this instance without a password.

IXLElementProtection Unprotect (String password)

Unprotects this instance using the specified password.

Param password: The password.

Properties

Boolean IsPasswordProtected { get; set; }

Gets a value indicating whether this instance is protected with a password.

true if this instance is password protected; otherwise, false.

Boolean IsProtected { get; set; }

Gets a value indicating whether this instance is protected, either with or without a password.

true if this instance is protected; otherwise, false.

interface IXLProtection : public IEquatable<IXLProtection>: Subclassed by XLProtection

interface IXLRange : public IXLRangeBase 

Subclassed by IXLTable, IXLTableRange, XLRange

Public Functions

IXLCell Cell (int row, int column)

Gets the cell at the specified row and column.

The cell address is relative to the parent range.

Param row: The cell’s row.
Param column: The cell’s column.

IXLCell Cell (string cellAddressInRange)

Gets the cell at the specified address.

The cell address is relative to the parent range.

Param cellAddressInRange: The cell address in the parent range.

IXLCell Cell (int row, string column)

Gets the cell at the specified row and column.

The cell address is relative to the parent range.

Param row: The cell’s row.
Param column: The cell’s column.

IXLCell Cell (IXLAddress cellAddressInRange)

Gets the cell at the specified address.

The cell address is relative to the parent range.

Param cellAddressInRange: The cell address in the parent range.

IXLRangeColumn Column (int columnNumber)

Gets the specified column of the range.

Param columnNumber: 1-based column number relative to the first column of this range.
Return: The relevant column

IXLRangeColumn Column (string columnLetter)

Gets the specified column of the range.

Param columnLetter: Column letter.

IXLRangeColumn FirstColumn (Func<IXLRangeColumn, Boolean> predicate = null): Gets the first column of the range.

IXLRangeColumn FirstColumnUsed (XLCellsUsedOptions options, Func<IXLRangeColumn, Boolean> predicate = null)

Gets the first non-empty column of the range that contains a cell with a value.

Param options: The options to determine whether a cell is used.
Param predicate: The predicate to choose cells.

IXLRangeColumn LastColumn (Func<IXLRangeColumn, Boolean> predicate = null): Gets the last column of the range.

IXLRangeColumn LastColumnUsed (XLCellsUsedOptions options, Func<IXLRangeColumn, Boolean> predicate = null)

Gets the last non-empty column of the range that contains a cell with a value.

Param options: The options to determine whether a cell is used.
Param predicate: The predicate to choose cells.

IXLRangeColumns Columns (Func<IXLRangeColumn, Boolean> predicate = null): Gets a collection of all columns in this range.

IXLRangeColumns Columns (int firstColumn, int lastColumn)

Gets a collection of the specified columns in this range.

Param firstColumn: The first column to return. 1-based column number relative to the first column of this range.
Param lastColumn: The last column to return. 1-based column number relative to the first column of this range.

IXLRangeColumns Columns (string firstColumn, string lastColumn)

Gets a collection of the specified columns in this range.

Param firstColumn: The first column to return.
Param lastColumn: The last column to return.
Return: The relevant columns

IXLRangeColumns Columns (string columns)

Gets a collection of the specified columns in this range, separated by commas.

e.g. Columns(“G:H”), Columns(“10:11,13:14”), Columns(“P:Q,S:T”), Columns(“V”)

Param columns: The columns to return.

IXLRangeColumn FindColumn (Func<IXLRangeColumn, Boolean> predicate): Returns the first row that matches the given predicate

IXLRangeRow FindRow (Func<IXLRangeRow, Boolean> predicate): Returns the first row that matches the given predicate

IXLRangeRow FirstRow (Func<IXLRangeRow, Boolean> predicate = null): Gets the first row of the range.

IXLRangeRow FirstRowUsed (XLCellsUsedOptions options, Func<IXLRangeRow, Boolean> predicate = null)

Gets the first non-empty row of the range that contains a cell with a value.

Param options: The options to determine whether a cell is used.
Param predicate: The predicate to choose cells.

IXLRangeRow LastRow (Func<IXLRangeRow, Boolean> predicate = null): Gets the last row of the range.

IXLRangeRow LastRowUsed (XLCellsUsedOptions options, Func<IXLRangeRow, Boolean> predicate = null)

Gets the last non-empty row of the range that contains a cell with a value.

Param options: The options to determine whether a cell is used.
Param predicate: The predicate to choose cells.

IXLRangeRow Row (int row)

Gets the specified row of the range.

Param row: 1-based row number relative to the first row of this range.
Return: The relevant row

IXLRangeRows Rows (int firstRow, int lastRow)

Gets a collection of the specified rows in this range.

Param firstRow: The first row to return. 1-based row number relative to the first row of this range.
Param lastRow: The last row to return. 1-based row number relative to the first row of this range.

IXLRangeRows Rows (string rows)

Gets a collection of the specified rows in this range, separated by commas.

e.g. Rows(“4:5”), Rows(“7:8,10:11”), Rows(“13”)

Param rows: The rows to return.

IXLRange Range (IXLRangeAddress rangeAddress)

Returns the specified range.

Param rangeAddress: The range boundaries.

IXLRange Range (string rangeAddress)

Returns the specified range.

e.g. Range(“A1”), Range(“A1:C2”)

Param rangeAddress: The range boundaries.

IXLRange Range (IXLCell firstCell, IXLCell lastCell)

Returns the specified range.

Param firstCell: The first cell in the range.
Param lastCell: The last cell in the range.

IXLRange Range (string firstCellAddress, string lastCellAddress)

Returns the specified range.

Param firstCellAddress: The first cell address in the range.
Param lastCellAddress: The last cell address in the range.

IXLRange Range (IXLAddress firstCellAddress, IXLAddress lastCellAddress)

Returns the specified range.

Param firstCellAddress: The first cell address in the range.
Param lastCellAddress: The last cell address in the range.

IXLRanges Ranges (string ranges)

Returns a collection of ranges, separated by commas.

e.g. Ranges(“A1”), Ranges(“A1:C2”), Ranges(“A1:B2,D1:D4”)

Param ranges: The ranges to return.

IXLRange Range (int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn)

Returns the specified range.

Param firstCellRow: The first cell’s row of the range to return.
Param firstCellColumn: The first cell’s column of the range to return.
Param lastCellRow: The last cell’s row of the range to return.
Param lastCellColumn: The last cell’s column of the range to return.
Return: .

int RowCount (): Gets the number of rows in this range.

int ColumnCount (): Gets the number of columns in this range.

IXLRangeColumns InsertColumnsAfter (int numberOfColumns)

Inserts X number of columns to the right of this range.

All cells to the right of this range will be shifted X number of columns.

Param numberOfColumns: Number of columns to insert.

IXLRangeColumns InsertColumnsBefore (int numberOfColumns)

Inserts X number of columns to the left of this range.

This range and all cells to the right of this range will be shifted X number of columns.

Param numberOfColumns: Number of columns to insert.

IXLRangeRows InsertRowsAbove (int numberOfRows)

Inserts X number of rows on top of this range.

This range and all cells below this range will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

IXLRangeRows InsertRowsBelow (int numberOfRows)

Inserts X number of rows below this range.

All cells below this range will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

void Delete (XLShiftDeletedCells shiftDeleteCells)

Deletes this range and shifts the surrounding cells accordingly.

Param shiftDeleteCells: How to shift the surrounding cells.

void Transpose (XLTransposeOptions transposeOption)

Transposes the contents and styles of all cells in this range.

Param transposeOption: How to handle the surrounding cells when transposing the range.

IXLTable AsTable ()

Use this range as a table, but do not add it to the Tables list

NOTES:

The AsTable method will use the first row of the range as a header row.

If this range contains only one row, then an empty data row will be inserted into the returned table.

IXLTable AsTable (String name)

Use this range as a table with the passed name, but do not add it to the Tables list

NOTES:

The AsTable method will use the first row of the range as a header row.

If this range contains only one row, then an empty data row will be inserted into the returned table.

Param name: Table name to be used.

IXLRange Sort ()

Sort rows of the range using the SortColumns (if non-empty) or by using all columns of the range in ascending order.

This method can be used fort sorting, after user specified desired sorting order in SortColumns.

Return: This range.

IXLRange Sort (String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)

Sort rows of the range according to values in columns specified by columnsToSortBy .

2 DESC, 1, C asc means sort by second column of a range in descending order, then by first column of a range in sortOrder and then by column C in ascending order.

.

Param columnsToSortBy: Columns which should be used to sort the range and their order. Columns are separated by a comma (,). The column can be specified either by column number or by column letter. Sort order is parsed case insensitive and can be ASC or DESC. The specified column is relative to the origin of the range.
Param sortOrder: What should be the default sorting order or columns in columnsToSortBy without specified sorting order.
Param matchCase: When cell value is a XLDataType.Text, should sorting be case insensitive (false, Excel default behavior) or case sensitive (true). Doesn’t affect other cell value types.
Param ignoreBlanks: When true (recommended, matches Excel behavior), blank cell values are always sorted at the end regardless of sorting order. When false, blank values are considered empty strings and are sorted among other cell values with a type XLDataType.Text.
Return: This range.

IXLRange Sort (Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)

Sort rows of the range according to values in columnToSortBy column.

Param columnToSortBy: Column number that will be used to sort the range rows.
Param sortOrder: Sorting order used by columnToSortBy .
Param matchCase
Param ignoreBlanks
Return: This range.

IXLRange SortLeftToRight (XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true)

Sort columns in a range. The sorting is done using the values in each column of the range.

Param sortOrder: In what order should columns be sorted
Param matchCase
Param ignoreBlanks
Return: This range.

new IXLRange Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this range.

Param clearOptions: Specify what you want to clear.

Properties

IXLSortElements SortRows { get; set; }: Rows used for sorting columns. Automatically updated each time a SortLeftToRight(XLSortOrder, bool, bool) is called.

IXLSortElements SortColumns { get; set; }

Columns used for sorting rows. Automatically updated each time a Sort(String, XLSortOrder, bool, bool) or Sort(Int32, XLSortOrder, bool, bool).

User can set desired sorting order here and then call Sort() method.

interface IXLRangeAddress

Subclassed by XLRangeAddress

Public Functions

IXLRange? AsRange ()

Allocates the current range address in the internal range repository and returns it

Return: Range of the address or null, if the range is not a valid address.

IXLRangeAddress Intersection (IXLRangeAddress otherRangeAddress)

Returns the intersection of this range address with another range address on the same worksheet.

Param otherRangeAddress: The other range address.
Return: The intersection’s range address

Boolean IsEntireColumn ()

Determines whether range address spans the entire column.

Return: true if is entire column; otherwise, false.

Boolean IsEntireRow ()

Determines whether range address spans the entire row.

Return: true if is entire row; otherwise, false.

Boolean IsEntireSheet ()

Determines whether the range address spans the entire worksheet.

Return: true if is entire sheet; otherwise, false.

IXLRangeAddress Relative (IXLRangeAddress sourceRangeAddress, IXLRangeAddress targetRangeAddress)

Returns a range address so that its offset from the target base address is equal to the offset of the current range address to the source base address. For example, if the current range address is D4:E4, the source base address is A1:C3, then the relative address to the target base address B10:D13 is E14:F14

Param sourceRangeAddress: The source base range address.
Param targetRangeAddress: The target base range address.
Return: The relative range

Properties

int ColumnSpan { get; set; }: Gets the number of columns in the area covered by the range address.

IXLAddress FirstAddress { get; set; }

Gets or sets the first address in the range.

The first address.

Boolean IsValid { get; set; }

Gets or sets a value indicating whether this range is valid.

true if this instance is valid; otherwise, false.

IXLAddress LastAddress { get; set; }

Gets or sets the last address in the range.

The last address.

int NumberOfCells { get; set; }: Gets the number of cells in the area covered by the range address.

int RowSpan { get; set; }: Gets the number of rows in the area covered by the range address.

interface IXLRangeBase : public IXLAddressable 

Subclassed by IXLColumn, IXLRange, IXLRangeColumn, IXLRangeRow, IXLRow, IXLWorksheet, XLRangeBase

Public Functions

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value. Formats are ignored.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

IXLCells Search (String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false)

Searches the cells’ contents for a given piece of text

Param searchText: The search text.
Param compareOptions: The compare options.
Param searchFormulae: if set to true search formulae instead of cell values.

IXLCell FirstCell (): Returns the first cell of this range.

IXLCell FirstCellUsed ()

Returns the first non-empty cell with a value of this range. Formats are ignored.

The cell’s address is going to be ([First Row with a value], [First Column with a value])

IXLCell FirstCellUsed (XLCellsUsedOptions options)

Returns the first non-empty cell with a value of this range.

Param options: The options to determine whether a cell is used.

IXLCell FirstCellUsed (XLCellsUsedOptions options, Func<IXLCell, Boolean> predicate)

Returns the first non-empty cell with a value of this range.

Param options: The options to determine whether a cell is used.
Param predicate: The predicate used to choose cells

IXLCell LastCell (): Returns the last cell of this range.

IXLCell LastCellUsed ()

Returns the last non-empty cell with a value of this range. Formats are ignored.

The cell’s address is going to be ([Last Row with a value], [Last Column with a value])

IXLCell LastCellUsed (XLCellsUsedOptions options)

Returns the last non-empty cell with a value of this range.

Param options: The options to determine whether a cell is used.

Boolean Contains (String rangeAddress)

Determines whether this range contains the specified range (completely).

For partial matches use the range.Intersects method.

Param rangeAddress: The range address.
Return: true if this range contains the specified range; otherwise, false.

Boolean Contains (IXLRangeBase range)

Determines whether this range contains the specified range (completely).

For partial matches use the range.Intersects method.

Param range: The range to match.
Return: true if this range contains the specified range; otherwise, false.

Boolean Intersects (String rangeAddress)

Determines whether this range intersects the specified range.

For whole matches use the range.Contains method.

Param rangeAddress: The range address.
Return: true if this range intersects the specified range; otherwise, false.

Boolean Intersects (IXLRangeBase range)

Determines whether this range contains the specified range.

For whole matches use the range.Contains method.

Param range: The range to match.
Return: true if this range intersects the specified range; otherwise, false.

IXLRange Unmerge (): Unmerges this range.

IXLRange Merge (): Merges this range. Only the top-left cell will have a value, other values will be blank.

IXLRange AddToNamed (String name)

Creates/adds this range to workbook scoped IXLDefinedNames.

If the named range exists, it will add this range to that named range.

Param name: Name of the defined name, without sheet.

IXLRange AddToNamed (String name, XLScope scope)

Creates/adds this range to IXLDefinedNames.

If the named range exists, it will add this range to that named range.

Param name: Name of the defined name, without sheet.
Param scope: The scope for the named range.

IXLRange AddToNamed (String name, XLScope scope, String comment)

Creates/adds this range to IXLDefinedNames.

If the named range exists, it will add this range to that named range.

Param name: Name of the defined name, without sheet.
Param scope: The scope for the named range.
Param comment: The comments for the named range.

IXLRangeBase Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this range.

Param clearOptions: Specify what you want to clear.

void DeleteComments (): Deletes the cell comments from this range.

IXLRangeBase SetValue (XLCellValue value): Set value to all cells in the range.

IXLRange AsRange (): Converts this object to a range.

Boolean IsEntireColumn ()

Determines whether range address spans the entire column.

Return: true if is entire column; otherwise, false.

Boolean IsEntireRow ()

Determines whether range address spans the entire row.

Return: true if is entire row; otherwise, false.

Boolean IsEntireSheet ()

Determines whether the range address spans the entire worksheet.

Return: true if is entire sheet; otherwise, false.

IXLDataValidation GetDataValidation (): Returns a data validation rule assigned to the range, if any, or creates a new instance of data validation rule if no rule exists.

IXLDataValidation CreateDataValidation (): Creates a new data validation rule for the range, replacing the existing one.

IXLRangeBase Grow (): Grows this the current range by one cell to each side

IXLRangeBase Grow (Int32 growCount)

Grows this the current range by the specified number of cells to each side.

Param growCount: The grow count.

IXLRangeBase Shrink (): Shrinks this current range by one cell.

IXLRangeBase Shrink (Int32 shrinkCount)

Shrinks the current range by the specified number of cells from each side.

Param shrinkCount: The shrink count.

IXLRangeAddress Intersection (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)

Returns the intersection of this range with another range on the same worksheet.

Param otherRange: The other range.
Param thisRangePredicate: Predicate applied to this range’s cells.
Param otherRangePredicate: Predicate applied to the other range’s cells.
Return: The range address of the intersection

IXLCells SurroundingCells (Func<IXLCell, Boolean> predicate = null)

Returns the set of cells surrounding the current range.

Param predicate: The predicate to apply on the resulting set of cells.

IXLCells Union (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)

Calculates the union of two ranges on the same worksheet.

Param otherRange: The other range.
Param thisRangePredicate: Predicate applied to this range’s cells.
Param otherRangePredicate: Predicate applied to the other range’s cells.
Return: The union

IXLCells Difference (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)

Returns all cells in the current range that are not in the other range.

Param otherRange: The other range.
Param thisRangePredicate: Predicate applied to this range’s cells.
Param otherRangePredicate: Predicate applied to the other range’s cells.

IXLRangeBase Relative (IXLRangeBase sourceBaseRange, IXLRangeBase targetBaseRange)

Returns a range so that its offset from the target base range is equal to the offset of the current range to the source base range. For example, if the current range is D4:E4, the source base range is A1:C3, then the relative range to the target base range B10:D13 is E14:F14

Param sourceBaseRange: The source base range.
Param targetBaseRange: The target base range.
Return: The relative range

Properties

XLCellValue Value { get; set; }

Sets a value to every cell in this range.

Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.

String FormulaA1 { get; set; }

Sets the cells’ formula with A1 references.

The formula with A1 references.

String FormulaArrayA1 { get; set; }

Create an array formula for all cells in the range.

Throws InvalidOperationException: When the range overlaps with a table, pivot table, merged cells or partially overlaps another array formula.

String FormulaR1C1 { get; set; }

Sets the cells’ formula with R1C1 references.

The formula with R1C1 references.

Boolean ShareString { get; set; }

Gets or sets a value indicating whether this cell’s text should be shared or not.

If false the cell’s text will not be shared and stored as an inline value.

interface IXLRangeColumn : public IXLRangeBase 

Subclassed by XLRangeColumn

Public Functions

IXLCell Cell (Int32 rowNumber)

Gets the cell in the specified row.

Param rowNumber: The cell’s row.

new IXLCells Cells (String cellsInColumn)

Returns the specified group of cells, separated by commas.

e.g. Cells(“1”), Cells(“1:5”), Cells(“1:2,4:5”)

Param cellsInColumn: The column cells to return.

IXLCells Cells (Int32 firstRow, Int32 lastRow)

Returns the specified group of cells.

Param firstRow: The first row in the group of cells to return.
Param lastRow: The last row in the group of cells to return.

IXLRangeColumns InsertColumnsAfter (int numberOfColumns)

Inserts X number of columns to the right of this range.

All cells to the right of this range will be shifted X number of columns.

Param numberOfColumns: Number of columns to insert.

IXLRangeColumns InsertColumnsBefore (int numberOfColumns)

Inserts X number of columns to the left of this range.

This range and all cells to the right of this range will be shifted X number of columns.

Param numberOfColumns: Number of columns to insert.

IXLCells InsertCellsAbove (int numberOfRows)

Inserts X number of cells on top of this column.

This column and all cells below it will be shifted X number of rows.

Param numberOfRows: Number of cells to insert.

IXLCells InsertCellsBelow (int numberOfRows)

Inserts X number of cells below this range.

All cells below this column will be shifted X number of rows.

Param numberOfRows: Number of cells to insert.

void Delete (): Deletes this range and shifts the cells at the right.

void Delete (XLShiftDeletedCells shiftDeleteCells)

Deletes this range and shifts the surrounding cells accordingly.

Param shiftDeleteCells: How to shift the surrounding cells.

Int32 ColumnNumber (): Gets this column’s number in the range

String ColumnLetter (): Gets this column’s letter in the range

new IXLRangeColumn Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this column.

Param clearOptions: Specify what you want to clear.

interface IXLRangeColumns : public IEnumerable<IXLRangeColumn>

Subclassed by XLRangeColumns

Public Functions

void Add (IXLRangeColumn columRange)

Adds a column range to this group.

Param columRange: The column range to add.

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

void Delete (): Deletes all columns and shifts the columns at the right of them accordingly.

IXLRangeColumns Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these columns.

Param clearOptions: Specify what you want to clear.

interface IXLRangeRow : public IXLRangeBase 

Subclassed by IXLTableRow, XLRangeRow

Public Functions

IXLCell Cell (Int32 columnNumber)

Gets the cell in the specified column.

Param columnNumber: The cell’s column.

IXLCell Cell (String columnLetter)

Gets the cell in the specified column.

Param columnLetter: The cell’s column.

new IXLCells Cells (String cellsInRow)

Returns the specified group of cells, separated by commas.

e.g. Cells(“1”), Cells(“1:5”), Cells(“1:2,4:5”)

Param cellsInRow: The row’s cells to return.

IXLCells Cells (Int32 firstColumn, Int32 lastColumn)

Returns the specified group of cells.

Param firstColumn: The first column in the group of cells to return.
Param lastColumn: The last column in the group of cells to return.

IXLCells Cells (String firstColumn, String lastColumn)

Returns the specified group of cells.

Param firstColumn: The first column in the group of cells to return.
Param lastColumn: The last column in the group of cells to return.

IXLCells InsertCellsAfter (int numberOfColumns)

Inserts X number of cells to the right of this row.

All cells to the right of this row will be shifted X number of columns.

Param numberOfColumns: Number of cells to insert.

IXLCells InsertCellsBefore (int numberOfColumns)

Inserts X number of cells to the left of this row.

This row and all cells to the right of it will be shifted X number of columns.

Param numberOfColumns: Number of cells to insert.

IXLRangeRows InsertRowsAbove (int numberOfRows)

Inserts X number of rows on top of this row.

This row and all cells below it will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

IXLRangeRows InsertRowsBelow (int numberOfRows)

Inserts X number of rows below this row.

All cells below this row will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

void Delete (): Deletes this range and shifts the cells below.

void Delete (XLShiftDeletedCells shiftDeleteCells)

Deletes this range and shifts the surrounding cells accordingly.

Param shiftDeleteCells: How to shift the surrounding cells.

Int32 RowNumber (): Gets this row’s number in the range

new IXLRangeRow Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this row.

Param clearOptions: Specify what you want to clear.

interface IXLRangeRows : public IEnumerable<IXLRangeRow>

Subclassed by XLRangeRows

Public Functions

void Add (IXLRangeRow rowRange)

Adds a row range to this group.

Param rowRange: The row range to add.

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

void Delete (): Deletes all rows and shifts the rows below them accordingly.

IXLRangeRows Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these rows.

Param clearOptions: Specify what you want to clear.

interface IXLRanges : public IEnumerable<IXLRange>

Subclassed by XLRanges

Public Functions

void Add (IXLRangeBase range)

Adds the specified range to this group.

Param range: The range to add to this group.

bool Remove (IXLRange range)

Removes the specified range from this group.

Param range: The range to remove from this group.

void RemoveAll (Predicate<IXLRange>? match = null, bool releaseEventHandlers = true)

Removes ranges matching the criteria from the collection, optionally releasing their event handlers.

Param match: Criteria to filter ranges. Only those ranges that satisfy the criteria will be removed. Null means the entire collection should be cleared.
Param releaseEventHandlers: Specify whether or not should removed ranges be unsubscribed from row/column shifting events. Until ranges are unsubscribed they cannot be collected by GC.

IEnumerable<IXLRange> GetIntersectedRanges (IXLRangeAddress rangeAddress): Filter ranges from a collection that intersect the specified address. Is much more efficient that using Linq expression .Where().

IEnumerable<IXLRange> GetIntersectedRanges (IXLAddress address): Filter ranges from a collection that intersect the specified address. Is much more efficient that using Linq expression .Where().

IEnumerable<IXLRange> GetIntersectedRanges (IXLCell cell): Filter ranges from a collection that intersect the specified cell. Is much more efficient that using Linq expression .Where().

IXLDataValidation CreateDataValidation (): Creates a new data validation rule for the ranges collection, replacing the existing ones.

IXLRanges AddToNamed (String rangeName)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

The default scope for the named range is Workbook.

Param rangeName: Name of the range.

IXLRanges AddToNamed (String rangeName, XLScope scope)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.

IXLRanges AddToNamed (String rangeName, XLScope scope, String comment)

Creates a named range out of these ranges.

If the named range exists, it will add these ranges to that named range.

Param rangeName: Name of the range.
Param scope: The scope for the named range.
Param comment: The comments for the named range.

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

IXLRanges Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these ranges.

Param clearOptions: Specify what you want to clear.

IXLRanges Consolidate (): Create a new collection of ranges which are consolidated version of source ranges.

Properties

XLCellValue Value { get; set; }

Sets the cells’ value.

Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.

interface IXLRichString : public IXLFontBase, public IEquatable<IXLRichString>, public IXLWithRichString : Subclassed by XLRichString

interface IXLRichText : public IXLFormattedText<IXLRichText>: Subclassed by XLRichText

interface IXLRow : public IXLRangeBase 

Subclassed by XLRow

Public Functions

void ClearHeight (): Clears the height for the row and defaults it to the spreadsheet row height.

void Delete ()

Deletes this row and shifts the rows below this one accordingly.

Don’t use in a loop due to poor performance. Use IXLRange.Delete(XLShiftDeletedCells) instead.

Int32 RowNumber (): Gets this row’s number

IXLRows InsertRowsBelow (Int32 numberOfRows)

Inserts X number of rows below this one.

All rows below will be shifted accordingly.

Param numberOfRows: The number of rows to insert.

IXLRows InsertRowsAbove (Int32 numberOfRows)

Inserts X number of rows above this one.

This row and all below will be shifted accordingly.

Param numberOfRows: The number of rows to insert.

IXLRow AdjustToContents (Int32 startColumn)

Adjusts the height of the row based on its contents, starting from the startColumn.

Param startColumn: The column to start calculating the row height.

IXLRow AdjustToContents (Int32 startColumn, Int32 endColumn)

Adjusts the height of the row based on its contents, starting from the startColumn and ending at endColumn.

Param startColumn: The column to start calculating the row height.
Param endColumn: The column to end calculating the row height.

IXLRow AdjustToContents (Int32 startColumn, Int32 endColumn, Double minHeightPt, Double maxHeightPt)

Adjust height of the column according to the content of the cells.

Param startColumn: Number of a first column whose content is considered.
Param endColumn: Number of a last column whose content is considered.
Param minHeightPt: Minimum height of adjusted column, in points.
Param maxHeightPt: Maximum height of adjusted column, in points.

IXLRow Hide (): Hides this row.

IXLRow Unhide (): Unhides this row.

IXLRow Group (): Adds this row to the next outline level (Increments the outline level for this row by 1).

IXLRow Group (Boolean collapse)

Adds this row to the next outline level (Increments the outline level for this row by 1).

Param collapse: If set to true the row will be shown collapsed.

IXLRow Group (Int32 outlineLevel)

Sets outline level for this row.

Param outlineLevel: The outline level.

IXLRow Group (Int32 outlineLevel, Boolean collapse)

Sets outline level for this row.

Param outlineLevel: The outline level.
Param collapse: If set to true the row will be shown collapsed.

IXLRow Ungroup (): Adds this row to the previous outline level (decrements the outline level for this row by 1).

IXLRow Ungroup (Boolean fromAll)

Adds this row to the previous outline level (decrements the outline level for this row by 1).

Param fromAll: If set to true it will remove this row from all outline levels.

IXLRow Collapse (): Show this row as collapsed.

IXLCell Cell (Int32 columnNumber)

Gets the cell in the specified column.

Param columnNumber: The cell’s column.

IXLCell Cell (String columnLetter)

Gets the cell in the specified column.

Param columnLetter: The cell’s column.

new IXLCells Cells (String cellsInRow)

Returns the specified group of cells, separated by commas.

e.g. Cells(“1”), Cells(“1:5”), Cells(“1,3:5”)

Param cellsInRow: The row’s cells to return.

IXLCells Cells (Int32 firstColumn, Int32 lastColumn)

Returns the specified group of cells.

Param firstColumn: The first column in the group of cells to return.
Param lastColumn: The last column in the group of cells to return.

IXLCells Cells (String firstColumn, String lastColumn)

Returns the specified group of cells.

Param firstColumn: The first column in the group of cells to return.
Param lastColumn: The last column in the group of cells to return.

IXLRow Expand (): Expands this row (if it’s collapsed).

IXLRow AddHorizontalPageBreak (): Adds a horizontal page break after this row.

new IXLRow Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this row.

Param clearOptions: Specify what you want to clear.

Properties

Double Height { get; set; }

Gets or sets the height of this row.

The width of this row in points.

Boolean IsHidden { get; set; }

Gets a value indicating whether this row is hidden or not.

true if this row is hidden; otherwise, false.

Int32 OutlineLevel { get; set; }

Gets or sets the outline level of this row.

The outline level of this row.

interface IXLRows : public IEnumerable<IXLRow>

Subclassed by XLRows

Public Functions

void Delete (): Deletes all rows and shifts the rows below them accordingly.

IXLRows AdjustToContents (): Adjusts the height of all rows based on its contents.

IXLRows AdjustToContents (Int32 startColumn)

Adjusts the height of all rows based on its contents, starting from the startColumn.

Param startColumn: The column to start calculating the row height.

IXLRows AdjustToContents (Int32 startColumn, Int32 endColumn)

Adjusts the height of all rows based on its contents, starting from the startColumn and ending at endColumn.

Param startColumn: The column to start calculating the row height.
Param endColumn: The column to end calculating the row height.

void Hide (): Hides all rows.

void Unhide (): Unhides all rows.

void Group (): Increments the outline level of all rows by 1.

void Group (Boolean collapse)

Increments the outline level of all rows by 1.

Param collapse: If set to true the rows will be shown collapsed.

void Group (Int32 outlineLevel)

Sets outline level for all rows.

Param outlineLevel: The outline level.

void Group (Int32 outlineLevel, Boolean collapse)

Sets outline level for all rows.

Param outlineLevel: The outline level.
Param collapse: If set to true the rows will be shown collapsed.

void Ungroup (): Decrements the outline level of all rows by 1.

void Ungroup (Boolean fromAll)

Decrements the outline level of all rows by 1.

Param fromAll: If set to true it will remove the rows from all outline levels.

void Collapse (): Show all rows as collapsed.

void Expand (): Expands all rows (if they’re collapsed).

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

IXLRows AddHorizontalPageBreaks (): Adds a horizontal page break after these rows.

IXLRows Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these rows.

Param clearOptions: Specify what you want to clear.

Properties

Double Height { get; set; }

Sets the height of all rows.

The height of all rows.

interface IXLSheetProtection : public IXLElementProtection<XLSheetProtectionElements>: Subclassed by XLSheetProtection

interface IXLSheetView

Subclassed by XLSheetView

Public Functions

void Freeze (Int32 rows, Int32 columns)

Freezes the specified rows and columns.

Param rows: The rows to freeze.
Param columns: The columns to freeze.

void FreezeColumns (Int32 columns)

Freezes the left X columns.

Param columns: The columns to freeze.

void FreezeRows (Int32 rows)

Freezes the top X rows.

Param rows: The rows to freeze.

Properties

Int32 SplitColumn { get; set; }: Gets or sets the column after which the horizontal split should take place.

Int32 SplitRow { get; set; }: Gets or sets the row after which the vertical split should take place.

IXLAddress TopLeftCellAddress { get; set; }

Gets or sets the location of the top left visible cell

The scroll position’s top left cell.

Int32 ZoomScale { get; set; }

Window zoom magnification for current view representing percent values. Horizontal and vertical scale together.

Representing percent values ranging from 10 to 400.

Int32 ZoomScaleNormal { get; set; }

Zoom magnification to use when in normal view. Horizontal and vertical scale together

Representing percent values ranging from 10 to 400.

Int32 ZoomScalePageLayoutView { get; set; }

Zoom magnification to use when in page layout view. Horizontal and vertical scale together.

Representing percent values ranging from 10 to 400.

Int32 ZoomScaleSheetLayoutView { get; set; }

Zoom magnification to use when in page break preview. Horizontal and vertical scale together.

Representing percent values ranging from 10 to 400.

interface IXLSortElement

Properties

Int32 ElementNumber { get; set; }: Column or row number whose values will be used for sorting.

XLSortOrder SortOrder { get; set; }: Sorting order.

Boolean IgnoreBlanks { get; set; }: When true (recommended, matches Excel behavior), blank cell values are always sorted at the end regardless of sorting order. When false, blank values are considered empty strings and are sorted among other cell values with a type XLDataType.Text.

Boolean MatchCase { get; set; }: When cell value is a XLDataType.Text, should sorting be case insensitive (false, Excel default behavior) or case sensitive (true). Doesn’t affect other cell value types.

interface IXLSortElements : public IEnumerable<IXLSortElement>: Subclassed by XLSortElements

interface IXLSparkline: Subclassed by XLSparkline

interface IXLSparklineGroup : public IEnumerable<IXLSparkline>

Subclassed by XLSparklineGroup

Public Functions

IXLSparklineGroup CopyTo (IXLWorksheet targetSheet)

Copy this sparkline group to the specified worksheet

Param targetSheet: The worksheet to copy this sparkline group to

interface IXLSparklineGroups : public IEnumerable<IXLSparklineGroup>: Subclassed by XLSparklineGroups

interface IXLSparklineHorizontalAxis: Subclassed by XLSparklineHorizontalAxis

interface IXLSparklineStyle: Subclassed by XLSparklineStyle

interface IXLSparklineVerticalAxis: Subclassed by XLSparklineVerticalAxis

interface IXLStyle : public IEquatable<IXLStyle>

Subclassed by XLStyle

Properties

Boolean IncludeQuotePrefix { get; set; }: Should the text values of a cell saved to the file be prefixed by a quote (') character? Has no effect if cell values is not a XLDataType.Text. Doesn’t affect values during runtime, text values are returned without quote.

interface IXLStylized

Subclassed by XLCell, XLCells, XLColumns, XLConditionalFormat, XLRangeBase, XLRangeColumns, XLRangeRows, XLRanges, XLRows, XLStylizedBase, XLStylizedEmpty, XLTableRows

Properties

XLStyleValue StyleValue { get; set; }: Immutable style

interface IXLTable : public IXLRange 

Subclassed by XLTable

Public Functions

new IXLTable Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this table.

Param clearOptions: Specify what you want to clear.

IXLTableField Field (string fieldName)

Get field of the table.

Param fieldName: Name of the field. Field names are case-insensitive.
Throws ArgumentOutOfRangeException: Table doesn’t contain fieldName field.
Return: Requested field.

IXLRange AppendData (IEnumerable data, Boolean propagateExtraColumns = false)

Appends the IEnumerable data elements and returns the range of the new rows.

Param data: The IEnumerable data.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The range of the new rows.

IXLRange AppendData (IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false)

Appends the IEnumerable data elements and returns the range of the new rows.

Param data: The IEnumerable data.
Param transpose: if set to true the data will be transposed before inserting.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The range of the new rows.

IXLRange AppendData (DataTable dataTable, Boolean propagateExtraColumns = false)

Appends the data of a data table and returns the range of the new rows.

Param dataTable: The data table.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The range of the new rows.

IXLRange AppendData<T> (IEnumerable<T> data, Boolean propagateExtraColumns = false)

Appends the IEnumerable data elements and returns the range of the new rows.

Tparam T
Param data: The table data.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The range of the new rows.

IXLRange ReplaceData (IEnumerable data, Boolean propagateExtraColumns = false)

Replaces the IEnumerable data elements and returns the table’s data range.

Param data: The IEnumerable data.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The table’s data range.

IXLRange ReplaceData (IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false)

Replaces the IEnumerable data elements and returns the table’s data range.

Param data: The IEnumerable data.
Param transpose: if set to true the data will be transposed before inserting.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The table’s data range.

IXLRange ReplaceData (DataTable dataTable, Boolean propagateExtraColumns = false)

Replaces the data from the records of a data table and returns the table’s data range.

Param dataTable: The data table.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The table’s data range.

IXLRange ReplaceData<T> (IEnumerable<T> data, Boolean propagateExtraColumns = false)

Replaces the IEnumerable data elements as a table and the table’s data range.

Tparam T
Param data: The table data.
Param propagateExtraColumns: if set to true propagate extra columns’ values and formulas.
Return: The table’s data range.

IXLTable Resize (IXLRange range)

Resizes the table to the specified range address.

Param range: The new table range.

IXLTable Resize (IXLRangeAddress rangeAddress)

Resizes the table to the specified range address.

Param rangeAddress: The range boundaries.

IXLTable Resize (string rangeAddress)

Resizes the table to the specified range address.

Param rangeAddress: The range boundaries.

IXLTable Resize (IXLCell firstCell, IXLCell lastCell)

Resizes the table to the specified range.

Param firstCell: The first cell in the range.
Param lastCell: The last cell in the range.

IXLTable Resize (string firstCellAddress, string lastCellAddress)

Resizes the table to the specified range.

Param firstCellAddress: The first cell address in the worksheet.
Param lastCellAddress: The last cell address in the worksheet.

IXLTable Resize (IXLAddress firstCellAddress, IXLAddress lastCellAddress)

Resizes the table to the specified range.

Param firstCellAddress: The first cell address in the worksheet.
Param lastCellAddress: The last cell address in the worksheet.

IXLTable Resize (int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn)

Resizes the table to the specified range.

Param firstCellRow: The first cell’s row of the range to return.
Param firstCellColumn: The first cell’s column of the range to return.
Param lastCellRow: The last cell’s row of the range to return.
Param lastCellColumn: The last cell’s column of the range to return.

IEnumerable<dynamic> AsDynamicEnumerable (): Converts the table to an enumerable of dynamic objects

DataTable AsNativeDataTable (): Converts the table to a standard .NET System.Data.DataTable

Properties

string Name { get; set; }

Change the name of a table. Structural references to the table are not updated.

Throws ArgumentException: If the new table name is already used by other table in the sheet.

interface IXLTableField

Subclassed by XLTableField

Public Functions

void Delete (): Deletes this table field from the table.

Boolean IsConsistentDataType (): Determines whether all cells this table field have a consistent data type.

Boolean IsConsistentFormula (): Determines whether all cells this table field have a consistent formula.

Boolean IsConsistentStyle (): Determines whether all cells this table field have a consistent style.

Properties

IXLRangeColumn Column { get; set; }

Gets the corresponding column for this table field. Includes the header and footer cells

The column.

IXLCells DataCells { get; set; }

Gets the collection of data cells for this field Excludes the header and footer cells

The data cells

IXLCell TotalsCell { get; set; }

Gets the footer cell for the table field.

The footer cell. null, if the table doesn’t have set IXLTable.ShowTotalsRow.

IXLCell HeaderCell { get; set; }

Gets the header cell for the table field.

The header cell.null, if the table doesn’t have set IXLTable.ShowHeaderRow.

Int32 Index { get; set; }

Gets the index of the column (0-based).

The index.

String Name { get; set; }

Gets or sets the name/header of this table field. The corresponding header cell’s value will change if you set this.

The name.

IXLTable Table { get; set; }: Gets the underlying table for this table field.

String TotalsRowFormulaA1 { get; set; }

Gets or sets the totals row formula in A1 format.

The totals row formula a1.

String TotalsRowFormulaR1C1 { get; set; }

Gets or sets the totals row formula in R1C1 format.

The totals row formula r1 c1.

XLTotalsRowFunction TotalsRowFunction { get; set; }

Gets or sets the totals row function.

The totals row function.

String TotalsRowLabel { get; set; }

Gets or sets the totals row label (the leftmost cell in the totals row).

The totals row label.

If the totals row is not displayed for the table.

interface IXLTableRange : public IXLRange 

Subclassed by XLTableRange

Public Functions

new IXLTableRows InsertRowsAbove (int numberOfRows)

Inserts X number of rows on top of this range.

This range and all cells below this range will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

new IXLTableRows InsertRowsBelow (int numberOfRows)

Inserts X number of rows below this range.

All cells below this range will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

new IXLTableRow Row (int row)

Rows the specified row.

Param row: 1-based row number relative to the first row of this range.

new IXLTableRows Rows (int firstRow, int lastRow)

Returns a subset of the rows

Param firstRow: The first row to return. 1-based row number relative to the first row of this range.
Param lastRow: The last row to return. 1-based row number relative to the first row of this range.

new IXLTableRows Rows (string rows)

Gets a collection of the specified rows in this range, separated by commas.

e.g. Rows(“4:5”), Rows(“7:8,10:11”), Rows(“13”)

Param rows: The rows to return.

interface IXLTableRow : public IXLRangeRow 

Subclassed by XLTableRow

Public Functions

new IXLTableRow Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this row.

Param clearOptions: Specify what you want to clear.

new IXLTableRows InsertRowsAbove (int numberOfRows)

Inserts X number of rows on top of this row.

This row and all cells below it will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

new IXLTableRows InsertRowsBelow (int numberOfRows)

Inserts X number of rows below this row.

All cells below this row will be shifted X number of rows.

Param numberOfRows: Number of rows to insert.

interface IXLTableRows : public IEnumerable<IXLTableRow>

Subclassed by XLTableRows

Public Functions

void Add (IXLTableRow tableRow)

Adds a table row to this group.

Param tableRow: The row table to add.

IXLCells Cells (): Returns the collection of cells.

IXLCells CellsUsed (): Returns the collection of cells that have a value.

IXLCells CellsUsed (XLCellsUsedOptions options)

Returns the collection of cells that have a value.

Param options: The options to determine whether a cell is used.

IXLTableRows Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these rows.

Param clearOptions: Specify what you want to clear.

interface IXLTables : public IEnumerable<IXLTable>

Subclassed by XLTables

Public Functions

IXLTables Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of these tables.

Param clearOptions: Specify what you want to clear.

interface IXLTheme: Subclassed by XLTheme

interface IXLValidationCriteria: Subclassed by XLValidationCriteria

interface IXLWithRichString: Subclassed by IXLFormattedText< T >, IXLHFItem, IXLRichString

interface IXLWorkbook : public IXLProtectable<IXLWorkbookProtection, XLWorkbookProtectionElements>, public IDisposable 

Subclassed by XLWorkbook

Public Functions

IXLWorksheet AddWorksheet (DataTable dataTable)

Add a worksheet with a table at Cell(row:1, column:1). The dataTable’s name is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

Param dataTable: Datatable to insert
Return: Inserted Worksheet

IXLWorksheet AddWorksheet (DataTable dataTable, String sheetName)

Add a worksheet with a table at Cell(row:1, column:1). The sheetName provided is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

Param dataTable: dataTable to insert as Excel Table
Param sheetName: Worksheet and Excel Table name
Return: Inserted Worksheet

IXLWorksheet AddWorksheet (DataTable dataTable, String sheetName, String tableName)

Add a worksheet with a table at Cell(row:1, column:1).

Param dataTable: dataTable to insert as Excel Table
Param sheetName: Worksheet name
Param tableName: Excel Table name
Return: Inserted Worksheet

XLCellValue Evaluate (String expression)

Evaluate a formula expression.

Param expression: Formula expression to evaluate.
Throws MissingContextException: If the expression contains a function that requires a context (e.g. current cell or worksheet).

IXLDefinedName? DefinedName (String name)

Try to find a defined name. If name specifies a sheet, try to find name in the sheet first and fall back to the workbook if not found in the sheet.

Requested name Sheet1!Name will first try to find Name in a sheet Sheet1 (if such sheet exists) and if not found there, tries to find Name in workbook.

Requested name Name will be searched only in a workbooks DefinedNames.

Param name: Name of requested name, either plain name (e.g. Name) or with sheet specified (e.g. Sheet!Name).
Return: Found name or null.

void RecalculateAllFormulas (): Force recalculation of all cell formulas.

void Save (): Saves the current workbook.

void Save (Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook and optionally performs validation

void SaveAs (String file): Saves the current workbook to a file.

void SaveAs (String file, Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook to a file and optionally validates it.

void SaveAs (Stream stream): Saves the current workbook to a stream.

void SaveAs (Stream stream, Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook to a stream and optionally validates it.

IEnumerable<IXLCell> Search (String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false)

Searches the cells’ contents for a given piece of text

Param searchText: The search text.
Param compareOptions: The compare options.
Param searchFormulae: if set to true search formulae instead of cell values.

IXLTable Table (String tableName, StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)

Gets the Excel table of the given name

Param tableName: Name of the table to return.
Param comparisonType: One of the enumeration values that specifies how the strings will be compared.
Throws ArgumentOutOfRangeException: If no tables with this name could be found in the workbook.
Return: The table with given name

Properties

XLCalculateMode CalculateMode { get; set; }: Gets or sets the workbook’s calculation mode.

Double ColumnWidth { get; set; }

Gets or sets the default column width for the workbook.

All new worksheets will use this column width.

IXLDefinedNames DefinedNames { get; set; }: Gets an object to manipulate this workbook’s defined names.

IXLOutline Outline { get; set; }

Gets or sets the default outline options for the workbook.

All new worksheets will use these outline options.

IXLPageSetup PageOptions { get; set; }

Gets or sets the default page options for the workbook.

All new worksheets will use these page options.

IXLPivotCaches PivotCaches { get; set; }: Gets all pivot caches in a workbook. A one cache can be used by multiple tables. Unused caches are not saved.

XLWorkbookProperties Properties { get; set; }: Gets or sets the workbook’s properties.

XLReferenceStyle ReferenceStyle { get; set; }: Gets or sets the workbook’s reference style.

Double RowHeight { get; set; }

Gets or sets the default row height for the workbook.

All new worksheets will use this row height.

IXLStyle Style { get; set; }

Gets or sets the default style for the workbook.

All new worksheets will use this style.

IXLTheme Theme { get; set; }: Gets an object to manipulate this workbook’s theme.

IXLWorksheets Worksheets { get; set; }: Gets an object to manipulate the worksheets.

interface IXLWorkbookProtection : public IXLElementProtection<XLWorkbookProtectionElements>: Subclassed by XLWorkbookProtection

interface IXLWorksheet : public IXLRangeBase, public IXLProtectable<IXLSheetProtection, XLSheetProtectionElements>

Subclassed by XLWorksheet

Public Functions

IXLRow FirstRow (): Gets the first row of the worksheet.

IXLRow? FirstRowUsed ()

Gets the first non-empty row of the worksheet that contains a cell with a value.

Formatted empty cells do not count.

IXLRow? FirstRowUsed (XLCellsUsedOptions options)

Gets the first non-empty row of the worksheet that contains a cell with a value.

Param options: The options to determine whether a cell is used.

IXLRow LastRow (): Gets the last row of the worksheet.

IXLRow? LastRowUsed (): Gets the last non-empty row of the worksheet that contains a cell with a value.

IXLRow? LastRowUsed (XLCellsUsedOptions options)

Gets the last non-empty row of the worksheet that contains a cell with a value.

Param options: The options to determine whether a cell is used.

IXLColumn FirstColumn (): Gets the first column of the worksheet.

IXLColumn? FirstColumnUsed (): Gets the first non-empty column of the worksheet that contains a cell with a value.

IXLColumn? FirstColumnUsed (XLCellsUsedOptions options)

Gets the first non-empty column of the worksheet that contains a cell with a value.

Param options: The options to determine whether a cell is used.

IXLColumn LastColumn (): Gets the last column of the worksheet.

IXLColumn? LastColumnUsed (): Gets the last non-empty column of the worksheet that contains a cell with a value.

IXLColumn? LastColumnUsed (XLCellsUsedOptions options)

Gets the last non-empty column of the worksheet that contains a cell with a value.

Param options: The options to determine whether a cell is used.

IXLColumns Columns (): Gets a collection of all columns in this worksheet.

IXLColumns Columns (String columns)

Gets a collection of the specified columns in this worksheet, separated by commas.

e.g. Columns(“G:H”), Columns(“10:11,13:14”), Columns(“P:Q,S:T”), Columns(“V”)

Param columns: The columns to return.

IXLColumns Columns (String firstColumn, String lastColumn)

Gets a collection of the specified columns in this worksheet.

Param firstColumn: The first column to return.
Param lastColumn: The last column to return.

IXLColumns Columns (Int32 firstColumn, Int32 lastColumn)

Gets a collection of the specified columns in this worksheet.

Param firstColumn: The first column to return.
Param lastColumn: The last column to return.

IXLRows Rows (): Gets a collection of all rows in this worksheet.

IXLRows Rows (String rows)

Gets a collection of the specified rows in this worksheet, separated by commas.

e.g. Rows(“4:5”), Rows(“7:8,10:11”), Rows(“13”)

Param rows: The rows to return.

IXLRows Rows (Int32 firstRow, Int32 lastRow)

Gets a collection of the specified rows in this worksheet.

Param firstRow: The first row to return.
Param lastRow: The last row to return.

IXLRow Row (Int32 row)

Gets the specified row of the worksheet.

Param row: The worksheet’s row.

IXLColumn Column (Int32 column)

Gets the specified column of the worksheet.

Param column: The worksheet’s column.

IXLColumn Column (String column)

Gets the specified column of the worksheet.

Param column: The worksheet’s column.

IXLCell Cell (int row, int column)

Gets the cell at the specified row and column.

Param row: The cell’s row.
Param column: The cell’s column.

IXLCell Cell (string cellAddressInRange)

Gets the cell at the specified address.

Param cellAddressInRange: The cell address in the worksheet.
Throws ArgumentException: Address is not A1 or workbook-scoped named range.

IXLCell Cell (int row, string column)

Gets the cell at the specified row and column.

Param row: The cell’s row.
Param column: The cell’s column.

IXLCell Cell (IXLAddress cellAddressInRange)

Gets the cell at the specified address.

Param cellAddressInRange: The cell address in the worksheet.

IXLRange Range (IXLRangeAddress rangeAddress)

Returns the specified range.

Param rangeAddress: The range boundaries.

IXLRange Range (string rangeAddress)

Returns the specified range.

e.g. Range(“A1”), Range(“A1:C2”)

Param rangeAddress: The range boundaries.
Throws ArgumentException: rangeAddress is not a valid address or named range.

IXLRange Range (IXLCell firstCell, IXLCell lastCell)

Returns the specified range.

Param firstCell: The first cell in the range.
Param lastCell: The last cell in the range.

IXLRange Range (string firstCellAddress, string lastCellAddress)

Returns the specified range.

Param firstCellAddress: The first cell address in the worksheet.
Param lastCellAddress: The last cell address in the worksheet.

IXLRange Range (IXLAddress firstCellAddress, IXLAddress lastCellAddress)

Returns the specified range.

Param firstCellAddress: The first cell address in the worksheet.
Param lastCellAddress: The last cell address in the worksheet.

IXLRanges Ranges (string ranges)

Returns a collection of ranges, separated by commas.

e.g. Ranges(“A1”), Ranges(“A1:C2”), Ranges(“A1:B2,D1:D4”)

Param ranges: The ranges to return.

IXLRange Range (int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn)

Returns the specified range.

Param firstCellRow: The first cell’s row of the range to return.
Param firstCellColumn: The first cell’s column of the range to return.
Param lastCellRow: The last cell’s row of the range to return.
Param lastCellColumn: The last cell’s column of the range to return.
Return: .

int RowCount (): Gets the number of rows in this worksheet.

int ColumnCount (): Gets the number of columns in this worksheet.

IXLWorksheet CollapseRows (): Collapses all outlined rows.

IXLWorksheet CollapseColumns (): Collapses all outlined columns.

IXLWorksheet ExpandRows (): Expands all outlined rows.

IXLWorksheet ExpandColumns (): Expands all outlined columns.

IXLWorksheet CollapseRows (Int32 outlineLevel)

Collapses the outlined rows of the specified level.

Param outlineLevel: The outline level.

IXLWorksheet CollapseColumns (Int32 outlineLevel)

Collapses the outlined columns of the specified level.

Param outlineLevel: The outline level.

IXLWorksheet ExpandRows (Int32 outlineLevel)

Expands the outlined rows of the specified level.

Param outlineLevel: The outline level.

IXLWorksheet ExpandColumns (Int32 outlineLevel)

Expands the outlined columns of the specified level.

Param outlineLevel: The outline level.

void Delete (): Deletes this worksheet.

IXLDefinedName DefinedName (String name)

Gets the specified defined name.

Param name: Name identifier of defined name, without sheet name.
Throws ArgumentException: Name wasn’t found in sheets defined names.

IXLTable Table (Int32 index)

Gets the Excel table of the given index

Param index: Index of the table to return

IXLTable Table (String name)

Gets the Excel table of the given name

Param name: Name of the table to return

IXLWorksheet CopyTo (String newSheetName)

Copies the

Param newSheetName

IXLWorksheet CopyTo (XLWorkbook workbook, String newSheetName)

Copy a worksheet from this workbook to a different workbook as a new sheet.

Param workbook: Workbook into which copy this sheet.
Param newSheetName: Name of new sheet in the workbook where will the data be copied. Sheet will be in the last position.
Return: Newly created sheet in the workbook .

XLCellValue Evaluate (String expression, string? formulaAddress = null)

Evaluate an formula and return a result.

Param expression: Formula to evaluate.
Param formulaAddress: A cell address that is used to provide context for formula calculation (mostly implicit intersection).
Throws MissingContextException: If formulaAddress was needed for some part of calculation.

void RecalculateAllFormulas (): Force recalculation of all cell formulas in the sheet while leaving other sheets without change, even if their dirty cells.

Properties

XLWorkbook Workbook { get; set; }: Gets the workbook that contains this worksheet

Double ColumnWidth { get; set; }: Gets or sets the default column width for this worksheet.

Double RowHeight { get; set; }: Gets or sets the default row height for this worksheet.

String Name { get; set; }: Gets or sets the name (caption) of this worksheet. The sheet rename also renames sheet in formulas and defined names.

Int32 Position { get; set; }

Gets or sets the position of the sheet.

When setting the Position all other sheets’ positions are shifted accordingly.

IXLPageSetup PageSetup { get; set; }: Gets an object to manipulate the sheet’s print options.

IXLOutline Outline { get; set; }: Gets an object to manipulate the Outline levels.

IXLDefinedNames DefinedNames { get; set; }: Gets an object to manage this worksheet’s defined names.

IXLSheetView SheetView { get; set; }: Gets an object to manage how the worksheet is going to displayed by Excel.

IXLTables Tables { get; set; }: Gets an object to manage this worksheet’s Excel tables

IXLCell? ActiveCell { get; set; }: The active cell of the worksheet.

interface IXLWorksheets : public IEnumerable<IXLWorksheet>: Subclassed by XLWorksheets

class LoadOptions

A class that defines various aspects of a newly created workbook.

Properties

? IXLGraphicEngine DefaultGraphicEngine { get; set; }: A graphics engine that will be used for workbooks without explicitly set engine.

Boolean RecalculateAllFormulas { get; set; } = false: Should all formulas in a workbook be recalculated during load? Default value is false.

IXLGraphicEngine? GraphicEngine { get; set; }: Graphic engine used by the workbook.

Point? Dpi { get; set; }

DPI for the workbook. Default is 96.

Used in various places, e.g. determining a physical size of an image without a DPI or to determine a size of a text in a cell.

class ObjectExtensions

class PivotLabelFieldReference : public AbstractPivotFieldReference 

class PivotValueFieldReference : public AbstractPivotFieldReference 

class RangeEventArgs : public EventArgs

class SaveOptions

Properties

Boolean EvaluateFormulasBeforeSaving { get; set; } = false

Evaluate a cells with a formula and save the calculated value along with the formula.

True - formulas are evaluated and the calculated values are saved to the file. If evaluation of a formula throws an exception, value is not saved but file is still saved.
False (default) - formulas are not evaluated and the formula cells don’t have their values saved to the file.

Boolean? FilterPrivacy { get; set; }: Gets or sets the filter privacy flag. Set to null to leave the current property in saved workbook unchanged

class SharedStringTable: A class that holds all texts in a workbook. Each text can be either a simple string or a XLImmutableRichText.

class Slice<TElement> : public ISlice 

Public Functions

IEnumerator<XLSheetPoint> GetEnumerator (XLSheetRange range, bool reverse = false): Get enumerator over used values of the range.

class StringExtensions

class TableNameGenerator

class TimeSpanExtensions

Public Static Functions

String ToExcelString (this TimeSpan ts, CultureInfo culture)

Return a string representation of a TimeSpan that can be parsed by an Excel through text-to-number coercion.

Excel can convert time span string back to a number, but only if it doesn’t has days in the string, only hours. It’s an opposite of TimeSpanParser.

class TypeExtensions

class ValueSlice : public ISlice : A slice of a single worksheet for values of a cell.

class XDocumentExtensions

struct XLAddress : public IXLAddress, public IEquatable<XLAddress>

Public Functions

XLAddress (int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn)

Initializes a new XLAddress struct using a mixed notation. Attention: without worksheet for calculation only!

Param rowNumber: The row number of the cell address.
Param columnLetter: The column letter of the cell address.
Param fixedRow
Param fixedColumn

XLAddress (XLWorksheet worksheet, int rowNumber, string columnLetter, bool fixedRow, bool fixedColumn)

Initializes a new XLAddress struct using a mixed notation.

Param worksheet
Param rowNumber: The row number of the cell address.
Param columnLetter: The column letter of the cell address.
Param fixedRow
Param fixedColumn

XLAddress (int rowNumber, int columnNumber, bool fixedRow, bool fixedColumn)

Initializes a new XLAddress struct using R1C1 notation. Attention: without worksheet for calculation only!

Param rowNumber: The row number of the cell address.
Param columnNumber: The column number of the cell address.
Param fixedRow
Param fixedColumn

XLAddress (XLWorksheet worksheet, int rowNumber, int columnNumber, bool fixedRow, bool fixedColumn)

Initializes a new XLAddress struct using R1C1 notation.

Param worksheet
Param rowNumber: The row number of the cell address.
Param columnNumber: The column number of the cell address.
Param fixedRow
Param fixedColumn

Properties

Int32 RowNumber { get; set; }: Gets the row number of this address.

Int32 ColumnNumber { get; set; }: Gets the column number of this address.

String ColumnLetter { get; set; }: Gets the column letter(s) of this address.

Public Static Functions

XLAddress Create (string cellAddressString)

Create address without worksheet. For calculation only!

Param cellAddressString

class XLAlignment : public IXLAlignment 

Public Functions

XLAlignment (XLStyle? style, XLAlignmentValue value)

Create an instance of XLAlignment initializing it with the specified value.

Param style: Style to attach the new instance to.
Param value: Style value to use.

struct XLAlignmentKey : public IEquatable<XLAlignmentKey>

class XLAlignmentValue

class XLAutoFilter : public IXLAutoFilter 

struct XLBookArea : public IEquatable<XLBookArea>

A specification of an area (rectangular range) of a sheet.

Public Functions

XLBookArea? Intersect (XLBookArea other)

Perform an intersection.

Param other: The area that is being intersected with this one.
Return: The intersection (=same sheet and has non-empty intersection) or null if intersection isn’t possible.

Public Members

readonly string Name: Name of the sheet. Sheet may exist or not (e.g. deleted). Never null.

readonly XLSheetRange Area: An area in the sheet.

struct XLBookPoint : public IEquatable<XLBookPoint>

A single point in a workbook. The book point might point to a deleted worksheet, so it might be invalid. Make sure it is checked when determining the properties of the actual data of the point.

Properties

uint SheetId { get; set; }

A sheet id of a point. Id of a sheet never changes during workbook lifecycle (XLWorksheet.SheetId), but the sheet may be deleted, making the sheetId and thus book point invalid.

TODO: SheetId doesn’t work nicely with renames, but will in the future.

XLSheetPoint Point { get; set; }: A point in the sheet.

class XLBorder : public IXLBorder 

Public Functions

XLBorder (IXLStylized container, XLStyle style, XLBorderValue value)

Create an instance of XLBorder initializing it with the specified value.

Param container: Container the border is applied to.
Param style: Style to attach the new instance to.
Param value: Style value to use.

struct XLBorderKey : public IEquatable<XLBorderKey>

class XLBorderValue

class XLCell : public XLStylizedBase, public IXLCell, public IXLStylized 

Public Functions

void Evaluate (Boolean force)

Perform an evaluation of cell formula. If cell does not contain formula nothing happens, if cell does not need recalculation (NeedsRecalculation is False) nothing happens either, unless force flag is specified. Otherwise recalculation is performed, result value is preserved in CachedValue and returned.

Param force: Flag indicating whether a recalculation must be performed even is cell does not need it.
Return: Null if cell does not contain a formula. Calculated value otherwise.

Public Members

bool NeedsRecalculation = > Formula is not null && Formula.IsDirty: Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated.

IXLSparkline Sparkline = > Worksheet.SparklineGroups.GetSparkline(this): The sparkline assigned to the cell

Properties

bool ShareString { get; set; }: A flag indicating if a string should be stored in the shared table or inline.

class XLCellFormula

A representation of a cell formula, not the formula itself (i.e. the tree).

Public Functions

string GetFormulaA1 (XLSheetPoint cellAddress)

Get stored formula in A1 notation. Returned formula doesn’t contain equal sign.

Param cellAddress: Address of the formula cell. Used to convert relative R1C1 to A1, if conversion is necessary.

string GetFormulaR1C1 (XLSheetPoint cellAddress): Get stored formula in R1C1 notation. Returned formula doesn’t contain equal sign.

Formula GetAst (XLCalcEngine engine)

Get a lazy initialized AST for the formula.

Param engine: Engine to parse the formula into AST, if necessary.

class XLCells : public XLStylizedBase, public IXLCells, public IXLStylized, public IEnumerable<XLCell>

class XLCellsCollection : public IWorkbookListener

struct XLCellValue : public IEquatable<XLCellValue>, public IEquatable<Blank>, public IEquatable<Boolean>, public IEquatable<Double>, public IEquatable<String>, public IEquatable<XLError>, public IEquatable<DateTime>, public IEquatable<TimeSpan>, public IEquatable<int>

A value of a single cell. It contains a value a specific Type. Structure provides following group of methods:

Is* properties to check type (IsNumber, IsBlank…)
Get* methods that return the stored value or throw InvalidCastException for incorrect type.
Explicit operators to convert XLCellValue to a concrete type. It is an equivalent of Get* methods.
TryConvert methods to try to get value of a specific type, even if the value is of a different type.

Public Functions

Blank GetBlank (): If the value is of type XLDataType.Blank, return Blank.Value, otherwise throw InvalidCastException.

Boolean GetBoolean (): If the value is of type XLDataType.Boolean, return logical, otherwise throw InvalidCastException.

Double GetNumber (): If the value is of type XLDataType.Number, return number, otherwise throw InvalidCastException.

String GetText (): If the value is of type XLDataType.Text, return text, otherwise throw InvalidCastException.

XLError GetError (): If the value is of type XLDataType.Error, return error, otherwise throw InvalidCastException.

DateTime GetDateTime (): If the value is of type XLDataType.DateTime, return date time, otherwise throw InvalidCastException.

TimeSpan GetTimeSpan (): If the value is of type XLDataType.TimeSpan, return time span, otherwise throw InvalidCastException.

double GetUnifiedNumber ()

Get a number, either directly from type number or from serialized time span or serialized date time.

Throws InvalidCastException: If type is not XLDataType.Number or XLDataType.DateTime or XLDataType.TimeSpan.

override string ToString (): Return text representation of a value in current culture.

string ToString (CultureInfo culture): Return text representation of a value in the specified culture.

bool Equals (string other): Is the cell value text and is equal to the other ? Text comparison is case sensitive.

bool TryGetText (out string value)

Get a value, if it is a XLDataType.Text.

Return: True if value was retrieved, false otherwise.

bool TryConvert (out Blank value)

Try to convert the value to a XLDataType.Blank and return it. Method succeeds, when value is

Type XLDataType.Blank.
Type XLDataType.Text and the text is empty.

bool TryConvert (out Boolean value)

Try to convert the value to a XLDataType.Boolean and return it. Method succeeds, when value is

Type XLDataType.Boolean.
Type XLDataType.Number, then the value of 0 means false and any other value is true.
Type XLDataType.Text and the value is TRUE or FALSE (case insensitive). Note that calc engine generally doesn’t coerce text to logical (e.g. arithmetic operations), though it happens in most function arguments (e.g. IF or AND).

bool TryConvert (out Double value, CultureInfo culture)

Try to convert the value to a XLDataType.Number and return it.

Double value - return the value.
Boolean value - return the 0 for TRUE and 1 for FALSE.
Text value - use the VALUE semantic to convert a text to a number.
DateTime value - return the serial date time number.
TimeSpan value - return the serial time span value.

Note that the coercion is current culture specific (e.g. decimal separators can differ).

Param value: The converted value. Result is never infinity or NaN.
Param culture: The culture used to convert the value for texts.

bool TryConvert (out DateTime value)

Try to convert the value to a DateTime and return it. Method succeeds, when value is

Type XLDataType.DateTime.
Type XLDataType.Number and when the number is interpreted is a serial date time, it falls within the DateTime range.
Type XLDataType.TimeSpan and when the number is interpreted is a serial date time, it falls within the DateTime range.

bool TryConvert (out TimeSpan value, CultureInfo culture)

Try to convert the value to a TimeSpan and return it. Method succeeds, when value is

Type XLDataType.TimeSpan.
Type XLDataType.Number, the number is interpreted is a time span date time.
Type XLDataType.Text and it can be parsed as a time span (accepts even hours over 24 hours).

Param value: The converted value.
Param culture: The culture used to get time and decimal separators.

Public Members

bool IsBlank = > Type == XLDataType.Blank: Is the type of value Blank?

bool IsBoolean = > Type == XLDataType.Boolean: Is the type of value XLDataType.Boolean?

bool IsNumber = > Type == XLDataType.Number: Is the type of value XLDataType.Number?

bool IsText = > Type == XLDataType.Text: Is the type of value XLDataType.Text?

bool IsError = > Type == XLDataType.Error: Is the type of value XLDataType.Error?

bool IsDateTime = > Type == XLDataType.DateTime: Is the type of value XLDataType.DateTime?

bool IsTimeSpan = > Type == XLDataType.TimeSpan: Is the type of value XLDataType.TimeSpan?

bool IsUnifiedNumber = > IsNumber || IsDateTime || IsTimeSpan: Is the value XLDataType.Number or XLDataType.DateTime or XLDataType.TimeSpan.

Properties

XLDataType Type { get; set; }: Type of the value.

Public Static Functions

XLCellValue FromObject (object obj, IFormatProvider provider = null)

Creates an XLCellValue from an object. If the type of the object has an implicit conversion operator then it is used. Otherwise, the Convert.ToString(object, IFormatProvider) method is used to convert the provided object to a string.

The following types and their nullable counterparts are supported without requiring to be converted to a string:

Blank
bool
string
XLError
DateTime
TimeSpan
sbyte
byte
short
ushort
int
uint
long
ulong
float
double
decimal

Param obj: The object to convert.
Param provider: An object that supplies culture-specific formatting information.
Return: An XLCellValue representation of the object.

class XLCellValueSortComparer : public IComparer<XLCellValue>

A comparator of two cell value. It uses semantic of a sort feature in Excel:

Order by type is number, text, logical, error, blank.
Errors are not sorted.
Blanks are always last, both in ascending and descending order.
Stable sort.

class XLCFBaseConverter

class XLCFCellIsConverter : public IXLCFConverter 

class XLCFColorScaleConverter : public IXLCFConverter 

class XLCFColorScaleMax : public IXLCFColorScaleMax 

class XLCFColorScaleMid : public IXLCFColorScaleMid 

class XLCFColorScaleMin : public IXLCFColorScaleMin 

class XLCFContainsConverter : public IXLCFConverter 

class XLCFConverters

class XLCFConvertersExtension

class XLCFDataBarConverter : public IXLCFConverter 

class XLCFDataBarConverterExtension : public IXLCFConverterExtension 

class XLCFDataBarMax : public IXLCFDataBarMax 

class XLCFDataBarMin : public IXLCFDataBarMin 

class XLCFDatesOccurringConverter : public IXLCFConverter 

class XLCFEndsWithConverter : public IXLCFConverter 

class XLCFIconSet : public IXLCFIconSet 

class XLCFIconSetConverter : public IXLCFConverter 

class XLCFIsBlankConverter : public IXLCFConverter 

class XLCFIsErrorConverter : public IXLCFConverter 

class XLCFNotBlankConverter : public IXLCFConverter 

class XLCFNotContainsConverter : public IXLCFConverter 

class XLCFNotErrorConverter : public IXLCFConverter 

class XLCFStartsWithConverter : public IXLCFConverter 

class XLCFTopConverter : public IXLCFConverter 

class XLCFUniqueConverter : public IXLCFConverter 

class XLChart : public ClosedXML::Excel::XLDrawing<IXLChart>, public IXLChart 

class XLCharts : public IXLCharts 

class XLClearOptionsExtensions

class XLColor

struct XLColorKey : public IEquatable<XLColorKey>

class XLColumn : public XLRangeBase, public IXLColumn 

Public Functions

XLColumn (XLWorksheet worksheet, Int32 column): The direct constructor should only be used in XLWorksheet.RangeFactory.

IXLColumn AddVerticalPageBreak (): Adds a vertical page break after this column.

class XLColumns : public XLStylizedBase, public IXLColumns, public IXLStylized 

Public Functions

XLColumns (XLWorksheet? worksheet, XLStyleValue? defaultStyle = null, IEnumerable<XLColumn>? lazyEnumerable = null)

Create a new instance of XLColumns.

Param worksheet: If worksheet is specified it means that the created instance represents all columns on a worksheet so changing its width will affect all columns.
Param defaultStyle: Default style to use when initializing child entries.
Param lazyEnumerable: A predefined enumerator of XLColumn to support lazy initialization.

IXLColumns AddVerticalPageBreaks (): Adds a vertical page break after this column.

class XLColumnsCollection : public IDictionary<Int32, XLColumn>

class XLComment : public ClosedXML::Excel::XLFormattedText<IXLComment>, public IXLComment 

class XLConditionalFormat : public XLStylizedBase, public IXLConditionalFormat, public IXLStylized 

class XLConditionalFormats : public IXLConditionalFormats 

A container for conditional formatting of a XLWorksheet. It contains a collection of XLConditionalFormat. Doesn’t contain pivot table formats, they are in pivot table XLPivotTable.ConditionalFormats,

Public Functions

void ReorderAccordingToOriginalPriority (): Reorders the according to original priority. Done during load process

class XLConstants

class PivotTable

class XLCustomProperties : public IXLCustomProperties, public IEnumerable<IXLCustomProperty>

class XLCustomProperty : public IXLCustomProperty 

class XLDataValidation : public IXLDataValidation 

Public Functions

void AddRange (IXLRange range)

Add a range to the collection of ranges this rule applies to. If the specified range does not belong to the worksheet of the data validation rule it is transferred to the target worksheet.

Param range: A range to add.

void AddRanges (IEnumerable<IXLRange> ranges)

Add a collection of ranges to the collection of ranges this rule applies to. Ranges that do not belong to the worksheet of the data validation rule are transferred to the target worksheet.

Param ranges: Ranges to add.

void ClearRanges (): Detach data validation rule of all ranges it applies to.

bool RemoveRange (IXLRange range)

Remove the specified range from the collection of range this rule applies to.

Param range: A range to remove.

class XLDataValidations : public IXLDataValidations 

Public Functions

IEnumerable<IXLDataValidation> GetAllInRange (IXLRangeAddress rangeAddress): Get all data validation rules applied to ranges that intersect the specified range.

bool TryGet (IXLRangeAddress rangeAddress, out IXLDataValidation? dataValidation)

Get the data validation rule for the range with the specified address if it exists.

Param rangeAddress: A range address.
Param dataValidation: Data validation rule which ranges collection includes the specified address. The specified range should be fully covered with the data validation rule. For example, if the rule is applied to ranges A1:A3,C1:C3 then this method will return True for ranges A1:A3, C1:C2, A2:A3, and False for ranges A1:C3, A1:C1, etc.
Return: True is the data validation rule was found, false otherwise.

class XLDateCriteria : public XLValidationCriteria 

class XLDecimalCriteria : public XLValidationCriteria 

class XLDefinedNames : public IXLDefinedNames, public IEnumerable<XLDefinedName>

A collection of a named ranges, either for workbook or for worksheet.

Public Functions

IEnumerable<IXLDefinedName> ValidNamedRanges (): Returns a subset of named ranges that do not have invalid references.

IEnumerable<IXLDefinedName> InvalidNamedRanges (): Returns a subset of named ranges that do have invalid references.

class XLDictionary<T> : public Dictionary<Int32, T>

class XLDrawing<T> : public ClosedXML::Excel::IXLDrawing<T>: Subclassed by XLChart

class XLDrawingAlignment : public IXLDrawingAlignment 

class XLDrawingColorsAndLines : public IXLDrawingColorsAndLines 

class XLDrawingFont : public IXLDrawingFont 

class XLDrawingMargins : public IXLDrawingMargins 

class XLDrawingPosition : public IXLDrawingPosition 

class XLDrawingProperties : public IXLDrawingProperties 

class XLDrawingProtection : public IXLDrawingProtection 

class XLDrawingSize : public IXLDrawingSize 

class XLDrawingStyle : public IXLDrawingStyle 

class XLDrawingWeb : public IXLDrawingWeb 

class XLFileSharing : public IXLFileSharing 

class XLFill : public IXLFill 

Public Functions

XLFill (XLStyle? style, XLFillValue value)

Create an instance of XLFill initializing it with the specified value.

Param style: Style to attach the new instance to.
Param value: Style value to use.

struct XLFillKey : public IEquatable<XLFillKey>

class XLFillValue

class XLFilter

A single filter condition for auto filter.

Properties

XLCellValue CustomValue { get; set; }: Value for XLFilterType.Custom that is compared using Operator.

Object Value { get; set; }: Value for XLFilterType.Regular filter.

class XLFilterColumn : public IXLFilterColumn, public IXLFilteredColumn, public IEnumerable<XLFilter>

Properties

Double DynamicValue { get; set; } = double.NaN: Basically average for dynamic filters. Value is refreshed during filter reapply.

class XLFont : public IXLFont 

Public Functions

XLFont (XLStyle? style, XLFontValue value)

Create an instance of XLFont initializing it with the specified value.

Param style: Style to attach the new instance to.
Param value: Style value to use.

XLFont (XLStyle style)

Create a new font that is attached to a style and the changes to the font object are propagated to the style.

Param style: The container style that will be modified by changes of created XLFont.

XLFont (IXLFontBase font): Create a new font. The changes to the object are not propagated to a style.

struct XLFontKey : public IEquatable<XLFontKey>

class XLFontValue

class XLFormattedText<T> : public ClosedXML::Excel::IXLFormattedText<T>: Subclassed by XLComment, XLRichText

class XLFormula

class XLHeaderFooter : public IXLHeaderFooter 

class XLHelper

Common methods

Public Static Functions

int GetColumnNumberFromLetter (string columnLetter)

Gets the column number of a given column letter.

Param columnLetter: The column letter to translate into a column number.

string GetColumnLetterFromNumber (int columnNumber, bool trimToAllowed = false)

Gets the column letter of a given column number.

Param columnNumber: The column number to translate into a column letter.
Param trimToAllowed: if set to true the column letter will be restricted to the allowed range.

string CreateSafeSheetName (string nameProposal, char replaceChar = ' ')

Creates a valid sheet name, which conforms to the following rules. A sheet name: is never null, has minimum length of 1 and maximum length of 31, doesn’t contain special chars: (: 0x0000 0x0003 / \ ? * ] [). Sheet names must not begin or end with ‘ (apostrophe)

Param nameProposal: can be any string, will be truncated if necessary, allowed to be null
Param replaceChar: the char to replace invalid characters.
Return: a valid string, “empty” if too short, “null” if null

void ValidateSheetName (String sheetName)

Validates the name of the sheet. The character count MUST be greater than or equal to 1 and less than or equal to 31. The string MUST NOT contain the any of the following characters:

0x0000
0x0003
colon (:)
backslash(\)
asterisk(*)
question mark(?)
forward slash(/)
opening square bracket([)
closing square bracket(]) The string MUST NOT begin or end with the single quote(’) character.

Param sheetName: Name of the sheet.
Throws ArgumentException

class XLHFItem : public IXLHFItem 

class XLHFText

class XLHyperlink

class XLHyperlinks : public IXLHyperlinks 

class XLIdManager

class XLImmutableRichText : public IEquatable<XLImmutableRichText>

A class for holding XLRichText in a SharedStringTable. It’s immutable (keys in reverse dictionary can’t change) and more memory efficient than mutable rich text.

Public Members

IReadOnlyList< RichTextRun > Runs => _runs: Individual rich text runs that make up the Text, in ascending order, non-overlapping.

IReadOnlyList< PhoneticRun > PhoneticRuns => _phoneticRuns: All phonetics runs of rich text. Empty array, if no phonetic run. In ascending order, non-overlapping.

Properties

string Text { get; set; }: A text of a whole rich text, without styling.

PhoneticProperties? PhoneticsProperties { get; set; }: Properties used to display phonetic runs.

class XLMargins : public IXLMargins 

struct XLMiscSliceContent

struct XLName : public IEquatable<XLName>

A name in a worksheet. Unlike IXLDefinedName, this is basically only a reference. The actual

Properties

string? SheetName { get; set; }: Name of a sheet. If null, the scope is a workbook. The sheet might not exist, e.g. it is only in a formula. The name of a sheet is not escaped.

string Name { get; set; }: The defined name in the scope. Case insensitive during comparisons.

class XLNumberFormat : public IXLNumberFormat 

Public Functions

XLNumberFormat (XLStyle? style, XLNumberFormatValue value)

Create an instance of XLNumberFormat initializing it with the specified value.

Param style: Style to attach the new instance to.
Param value: Style value to use.

struct XLNumberFormatKey : public IEquatable<XLNumberFormatKey>

class XLNumberFormatValue

Properties

int NumberFormatId { get; set; }: Id of the number format. Every workbook has XLConstants.NumberOfBuiltInStyles built-int formats that start at 0 (General format). The built-int formats are not explicitly written and might differ depending on culture. Custom number formats have a valid Format and the id is -1.

class XLOutline : public IXLOutline 

class XLPageSetup : public IXLPageSetup 

class XLPhonetic : public IXLPhonetic 

class XLPhonetics : public IXLPhonetics 

class XLPivotCache : public IXLPivotCache 

class XLPivotCaches : public IXLPivotCaches, public IEnumerable<XLPivotCache>

class XLPivotCacheSharedItems

A list of XLPivotCacheValue in the pivot table cache definition. Generally, it contains all strings of the field records (record just indexes them through XLPivotCacheValueType.Index) and also values used directly in pivot table (e.g. filter field reference the table definition, not record).

Shared items can’t contain XLPivotCacheValueType.Index.

struct XLPivotCacheValue: Represents a single value in a pivot cache record.

class XLPivotCacheValues: All values of a cache field for a pivot table.

struct XLPivotCacheValuesStats: Statistics about a pivot cache field values. These statistics are available, even if cache field doesn’t have any record values.

class XLPivotField : public IXLPivotField 

class XLPivotFields : public IXLPivotFields 

class XLPivotFieldStyleFormats : public IXLPivotFieldStyleFormats 

class XLPivotSourceReference : public IEquatable<XLPivotSourceReference>: A reference to the pivot source. The source might exist or not, that is evaluated during pivot cache record refresh.

class XLPivotStyleFormat : public IXLPivotStyleFormat : Subclassed by XLPivotValueStyleFormat

class XLPivotStyleFormats : public IXLPivotStyleFormats 

class XLPivotTable : public IXLPivotTable 

Properties

XLPivotTableTheme Theme { get; set; }: Table theme this pivot table will use.

String? ErrorValueReplacement { get; set; }: Text to display when in cells that contain error.

bool DisplayItemLabels { get; set; } = true

Should field items be displayed on the axis despite pivot table not having any value field? true will display items even without data field, false won’t.

Example: There is an empty pivot table with no value fields. Add field ‘Name’ to row fields. Should names be displayed on row despite not having any value field?

Also called ShowItems

Boolean ShowExpandCollapseButtons { get; set; } = true

A flag indicating whether UI should display collapse/expand (drill) buttons in pivot table axes.

Also called ShowDrill.

Boolean PrintExpandCollapsedButtons { get; set; } = false

A flag indicating whether collapse/expand (drill) buttons in pivot table axes should be printed.

Also called PrintDrill.

Boolean ShowPropertiesInTooltips { get; set; }: OLAP only. Also called ShowMemberPropertyTips.

Boolean ShowContextualTooltips { get; set; }

A flag indicating whether UI should display a tooltip on data items of pivot table. The tooltip contain info about value field name, row/col items used to aggregate the value ect. Note that this tooltip generally hides cell notes, because mouseover displays data tool tip, rather than the note.

Also called ShowDataTips.

Boolean EnableShowDetails { get; set; } = true: Likely OLAP only. Do not confuse with collapse/expand buttons.

Boolean PreserveCellFormatting { get; set; } = true

A flag that indicates whether the formatting applied by the user to the pivot table cells is preserved on refresh.

Once again, ISO-29500 is buggy and says the opposite. Also called PreserveFormatting

Boolean AutofitColumns { get; set; } = false

A flag that indicates whether legacy auto formatting has been applied to the PivotTable view.

Also called UseAutoFormatting.

Int32 FilterFieldsPageWrap { get; set; }

Specifies the number of page fields to display before starting another row or column. Value <= 0 means unlimited.

Also called PageWrap.

XLFilterAreaOrder FilterAreaOrder { get; set; } = XLFilterAreaOrder.DownThenOver

Page field layout setting that indicates layout order of page fields. The layout uses FilterFieldsPageWrap to determine when to break to a new row or column.

Also called PageOverThenDown.

bool FilteredItemsInSubtotals { get; set; } = false

A flag that indicates whether hidden pivot items should be included in subtotal calculated values. If true, data for hidden items are included in subtotals calculated values. If false, hidden values are not included in subtotal calculations.

Also called SubtotalHiddenItems. OLAP only. Option in Excel is grayed out and does nothing. The option is un-grayed out when pivot cache is part of data model.

Boolean ShowGrandTotalsRows { get; set; } = true

A flag indicating whether grand totals should be displayed for the PivotTable rows.

Also called RowGrandTotals.

Boolean ShowGrandTotalsColumns { get; set; } = true

A flag indicating whether grand totals should be displayed for the PivotTable columns.

Also called ColumnGrandTotals.

Boolean PrintTitles { get; set; } = false

A flag indicating whether when a field name should be printed on all pages.

Also called FieldPrintTitles.

Boolean RepeatRowLabels { get; set; } = false

A flag indicating whether whether PivotItem names should be repeated at the top of each printed page (e.g. if axis item spans multiple pages, it will be repeated an all pages).

Also called ItemPrintTitles.

Boolean MergeAndCenterWithLabels { get; set; } = false

A flag indicating whether row or column titles that span multiple cells should be merged into a single cell. Useful only in in tabular layout, titles in other layouts don’t span across multiple cells.

Also called MergeItem.

Int32 RowLabelIndent { get; set; } = 1

A row indentation increment for row axis when pivot table is in compact layout. Units are characters.

Also called Indent.

Boolean ShowEmptyItemsOnRows { get; set; } = false

A flag indicating whether to include empty rows in the pivot table (i.e. row axis items are blank and data items are blank).

Also called ShowEmptyRow.

Boolean ShowEmptyItemsOnColumns { get; set; }

A flag indicating whether to include empty columns in the table (i.e. column axis items are blank and data items are blank).

Also called ShowEmptyColumn.

Boolean DisplayCaptionsAndDropdowns { get; set; } = true

A flag indicating whether to show field names on axis. The axis items are still displayed, only field names are not. The dropdowns next to the axis field names are also displayed/hidden based on the flag.

Also called ShowHeaders.

Boolean ClassicPivotTableLayout { get; set; } = false

A flag that indicates whether to apply the classic layout. Classic layout displays the grid zones in UI where user can drop fields (unless disabled through ShowDropZones).

Also called GridDropZones.

Boolean AllowMultipleFilters { get; set; } = true

A flag indicating whether field can have at most most one filter type used. This flag doesn’t allow multiple filters of same type, only multiple different filter types.

If false, field can have at most one filter, if user tries to set multiple, previous one is cleared.

Also called multipleFieldFilters.

String? RowHeaderCaption { get; set; } = null: The text that will be displayed in row header in compact mode. It is next to drop down (if enabled) of a label/values filter for fields (if DisplayCaptionsAndDropdowns is set to true). Use localized text Row labels if property is not specified.

String? ColumnHeaderCaption { get; set; } = null: The text that will be displayed in column header in compact mode. It is next to drop down (if enabled) of a label/values filter for fields (if DisplayCaptionsAndDropdowns is set to true). Use localized text Column labels if property is not specified.

Boolean SortFieldsAtoZ { get; set; } = false

A flag that controls how are fields sorted in the field list UI. true will display fields sorted alphabetically, false will display fields in the order fields appear in XLPivotCache. OLAP data sources always use alphabetical sorting.

Also called fieldListSortAscending.

Boolean UseCustomListsForSorting { get; set; }

A flag that indicates whether custom lists are used for sorting items of fields, both initially when the PivotField is initialized and the PivotItems are ordered by their captions, and later when the user applies a sort.

Also called customSortList.

class XLPivotTables : public IXLPivotTables, public IEnumerable<XLPivotTable>

class XLPivotTableStyleFormats : public IXLPivotTableStyleFormats 

class XLPivotValue : public IXLPivotValue 

class XLPivotValueCombination : public IXLPivotValueCombination 

class XLPivotValueFormat : public IXLPivotValueFormat 

class XLPivotValues : public IXLPivotValues 

class XLPivotValueStyleFormat : public XLPivotStyleFormat, public IXLPivotValueStyleFormat 

class XLPredefinedFormat

Reference point of date/number formats available. See more at: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformat.aspx

Properties

int General { get; set; }: General

class XLPrintAreas : public IXLPrintAreas 

class XLProtection : public IXLProtection 

Public Functions

XLProtection (XLStyle? style, XLProtectionValue value)

Create an instance of XLProtection initializing it with the specified value.

Param style: Style to attach the new instance to.
Param value: Style value to use.

class XLProtectionAlgorithm

struct XLProtectionKey : public IEquatable<XLProtectionKey>

class XLProtectionValue

class XLRange : public XLRangeBase, public IXLRange : Subclassed by XLTable, XLTableRange

struct XLRangeAddress : public IXLRangeAddress, public IEquatable<XLRangeAddress>

Public Functions

XLRangeAddress Normalize (): Lead a range address to a normal form - when FirstAddress points to the top-left address and LastAddress points to the bottom-right address.

bool ContainsWhole (IXLRangeAddress range): Does this range contains whole another range?

class XLRangeBase : public XLStylizedBase, public IXLRangeBase, public IXLStylized : Subclassed by XLColumn, XLRange, XLRangeColumn, XLRangeRow, XLRow, XLWorksheet

class XLRangeColumn : public XLRangeBase, public IXLRangeColumn 

Public Functions

XLRangeColumn (XLRangeParameters rangeParameters): The direct constructor should only be used in XLWorksheet.RangeFactory.

class XLRangeColumns : public XLStylizedBase, public IXLRangeColumns, public IXLStylized 

class XLRangeColumnsSortComparer : public IComparer<int>

class XLRangeConsolidationEngine: Engine for ranges consolidation. Supports IXLRanges including ranges from either one or multiple worksheets.

class XLRangeFactory

struct XLRangeKey : public IEquatable<XLRangeKey>

Public Functions

bool Equals (XLRangeKey other)

Indicates whether the current object is equal to another object of the same type.

Param other: An object to compare with this object.
Return: true if the current object is equal to the other parameter; otherwise, false.

override bool Equals (object obj)

Indicates whether this instance and a specified object are equal.

Param obj: Another object to compare to.
Return: true if obj and this instance are the same type and represent the same value; otherwise, false.

override int GetHashCode ()

Returns the hash code for this instance.

Return: A 32-bit signed integer that is the hash code for this instance.

class XLRangeParameters

class XLRangeRow : public XLRangeBase, public IXLRangeRow 

Subclassed by XLTableRow

Public Functions

XLRangeRow (XLRangeParameters rangeParameters): The direct constructor should only be used in XLWorksheet.RangeFactory.

class XLRangeRows : public XLStylizedBase, public IXLRangeRows, public IXLStylized 

class XLRangeRowsSortComparer : public IComparer<int>

A comparer of rows in a range. It uses semantic of a sort feature in Excel.

The comparer should work separate from data, but it would necessitate to sort over XLRangeRow. That would require to not only instantiate a new object for each sorted row, but since XLRangeRow, it would also be be tracked in range repository, slowing each subsequent operation. To improve performance, comparer has reference to underlaying data and compares row numbers that can be stores in a single allocated array of indexes.

class XLRanges : public XLStylizedBase, public IXLRanges, public IXLStylized 

Public Functions

void RemoveAll (Predicate<IXLRange>? match = null, bool releaseEventHandlers = true)

Removes ranges matching the criteria from the collection, optionally releasing their event handlers.

Param match: Criteria to filter ranges. Only those ranges that satisfy the criteria will be removed. Null means the entire collection should be cleared.
Param releaseEventHandlers: Specify whether or not should removed ranges be unsubscribed from row/column shifting events. Until ranges are unsubscribed they cannot be collected by GC.

IEnumerable<IXLRange> GetIntersectedRanges (IXLRangeAddress rangeAddress): Filter ranges from a collection that intersect the specified address. Is much more efficient that using Linq expression .Where().

IEnumerable<IXLRange> GetIntersectedRanges (IXLAddress address): Filter ranges from a collection that intersect the specified address. Is much more efficient that using Linq expression .Where().

class XLRichString : public IXLRichString 

class XLRichText : public ClosedXML::Excel::XLFormattedText<IXLRichText>, public IXLRichText 

Public Functions

XLRichText (XLCell cell, XLImmutableRichText original): Copy ctor to return user modifiable rich text from immutable rich text stored in the shared string table.

class XLRow : public XLRangeBase, public IXLRow 

Public Functions

XLRow (XLWorksheet worksheet, Int32 row): The direct constructor should only be used in XLWorksheet.RangeFactory.

Properties

Double? DyDescent { get; set; }

Distance in pixels from the bottom of the cells in the current row to the typographical baseline of the cell content if, hypothetically, the zoom level for the sheet containing this row is 100 percent and the cell has bottom-alignment formatting.

If the attribute is set, it sets customHeight to true even if the customHeight is explicitly set to false. Custom height means no auto-sizing by Excel on load, so if row has this attribute, it stops Excel from auto-sizing the height of a row to fit the content on load.

Boolean ShowPhonetic { get; set; }: Should cells in the row display phonetic? This doesn’t actually affect whether the phonetic are shown in the row, that depends entirely on the IXLCell.ShowPhonetic property of a cell. This property determines whether a new cell in the row will have it’s phonetic turned on (and also the state of the “Show or hide phonetic” in Excel when whole row is selected). Default is false.

Boolean HeightChanged { get; set; }: Does row have an individual height or is it derived from the worksheet XLWorksheet.RowHeight?

class XLRows : public XLStylizedBase, public IXLRows, public IXLStylized 

Public Functions

XLRows (XLWorksheet? worksheet, XLStyleValue? defaultStyle = null, IEnumerable<XLRow>? lazyEnumerable = null)

Create a new instance of XLRows.

Param worksheet: If worksheet is specified it means that the created instance represents all rows on a worksheet so changing its height will affect all rows.
Param defaultStyle: Default style to use when initializing child entries.
Param lazyEnumerable: A predefined enumerator of XLRow to support lazy initialization.

class XLRowsCollection : public IDictionary<Int32, XLRow>

struct XLSheetPoint : public IEquatable<XLSheetPoint>, public IComparable<XLSheetPoint>

An point (address) in a worksheet, an equivalent of ST_CellRef.

Unlike the XLAddress, sheet can never be invalid.

Public Functions

int Format (Span<char> output)

Write the sheet point as a reference to the span (e.g. A1).

Param output: Must be at least 10 chars long
Return: Number of chars

Public Members

readonly Int32 Row: 1-based row number in a sheet.

readonly Int32 Column: 1-based column number in a sheet.

Public Static Functions

XLSheetPoint Parse (ReadOnlySpan<char> input)

Parse point per type ST_CellRef from 2.1.1108 Part 4 Section 3.18.8, ST_CellRef (Cell Reference)

Param input: Input text
Throws FormatException: If the input doesn’t match expected grammar.

XLSheetPoint FromAddress (IXLAddress address): Create a sheet point from the address. Workbook is ignored.

class XLSheetProtection : public IXLSheetProtection 

struct XLSheetRange : public IEquatable<XLSheetRange>

A representation of a ST_Ref, i.e. an area in a sheet (no reference to the sheet).

Public Functions

int Format (Span<char> output)

Write the sheet range to the span. If range has only one cell, write only the cell.

Param output: Must be at least 21 chars long.
Return: Number of written characters.

XLSheetRange SliceFromBottom (int rows)

Create a new range from this one by taking a number of rows from the bottom row up.

Param rows: How many rows to take, must be at least one.

XLSheetRange SliceFromTop (int rows)

Create a new range from this one by taking a number of rows from the top row down.

Param rows: How many rows to take, must be at least one.

XLSheetRange SliceFromRight (int columns)

Create a new range from this one by taking a number of rows from the bottom row up.

Param columns: How many columns to take, must be at least one.

XLSheetRange Range (XLSheetRange otherRange)

Create a new sheet range that is a result of range operator (:) of this sheet range and otherRange

Param otherRange: The other range.
Return: A range that contains both this range and otherRange .

XLSheetRange? Intersect (XLSheetRange other)

Do an intersection between this range and other range.

Param other: Other range.
Return: The intersection range if it exists and is non-empty or null, if intersection doesn’t exist.

bool IsEntireRow (): Does range cover all columns, from first to last column of a sheet.

Public Members

readonly XLSheetPoint FirstPoint: Top-left point of the sheet range.

readonly XLSheetPoint LastPoint: Bottom-right point of the sheet range.

int LeftColumn = > FirstPoint.Column: The left column number of the range. From 1 to XLHelper.MaxColumnNumber.

int RightColumn = > LastPoint.Column: The right column number of the range. From 1 to XLHelper.MaxColumnNumber. Greater or equal to LeftColumn.

int TopRow = > FirstPoint.Row: The top row number of the range. From 1 to XLHelper.MaxRowNumber.

int BottomRow = > LastPoint.Row: The bottom row number of the range. From 1 to XLHelper.MaxRowNumber. Greater or equal to TopRow.

Public Static Functions

XLSheetRange Parse (ReadOnlySpan<char> input)

Parse point per type ST_Ref from 2.1.1119 Part 4 Section 3.18.64, ST_Ref (Cell Range Reference)

Can be one cell reference (A1) or two separated by a colon (A1:B2). First reference is always in top left corner

Param input: Input text
Throws FormatException: If the input doesn’t match expected grammar.

Public Static Attributes

readonly XLSheetRange Full = new(new XLSheetPoint(XLHelper.MinRowNumber, XLHelper.MinColumnNumber),new XLSheetPoint(XLHelper.MaxRowNumber, XLHelper.MaxColumnNumber)): A range that covers whole worksheet.

class XLSheetView : public IXLSheetView 

class XLSortElements : public IXLSortElements 

class XLSparkline : public IXLSparkline 

Public Functions

XLSparkline (IXLSparklineGroup sparklineGroup, IXLCell cell, IXLRange sourceData)

Create a new sparkline

Param sparklineGroup: The sparkline group to add the sparkline to
Param cell: The cell to place the sparkline in
Param sourceData: The range the sparkline gets data from

class XLSparklineGroup : public IXLSparklineGroup 

Public Functions

XLSparklineGroup (IXLWorksheet targetWorksheet, IXLSparklineGroup copyFrom)

Add a new sparkline group copied from an existing sparkline group to the specified worksheet

Param targetWorksheet: The worksheet the sparkline group is being added to
Param copyFrom: The sparkline group to copy from
Return: The new sparkline group added

XLSparklineGroup (IXLWorksheet targetWorksheet, string locationAddress, string sourceDataAddress)

Add a new sparkline group copied from an existing sparkline group to the specified worksheet

Return: The new sparkline group added

XLSparklineGroup (IXLCell location, IXLRange sourceData)

Add a new sparkline group copied from an existing sparkline group to the specified worksheet

Return: The new sparkline group added

XLSparklineGroup (IXLRange locationRange, IXLRange sourceDataRange)

Add a new sparkline group copied from an existing sparkline group to the specified worksheet

Return: The new sparkline group added

IXLSparkline Add (IXLCell location, IXLRange sourceData)

Add a sparkline to the group.

Param location: The cell to add sparklines to. If it already contains a sparkline it will be replaced.
Param sourceData: The range the sparkline gets data from
Return: A newly created sparkline.

void CopyFrom (IXLSparklineGroup sparklineGroup)

Copy the details from a specified sparkline group

Param sparklineGroup: The sparkline group to copy from

void Remove (IXLCell cell)

Remove all sparklines in the specified cell from this group

Param cell: The cell to remove sparklines from

void Remove (IXLSparkline sparkline)

Remove the sparkline from this group

Param sparkline

void RemoveAll (): Remove all sparklines from this group

Public Members

IXLWorksheet Worksheet = > _worksheet: The worksheet this sparkline group is associated with

class XLSparklineGroups : public IXLSparklineGroups 

Public Functions

IXLSparklineGroup Add (IXLSparklineGroup sparklineGroup)

Add the sparkline group to the collection.

Param sparklineGroup: The sparkline group to add to the collection
Return: The same sparkline group

IXLSparklineGroup AddCopy (IXLSparklineGroup sparklineGroupToCopy, IXLWorksheet targetWorksheet)

Add a copy of an existing sparkline group to the specified worksheet

Param sparklineGroupToCopy: The sparkline group to copy
Param targetWorksheet: The worksheet the sparkline group is being added to
Return: The new sparkline group added

void CopyTo (IXLWorksheet targetSheet)

Copy this sparkline group to a different worksheet

Param targetSheet: The worksheet to copy the sparkline group to

IXLSparkline GetSparkline (IXLCell cell)

Search for the first sparkline that is in the specified cell

Param cell: The cell to find the sparkline for
Return: The sparkline in the cell or null if no sparklines are found

IEnumerable<IXLSparkline> GetSparklines (IXLRangeBase searchRange)

Find all sparklines located in a given range

Param searchRange: The range to search
Return: The sparkline in the cell or null if no sparklines are found

void Remove (IXLCell cell)

Remove all sparklines in the specified cell

Param cell: The cell to remove sparklines from

void Remove (IXLSparklineGroup sparklineGroup)

Remove the sparkline group from the worksheet

Param sparklineGroup: The sparkline group to remove

void RemoveAll (): Remove all sparkline groups and their contents from the worksheet.

class XLSparklineHorizontalAxis : public IXLSparklineHorizontalAxis 

class XLSparklineStyle : public IXLSparklineStyle, public IEquatable<XLSparklineStyle>

Public Functions

bool Equals (XLSparklineStyle other)

Indicates whether the current object is equal to another object of the same type.

Param other: An object to compare with this object.
Return: true if the current object is equal to the other other parameter; otherwise, false.

override bool Equals (object obj)

Determines whether the specified object is equal to the current object.

Param obj: The object to compare with the current object.
Return: true if the specified object is equal to the current object; otherwise, false.

override int GetHashCode ()

Serves as the default hash function.

Return: A hash code for the current object.

Public Static Functions

bool operator!= (XLSparklineStyle left, XLSparklineStyle right)

Returns a value that indicates whether two T:ClosedXML.Excel.XLSparklineStyle objects have different values.

Param left: The first value to compare.
Param right: The second value to compare.
Return: true if left and right are not equal; otherwise, false.

bool operator== (XLSparklineStyle left, XLSparklineStyle right)

Returns a value that indicates whether the values of two T:ClosedXML.Excel.XLSparklineStyle objects are equal.

Param left: The first value to compare.
Param right: The second value to compare.
Return: true if the left and right parameters have the same value; otherwise, false.

class XLSparklineTheme

class XLSparklineVerticalAxis : public IXLSparklineVerticalAxis 

class XLStyle : public IXLStyle 

struct XLStyleKey : public IEquatable<XLStyleKey>

class XLStyleValue : public IEquatable<XLStyleValue?>

class XLStylizedBase : public IXLStylized 

Base class for any workbook element that has or may have a style.

Subclassed by XLCell, XLCells, XLColumns, XLConditionalFormat, XLRangeBase, XLRangeColumns, XLRangeRows, XLRanges, XLRows, XLStylizedEmpty, XLTableRows

Properties

IXLStyle Style { get; set; }: Editable style of the workbook element. Modification of this property DOES affect styles of child objects as well - they will be changed accordingly. Accessing this property causes a new XLStyle instance generated so use this property with caution. If you need only _read_ the style consider using StyleValue property instead.

IXLStyle InnerStyle { get; set; }: Editable style of the workbook element. Modification of this property DOES NOT affect styles of child objects. Accessing this property causes a new XLStyle instance generated so use this property with caution. If you need only _read_ the style consider using StyleValue property instead.

class ReferenceEqualityComparer<T> : public IEqualityComparer<T>

class XLStylizedEmpty : public XLStylizedBase, public IXLStylized 

class XLTable : public XLRange, public IXLTable 

Public Functions

XLTable (XLRangeParameters xlRangeParameters): The direct constructor should only be used in XLWorksheet.RangeFactory.

class XLTableField : public IXLTableField 

class XLTableRange : public XLRange, public IXLTableRange 

class XLTableRow : public XLRangeRow, public IXLTableRow 

class XLTableRows : public XLStylizedBase, public IXLTableRows, public IXLStylized 

class XLTables : public IXLTables, public IEnumerable<XLTable>

class XLTableTheme

class XLTextLengthCriteria : public XLWholeNumberCriteriaBase 

class XLTheme : public IXLTheme 

class XLTimeCriteria : public XLValidationCriteria 

class XLValidationCriteria : public IXLValidationCriteria : Subclassed by XLDateCriteria, XLDecimalCriteria, XLTimeCriteria, XLWholeNumberCriteriaBase

class XLWholeNumberCriteria : public XLWholeNumberCriteriaBase 

class XLWholeNumberCriteriaBase : public XLValidationCriteria : Subclassed by XLTextLengthCriteria, XLWholeNumberCriteria

class XLWorkbook : public IXLWorkbook 

Public Functions

IXLDefinedName? DefinedName (String name)

void Save (): Saves the current workbook.

void Save (Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook and optionally performs validation

void SaveAs (String file): Saves the current workbook to a file.

void SaveAs (String file, Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook to a file and optionally validates it.

void SaveAs (Stream stream): Saves the current workbook to a stream.

void SaveAs (Stream stream, Boolean validate, Boolean evaluateFormulae = false): Saves the current workbook to a stream and optionally validates it.

IEnumerable<IXLCell> Search (String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false)

Searches the cells’ contents for a given piece of text

Param searchText: The search text.
Param compareOptions: The compare options.
Param searchFormulae: if set to true search formulae instead of cell values.

XLWorkbook (): Creates a new Excel workbook.

XLWorkbook (String file)

Opens an existing workbook from a file.

Param file: The file to open.

XLWorkbook (Stream stream)

Opens an existing workbook from a stream.

Param stream: The stream to open.

IXLWorksheet AddWorksheet (DataTable dataTable)

Add a worksheet with a table at Cell(row:1, column:1). The dataTable’s name is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

Param dataTable: Datatable to insert
Return: Inserted Worksheet

IXLWorksheet AddWorksheet (DataTable dataTable, String sheetName)

Add a worksheet with a table at Cell(row:1, column:1). The sheetName provided is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

Param dataTable: dataTable to insert as Excel Table
Param sheetName: Worksheet and Excel Table name
Return: Inserted Worksheet

IXLWorksheet AddWorksheet (DataTable dataTable, String sheetName, String tableName)

Add a worksheet with a table at Cell(row:1, column:1).

Param dataTable: dataTable to insert as Excel Table
Param sheetName: Worksheet name
Param tableName: Excel Table name
Return: Inserted Worksheet

XLCellValue Evaluate (String expression)

Evaluate a formula expression.

Param expression: Formula expression to evaluate.
Throws MissingContextException: If the expression contains a function that requires a context (e.g. current cell or worksheet).

void RecalculateAllFormulas (): Force recalculation of all cell formulas.

Public Members

IXLDefinedNames DefinedNames = > DefinedNamesInternal: Gets an object to manipulate this workbook’s named ranges.

IXLPivotCaches PivotCaches = > PivotCachesInternal: All pivot caches in the workbook, whether they have a pivot table or not.

Properties

XLCellSetValueBehavior CellSetValueBehavior { get; set; }: Behavior for IXLCell.set_Value

IXLWorksheets Worksheets { get; set; }: Gets an object to manipulate the worksheets.

IXLTheme Theme { get; set; }: Gets an object to manipulate this workbook’s theme.

IXLStyle Style { get; set; }

Gets or sets the default style for the workbook.

All new worksheets will use this style.

Double RowHeight { get; set; }

Gets or sets the default row height for the workbook.

All new worksheets will use this row height.

Double ColumnWidth { get; set; }

Gets or sets the default column width for the workbook.

All new worksheets will use this column width.

IXLPageSetup PageOptions { get; set; }

Gets or sets the default page options for the workbook.

All new worksheets will use these page options.

IXLOutline Outline { get; set; }

Gets or sets the default outline options for the workbook.

All new worksheets will use these outline options.

XLWorkbookProperties Properties { get; set; }: Gets or sets the workbook’s properties.

XLCalculateMode CalculateMode { get; set; }: Gets or sets the workbook’s calculation mode.

XLReferenceStyle ReferenceStyle { get; set; }: Gets or sets the workbook’s reference style.

Public Static Functions

XLCellValue EvaluateExpr (String expression): Evaluate a formula and return a value. Formulas with references don’t work and culture used for conversion is invariant.

class XLWorkbookProperties

class XLWorkbookProtection : public IXLWorkbookProtection 

class XLWorksheet : public XLRangeBase, public IXLWorksheet 

Public Functions

XLRangeRow RangeRow (XLRangeAddress address, IXLStyle? defaultStyle = null)

Get a range row from the shared repository or create a new one.

Param address: Address of range row.
Param defaultStyle: Style to apply. If null the worksheet’s style is applied.
Return: Range row with the specified address.

XLRangeColumn RangeColumn (XLRangeAddress address, IXLStyle? defaultStyle = null)

Get a range column from the shared repository or create a new one.

Param address: Address of range column.
Param defaultStyle: Style to apply. If null the worksheet’s style is applied.
Return: Range column with the specified address.

Public Members

string? LegacyDrawingId: Reference to a VML that contains notes, forms controls and so on. All such things are generally unified into a single legacy VML file, set during load/save.

class XLWorksheetInternals : public IDisposable 

class XLWorksheets : public IXLWorksheets, public IEnumerable<XLWorksheet>

namespace Caching

interface IXLRepository<Tkey, Tvalue> : public IXLRepository, public IEnumerable<Tvalue>

Public Functions

void Clear (): Clear the repository;

Tvalue? Store (ref Tkey key, Tvalue value)

Put the value into the repository under the specified key if there is no such key present.

Param key: Key to identify the value.
Param value: Value to put into the repository if key does not exist.
Return: Value stored in the repository under the specified key . If key already existed returned value may differ from the input one.

class XLAlignmentRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLAlignmentKey, XLAlignmentValue>

class XLBorderRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLBorderKey, XLBorderValue>

class XLColorRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLColorKey, XLColor>

class XLFillRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLFillKey, XLFillValue>

class XLFontRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLFontKey, XLFontValue>

class XLNumberFormatRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLNumberFormatKey, XLNumberFormatValue>

class XLProtectionRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLProtectionKey, XLProtectionValue>

class XLRangeRepository : public ClosedXML::Excel::Caching::XLWorkbookElementRepositoryBase<XLRangeKey, XLRangeBase>

class XLRepositoryBase<Tkey, Tvalue> : public IXLRepository, public XLRepositoryBase, public ClosedXML::Excel::Caching::IXLRepository<Tkey, Tvalue>

Public Functions

bool ContainsKey (ref Tkey key, out Tvalue? value)

Check if the specified key is presented in the repository.

Param key: Key to look for.
Param value: Value from the repository stored under specified key or null if key does not exist or the entry under this key has already bee GCed.
Return: True if entry exists and alive, false otherwise.

Tvalue? Store (ref Tkey key, Tvalue value)

Put the entity into the repository under the specified key if no other entity with the same key is presented.

Param key: Key to identify the entity.
Param value: Entity to store.
Return: Entity that is stored in the repository under the specified key (it can be either the value or another entity that has been added to the repository before.)

IEnumerator<Tvalue> GetEnumerator (): Enumerate items in repository removing “dead” entries.

class XLStyleRepository : public ClosedXML::Excel::Caching::XLRepositoryBase<XLStyleKey, XLStyleValue>

class XLWorkbookElementRepositoryBase<Tkey, Tvalue> : public ClosedXML::Excel::Caching::XLRepositoryBase<Tkey, Tvalue>

Base repository for XLWorkbook elements.

Subclassed by XLRangeRepository

namespace CalcEngine

Enums

enum UnaryOp

Values:

Add

Subtract

Percentage

SpillRange

ImplicitIntersection

enum BinaryOp

Values:

Concat

Add

Sub

Mult

Div

Exp

Lt

Lte

Eq

Neq

Gte

Gt

Range

Union

Intersection

enum FormulaFlags

Flags that contain some useful information about a formula (mostly derived from flags in functions).

Values:

None: Basic formula that takes an input and returns output that is determined solely by the input. No side effects.

Volatile

Formula contains a function whose value can be different each time the function is called, even if arguments and workbook are same.

TODAY, RAND

SideEffect

Formula that has a side effects beside returning the value.

HYPERLINK changes the content of a cell in a workbook.

HasSubtotal

Formula contains a reference to the SUBTOTAL function.

Performance optimization, so a formula with SUBTOTAL doesn’t have to check each dependent cell each time it is evaluated.

enum FunctionFlags

Function flags that indicate what does function do. It is used by CalcEngine for calculation chain and formula execution.

Values:

Scalar: Function that takes an input and returns an output. It is designed for a single value arguments. If scalar function is used for array formula or dynamic array formula, the function is called for each element separately.

Range: Non-scalar function. At least one of arguments of the function accepts a range. It means that implicit intersection works differently.

SideEffect

Function has side effects, e.g. it changes something.

HYPERLINK

ReturnsArray: Function returns array. Functions without this flag return a scalar value. CalcEngine treats such functions differently for array formulas.

enum AllowRange

Which parameters of a function allow ranges. That is important for implicit intersection.

Values:

None: None of parameters allow ranges.

All: All parameters allow ranges.

Except: All parameters except marked ones allow ranges.

Only: Only marked parameters allow ranges.

Functions

delegate ScalarValue BinaryFunc (in ScalarValue lhs, in ScalarValue rhs, CalcContext ctx)

delegate AnyValue CalcEngineFunction (CalcContext ctx, Span<AnyValue> arg)

delegate object LegacyCalcEngineFunction (List<Expression> parms)

Delegate that represents CalcEngine functions.

Param parms: List of Expression objects that represent the parameters to be used in the function call.
Return: The function result.

struct AnyValue

A discriminated union representing any value that can be passed around in the formula evaluation.

Public Functions

bool TryPickArea (out XLRangeAddress area, out XLError error)

Try to get a reference that is a one area from the value.

Param area: The found area.
Param error: Original error, if the value is error, #VALUE! if type is not a reference or #REF! if more than one area in the reference.
Return: True if area could be determined, false otherwise.

AnyValue ImplicitIntersection (CalcContext context)

Implicit intersection for arguments of functions that don’t accept range as a parameter (Excel 2016).

Return: Unchanged value for anything other than reference. Reference is changed into a single cell/#VALUE!

Array GetArray ()

Return the array value.

Throws InvalidCastException

Public Members

bool IsScalarType = > IsBlank || IsLogical || IsNumber || IsText || IsError: Is the value a scalar (blank, logical, number, text or error).

int Rows: Get 2d size of the value. For scalars, it’s 1x1, for multi-area references, it’s also 1x1,because it is converted to #VALUE! error.

Public Static Functions

AnyValue ReferenceRange (in AnyValue left, in AnyValue right, CalcContext ctx): Create a new reference that has one area that contains both operands or #VALUE! if not possible.

AnyValue ReferenceUnion (in AnyValue left, in AnyValue right): Create a new reference by combining areas of both arguments. Areas of the new reference can overlap = some overlapping cells might be counted multiple times (SUM((A1;A1)) = 2 if A1 is 1).

Public Static Attributes

readonly AnyValue Blank = new(BlankValue, default, default, default, default, default, default): A value of a blank cell or missing argument. Conversion methods mostly treat blank like 0 or an empty string.

class Array : public IEnumerable<ScalarValue>

A base class for an 2D array. Every array is at least 1x1.

Subclassed by ConstArray, LiteralArray, NumberArray, ReferenceArray, RepeatedColumnArray, RepeatedRowArray, ResizedArray, ScalarArray, TransposedArray

Public Functions

IEnumerator<ScalarValue> GetEnumerator (): An iterator over all elements of an array, from top to bottom, from left to right.

Array Apply (Func<ScalarValue, ScalarValue> op): Return a new array that was created by applying a function to each element of the array.

Array Apply (Array rightArray, BinaryFunc func, CalcContext ctx): Return a new array that was created by applying a function to each element of the left and right array. Arrays can have different size and missing values are replaced by #N/A.

Array Broadcast (int rows, int columns): Broadcast array for calculation of array formulas.

Properties

abstract int Width { get; set; }: Width of the array, at least 1.

abstract int Height { get; set; }: Height of the array, at least 1.

y, int x] { get; set; }

Get a value at specified coordinate.

Param y: Uses 0-based notation.
Param x: Uses 0-based notation.

class ArrayNode : public ValueNode : AST node that contains a constant array. Array is at least 1x1.

class AstNode

Base class for all AST nodes. All AST nodes must be immutable.

Subclassed by FileNode, PrefixNode, ValueNode

Public Functions

abstract TResult Accept<TContext, TResult> (TContext context, IFormulaVisitor<TContext, TResult> visitor): Method to accept a visitor (=call a method of visitor with correct type of the node).

class BinaryNode : public ValueNode : Binary expression, e.g. 1+2

class CalcContext

Public Members

XLWorkbook Workbook = > _workbook ?? throw new MissingContextException(): Worksheet of the cell the formula is calculating.

XLWorksheet Worksheet = > _worksheet ?? throw new MissingContextException(): Worksheet of the cell the formula is calculating.

IXLAddress FormulaAddress = > _formulaAddress ?? throw new MissingContextException(): Address of the calculated formula.

bool UseImplicitIntersection = > true: Excel 2016 and earlier doesn’t support dynamic array formulas (it used an array formulas instead). As a consequence, all arguments for scalar functions where passed through implicit intersection before calling the function.

Properties

CultureInfo Culture { get; set; }: A culture used for comparisons and conversions (e.g. text to number).

bool IsArrayCalculation { get; set; }: Should functions be calculated per item of multi-values argument in the scalar parameters.

uint? RecalculateSheetId { get; set; }: Sheet that is being recalculated. If set, formula can read dirty values from other sheets, but not from this sheetId.

class CalcEngineHelpers

class CalculationVisitor : public ClosedXML::Excel::CalcEngine::IFormulaVisitor<CalcContext, AnyValue>

class CellRangeReference : public IValueObject, public IEnumerable 

class ConstArray : public Array : An array of scalar values.

class DateTimeParser

class DefaultFormulaVisitor<TContext> : public ClosedXML::Excel::CalcEngine::IFormulaVisitor<TContext, AstNode>: A default visitor that copies a formula.

class DependenciesContext: Context for DependenciesVisitor, it is used to collect all objects a formula depends on during calculation.

class DependenciesVisitor : public ClosedXML::Excel::CalcEngine::IFormulaVisitor<DependenciesContext, List<XLBookArea>?>

Visit each node and determine all ranges that might affect the formula. It uses concrete values (e.g. actual range for structured references) and should be refreshed when structured reference or name is changed in a workbook.

The areas found by the visitor shouldn’t change when data on a worksheet changes, so the output is a superset of areas, if necessary.

Precedents visitor is not completely accurate, in case of uncertainty, it uses a larger area. At worst the end result is unnecessary recalculation. For simple cases, it works fine and freaks like A1:IF(Other!B5,B7,Different!G3) will be marked as dirty more often than strictly necessary.

Each node visitor evaluates, if the output is a reference or a value/array. If the result is an array, it propagates to upper nodes, where can be things like range operator.

class DependencyTree

A dependency tree structure to hold all formulas of the workbook and reference objects they depend on. The key feature of dependency tree is to propagate dirty flag across formulas.

When a data in a cell changes, all formulas that depend on it should be marked as dirty, but it is hard to find which cells are affected - that is what dependency tree does.

Dependency tree must be updated, when structure of a workbook is updated:

Sheet is added, renamed or deleted.
Name is added or deleted.
Table is resized, renamed, added or deleted.

Any such action changes what cells formula depends on and the formula dependencies must be updated.

class EmptyValueExpression : public Expression : Expression that represents an omitted parameter.

class Engineering

class Expression : public IComparable<Expression>

An adapter for legacy function implementations.

Subclassed by EmptyValueExpression, XObjectExpression

class ExpressionCache

Caches expressions based on their string representation. This saves parsing time.

Uses weak references to avoid accumulating unused expressions.

class ExpressionParseException : public Exception

The exception that is thrown when the strings to be parsed to an expression is invalid.

Public Functions

ExpressionParseException (string message)

Initializes a new instance of the ExpressionParseException class with a specified error message.

Param message: The message that describes the error.

class FileNode : public AstNode 

AST node for an reference to an external file in a formula.

Properties

int? Numeric { get; set; }: If the file is references indirectly, numeric identifier of a file.

string? Path { get; set; }: If a file is referenced directly, a path to the file on the disc/UNC/web link, .

class Financial

class Formula

A non-state representation of a formula that can be used by many cells.

Properties

string Text { get; set; }: Text of the formula.

class FormulaDependencies

A list of objects a cell formula depends on. If one of them changes, the formula value might no longer be accurate and needs to be recalculated.

Public Members

IReadOnlyCollection< XLBookArea > Areas => _areas: List of areas the formula depends on. It is likely a superset of accurate result for unusual formulas, but if a value in an areas changes, the dependent formula should be marked as dirty.

IReadOnlyCollection< XLName > Names => _names: A collection of names in the formula. If a name changes (added, deleted), the formula dependencies should be refreshed, because new name might refer to different references (e.g. a name previously referred to A5 and is redefined to B7 or just value 7 => formula no longer depends on A5).

class FormulaParser

Public Functions

Formula GetAst (string formula, bool isA1): Parse a formula into an abstract syntax tree.

class FractionParser: Parse a fraction for text-to-number type coercion.

class FunctionDefinition

Function definition class (keeps function name, parameter counts, and delegate).

Public Functions

AnyValue CallAsArray (CalcContext ctx, Span<AnyValue> args): Evaluate the function with array formula semantic.

class FunctionNode : public ValueNode 

A function call, e.g. SIN(0.5).

Properties

string Name { get; set; }: Name of the function.

ValueNode > Parameters { get; set; }: AST nodes for arguments of the function.

class FunctionRegistry

Public Functions

void RegisterFunction (string functionName, int minParams, int maxParams, CalcEngineFunction fn, FunctionFlags flags, AllowRange allowRanges = AllowRange.None, params int[] markedParams)

Add a function to the registry.

Param functionName: Name of function in formulas.
Param minParams: Minimum number of parameters.
Param maxParams: Maximum number of parameters.
Param fn: A delegate of a function that will be called when function is supposed to be evaluated.
Param flags: Flags that indicate some additional info about function.
Param allowRanges: Which parameters allow ranges to be argument. Useful for array formulas.
Param markedParams: Index of parameter that is marked, start from 0

interface IFormulaVisitor<in TContext, out TResult>: Subclassed by CalculationVisitor, DefaultFormulaVisitor< TContext >, DependenciesVisitor

interface IValueObject

Interface supported by external objects that have to return a value other than themselves (e.g. a cell range object should return the cell content instead of the range itself).

Subclassed by CellRangeReference

class LiteralArray : public Array 

Array for array literal from a parser. It uses a 1D array of values as a storage.

Public Functions

LiteralArray (int rows, int columns, IReadOnlyList<ScalarValue> elements)

Create a new instance of a LiteralArray.

Param rows: Number of rows of an array/
Param columns: Number of columns of an array.
Param elements: Row by row data of the array. Has the expected size of an array.

class Logical

class MathTrig

class NameNode : public ValueNode 

A name node in the formula. Name can refers to a generic formula, in most cases a reference, but it can be any kind of calculation (e.g. A1+7).

Properties

PrefixNode? Prefix { get; set; }: An optional prefix for reference item.

class NotSupportedNode : public ValueNode : An placeholder node for AST nodes that are not yet supported in ClosedXML.

class NumberArray : public Array : A special case of an array that is actually only numbers.

struct OneOf<T0, T1>

class PrefixNode : public AstNode 

AST node for prefix of a reference in a formula. Prefix is a specification where to look for a reference.

Prefix specifies a Sheet - used for references in the local workbook.
Prefix specifies a FirstSheet and a LastSheet - 3D reference, references uses all sheets between first and last.
Prefix specifies a File, no sheet is specified - used for named ranges in external file.
Prefix specifies a File and a Sheet - references looks for its address in the sheet of the file.

Properties

FileNode? File { get; set; }: If prefix references data from another file, can be empty.

string? Sheet { get; set; }: Name of the sheet, without ! or escaped quotes. Can be null in some cases e.g. reference to a named range in an another file).

string? FirstSheet { get; set; }: If the prefix is for 3D reference, name of first sheet. Empty otherwise.

string? LastSheet { get; set; }: If the prefix is for 3D reference, name of the last sheet. Empty otherwise.

class Reference

Reference is a collection of cells in the workbook. It’s used in formula evaluation. Every reference has at least one cell.

Public Functions

Reference (List<XLRangeAddress> areas): Ctor that reuses parameter to keep allocations low - don’t modify the collection after it is passed to ctor.

IEnumerable<ScalarValue> GetCellsValues (CalcContext ctx): An iterator over all nonblank cells of the range. Some cells can be iterated over multiple times (e.g. a union of two ranges with overlapping cells).

OneOf<Reference, XLError> ImplicitIntersection (IXLAddress formulaAddress)

Do an implicit intersection of an address.

Param formulaAddress
Return: An address of the intersection or error if intersection failed.

class ReferenceArray : public Array : An array that retrieves its value directly from the worksheet without allocating extra memory.

class ReferenceNode : public ValueNode 

AST node for a reference of an area in some sheet.

Properties

PrefixNode? Prefix { get; set; }: An optional prefix for reference item.

string Address { get; set; }: An address of a reference that corresponds to Type. Always without sheet (that is in the prefix).

ReferenceArea ReferenceArea { get; set; }: An area from a parser.

bool IsA1 { get; set; }: Is the reference in A1 style? If false, then it is R1C1.

class RepeatedColumnArray : public Array 

class RepeatedRowArray : public Array 

class ResizedArray : public Array : A resize array from another array. Extra items without value have #N/A.

class ScalarArray : public Array : An array where all elements have same value.

class ScalarNode : public ValueNode : AST node that contains a blank, logical, number, text or an error value.

struct ScalarValue

A representation of a value as a discriminated union.

A bare bone copy of OneOf that can be more optimized:

readonly struct to get rid of defensive copies
struct can be smaller through offsets (based on NoBox)
allows to pass additional arguments to Match function to skip a need to instantiate a new lambda instance on each call and allow easier inlining.

Public Functions

OneOf<string, XLError> ToText (CultureInfo culture): Convert value to text. Error is not convertible.

OneOf<double, XLError> ToNumber (CultureInfo culture): Convert value to number. Error is not convertible.

bool HaveSameType (ScalarValue other): Does this value have same type as the other one?

bool TryCoerceLogicalOrBlankOrNumberOrText (out Boolean value, out XLError error)

Get the logical value, if it is either blank (false), logical or number (0 = false, otherwise true)a text TRUE or FALSE (case insensitive).

Used for coercion in functions.

Public Static Attributes

readonly ScalarValue Blank = new(BlankValue, default, default, default, default)

A blank value of a scalar. It can behave as a 0 or empty string, depending on context.

A1+5 is a number 5, blank behaves as 0, A1 & "text" is a “text”, blank behaves as empty string.

class ScalarValueComparer : public IComparer<ScalarValue>

A comparer of a scalar logic. Each comparer with it’s logic can be accessed through a static property.

Properties

ScalarValueComparer SortIgnoreCase { get; set; } = new(StringComparer.OrdinalIgnoreCase)

Compare scalar values according to logic of “Sort” data in Excel, though texts are compared case insensitive.

Order is

Type Number, from low to high
Type Text, from low to high (non-culture specific, ordinal compare)
Type Logical, FALSE, then TRUE.
Type Error, all error values are treated as equal (at least they don’t change order).
Type Blank, all values are treated as equal.

class Statistical

class StructuredReferenceNode : public ValueNode 

Properties

PrefixNode? Prefix { get; set; }: Can be empty if no prefix available.

string? Table { get; set; }: Table of the reference. It can be empty, if formula using the reference is within the table itself (e.g. total formulas).

StructuredReferenceArea Area { get; set; }: Area of the table that is considered for the range of cell of reference.

string? FirstColumn { get; set; }: First column of column range. If the reference refers to the whole table, the value is null.

string? LastColumn { get; set; }: Last column of column range. If structured reference refers only to one column, it is same as FirstColumn. If the reference refers to the whole table, the value is null.

class Tally : public IEnumerable<Object>

class Text

class TimeSpanParser

A parser of timespan format used by excel during coercion from text to number. TimeSpan parsing methods don’t allow for several features required by excel (e.g. seconds/minutes over 60, hours over 24). Parser can parse following formats from ECMA-376, Part 1, §18.8.30. due to standard text-to-number coercion:

Format 20 - h:mm.
Format 21 - h:mm:ss.
Format 47 - mm:ss.0 (format is incorrectly described as mmss.0 in the standard, but fixed in an implementation errata).

Timespan is never interpreted through format 45 (mm:ss), instead preferring the format 20 (h:mm). Timespan is never interpreted through format 46 ([h]:mm:ss], such values are covered by format 21 (h:mm:ss).

Note that the decimal fraction differs format 20 and 47, thus mere addition of decimal place means significantly different values. Parser also copies features of Excel, like whitespaces around a decimal place (10:20 . 5 is allowed).

20:30 is detected as format 20 and the first number is interpreted as hours, thus the serial time is 0.854167. 20:30.0 is detected as format 47 and the first number is interpreted as minutes, thus the serial time is 0.014236111.

class TransposedArray : public Array 

class UnaryNode : public ValueNode : Unary expression, e.g. +123

class ValueNode : public AstNode 

A base class for all AST nodes that can be evaluated to produce a value.

Subclassed by ArrayNode, BinaryNode, FunctionNode, NameNode, NotSupportedNode, ReferenceNode, ScalarNode, StructuredReferenceNode, UnaryNode

struct Wildcard

A wildcard is at most 255 chars long text. It can contain * which indicates any number characters (including zero) and ? which indicates any single character. If you need to find * or ? in a text, prefix them with an escape character ~.

Public Functions

int Search (ReadOnlySpan<char> text)

Search for the wildcard anywhere in the text.

Param text: Text used to search for a pattern.
Return: zero-based index of a first character in a text that matches to a pattern or -1, if match wasn’t found.

class XLAddressComparer : public IEqualityComparer<IXLAddress>

class XLCalcEngine : public ISheetListener, public IWorkbookListener

CalcEngine parses strings and returns Expression objects that can be evaluated.

This class has three extensibility points:

Use the RegisterFunction method to define custom functions.

Public Functions

Formula Parse (string expression)

Parses a string into an Expression.

Param expression: String to parse.
Return: An Expression object that can be evaluated.

class XLCalculationChain

A calculation chain of formulas. Contains all formulas in the workbook.

Calculation chain is an ordering of all cells that have value calculated by a formula (note that one formula can determine value of multiple cells, e.g. array). Formulas are calculated in specified order and if currently processed formula needs data from a cell whose value is dirty (i.e. it is determined by a not-yet-calculated formula), the current formula is stopped and the required formula is placed before the current one and starts to be processed. Once it is done, the original formula is starts to be processed again. It might have encounter another not-yet-calculated formula or it will finish and the calculation chain moves to the next one.

Chain can be traversed through Current, MoveAhead, MoveToCurrent and Reset, but only one traversal can go on at the same time due to shared info about cycle detection.

class XLRangeAddressComparer : public IEqualityComparer<IXLRangeAddress>

class XObjectExpression : public Expression, public IEnumerable : Expression that represents an external object.

namespace Exceptions

class FormulaErrorException : public Exception: An exception to propagate error from legacy expression function.

class GettingDataException : public Exception: Exception that happens when formula in a cell depends on other cells, but the supporting formulas are still dirty.

class MissingContextException : public InvalidOperationException: Evaluation of the formula needs an information that wasn’t available. That can happen if the formula is evaluated from methods like XLWorkbook.Evaluate(string). Causes vary, e.g. implicit intersection needs an address of the formula cell. Various methods in ClosedXML are missing different information, e.g. IXLWorksheet.Evaluate(string, string) has worksheet, but no cell address (=ranges will work, other things won’t).

namespace Functions

class ArgumentsExtensions

An extension methods

Public Static Functions

OneOf<TValue, XLError> Aggregate<TValue> (this Span<AnyValue> args, CalcContext ctx, TValue initialValue, OneOf<TValue, XLError> noElementsResult, Func<TValue, TValue, TValue> aggregate, Func<ScalarValue, CalcContext, OneOf<TValue, XLError>> convert, Func<ScalarValue, bool>? collectionFilter = null)

Aggregate all values in the arguments of a function into a single value. If any value is error, return the error.

A lot of functions take all argument values and aggregate the values to a different value. These aggregation functions apply aggregation on each argument and if the argument is a collection (array/reference), the aggregation function is also applied to each element of the array/reference (e.g. SUM({1, 2}, 3) applies sum on each element of an array {1,2} and thus result is 1+2+3).

>

Tparam TValue: Type of the value that is being aggregated.
Param args: Arguments of a function. Method goes over all elements of the arguments.
Param ctx: Calculation context.
Param initialValue: Initial value of the accumulator. It is used as an input into the first call of aggregate .
Param noElementsResult: What should be the result of aggregation, if there are no elements. Common choices are XLError.IncompatibleValue or the initialValue .
Param aggregate: The aggregation function. First parameter is the accumulator, second parameter is the value of current element taken from convert . Make sure the method is static lambda to avoid useless allocations.
Param convert: A function that converts a scalar value of an element into the TValue or an error if it can’t be converted. Make sure the method is static lambda to avoid useless allocations.
Param collectionFilter: Some functions skip elements in a array/reference that would be accepted as an argument, e.g. SUM("1", {2,"4"}) is 3 - it converts string "3" to a number 3 in for root arguments, but omits element "4" in the array. This is a function that determines which elements to include and which to skip. If null, all elements of array are included and all values are treated same. Make sure the method is static lambda to avoid useless allocations.

class Database

class DateAndTime

class Information

class Lookup

class SignatureAdapter: A collection of adapter functions from a more a generic formula function to more specific ones.

class XLMath

class XLMatrix

namespace Cells

class XLCellValueComparer : public IEqualityComparer<XLCellValue>

namespace ContentManagers

Enums

enum XLSheetViewContents

Values:

Pane

Selection

PivotSelection

ExtensionList

enum XLWorksheetContents

Values:

SheetProperties

SheetDimension

SheetViews

SheetFormatProperties

Columns

SheetData

SheetCalculationProperties

SheetProtection

ProtectedRanges

Scenarios

AutoFilter

SortState

DataConsolidate

CustomSheetViews

MergeCells

PhoneticProperties

ConditionalFormatting

DataValidations

Hyperlinks

PrintOptions

PageMargins

PageSetup

HeaderFooter

RowBreaks

ColumnBreaks

CustomProperties

CellWatches

IgnoredErrors

SmartTags

Drawing

LegacyDrawing

LegacyDrawingHeaderFooter

DrawingHeaderFooter

Picture

OleObjects

Controls

AlternateContent

WebPublishItems

TableParts

WorksheetExtensionList

class XLBaseContentManager<T>: Subclassed by XLSheetViewContentManager, XLWorksheetContentManager

class XLSheetViewContentManager : public ClosedXML::Excel::ContentManagers::XLBaseContentManager<XLSheetViewContents>

class XLWorksheetContentManager : public ClosedXML::Excel::ContentManagers::XLBaseContentManager<XLWorksheetContents>

namespace Drawings

Enums

enum XLMarkerPosition

Values:

TopLeft

BottomRight

enum XLPictureFormat

Values:

Unknown

Bmp

Gif

Png

Tiff

Icon

Pcx

Jpeg

Emf

Wmf

Webp

enum XLPicturePlacement

Values:

MoveAndSize

Move

FreeFloating

interface IXLPicture : public IDisposable 

Subclassed by XLPicture

Public Functions

IXLPicture CopyTo (IXLWorksheet targetSheet)

Create a copy of the picture on a different worksheet.

Param targetSheet: The worksheet to which the picture will be copied.
Return: A created copy of the picture.

void Delete (): Deletes this picture.

IXLPicture Duplicate ()

Create a copy of the picture on the same worksheet.

Return: A created copy of the picture.

Properties

XLPictureFormat Format { get; set; }: Type of image. The supported formats are defined by OpenXML’s ImagePartType. Default value is “jpeg”

Int32 Width { get; set; }: Current width of the picture in pixels.

Int32 Height { get; set; }: Current height of the picture in pixels.

Int32 OriginalHeight { get; set; }: Original height of the picture in pixels.

Int32 OriginalWidth { get; set; }: Original width of the picture in pixels.

interface IXLPictures : public IEnumerable<IXLPicture>: Subclassed by XLPictures

class XLMarker

class XLPicture : public IXLPicture 

Public Functions

IXLPicture CopyTo (IXLWorksheet targetSheet)

Create a copy of the picture on a different worksheet.

Param targetSheet: The worksheet to which the picture will be copied.
Return: A created copy of the picture.

IXLPicture Duplicate ()

Create a copy of the picture on the same worksheet.

Return: A created copy of the picture.

class XLPictures : public IXLPictures, public IEnumerable<XLPicture>

namespace Exceptions

class ClosedXMLException : public Exception: Subclassed by EmptyTableException

class EmptyTableException : public ClosedXMLException 

class InvalidReferenceException : public Exception: A reference to the data in a worksheet is not valid. E.g. sheet with specific name doesn’t exist, name doesn’t exist.

namespace InsertData

class ArrayReader : public IInsertDataReader 

class DataRecordReader : public IInsertDataReader 

class DataTableReader : public IInsertDataReader 

interface IInsertDataReader

A universal interface for different data readers used in InsertData logic.

Subclassed by ArrayReader, DataRecordReader, DataTableReader, NullDataReader, ObjectReader, SimpleNullableTypeReader, SimpleTypeReader, UntypedObjectReader

Public Functions

IEnumerable<IEnumerable<XLCellValue>> GetRecords (): Get a collection of records, each as a collection of values, extracted from a source.

int GetPropertiesCount (): Get the number of properties to use as a table with. Actual number of may vary in different records.

string? GetPropertyName (int propertyIndex): Get the title of the property with the specified index.

class InsertDataReaderFactory

class NullDataReader : public IInsertDataReader 

class ObjectReader : public IInsertDataReader 

class SimpleNullableTypeReader : public IInsertDataReader 

class SimpleTypeReader : public IInsertDataReader 

class UntypedObjectReader : public IInsertDataReader 

namespace IO

class CalculationChainPartWriter

class CommentPartWriter

class CustomFilePropertiesPartWriter

class ExtendedFilePropertiesPartWriter

class OpenXmlConst

Constants used across writers.

Public Static Attributes

readonly String TrueValue = "1": Valid and shorter than normal true.

readonly String FalseValue = "0": Valid and shorter than normal false.

class PartStructureException : public Exception: An exception thrown from parser when there is a problem with data in XML. The exception messages are rather generic and not very helpful, but they aren’t supposed to be. If this exception is thrown, there is either a problem with producer of a workbook or ClosedXML. Both should do investigation based on a the file causing an error.

class PivotTableCacheDefinitionPartReader

class PivotTableCacheDefinitionPartWriter

class PivotTableCacheRecordsPartWriter

class PivotTableDefinitionPartWriter

class SharedStringTableWriter

class TablePartWriter: A writer for table definition part.

class TextSerializer

class ThemePartWriter

class VmlDrawingPartWriter

class WorkbookPartWriter

class WorkbookStylesPartWriter

class WorksheetPartWriter

namespace Patterns

class Quadrant

Implementation of QuadTree adapted to Excel worksheet specifics. Differences with the classic implementation are that the topmost level is split to 128 square parts (2 columns of 64 blocks, each 8192*8192 cells) and that splitting the quadrant onto 4 smaller quadrants does not depend on the number of items in this quadrant. When the range is added to the QuadTree it is placed on the bottommost level where it fits to a single quadrant. That means, row-wide and column-wide ranges are always placed at the level 0, and the smaller the range is the deeper it goes down the tree. This approach eliminates the need of transferring ranges between levels.

Subclassed by Quadrant< T >

namespace Ranges

namespace Index

interface IXLRangeIndex<T> : public IXLRangeIndex 

class XLRangeIndex<T> : public IXLRangeIndex, public XLRangeIndex, public ClosedXML::Excel::Ranges::Index::IXLRangeIndex<T>: Generic version of XLRangeIndex.