API Index

namespace ClosedXML.Excel

Enums

enum XLFilterDynamicType

Values:

AboveAverage
BelowAverage
enum XLFilterType

Values:

Regular
Custom
TopBottom
Dynamic
DateTimeGrouping
enum XLTopBottomPart

Values:

Top
Bottom
enum XLTopBottomType

Values:

Items
Percent
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 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 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 XLNamedRangeScope

Values:

Worksheet
Workbook
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 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 XLPivotSortType

Values:

Default
Ascending
Descending
enum XLPivotSubtotals

Values:

DoNotShow
AtTop
AtBottom
enum XLFilterAreaOrder

Values:

DownThenOver
OverThenDown
enum XLItemsToRetain

Values:

Automatic
None
Max
enum XLPivotTableSourceType

Values:

Range
Table
enum XLPivotStyleFormatElement

Values:

None
Label
Data
All
enum XLPivotStyleFormatTarget

Values:

PivotTable
GrandTotal
Subtotal
Header
Label
Data
enum XLPivotSummary

Values:

Sum
Count
Average
Minimum
Maximum
Product
CountNumbers
StandardDeviation
PopulationStandardDeviation
Variance
PopulationVariance
enum XLPivotCalculation

Values:

Normal
DifferenceFrom
PercentageOf
PercentageDifferenceFrom
RunningTotal
PercentageOfRow
PercentageOfColumn
PercentageOfTotal
Index
enum XLPivotCalculationItem

Values:

Value
Previous
Next
enum XLPivotAreaValues

Values:

None
Normal
Data
All
Origin
Button
TopRight
TopEnd
enum XLSheetProtectionElements

Values:

None
AutoFilter
DeleteColumns
DeleteRows
EditObjects
EditScenarios
FormatCells
FormatColumns
FormatRows
InsertColumns
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

Values:

MoveCells
ReplaceCells
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
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 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
class DoubleValueExtensions
class EnumConverter
class ><T> EnumerableComparer : public IEqualityComparer< IEnumerable< T>
class EnumerableExtensions
class FontBaseExtensions
class IntegerExtensions
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.

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

Subclassed by XLAutoFilter

interface IXLBaseCollection<TSingle, TMultiple> : public System::Collections::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.

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 Boolean.

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 ()

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

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

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.

Boolean NeedsRecalculation { get; set; }

Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated.

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.

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 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.

The width of the column as multiple of maximum digit width (MDW). MDW is a maximum width of a 0-9 digit character.

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 System::Collections::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

Subclassed by XLCustomFilteredColumn

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 System::Collections::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 IXLDateTimeGroupFilteredColumn

Subclassed by XLDateTimeGroupFilteredColumn

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 ICloneable, public IXLElementProtection

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

Subclassed by XLFilterColumn

interface IXLFilterConnector

Subclassed by XLFilterConnector

interface IXLFilteredColumn

Subclassed by XLFilteredColumn

interface IXLFont : public IXLFontBase, public IEquatable<IXLFont>

Subclassed by XLFont

interface IXLFontBase

Subclassed by IXLDrawingFont, IXLFont, IXLPhonetics, IXLRichString

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

Subclassed by XLFormattedText< T >

Public Functions

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

Copy the text and formatting from the original text.

Param original

Original to copy from.

Return

This text.

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.

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 IXLNamedRange

Subclassed by XLNamedRange

Public Functions

IXLRanges Add (XLWorkbook workbook, String rangeAddress)

Adds the specified range to this named range.

Note: A named range can point to multiple ranges.

Param workbook

Workbook containing the range

Param rangeAddress

The range address to add.

IXLRanges Add (IXLRange range)

Adds the specified range to this named range.

Note: A named range can point to multiple ranges.

Param range

The range to add.

IXLRanges Add (IXLRanges ranges)

Adds the specified ranges to this named range.

Note: A named range can point to multiple ranges.

Param ranges

The ranges to add.

void Clear ()

Clears the list of ranges associated with this named range.

(it does not clear the cells)

void Delete ()

Deletes this named range (not the cells).

void Remove (String rangeAddress)

Removes the specified range from this named range.

