API Index
- namespace ClosedXML.Excel
Enums
- 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 intersectB1:B1
area andC1: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.
- 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 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 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 XLOperator
Values:
- EqualTo
- NotEqualTo
- GreaterThan
- LessThan
- EqualOrGreaterThan
- EqualOrLessThan
- Between
- NotBetween
- enum XLDashStyle
Values:
- Solid
- RoundDot
- SquareDot
- Dash
- DashDot
- LongDash
- LongDashDot
- LongDashDotDot
- enum XLHFPredefinedText
Values:
- PageNumber
- NumberOfPages
- Date
- Time
- FullPath
- Path
- File
- SheetName
- 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 XLSubtotalFunction
Values:
- Automatic
- None
- Sum
- Count
- Average
- Minimum
- Maximum
- Product
- CountNumbers
- StandardDeviation
- PopulationStandardDeviation
- Variance
- PopulationVariance
- enum XLPivotCalculation
Values:
- Normal
- DifferenceFrom
- PercentageOf
- PercentageDifferenceFrom
- RunningTotal
- PercentageOfRow
- PercentageOfColumn
- PercentageOfTotal
- Index
- enum XLPivotSummary
Values:
- Sum
- Count
- Average
- Minimum
- Maximum
- Product
- CountNumbers
- StandardDeviation
- PopulationStandardDeviation
- Variance
- PopulationVariance
- enum XLPivotCacheValueType
An enum that represents types of values in pivot cache records. It represents values under
CT_Record
type.Values:
- Missing
A blank value. Keep at 0 so newly allocated arrays of values have a value of missing.
- Number
Double precision number, not
NaN
orinfinity
.
- Boolean
Bool value.
- String
Cache value is a string. Because references can’t be converted to number (GC would not accept it), the value is an index into a table of strings in the cache.
- DateTime
Value is a date time. Although the value can be in theory
csd:dateTime
(i.e. with offsets and zulu), the time offsets are not permitted (Excel refused to load cache data) and zulu is ignored.
- Index
Value is a reference to the shared item. The index value is an index into the shared items array of the field.
- enum XLItemsToRetain
Specifies the number of unused items to allow in a IXLPivotCache before discarding unused items.
Values:
- Automatic
The threshold is set automatically based on the number of items.
Default behavior.
- None
When even one item is unused.
- Max
When all shared items of a filed are unused.
- enum XLPivotTableTheme
Values:
- None
- PivotStyleDark1
- PivotStyleDark10
- PivotStyleDark11
- PivotStyleDark12
- PivotStyleDark13
- PivotStyleDark14
- PivotStyleDark15
- PivotStyleDark16
- PivotStyleDark17
- PivotStyleDark18
- PivotStyleDark19
- PivotStyleDark2
- PivotStyleDark20
- PivotStyleDark21
- PivotStyleDark22
- PivotStyleDark23
- PivotStyleDark24
- PivotStyleDark25
- PivotStyleDark26
- PivotStyleDark27
- PivotStyleDark28
- PivotStyleDark3
- PivotStyleDark4
- PivotStyleDark5
- PivotStyleDark6
- PivotStyleDark7
- PivotStyleDark8
- PivotStyleDark9
- PivotStyleLight1
- PivotStyleLight10
- PivotStyleLight11
- PivotStyleLight12
- PivotStyleLight13
- PivotStyleLight14
- PivotStyleLight15
- PivotStyleLight16
- PivotStyleLight17
- PivotStyleLight18
- PivotStyleLight19
- PivotStyleLight2
- PivotStyleLight20
- PivotStyleLight21
- PivotStyleLight22
- PivotStyleLight23
- PivotStyleLight24
- PivotStyleLight25
- PivotStyleLight26
- PivotStyleLight27
- PivotStyleLight28
- PivotStyleLight3
- PivotStyleLight4
- PivotStyleLight5
- PivotStyleLight6
- PivotStyleLight7
- PivotStyleLight8
- PivotStyleLight9
- PivotStyleMedium1
- PivotStyleMedium10
- PivotStyleMedium11
- PivotStyleMedium12
- PivotStyleMedium13
- PivotStyleMedium14
- PivotStyleMedium15
- PivotStyleMedium16
- PivotStyleMedium17
- PivotStyleMedium18
- PivotStyleMedium19
- PivotStyleMedium2
- PivotStyleMedium20
- PivotStyleMedium21
- PivotStyleMedium22
- PivotStyleMedium23
- PivotStyleMedium24
- PivotStyleMedium25
- PivotStyleMedium26
- PivotStyleMedium27
- PivotStyleMedium28
- PivotStyleMedium3
- PivotStyleMedium4
- PivotStyleMedium5
- PivotStyleMedium6
- PivotStyleMedium7
- PivotStyleMedium8
- PivotStyleMedium9
- enum XLPivotTableSourceType
Values:
- Area
A range in a sheet of the workbook.
- Named
Book-scoped named range or a table.
- enum XLSheetProtectionElements
Values:
- None
- AutoFilter
- DeleteColumns
- DeleteRows
- EditObjects
- EditScenarios
- FormatCells
- FormatColumns
- FormatRows
- InsertColumns
- InsertHyperlinks
- InsertRows
- PivotTables
- SelectLockedCells
- SelectUnlockedCells
- Sort
- DeleteEverything
- FormatEverything
- InsertEverything
- SelectEverything
- Everything
- enum XLWorkbookProtectionElements
Values:
- None
- Structure
- Windows
The Windows option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac. Select the Windows option if you want to prevent users from moving, resizing, or closing the workbook window, or hide/unhide windows.
- Everything
- enum XLTransposeOptions
A behavior of extra outside cells for transpose operation. The option is meaningful only for transposition of non-squared ranges, because squared ranges can always be transposed without effecting outside cells.
Values:
- MoveCells
Shift cells of the smaller side to its direction so there is a space to transpose other side (e.g. if A1:C5 range is transposed, move D1:XFD5 are moved 2 columns to the right).
- ReplaceCells
Data of the cells are replaced by the transposed cells.
- enum XLSparklineMarkers
Values:
- None
- HighPoint
- LowPoint
- FirstPoint
- LastPoint
- NegativePoints
- Markers
- All
- enum XLThemeColor
Values:
- Background1
- Text1
- Background2
- Text2
- Accent1
- Accent2
- Accent3
- Accent4
- Accent5
- Accent6
- Hyperlink
- FollowedHyperlink
- enum XLAlignmentHorizontalValues
Values:
- Center
- CenterContinuous
- Distributed
- Fill
- General
- Justify
- Left
- Right
- 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 XLFontCharSet
Values:
- Ansi
ASCII character set.
- Default
System default character set.
- Symbol
Symbol character set.
- Mac
Characters used by Macintosh.
- ShiftJIS
Japanese character set.
- Hangul
Korean character set.
- Hangeul
Another common spelling of the Korean character set.
- Johab
Korean character set.
- GB2312
Chinese character set used in mainland China.
- ChineseBig5
Chinese character set used mostly in Hong Kong SAR and Taiwan.
- Greek
Greek character set.
- Turkish
Turkish character set.
- Vietnamese
Vietnamese character set.
- Hebrew
Hebrew character set.
- Arabic
Arabic character set.
- Baltic
Baltic character set.
- Russian
Russian character set.
- Thai
Thai character set.
- EastEurope
Eastern European character set.
- Oem
Extended ASCII character set used with disk operating system (DOS) and some Microsoft Windows fonts.
- enum XLFontScheme
A font theme scheme. Theme has categories of fonts and when the theme changes, texts that are associated with the particular theme scheme are switched to a font of a new theme.
Values:
- None
Not a part of theme scheme.
- Major
A major font of a theme, generally used for headings.
- Minor
A minor font of a theme, generally used to body and paragraphs.
- enum XLTotalsRowFunction
Values:
- None
- Sum
- Minimum
- Maximum
- Average
- Count
- CountNumbers
- StandardDeviation
- Variance
- Custom
- enum XLCellsUsedOptions
Values:
- None
- NoConstraints
- Contents
- DataType
- NormalFormats
- ConditionalFormats
- Comments
- DataValidation
- MergedRanges
- Sparklines
- AllFormats
- AllContents
- All
- 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.
- class ColorExtensions
- class DateTimeExtensions
- class ><TKey, TValue> DictionaryComparer : public IEqualityComparer< Dictionary< TKey, TValue>
- class DictionaryExtensions
- class DoubleExtensions
Public Static Functions
- Int32 RoundToInt (this Double value)
Round the number to the integer.
A helper method to avoid need to specify the midpoint rounding and casting each time.
- Double Round (this Double value, int digits)
Round the number to specified number of digits.
A helper method to avoid need to specify the midpoint rounding each time.
- class DoubleValueExtensions
- class EnumConverter
- class ><T> EnumerableComparer : public IEqualityComparer< IEnumerable< T>
- class EnumerableExtensions
- class FontBaseExtensions
- class IntegerExtensions
- interface ISheetListener
An interface for components reacting on changes in a worksheet.
Subclassed by XLCalcEngine
Public Functions
- void OnInsertAreaAndShiftDown (XLWorksheet sheet, XLSheetRange area)
A handler called after the area was put into the sheet and cells shifted down.
- Param sheet
Sheet where change happened.
- Param area
Area that has been inserted. The original cells were shifted down.
- void OnInsertAreaAndShiftRight (XLWorksheet sheet, XLSheetRange area)
A handler called after the area was put into the sheet and cells shifted right.
- Param sheet
Sheet where change happened.
- Param area
Area that has been inserted. The original cells were shifted right.
- void OnDeleteAreaAndShiftLeft (XLWorksheet sheet, XLSheetRange deletedRange)
A handler called after the area was deleted from the sheet and cells shifted left.
- Param sheet
Sheet where change happened.
- Param deletedRange
Range that has been deleted and cells to the right were shifted left.
- void OnDeleteAreaAndShiftUp (XLWorksheet sheet, XLSheetRange deletedRange)
A handler called after the area was deleted from the sheet and cells shifted up.
- Param sheet
Sheet where change happened.
- Param deletedRange
Range that has been deleted and cells below were shifted up.
- interface ISlice
An interface for methods of Slice<TElement> without specified type of an element.
Subclassed by FormulaSlice, Slice< TElement >, ValueSlice
Public Functions
- void Clear (XLSheetRange range)
Clear all values in the range and mark them as unused.
- void DeleteAreaAndShiftLeft (XLSheetRange rangeToDelete)
Clear all values in the rangeToDelete and shift all values right of the deleted area to the deleted place.
- void DeleteAreaAndShiftUp (XLSheetRange rangeToDelete)
Clear all values in the rangeToDelete and shift all values below the deleted area to the deleted place.
- IEnumerator<XLSheetPoint> GetEnumerator (XLSheetRange range, bool reverse = false)
Get all used points in a slice.
- Param range
Range to iterate over.
- Param reverse
false
= left to right, top to bottom.true
= right to left, bottom to top.
- void InsertAreaAndShiftDown (XLSheetRange range)
Shift all values at the range and all cells below it down by XLSheetRange.Height of the range . The insert area is cleared.
- void InsertAreaAndShiftRight (XLSheetRange range)
Shift all values at the range and all cells right of it to the right by XLSheetRange.Width of the range . The insert area is cleared.
- bool IsUsed (XLSheetPoint address)
Does slice contains a non-default value at specified point?
- void Swap (XLSheetPoint sp1, XLSheetPoint sp2)
Swap content of two points.
Properties
- bool IsEmpty { get; set; }
Is at least one cell in the slice used?
- int MaxColumn { get; set; }
Get maximum used column in the slice or 0, if no column is used.
- int MaxRow { get; set; }
Get maximum used row in the slice or 0, if no row is used.
- int >.KeyCollection UsedColumns { get; set; }
A set of columns that have at least one used cell. Order of columns is non-deterministic.
- interface IXLAddress : public IEqualityComparer<IXLAddress>, public IEquatable<IXLAddress>
Reference to a single cell in a workbook. Reference can be absolute, relative or mixed. Reference can be with or without a worksheet.
Subclassed by XLAddress
Properties
- IXLWorksheet Worksheet { get; set; }
Worksheet of the reference. Value is null for address without a worksheet.
- interface IXLAddressable
A very lightweight interface for entities that have an address as a rectangular range.
Subclassed by IXLRangeBase
Properties
- IXLRangeAddress RangeAddress { get; set; }
Gets an object with the boundaries of this range.
- interface IXLAlignment : public IEquatable<IXLAlignment>
Subclassed by XLAlignment
Properties
- XLAlignmentHorizontalValues Horizontal { get; set; }
Gets or sets the cell’s horizontal alignment.
- XLAlignmentVerticalValues Vertical { get; set; }
Gets or sets the cell’s vertical alignment.
- Int32 Indent { get; set; }
Gets or sets the cell’s text indentation.
- Boolean JustifyLastLine { get; set; }
Gets or sets whether the cell’s last line is justified or not.
- XLAlignmentReadingOrderValues ReadingOrder { get; set; }
Gets or sets the cell’s reading order.
- Int32 RelativeIndent { get; set; }
Gets or sets the cell’s relative indent.
- Boolean ShrinkToFit { get; set; }
Gets or sets whether the cell’s font size should decrease to fit the contents.
- Int32 TextRotation { get; set; }
Gets or sets the cell’s text rotation in degrees. Allowed values are -90 (text is rotated clockwise) to 90 (text is rotated counterclockwise) and 255 for vertical layout of a text.
- Boolean WrapText { get; set; }
Gets or sets whether the cell’s text should wrap if it doesn’t fit.
- Boolean TopToBottom { get; set; }
Gets or sets whether the cell’s text should be displayed from to to bottom
(as opposed to the normal left to right).
- interface IXLAutoFilter
Subclassed by XLAutoFilter
- interface IXLBaseCollection<TSingle, TMultiple> : public IEnumerable<TSingle>
Public Functions
- TMultiple AddToNamed (String rangeName)
Creates a named range out of these ranges.
If the named range exists, it will add these ranges to that named range.
The default scope for the named range is Workbook.
- Param rangeName
Name of the range.
- TMultiple AddToNamed (String rangeName, XLScope scope)
Creates a named range out of these ranges.
If the named range exists, it will add these ranges to that named range.
- Param rangeName
Name of the range.
- Param scope
The scope for the named range.
- TMultiple AddToNamed (String rangeName, XLScope scope, String comment)
Creates a named range out of these ranges.
If the named range exists, it will add these ranges to that named range.
- Param rangeName
Name of the range.
- Param scope
The scope for the named range.
- Param comment
The comments for the named range.
- IXLCells 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 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.
- IXLCell Clear (XLClearOptions clearOptions = XLClearOptions.All)
Clears the contents of this cell.
- Param clearOptions
Specify what you want to clear.
- IXLCell CopyFrom (IXLRangeBase rangeBase)
Copy range content to an area of same size starting at the cell. Original content of cells is overwritten.
- Param rangeBase
Range whose content to copy.
- Return
This cell.
- IXLComment CreateComment ()
Creates a new comment for the cell, replacing the existing one.
- IXLDataValidation CreateDataValidation ()
Creates a new data validation rule for the cell, replacing the existing one.
- XLHyperlink CreateHyperlink ()
Creates a new hyperlink replacing the existing one.
- IXLRichText CreateRichText ()
Replaces a value of the cell with a newly created rich text object.
- void Delete (XLShiftDeletedCells shiftDeleteCells)
Deletes the current cell and shifts the surrounding cells according to the shiftDeleteCells parameter.
- Param shiftDeleteCells
How to shift the surrounding cells.
- IXLComment GetComment ()
Returns the comment for the cell or create a new instance if there is no comment on the cell.
- IXLDataValidation GetDataValidation ()
Returns a data validation rule assigned to the cell, if any, or creates a new instance of data validation rule if no rule exists.
- Boolean GetBoolean ()
Gets the cell’s value as a Boolean.
Shortcut for
Value.GetBoolean()
- Throws InvalidCastException
If the value of the cell is not a logical.
- Double GetDouble ()
Gets the cell’s value as a Double.
Shortcut for
Value.GetNumber()
- Throws InvalidCastException
If the value of the cell is not a number.
- String GetText ()
Gets the cell’s value as a String.
Shortcut for
Value.GetText()
. Returned value is never null.- Throws InvalidCastException
If the value of the cell is not a text.
- XLError GetError ()
Gets the cell’s value as a XLError.
Shortcut for
Value.GetError()
- Throws InvalidCastException
If the value of the cell is not an error.
- DateTime GetDateTime ()
Gets the cell’s value as a DateTime.
Shortcut for
Value.GetDateTime()
- Throws InvalidCastException
If the value of the cell is not a DateTime.
- TimeSpan GetTimeSpan ()
Gets the cell’s value as a TimeSpan.
Shortcut for
Value.GetTimeSpan()
- Throws InvalidCastException
If the value of the cell is not a TimeSpan.
- Boolean TryGetValue<T> (out T value)
Try to get cell’s value converted to the T type.
Supported T types:
Boolean - uses a logic of XLCellValue.TryConvert(out Boolean)
Number (
s/byte
,u/short
,u/int
,u/long
,float
,double
, ordecimal
)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.
- XLHyperlink GetHyperlink ()
Returns a hyperlink for the cell, if any, or creates a new instance is there is no hyperlink.
- IXLRichText GetRichText ()
Returns the value of the cell if it formatted as a rich text.
- IXLRange InsertData (IEnumerable data)
Inserts the IEnumerable data elements and returns the range it occupies.
- Param data
The IEnumerable data.
- IXLRange InsertData (IEnumerable data, Boolean transpose)
Inserts the IEnumerable data elements and returns the range it occupies.
- Param data
The IEnumerable data.
- Param transpose
if set to
true
the data will be transposed before inserting.
- IXLRange InsertData (DataTable dataTable)
Inserts the data of a data table.
- Param dataTable
The data table.
- Return
The range occupied by the inserted data
- IXLTable InsertTable<T> (IEnumerable<T> data)
Inserts the IEnumerable data elements as a table and returns it.
The new table will receive a generic name: Table#
- Param data
The table data.
- IXLTable InsertTable<T> (IEnumerable<T> data, Boolean createTable)
Inserts the IEnumerable data elements as a table and returns it.
The new table will receive a generic name: Table#
if set to
false
the table will be created in memory.- Param data
The table data.
- Param createTable
if set to
true
it will create an Excel table.
- IXLTable InsertTable<T> (IEnumerable<T> data, String tableName)
Creates an Excel table from the given IEnumerable data elements.
- Param data
The table data.
- Param tableName
Name of the table.
- IXLTable InsertTable<T> (IEnumerable<T> data, String tableName, Boolean createTable)
Inserts the IEnumerable data elements as a table and returns it.
if set to
false
the table will be created in memory.- Param data
The table data.
- Param tableName
Name of the table.
- Param createTable
if set to
true
it will create an Excel table.
- IXLTable InsertTable (DataTable data)
Inserts the DataTable data elements as a table and returns it.
The new table will receive a generic name: Table#
- Param data
The table data.
- IXLTable InsertTable (DataTable data, Boolean createTable)
Inserts the DataTable data elements as a table and returns it.
The new table will receive a generic name: Table#
if set to
false
the table will be created in memory.- Param data
The table data.
- Param createTable
if set to
true
it will create an Excel table.
- IXLTable InsertTable (DataTable data, String tableName)
Creates an Excel table from the given DataTable data elements.
- Param data
The table data.
- Param tableName
Name of the table.
- IXLTable InsertTable (DataTable data, String tableName, Boolean createTable)
Inserts the DataTable data elements as a table and returns it.
if set to
false
the table will be created in memory.- Param data
The table data.
- Param tableName
Name of the table.
- Param createTable
if set to
true
it will create an Excel table.
- void InvalidateFormula ()
Invalidate CachedValue so the formula will be re-evaluated next time Value is accessed. If cell does not contain formula nothing happens.
- IXLCell SetValue (XLCellValue value)
- Return
This cell.
- string ToString (string format)
Returns a string that represents the current state of the cell according to the format.
- Param format
A: address, F: formula, NF: number format, BG: background color, FG: foreground color, V: formatted value
Properties
- Boolean Active { get; set; }
Is this cell the active cell of the worksheet? Setting false deactivates cell only when the cell is currently active.
- IXLAddress Address { get; set; }
Gets this cell’s address, relative to the worksheet.
The cell’s address.
- XLCellValue CachedValue { get; set; }
Get the value of a cell without evaluation of a formula. If the cell contains a formula, it returns the last calculated value or a blank value. If the cell doesn’t contain a formula, it returns same value as Value. May hold invalid value when NeedsRecalculation flag is True.
Can be useful to decrease a number of formula evaluations.
- IXLRange CurrentRegion { get; set; }
Returns the current region. The current region is a range bounded by any combination of blank rows and blank columns
The current region.
- XLDataType DataType { get; set; }
Gets the type of this cell’s data.
The type of the cell’s data.
- String FormulaA1 { get; set; }
Gets or sets the cell’s formula with A1 references.
The formula with A1 references.
- String FormulaR1C1 { get; set; }
Gets or sets the cell’s formula with R1C1 references.
The formula with R1C1 references.
- IXLRangeAddress FormulaReference { get; set; }
An indication that value of this cell is calculated by a array formula that calculates values for cells in the referenced address. Null if not part of such formula.
- Boolean NeedsRecalculation { get; set; }
Flag indicating that previously calculated cell value may be not valid anymore and has to be re-evaluated. Only cells with formula may return
true
, value cells always returnfalse
.
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.
- XLCellValue Value { get; set; }
Gets or sets the cell’s value.
Getter will return value of a cell or value of formula. Getter will evaluate a formula, if the cell NeedsRecalculation, before returning up-to-date value.
Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.
- Boolean ShowPhonetic { get; set; }
Should the cell show phonetic (i.e. furigana) above the rich text of the cell? It shows phonetic runs in the rich text, it is not autogenerated. Default is
false
.
- interface IXLCells : public IEnumerable<IXLCell>
Subclassed by XLCells
Public Functions
- IXLCells Clear (XLClearOptions clearOptions = XLClearOptions.All)
Clears the contents of these cells.
- Param clearOptions
Specify what you want to clear.
- void DeleteComments ()
Delete the comments of these cells.
- void DeleteSparklines ()
Delete the sparklines of these cells.
Properties
- XLCellValue Value { get; set; }
Sets the cells’ value.
Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.
- String FormulaA1 { get; set; }
Sets the cells’ formula with A1 references.
The formula with A1 references.
- String FormulaR1C1 { get; set; }
Sets the cells’ formula with R1C1 references.
The formula with R1C1 references.
- interface IXLCFColorScaleMax
Subclassed by XLCFColorScaleMax
- interface IXLCFColorScaleMid
Subclassed by XLCFColorScaleMid
- interface IXLCFColorScaleMin
Subclassed by XLCFColorScaleMin
- interface IXLCFConverter
Subclassed by XLCFCellIsConverter, XLCFColorScaleConverter, XLCFContainsConverter, XLCFDataBarConverter, XLCFDatesOccurringConverter, XLCFEndsWithConverter, XLCFIconSetConverter, XLCFIsBlankConverter, XLCFIsErrorConverter, XLCFNotBlankConverter, XLCFNotContainsConverter, XLCFNotErrorConverter, XLCFStartsWithConverter, XLCFTopConverter, XLCFUniqueConverter
- interface IXLCFConverterExtension
Subclassed by XLCFDataBarConverterExtension
- interface IXLCFDataBarMax
Subclassed by XLCFDataBarMax
- interface IXLCFDataBarMin
Subclassed by XLCFDataBarMin
- interface IXLCFIconSet
Subclassed by XLCFIconSet
- interface IXLChart : public IXLDrawing<IXLChart>
Subclassed by XLChart
- interface IXLCharts : public IEnumerable<IXLChart>
Subclassed by XLCharts
- interface IXLColumn : public IXLRangeBase
Subclassed by XLColumn
Public Functions
- void Delete ()
Deletes this column and shifts the columns at the right of this one accordingly.
Don’t use in a loop due to poor performance. Use IXLRange.Delete(XLShiftDeletedCells) instead.
- Int32 ColumnNumber ()
Gets this column’s number
- String ColumnLetter ()
Gets this column’s letter
- IXLColumns InsertColumnsAfter (Int32 numberOfColumns)
Inserts X number of columns at the right of this one.
All columns at the right will be shifted accordingly.
- Param numberOfColumns
The number of columns to insert.
- IXLColumns InsertColumnsBefore (Int32 numberOfColumns)
Inserts X number of columns at the left of this one.
This column and all at the right will be shifted accordingly.
- Param numberOfColumns
The number of columns to insert.
- 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 (Int32 startRow)
Adjusts the width of the column based on its contents, starting from the startRow.
- Param startRow
The row to start calculating the column width.
- IXLColumn AdjustToContents (Int32 startRow, Int32 endRow)
Adjusts the width of the column based on its contents, starting from the startRow and ending at endRow.
- Param startRow
The row to start calculating the column width.
- Param endRow
The row to end calculating the column width.
- IXLColumn AdjustToContents (Int32 startRow, Int32 endRow, Double minWidth, Double maxWidth)
Adjust width of the column according to the content of the cells.
- Param startRow
Number of a first row whose content is considered.
- Param endRow
Number of a last row whose content is considered.
- Param minWidth
Minimum width of adjusted column, in NoC.
- Param maxWidth
Maximum width of adjusted column, in NoC.
- IXLColumn 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.
- new IXLColumn Clear (XLClearOptions clearOptions = XLClearOptions.All)
Clears the contents of this column.
- Param clearOptions
Specify what you want to clear.
Properties
- Double Width { get; set; }
Gets or sets the width of this column in number of characters (NoC).
NoC are a non-linear units displayed as a column width in Excel, next to pixels. NoC combined with default font of the workbook can express width of the column in pixels and other units.
- Boolean IsHidden { get; set; }
Gets a value indicating whether this column is hidden or not.
true
if this column is hidden; otherwise,false
.
- Int32 OutlineLevel { get; set; }
Gets or sets the outline level of this column.
The outline level of this column.
- interface IXLColumns : public IEnumerable<IXLColumn>
Subclassed by XLColumns
Public Functions
- void Delete ()
Deletes all columns and shifts the columns at the right of them accordingly.
- IXLColumns AdjustToContents ()
Adjusts the width of all columns based on its contents.
- IXLColumns AdjustToContents (Int32 startRow)
Adjusts the width of all columns based on its contents, starting from the startRow.
- Param startRow
The row to start calculating the column width.
- IXLColumns AdjustToContents (Int32 startRow, Int32 endRow)
Adjusts the width of all columns based on its contents, starting from the startRow and ending at endRow.
- Param startRow
The row to start calculating the column width.
- Param endRow
The row to end calculating the column width.
- void Hide ()
Hides all columns.
- void Unhide ()
Unhides all columns.
- void Group ()
Increments the outline level of all columns by 1.
- void Group (Boolean collapse)
Increments the outline level of all columns by 1.
- Param collapse
If set to
true
the columns will be shown collapsed.
- void Group (Int32 outlineLevel)
Sets outline level for all columns.
- Param outlineLevel
The outline level.
- void Group (Int32 outlineLevel, Boolean collapse)
Sets outline level for all columns.
- Param outlineLevel
The outline level.
- Param collapse
If set to
true
the columns will be shown collapsed.
- void Ungroup ()
Decrements the outline level of all columns by 1.
- void Ungroup (Boolean fromAll)
Decrements the outline level of all columns by 1.
- Param fromAll
If set to
true
it will remove the columns from all outline levels.
- void Collapse ()
Show all columns as collapsed.
- void Expand ()
Expands all columns (if they’re collapsed).
- IXLCells 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
- 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.
- interface IXLDataValidations : public IEnumerable<IXLDataValidation>
Subclassed by XLDataValidations
Public Functions
- IXLDataValidation Add (IXLDataValidation dataValidation)
Add data validation rule to the collection. If the specified rule refers to another worksheet than the collection, the copy will be created and its ranges will refer to the worksheet of the collection. Otherwise the original instance will be placed in the collection.
- Param dataValidation
A data validation rule to add.
- Return
The instance that has actually been added in the collection (may be a copy of the specified one).
- IEnumerable<IXLDataValidation> GetAllInRange (IXLRangeAddress rangeAddress)
Get all data validation rules applied to ranges that intersect the specified range.
- bool TryGet (IXLRangeAddress rangeAddress, out IXLDataValidation dataValidation)
Get the data validation rule for the range with the specified address if it exists.
- Param rangeAddress
A range address.
- Param dataValidation
Data validation rule which ranges collection includes the specified address. The specified range should be fully covered with the data validation rule. For example, if the rule is applied to ranges A1:A3,C1:C3 then this method will return True for ranges A1:A3, C1:C2, A2:A3, and False for ranges A1:C3, A1:C1, etc.
- Return
True is the data validation rule was found, false otherwise.
- interface 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 IXLElementProtection, public ICloneable
Public Functions
- IXLElementProtection<T> AllowElement (T element, Boolean allowed = true)
Adds the specified element to the list of allowed elements. Beware that if you pass through “None”, this will have no effect.
- Param element
The element to add
- Param allowed
Set to
true
to allow the element orfalse
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 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
Properties
- XLFontCharSet FontCharSet { get; set; }
Defines an expected character set used by the text of this
font
. It helps Excel to choose a font face, either because requested one isn’t present or is unsuitable. Each font file contains a list of charsets it is capable of rendering and this property is used to detect whether the charset of a text matches the rendering capabilities of a font face and is thus suitable.Example: The
FontCharSet
isXLFontCharSet.Default
, but the selected font name is B Mitra that contains only arabic alphabet and declares so in its file. Excel will detect this discrepancy and choose a different font to display the text. The outcome is that text is not displayed with the B Mitra font, but with a different one and user doesn’t see persian numbers. To use the B Mitra font, this property must be set toXLFontCharSet.Arabic
that would match the font declared capabilities.Due to prevalence of unicode fonts, this property is rarely used.
- XLFontScheme FontScheme { get; set; }
Determines a theme font scheme a text belongs to. If the text belongs to a scheme and user changes theme in Excel, the font of the text will switch to the new theme font. Scheme font has precedence and will be used instead of a set font.
- interface IXLFormattedText<T> : public IEnumerable<IXLRichString>, public IEquatable<IXLFormattedText<T>>, public IXLWithRichString
Subclassed by XLFormattedText< T >
Public Functions
- IXLFormattedText<T> CopyFrom (IXLFormattedText<T> original)
Replace the text and formatting of this text by texts and formatting from the original text.
- Param original
Original to copy from.
- Return
This text.
Properties
- Int32 Count { get; set; }
How many rich strings is the formatted text composed of.
- Int32 Length { get; set; }
Length of the whole formatted text.
- String Text { get; set; }
Get text of the whole formatted text.
- Boolean HasPhonetics { get; set; }
Does this text has phonetics? Unlike accessing the Phonetics property, this method doesn’t create a new instance on access.
- IXLPhonetics Phonetics { get; set; }
Get or create phonetics for the text. Use HasPhonetics to check for existence to avoid unnecessary creation.
Subclassed by XLHeaderFooter
Public Functions
Gets the text of the specified header/footer occurrence.
- Param occurrence
The occurrence.
Properties
Gets the left header/footer item.
Gets the middle header/footer item.
Gets the right header/footer item.
- interface IXLHFItem : public IXLWithRichString
Subclassed by XLHFItem
Public Functions
- String GetText (XLHFOccurrence occurrence)
Gets the text of the specified header/footer occurrence.
- Param occurrence
The occurrence.
- IXLRichString AddText (XLHFPredefinedText predefinedText)
Adds the given predefined text to this header/footer item.
- Param predefinedText
The predefined text to add to this header/footer item.
- IXLRichString AddText (String text, XLHFOccurrence occurrence)
Adds the given text to this header/footer item.
- Param text
The text to add to this header/footer item.
- Param occurrence
The occurrence for the text.
- IXLRichString AddText (XLHFPredefinedText predefinedText, XLHFOccurrence occurrence)
Adds the given predefined text to this header/footer item.
- Param predefinedText
The predefined text to add to this header/footer item.
- Param occurrence
The occurrence for the predefined text.
- void Clear (XLHFOccurrence occurrence = XLHFOccurrence.AllPages)
Clears the text/formats of this header/footer item.
- Param occurrence
The occurrence to clear.
- interface IXLHyperlinks : public IEnumerable<XLHyperlink>
Subclassed by XLHyperlinks
- interface IXLMargins
Subclassed by XLMargins
Properties
- Double Left { get; set; }
Gets or sets the Left margin.
The Left margin.
- Double Right { get; set; }
Gets or sets the Right margin.
The Right margin.
- Double Top { get; set; }
Gets or sets the Top margin.
The Top margin.
- Double Bottom { get; set; }
Gets or sets the Bottom margin.
The Bottom margin.
- Double Header { get; set; }
Gets or sets the Header margin.
The Header margin.
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.
- String RefersTo { get; set; }
A formula of the named range. In most cases, name is just a range (e.g.
Sheet5!$A$4
), but it can be a constant, lambda or other values. The name formula can contain a bang reference (e.g. reference without a sheet, but with exclamation mark!$A$5
), but can’t contain plain local cell references (i.e. references without a sheet likeA5
).
- 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 IEnumerable<IXLNamedRange>
Subclassed by XLNamedRanges
Public Functions
- IXLNamedRange NamedRange (String rangeName)
Gets the specified named range.
- Param rangeName
Name of the range.
- Throws ArgumentException
Range wasn’t found.
- 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.
- Throws ArgumentException
The name or address is invalid.
- 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.
- Throws ArgumentException
The name is invalid.
- 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.
- Throws ArgumentException
The name is invalid.
- 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.
- Throws ArgumentException
The range name or address is invalid.
- 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.
- Throws ArgumentException
The range name is invalid.
- 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.
- Throws ArgumentException
The range name is invalid.
- 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.
- Throws ArgumentOutOfRangeException
The index is outside of named ranges array.
- 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 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.
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.
- XLPrintErrorValues PrintErrorValue { get; set; }
Gets or sets how error values will be printed.
- interface IXLPhonetic : public IEquatable<IXLPhonetic>
Subclassed by XLPhonetic
- interface IXLPhonetics : public IXLFontBase, public IEnumerable<IXLPhonetic>, public IEquatable<IXLPhonetics>
Subclassed by XLPhonetics
Public Functions
- IXLPhonetics Add (String text, Int32 start, Int32 end)
Add a phonetic run above a base text. Phonetic runs can’t overlap.
- Param text
Text to display above a section of a base text. Can’t be empty.
- Param start
Index of a first character of a base text above which should text be displayed. Valid values are
0
..length-1
.- Param end
The excluded ending index in a base text (the hint is not displayed above the
end
). Must be > start . Valid values are1
..length
.
- IXLPhonetics ClearText ()
Remove all phonetic runs. Keeps font properties.
- IXLPhonetics ClearFont ()
Reset font properties to the default font of a container (likely
IXLCell
). Keeps phonetic runs, Type and Alignment.
Properties
- Int32 Count { get; set; }
Number of phonetic runs above the base text.
- interface IXLPivotCache
A cache of pivot data - essentially a collection of fields and their values that can be displayed by a IXLPivotTable. Data for the cache are retrieved from an area (a table or a range). The pivot cache data are cached, i.e. the data in the source are not immediately updated once the data in a worksheet change.
Subclassed by XLPivotCache
Public Functions
- IXLPivotCache Refresh ()
Refresh data in the pivot source from the source reference data.
- Throws InvalidReferenceException
The data source for the pivot table can’t be found.
- IXLPivotCache SetRefreshDataOnOpen ()
Sets the value to
true
.
- IXLPivotCache SetSaveSourceData ()
Sets the value to
true
.
Properties
- String > FieldNames { get; set; }
Get names of all fields in the source, in left to right order. Every field name is unique.
The field names are case insensitive. The field names of the cached source might differ from actual names of the columns in the data cells.
- XLItemsToRetain ItemsToRetainPerField { get; set; }
Gets the number of unused items in shared items to allow before discarding unused items.
Shared items are distinct values of a source field values. Updating them can be expensive and this controls, when should the cache be updated. Application-dependent attribute.
Default value is XLItemsToRetain.Automatic.
- Boolean RefreshDataOnOpen { get; set; }
Will Excel refresh the cache when it opens the workbook.
Default value is
false
.
- Boolean SaveSourceData { get; set; }
Should the cached values of the pivot source be saved into the workbook file? If source data are not saved, they will have to be refreshed from the source reference which might cause a change in the table values.
Default value is
true
.
- interface IXLPivotCaches : public IEnumerable<IXLPivotCache>
A collection of pivot caches. Pivot cache can be added from a IXLRange or a IXLTable.
Subclassed by XLPivotCaches
Public Functions
- IXLPivotCache Add (IXLRange range)
Add a new pivot cache for the range. If the range area is same as an area of a table, the created cache will reference the table as source of data instead of a range of cells.
- Param range
Range for which to create the pivot cache.
- Return
The pivot cache for the range.
- interface IXLPivotField
Subclassed by XLPivotField
- interface IXLPivotFields : public IEnumerable<IXLPivotField>
Subclassed by XLPivotFields
- interface IXLPivotFieldStyleFormats
Subclassed by XLPivotFieldStyleFormats
- interface IXLPivotStyleFormat
Subclassed by IXLPivotValueStyleFormat, XLPivotStyleFormat
- interface IXLPivotStyleFormats : public IEnumerable<IXLPivotStyleFormat>
Subclassed by XLPivotStyleFormats
- interface IXLPivotTable
Subclassed by XLPivotTable
Properties
- IXLPivotCache PivotCache { get; set; }
The cache of data for the pivot table. The pivot table is created from cached data, not up-to-date data in a worksheet.
- interface IXLPivotTables : public IEnumerable<IXLPivotTable>
Subclassed by XLPivotTables
Public Functions
- IXLPivotTable Add (String name, IXLCell targetCell, IXLPivotCache pivotCache)
Add a pivot table that will use the pivot cache.
- Param name
Name of new pivot table.
- Param targetCell
A cell where will the pivot table be have it’s left top corner.
- Param pivotCache
Pivot cache to use for the pivot table.
- Throws ArgumentException
There already is a pivot table with the same name.
- Return
Added pivot table.
- IXLPivotTable Add (String name, IXLCell targetCell, IXLRange range)
Add a pivot table from source data of range . If workbook already contains a cache for same range as the range , the matching pivot cache is used.
- Param name
Name of new pivot table
- Param targetCell
A cell where will the pivot table be have it’s left top corner.
- Param range
A range to add/find pivot cache.
- Throws ArgumentException
There already is a pivot table with the same name.
- IXLPivotTable Add (String name, IXLCell targetCell, IXLTable table)
Add a pivot table from source data of table . If workbook already contains a cache for same range as the table , the matching pivot cache is used.
- Param name
Name of new pivot table
- Param targetCell
A cell where will the pivot table be have it’s left top corner.
- Param table
A table to add/find pivot cache.
- Throws ArgumentException
There already is a pivot table with the same name.
- IXLPivotTable PivotTable (String name)
Get pivot table with the specified name (case insensitive).
- Param name
Name of a pivot table to return.
- Throws KeyNotFoundException
No such pivot table found.
- interface IXLPivotTableStyleFormats
Subclassed by XLPivotTableStyleFormats
- interface IXLPivotValue
A pivot value field, it is basically a specification of how to determine and format values from source to display in the pivot table.
Subclassed by XLPivotValue
Properties
- String BaseFieldName { get; set; }
Specifies the index to the base field when the ShowDataAs calculation is in use. Instead of base item, previous or next value can be used through CalculationItem
Used only if the value should be showed Show Values As in the value field settings.
The name of the column of the relevant base field.
Show values as a percent of a specific value of a different field, e.g. as a % of units sold from Q1 (quarts is a base field and Q1 is a base item).
- XLCellValue BaseItemValue { get; set; }
The value of a base item to calculate a value to show in the pivot table. The base item is selected from values of a base field.
Used only if the value should be showed Show Values As in the value field settings.
The value of the referenced base field item.
Show values as a percent of a specific value of a different field, e.g. as a % of units sold from Q1 (quarts is a base field and Q1 is a base item).
- interface IXLPivotValueCombination
An interface for fluent configuration of how to show IXLPivotValue, when the value should be displayed not as a value itself, but in relation to another value (e.g. percentage difference in relation to different value).
Subclassed by XLPivotValueCombination
Public Functions
- IXLPivotValue AndPrevious ()
The base item value for calculation will be the value of the previous row of base field, depending on the order of base field values in a row/column. If there isn’t a previous value, the same value will be used.
This only affects display how are values displayed, not the values themselves.
Example: We have a table of sales and a pivot table, where sales are summed per month. The months are sorted from Jan to Dec. To display a percentage increase of sales per month (the base value is previous month):
IXLPivotValue sales; sales.SetSummaryFormula(XLPivotSummary.Sum).ShowAsPercentageDifferenceFrom("Month").AndPrevious();
- interface IXLPivotValueFormat : public IXLNumberFormatBase, public IEquatable<IXLNumberFormatBase>
Subclassed by XLPivotValueFormat
- interface IXLPivotValues : public IEnumerable<IXLPivotValue>
Subclassed by XLPivotValues
- interface IXLPivotValueStyleFormat : public IXLPivotStyleFormat
Subclassed by XLPivotValueStyleFormat
Public Functions
- IXLPivotValueStyleFormat AndWith (IXLPivotField field, Predicate<XLCellValue> predicate)
Adds a further limitation so the IXLPivotStyleFormat.Style is only applied to cells in a pivot table that are are within the field that has some values.
The pivot style is bound by the field index in a pivot table, not field value. E.g. if field values are Jan, Feb and the predicate marks Feb (offset 1) = second field (Feb) will be highlighted. If user later reverses order in Excel to Feb, Jan, the style would still apply to the second value - Jan.
- Param field
Only cells in a pivot table under this field will be styled.
- Param predicate
A predicate to determine which index of the field should be styled.
- interface IXLPrintAreas : public IEnumerable<IXLRange>
Subclassed by XLPrintAreas
Public Functions
- void Clear ()
Removes the print areas from the worksheet.
- void Add (Int32 firstCellRow, Int32 firstCellColumn, Int32 lastCellRow, Int32 lastCellColumn)
Adds a range to the print areas.
- Param firstCellRow
The first cell row.
- Param firstCellColumn
The first cell column.
- Param lastCellRow
The last cell row.
- Param lastCellColumn
The last cell column.
- void Add (String rangeAddress)
Adds a range to the print areas.
- Param rangeAddress
The range address to add.
- void Add (String firstCellAddress, String lastCellAddress)
Adds a range to the print areas.
- Param firstCellAddress
The first cell address.
- Param lastCellAddress
The last cell address.
- void Add (IXLAddress firstCellAddress, IXLAddress lastCellAddress)
Adds a range to the print areas.
- Param firstCellAddress
The first cell address.
- Param lastCellAddress
The last cell address.
- interface IXLProtectable<TProtection, in TElement> : public IXLProtectable
Public Functions
- TProtection Protect (TElement allowedElements)
Protects this instance without a password.
- new TProtection Protect (Algorithm algorithm = DefaultProtectionAlgorithm)
Protects this instance without a password.
- TProtection Protect (Algorithm algorithm, TElement allowedElements)
Protects this instance with the specified password, password hash algorithm and set elements that the user is allowed to change.
- Param algorithm
The algorithm.
- Param allowedElements
The allowed elements.
- new TProtection Protect (String password, Algorithm algorithm = DefaultProtectionAlgorithm)
Protects this instance using the specified password and password hash algorithm.
- Param password
The password.
- Param algorithm
The algorithm.
- TProtection Protect (String password, Algorithm algorithm, TElement allowedElements)
Protects this instance with the specified password, password hash algorithm and set elements that the user is allowed to change.
- Param password
The password.
- Param algorithm
The algorithm.
- Param allowedElements
The allowed elements.
- new TProtection Unprotect ()
Unprotects this instance without a password.
- new TProtection Unprotect (String password)
Unprotects this instance using the specified password.
- Param password
The password.
- IXLElementProtection Protect (Algorithm algorithm = DefaultProtectionAlgorithm)
Protects this instance without a password.
- IXLElementProtection Protect (String password, Algorithm algorithm = DefaultProtectionAlgorithm)
Protects this instance using the specified password and password hash algorithm.
- Param password
The password.
- Param algorithm
The algorithm.
- IXLElementProtection Unprotect ()
Unprotects this instance without a password.
- IXLElementProtection Unprotect (String password)
Unprotects this instance using the specified password.
- Param password
The password.
Properties
- Boolean IsPasswordProtected { get; set; }
Gets a value indicating whether this instance is protected with a password.
true
if this instance is password protected; otherwise,false
.
- Boolean IsProtected { get; set; }
Gets a value indicating whether this instance is protected, either with or without a password.
true
if this instance is protected; otherwise,false
.
- interface IXLProtection : public IEquatable<IXLProtection>
Subclassed by XLProtection
- interface IXLRange : public IXLRangeBase
Subclassed by IXLTable, IXLTableRange, XLRange
Public Functions
- IXLCell Cell (int row, int column)
Gets the cell at the specified row and column.
The cell address is relative to the parent range.
- Param row
The cell’s row.
- Param column
The cell’s column.
- IXLCell Cell (string cellAddressInRange)
Gets the cell at the specified address.
The cell address is relative to the parent range.
- Param cellAddressInRange
The cell address in the parent range.
- IXLCell Cell (int row, string column)
Gets the cell at the specified row and column.
The cell address is relative to the parent range.
- Param row
The cell’s row.
- Param column
The cell’s column.
- IXLCell Cell (IXLAddress cellAddressInRange)
Gets the cell at the specified address.
The cell address is relative to the parent range.
- Param cellAddressInRange
The cell address in the parent range.
- IXLRangeColumn Column (int columnNumber)
Gets the specified column of the range.
- Param columnNumber
1-based column number relative to the first column of this range.
- Return
The relevant column
- IXLRangeColumn Column (string columnLetter)
Gets the specified column of the range.
- Param columnLetter
Column letter.
- IXLRangeColumn FirstColumn (Func<IXLRangeColumn, Boolean> predicate = null)
Gets the first column of the range.
- IXLRangeColumn FirstColumnUsed (XLCellsUsedOptions options, Func<IXLRangeColumn, Boolean> predicate = null)
Gets the first non-empty column of the range that contains a cell with a value.
- Param options
The options to determine whether a cell is used.
- Param predicate
The predicate to choose cells.
- IXLRangeColumn LastColumn (Func<IXLRangeColumn, Boolean> predicate = null)
Gets the last column of the range.
- IXLRangeColumn LastColumnUsed (XLCellsUsedOptions options, Func<IXLRangeColumn, Boolean> predicate = null)
Gets the last non-empty column of the range that contains a cell with a value.
- Param options
The options to determine whether a cell is used.
- Param predicate
The predicate to choose cells.
- IXLRangeColumns Columns (Func<IXLRangeColumn, Boolean> predicate = null)
Gets a collection of all columns in this range.
- IXLRangeColumns Columns (int firstColumn, int lastColumn)
Gets a collection of the specified columns in this range.
- Param firstColumn
The first column to return. 1-based column number relative to the first column of this range.
- Param lastColumn
The last column to return. 1-based column number relative to the first column of this range.
- IXLRangeColumns Columns (string firstColumn, string lastColumn)
Gets a collection of the specified columns in this range.
- Param firstColumn
The first column to return.
- Param lastColumn
The last column to return.
- Return
The relevant columns
- IXLRangeColumns Columns (string columns)
Gets a collection of the specified columns in this range, separated by commas.
e.g. Columns(“G:H”), Columns(“10:11,13:14”), Columns(“P:Q,S:T”), Columns(“V”)
- Param columns
The columns to return.
- IXLRangeColumn FindColumn (Func<IXLRangeColumn, Boolean> predicate)
Returns the first row that matches the given predicate
- IXLRangeRow FindRow (Func<IXLRangeRow, Boolean> predicate)
Returns the first row that matches the given predicate
- IXLRangeRow FirstRow (Func<IXLRangeRow, Boolean> predicate = null)
Gets the first row of the range.
- IXLRangeRow FirstRowUsed (XLCellsUsedOptions options, Func<IXLRangeRow, Boolean> predicate = null)
Gets the first non-empty row of the range that contains a cell with a value.
- Param options
The options to determine whether a cell is used.
- Param predicate
The predicate to choose cells.
- IXLRangeRow LastRow (Func<IXLRangeRow, Boolean> predicate = null)
Gets the last row of the range.
- IXLRangeRow LastRowUsed (XLCellsUsedOptions options, Func<IXLRangeRow, Boolean> predicate = null)
Gets the last non-empty row of the range that contains a cell with a value.
- Param options
The options to determine whether a cell is used.
- Param predicate
The predicate to choose cells.
- IXLRangeRow Row (int row)
Gets the specified row of the range.
- Param row
1-based row number relative to the first row of this range.
- Return
The relevant row
- IXLRangeRows Rows (int firstRow, int lastRow)
Gets a collection of the specified rows in this range.
- Param firstRow
The first row to return. 1-based row number relative to the first row of this range.
- Param lastRow
The last row to return. 1-based row number relative to the first row of this range.
- IXLRangeRows Rows (string rows)
Gets a collection of the specified rows in this range, separated by commas.
e.g. Rows(“4:5”), Rows(“7:8,10:11”), Rows(“13”)
- Param rows
The rows to return.
- IXLRange Range (IXLRangeAddress rangeAddress)
Returns the specified range.
- Param rangeAddress
The range boundaries.
- IXLRange Range (string rangeAddress)
Returns the specified range.
e.g. Range(“A1”), Range(“A1:C2”)
- Param rangeAddress
The range boundaries.
- IXLRange Range (IXLCell firstCell, IXLCell lastCell)
Returns the specified range.
- Param firstCell
The first cell in the range.
- Param lastCell
The last cell in the range.
- IXLRange Range (string firstCellAddress, string lastCellAddress)
Returns the specified range.
- Param firstCellAddress
The first cell address in the range.
- Param lastCellAddress
The last cell address in the range.
- IXLRange Range (IXLAddress firstCellAddress, IXLAddress lastCellAddress)
Returns the specified range.
- Param firstCellAddress
The first cell address in the range.
- Param lastCellAddress
The last cell address in the range.
- IXLRanges Ranges (string ranges)
Returns a collection of ranges, separated by commas.
e.g. Ranges(“A1”), Ranges(“A1:C2”), Ranges(“A1:B2,D1:D4”)
- Param ranges
The ranges to return.
- IXLRange Range (int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn)
Returns the specified range.
- Param firstCellRow
The first cell’s row of the range to return.
- Param firstCellColumn
The first cell’s column of the range to return.
- Param lastCellRow
The last cell’s row of the range to return.
- Param lastCellColumn
The last cell’s column of the range to return.
- Return
.
- int RowCount ()
Gets the number of rows in this range.
- int ColumnCount ()
Gets the number of columns in this range.
- IXLRangeColumns InsertColumnsAfter (int numberOfColumns)
Inserts X number of columns to the right of this range.
All cells to the right of this range will be shifted X number of columns.
- Param numberOfColumns
Number of columns to insert.
- IXLRangeColumns InsertColumnsBefore (int numberOfColumns)
Inserts X number of columns to the left of this range.
This range and all cells to the right of this range will be shifted X number of columns.
- Param numberOfColumns
Number of columns to insert.
- IXLRangeRows InsertRowsAbove (int numberOfRows)
Inserts X number of rows on top of this range.
This range and all cells below this range will be shifted X number of rows.
- Param numberOfRows
Number of rows to insert.
- IXLRangeRows InsertRowsBelow (int numberOfRows)
Inserts X number of rows below this range.
All cells below this range will be shifted X number of rows.
- Param numberOfRows
Number of rows to insert.
- void Delete (XLShiftDeletedCells shiftDeleteCells)
Deletes this range and shifts the surrounding cells accordingly.
- Param shiftDeleteCells
How to shift the surrounding cells.
- void Transpose (XLTransposeOptions transposeOption)
Transposes the contents and styles of all cells in this range.
- Param transposeOption
How to handle the surrounding cells when transposing the range.
- IXLTable AsTable ()
Use this range as a table, but do not add it to the Tables list
NOTES:
The AsTable method will use the first row of the range as a header row.
If this range contains only one row, then an empty data row will be inserted into the returned table.
- IXLTable AsTable (String name)
Use this range as a table with the passed name, but do not add it to the Tables list
NOTES:
The AsTable method will use the first row of the range as a header row.
If this range contains only one row, then an empty data row will be inserted into the returned table.- Param name
Table name to be used.
- 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
- Return
Range of the address or null, if the range is not a valid address.
- IXLRangeAddress Intersection (IXLRangeAddress otherRangeAddress)
Returns the intersection of this range address with another range address on the same worksheet.
- Param otherRangeAddress
The other range address.
- Return
The intersection’s range address
- Boolean IsEntireColumn ()
Determines whether range address spans the entire column.
- Return
true
if is entire column; otherwise,false
.
- Boolean IsEntireRow ()
Determines whether range address spans the entire row.
- Return
true
if is entire row; otherwise,false
.
- Boolean IsEntireSheet ()
Determines whether the range address spans the entire worksheet.
- Return
true
if is entire sheet; otherwise,false
.
- IXLRangeAddress Relative (IXLRangeAddress sourceRangeAddress, IXLRangeAddress targetRangeAddress)
Returns a range address so that its offset from the target base address is equal to the offset of the current range address to the source base address. For example, if the current range address is D4:E4, the source base address is A1:C3, then the relative address to the target base address B10:D13 is E14:F14
- Param sourceRangeAddress
The source base range address.
- Param targetRangeAddress
The target base range address.
- Return
The relative range
Properties
- int ColumnSpan { get; set; }
Gets the number of columns in the area covered by the range address.
- IXLAddress FirstAddress { get; set; }
Gets or sets the first address in the range.
The first address.
- Boolean IsValid { get; set; }
Gets or sets a value indicating whether this range is valid.
true
if this instance is valid; otherwise,false
.
- IXLAddress LastAddress { get; set; }
Gets or sets the last address in the range.
The last address.
- int NumberOfCells { get; set; }
Gets the number of cells in the area covered by the range address.
- int RowSpan { get; set; }
Gets the number of rows in the area covered by the range address.
- interface IXLRangeBase : public IXLAddressable
Subclassed by IXLColumn, IXLRange, IXLRangeColumn, IXLRangeRow, IXLRow, IXLWorksheet, XLRangeBase
Public Functions
- IXLCells 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 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 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 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.
- Boolean IsEntireColumn ()
Determines whether range address spans the entire column.
- Return
true
if is entire column; otherwise,false
.
- Boolean IsEntireRow ()
Determines whether range address spans the entire row.
- Return
true
if is entire row; otherwise,false
.
- Boolean IsEntireSheet ()
Determines whether the range address spans the entire worksheet.
- Return
true
if is entire sheet; otherwise,false
.
- IXLDataValidation GetDataValidation ()
Returns a data validation rule assigned to the range, if any, or creates a new instance of data validation rule if no rule exists.
- IXLDataValidation CreateDataValidation ()
Creates a new data validation rule for the range, replacing the existing one.
- IXLRangeBase Grow ()
Grows this the current range by one cell to each side
- IXLRangeBase Grow (Int32 growCount)
Grows this the current range by the specified number of cells to each side.
- Param growCount
The grow count.
- IXLRangeBase Shrink ()
Shrinks this current range by one cell.
- IXLRangeBase Shrink (Int32 shrinkCount)
Shrinks the current range by the specified number of cells from each side.
- Param shrinkCount
The shrink count.
- IXLRangeAddress Intersection (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)
Returns the intersection of this range with another range on the same worksheet.
- Param otherRange
The other range.
- Param thisRangePredicate
Predicate applied to this range’s cells.
- Param otherRangePredicate
Predicate applied to the other range’s cells.
- Return
The range address of the intersection
- IXLCells SurroundingCells (Func<IXLCell, Boolean> predicate = null)
Returns the set of cells surrounding the current range.
- Param predicate
The predicate to apply on the resulting set of cells.
- IXLCells Union (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)
Calculates the union of two ranges on the same worksheet.
- Param otherRange
The other range.
- Param thisRangePredicate
Predicate applied to this range’s cells.
- Param otherRangePredicate
Predicate applied to the other range’s cells.
- Return
The union
- IXLCells Difference (IXLRangeBase otherRange, Func<IXLCell, Boolean> thisRangePredicate = null, Func<IXLCell, Boolean> otherRangePredicate = null)
Returns all cells in the current range that are not in the other range.
- Param otherRange
The other range.
- Param thisRangePredicate
Predicate applied to this range’s cells.
- Param otherRangePredicate
Predicate applied to the other range’s cells.
- IXLRangeBase Relative (IXLRangeBase sourceBaseRange, IXLRangeBase targetBaseRange)
Returns a range so that its offset from the target base range is equal to the offset of the current range to the source base range. For example, if the current range is D4:E4, the source base range is A1:C3, then the relative range to the target base range B10:D13 is E14:F14
- Param sourceBaseRange
The source base range.
- Param targetBaseRange
The target base range.
- Return
The relative range
Properties
- XLCellValue Value { get; set; }
Sets a value to every cell in this range.
Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.
- String FormulaA1 { get; set; }
Sets the cells’ formula with A1 references.
The formula with A1 references.
- String FormulaArrayA1 { get; set; }
Create an array formula for all cells in the range.
- Throws InvalidOperationException
When the range overlaps with a table, pivot table, merged cells or partially overlaps another array formula.
- String FormulaR1C1 { get; set; }
Sets the cells’ formula with R1C1 references.
The formula with R1C1 references.
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
- new IXLCells Cells (String cellsInColumn)
Returns the specified group of cells, separated by commas.
e.g. Cells(“1”), Cells(“1:5”), Cells(“1:2,4:5”)
- Param cellsInColumn
The column cells to return.
- IXLCells Cells (Int32 firstRow, Int32 lastRow)
Returns the specified group of cells.
- Param firstRow
The first row in the group of cells to return.
- Param lastRow
The last row in the group of cells to return.
- IXLRangeColumns InsertColumnsAfter (int numberOfColumns)
Inserts X number of columns to the right of this range.
All cells to the right of this range will be shifted X number of columns.
- Param numberOfColumns
Number of columns to insert.
- IXLRangeColumns InsertColumnsBefore (int numberOfColumns)
Inserts X number of columns to the left of this range.
This range and all cells to the right of this range will be shifted X number of columns.
- Param numberOfColumns
Number of columns to insert.
- IXLCells InsertCellsAbove (int numberOfRows)
Inserts X number of cells on top of this column.
This column and all cells below it will be shifted X number of rows.
- Param numberOfRows
Number of cells to insert.
- IXLCells InsertCellsBelow (int numberOfRows)
Inserts X number of cells below this range.
All cells below this column will be shifted X number of rows.
- Param numberOfRows
Number of cells to insert.
- void Delete ()
Deletes this range and shifts the cells at the right.
- void Delete (XLShiftDeletedCells shiftDeleteCells)
Deletes this range and shifts the surrounding cells accordingly.
- Param shiftDeleteCells
How to shift the surrounding cells.
- Int32 ColumnNumber ()
Gets this column’s number in the range
- String ColumnLetter ()
Gets this column’s letter in the range
- new IXLRangeColumn Clear (XLClearOptions clearOptions = XLClearOptions.All)
Clears the contents of this column.
- Param clearOptions
Specify what you want to clear.
- interface IXLRangeColumns : public IEnumerable<IXLRangeColumn>
Subclassed by XLRangeColumns
Public Functions
- void Add (IXLRangeColumn columRange)
Adds a column range to this group.
- Param columRange
The column range to add.
- IXLCells 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 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 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.
Properties
- XLCellValue Value { get; set; }
Sets the cells’ value.
Setter will clear a formula, if the cell contains a formula. If the value is a text that starts with a single quote, setter will prefix the value with a single quote through IXLStyle.IncludeQuotePrefix in Excel too and the value of cell is set to to non-quoted text.
- interface IXLRichString : public IXLFontBase, public IEquatable<IXLRichString>, public IXLWithRichString
Subclassed by XLRichString
- interface IXLRichText : public IXLFormattedText<IXLRichText>
Subclassed by XLRichText
- interface IXLRow : public IXLRangeBase
Subclassed by XLRow
Public Functions
- void ClearHeight ()
Clears the height for the row and defaults it to the spreadsheet row height.
- void Delete ()
Deletes this row and shifts the rows below this one accordingly.
Don’t use in a loop due to poor performance. Use IXLRange.Delete(XLShiftDeletedCells) instead.
- Int32 RowNumber ()
Gets this row’s number
- IXLRows InsertRowsBelow (Int32 numberOfRows)
Inserts X number of rows below this one.
All rows below will be shifted accordingly.
- Param numberOfRows
The number of rows to insert.
- IXLRows InsertRowsAbove (Int32 numberOfRows)
Inserts X number of rows above this one.
This row and all below will be shifted accordingly.
- Param numberOfRows
The number of rows to insert.
- IXLRow AdjustToContents (Int32 startColumn)
Adjusts the height of the row based on its contents, starting from the startColumn.
- Param startColumn
The column to start calculating the row height.
- IXLRow AdjustToContents (Int32 startColumn, Int32 endColumn)
Adjusts the height of the row based on its contents, starting from the startColumn and ending at endColumn.
- Param startColumn
The column to start calculating the row height.
- Param endColumn
The column to end calculating the row height.
- IXLRow AdjustToContents (Int32 startColumn, Int32 endColumn, Double minHeightPt, Double maxHeightPt)
Adjust height of the column according to the content of the cells.
- Param startColumn
Number of a first column whose content is considered.
- Param endColumn
Number of a last column whose content is considered.
- Param minHeightPt
Minimum height of adjusted column, in points.
- Param maxHeightPt
Maximum height of adjusted column, in points.
- IXLRow 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.
- 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.
- 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 (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 CellsUsed (XLCellsUsedOptions options)
Returns the collection of cells that have a value.
- Param options
The options to determine whether a cell is used.
- 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
Public Functions
- IXLSparklineGroup CopyTo (IXLWorksheet targetSheet)
Copy this sparkline group to the specified worksheet
- Param targetSheet
The worksheet to copy this sparkline group to
- interface IXLSparklineGroups : public IEnumerable<IXLSparklineGroup>
Subclassed by XLSparklineGroups
- interface IXLSparklineHorizontalAxis
Subclassed by XLSparklineHorizontalAxis
- interface IXLSparklineStyle
Subclassed by XLSparklineStyle
- interface IXLSparklineVerticalAxis
Subclassed by XLSparklineVerticalAxis
- interface IXLStyle : public IEquatable<IXLStyle>
Subclassed by XLStyle
Properties
- Boolean IncludeQuotePrefix { get; set; }
Should the text values of a cell saved to the file be prefixed by a quote (
'
) character? Has no effect if cell values is not a XLDataType.Text. Doesn’t affect values during runtime, text values are returned without quote.
- interface IXLStylized
Subclassed by XLCell, XLCells, XLColumns, XLConditionalFormat, XLRangeBase, XLRangeColumns, XLRangeRows, XLRanges, XLRows, XLStylizedBase, XLStylizedEmpty, XLTableRows
Properties
- XLStyleValue StyleValue { get; set; }
Immutable style
- interface IXLTable : public IXLRange
Subclassed by XLTable
Public Functions
- new IXLTable Clear (XLClearOptions clearOptions = XLClearOptions.All)
Clears the contents of this table.
- Param clearOptions
Specify what you want to clear.
- IXLRange AppendData (IEnumerable data, Boolean propagateExtraColumns = false)
Appends the IEnumerable data elements and returns the range of the new rows.
- Param data
The IEnumerable data.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The range of the new rows.
- IXLRange AppendData (IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false)
Appends the IEnumerable data elements and returns the range of the new rows.
- Param data
The IEnumerable data.
- Param transpose
if set to
true
the data will be transposed before inserting.- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The range of the new rows.
- IXLRange AppendData (DataTable dataTable, Boolean propagateExtraColumns = false)
Appends the data of a data table and returns the range of the new rows.
- Param dataTable
The data table.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The range of the new rows.
- IXLRange AppendData<T> (IEnumerable<T> data, Boolean propagateExtraColumns = false)
Appends the IEnumerable data elements and returns the range of the new rows.
- Tparam T
- Param data
The table data.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The range of the new rows.
- IXLRange ReplaceData (IEnumerable data, Boolean propagateExtraColumns = false)
Replaces the IEnumerable data elements and returns the table’s data range.
- Param data
The IEnumerable data.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The table’s data range.
- IXLRange ReplaceData (IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false)
Replaces the IEnumerable data elements and returns the table’s data range.
- Param data
The IEnumerable data.
- Param transpose
if set to
true
the data will be transposed before inserting.- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The table’s data range.
- IXLRange ReplaceData (DataTable dataTable, Boolean propagateExtraColumns = false)
Replaces the data from the records of a data table and returns the table’s data range.
- Param dataTable
The data table.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The table’s data range.
- IXLRange ReplaceData<T> (IEnumerable<T> data, Boolean propagateExtraColumns = false)
Replaces the IEnumerable data elements as a table and the table’s data range.
- Tparam T
- Param data
The table data.
- Param propagateExtraColumns
if set to
true
propagate extra columns’ values and formulas.- Return
The table’s data range.
- IXLTable Resize (IXLRange range)
Resizes the table to the specified range address.
- Param range
The new table range.
- IXLTable Resize (IXLRangeAddress rangeAddress)
Resizes the table to the specified range address.
- Param rangeAddress
The range boundaries.
- IXLTable Resize (string rangeAddress)
Resizes the table to the specified range address.
- Param rangeAddress
The range boundaries.
- IXLTable Resize (IXLCell firstCell, IXLCell lastCell)
Resizes the table to the specified range.
- Param firstCell
The first cell in the range.
- Param lastCell
The last cell in the range.
- IXLTable Resize (string firstCellAddress, string lastCellAddress)
Resizes the table to the specified range.
- Param firstCellAddress
The first cell address in the worksheet.
- Param lastCellAddress
The last cell address in the worksheet.
- IXLTable Resize (IXLAddress firstCellAddress, IXLAddress lastCellAddress)
Resizes the table to the specified range.
- Param firstCellAddress
The first cell address in the worksheet.
- Param lastCellAddress
The last cell address in the worksheet.
- IXLTable Resize (int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn)
Resizes the table to the specified range.
- Param firstCellRow
The first cell’s row of the range to return.
- Param firstCellColumn
The first cell’s column of the range to return.
- Param lastCellRow
The last cell’s row of the range to return.
- Param lastCellColumn
The last cell’s column of the range to return.
- IEnumerable<dynamic> AsDynamicEnumerable ()
Converts the table to an enumerable of dynamic objects
- DataTable AsNativeDataTable ()
Converts the table to a standard .NET System.Data.DataTable
Properties
- string Name { get; set; }
Change the name of a table. Structural references to the table are not updated.
- Throws ArgumentException
If the new table name is already used by other table in the sheet.
- interface IXLTableField
Subclassed by XLTableField