Note: A named range can point to multiple ranges.

Param rangeAddress

The range address to remove.

void Remove (IXLRange range)

Removes the specified range from this named range.

Note: A named range can point to multiple ranges.

Param range

The range to remove.

void Remove (IXLRanges ranges)

Removes the specified ranges from this named range.

Note: A named range can point to multiple ranges.

Param ranges

The ranges to remove.

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!).

String Name { get; set; }

Gets or sets the name of the range.

The name of the range.

IXLRanges Ranges { get; set; }

Gets the ranges associated with this named range.

Note: A named range can point to multiple ranges.

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 IXLNamedRanges : public System::Collections::IEnumerable<IXLNamedRange>

Subclassed by XLNamedRanges

Public Functions

IXLNamedRange NamedRange (String rangeName)

Gets the specified named range.

Param rangeName

Name of the range.

IXLNamedRange Add (String rangeName, String rangeAddress)

Adds a new named range.

Param rangeName

Name of the range to add.

Param rangeAddress

The range address to add.

IXLNamedRange Add (String rangeName, IXLRange range)

Adds a new named range.

Param rangeName

Name of the range to add.

Param range

The range to add.

IXLNamedRange Add (String rangeName, IXLRanges ranges)

Adds a new named range.

Param rangeName

Name of the range to add.

Param ranges

The ranges to add.

IXLNamedRange Add (String rangeName, String rangeAddress, String comment)

Adds a new named range.

Param rangeName

Name of the ranges to add.

Param rangeAddress

The range address to add.

Param comment

The comment for the new named range.

IXLNamedRange Add (String rangeName, IXLRange range, String comment)

Adds a new named range.

Param rangeName

Name of the ranges to add.

Param range

The range to add.

Param comment

The comment for the new named range.

IXLNamedRange Add (String rangeName, IXLRanges ranges, String comment)

Adds a new named range.

Param rangeName

Name of the ranges to add.

Param ranges

The ranges to add.

Param comment

The comment for the new named range.

void Delete (String rangeName)

Deletes the specified named range (not the cells).

Param rangeName

Name of the range to delete.

void Delete (Int32 rangeIndex)

Deletes the specified named range’s index (not the cells).

Param rangeIndex

Index of the named range to delete.

void DeleteAll ()

Deletes all named ranges (not the cells).

IEnumerable<IXLNamedRange> ValidNamedRanges ()

Returns a subset of named ranges that do not have invalid references.

IEnumerable<IXLNamedRange> InvalidNamedRanges ()

Returns a subset of named ranges that do have invalid references.

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.

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.

UInt32? 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.

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 System::Collections::IEnumerable<IXLPhonetic>, public IEquatable<IXLPhonetics>

Subclassed by XLPhonetics

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

interface IXLPivotTables : public IEnumerable<IXLPivotTable>

Subclassed by XLPivotTables

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 BaseField { get; set; }

Name of a base field to calculate a value to show in the pivot table. The base field determines which base items can be used. 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.

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 BaseItem { 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.

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

Subclassed by XLPivotValueCombination

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 System::Collections::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.

new IXLRange Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this range.

Param clearOptions

Specify what you want to clear.

interface IXLRangeAddress

Subclassed by XLRangeAddress

Public Functions

IXLRange AsRange ()

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

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 rangeName)

Creates a named range out of this range.

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.

IXLRange AddToNamed (String rangeName, XLScope scope)

Creates a named range out of this range.

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.

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

Creates a named range out of this range.

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.

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 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 System::Collections::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 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

Subclassed by XLSortElement

interface IXLSortElements : public IEnumerable<IXLSortElement>

Subclassed by XLSortElements

interface IXLSparkline

Subclassed by XLSparkline

interface IXLSparklineGroup : public IEnumerable<IXLSparkline>

Subclassed by XLSparklineGroup

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.

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

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.

IXLCell HeaderCell { get; set; }

Gets the header cell for the table field.

The header cell.

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 totals row label.

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

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.

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.

IXLNamedRanges NamedRanges { get; set; }

Gets an object to manipulate this workbook’s named ranges.

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.

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.

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.

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.