Cell

interface ClosedXML.Excel.IXLCell

Subclassed by XLCell

Public Functions

IXLCell AddToNamed (String rangeName)

Creates a named range out of this cell.

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

The default scope for the named range is Workbook.

Param rangeName

Name of the range.

IXLCell AddToNamed (String rangeName, XLScope scope)

Creates a named range out of this cell.

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

Param rangeName

Name of the range.

Param scope

The scope for the named range.

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

Creates a named range out of this cell.

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

Param rangeName

Name of the range.

Param scope

The scope for the named range.

Param comment

The comments for the named range.

IXLRange AsRange ()

Returns this cell as an IXLRange.

IXLCell Clear (XLClearOptions clearOptions = XLClearOptions.All)

Clears the contents of this cell.

Param clearOptions

Specify what you want to clear.

IXLCell CopyFrom (IXLRangeBase rangeBase)

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

Param rangeBase

Range whose content to copy.

Return

This cell.

IXLComment CreateComment ()

Creates a new comment for the cell, replacing the existing one.

IXLDataValidation CreateDataValidation ()

Creates a new data validation rule for the cell, replacing the existing one.

Creates a new hyperlink replacing the existing one.

IXLRichText CreateRichText ()

Replaces a value of the cell with a newly created rich text object.

void Delete (XLShiftDeletedCells shiftDeleteCells)

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

Param shiftDeleteCells

How to shift the surrounding cells.

IXLComment GetComment ()

Returns the comment for the cell or create a new instance if there is no comment on the cell.

IXLDataValidation GetDataValidation ()

Returns a data validation rule assigned to the cell, if any, or creates a new instance of data validation rule if no rule exists.

Boolean GetBoolean ()

Gets the cell’s value as a Boolean.

Shortcut for Value.GetBoolean()

Throws InvalidCastException

If the value of the cell is not a logical.

Double GetDouble ()

Gets the cell’s value as a Boolean.

Shortcut for Value.GetNumber()

Throws InvalidCastException

If the value of the cell is not a number.

String GetText ()

Gets the cell’s value as a String.

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

Throws InvalidCastException

If the value of the cell is not a text.

XLError GetError ()

Gets the cell’s value as a XLError.

Shortcut for Value.GetError()

Throws InvalidCastException

If the value of the cell is not an error.

DateTime GetDateTime ()

Gets the cell’s value as a DateTime.

Shortcut for Value.GetDateTime()

Throws InvalidCastException

If the value of the cell is not a DateTime.

TimeSpan GetTimeSpan ()

Gets the cell’s value as a TimeSpan.

Shortcut for Value.GetTimeSpan()

Throws InvalidCastException

If the value of the cell is not a TimeSpan.

Boolean TryGetValue<T> (out T value)

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

Supported T types:

  • Boolean - uses a logic of XLCellValue.TryConvert(out Boolean)

  • Number (s/byte, u/short, u/int, u/long, float, double, or decimal)

    • uses a logic of XLCellValue.TryConvert(out Double, System.Globalization.CultureInfo) and succeeds, if the value fits into the target type.

  • String - sets the result to a text representation of a cell value (using current culture).

  • DateTime - uses a logic of XLCellValue.TryConvert(out DateTime)

  • TimeSpan - uses a logic of XLCellValue.TryConvert(out TimeSpan, System.Globalization.CultureInfo)

  • XLError - if the value is of type XLDataType.Error, it will return the value.

  • Enum - tries to parse a value to a member by comparing the text of a cell value and a member name.

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

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

Tparam T

The requested type into which will the value be converted.

Param value

Value to store the value.

Return

true if the value was converted and the result is in the value , false otherwise.

T GetValue<T> ()

Conversion logic is identical with TryGetValue<T>.

Tparam T

The requested type into which will the value be converted.

Throws InvalidCastException

If the value can’t be converted to the type of T

String GetString ()

Return cell’s value represented as a string. Doesn’t use cell’s formatting or style.

String GetFormattedString ()

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

Returns a hyperlink for the cell, if any, or creates a new instance is there is no hyperlink.

IXLRichText GetRichText ()

Returns the value of the cell if it formatted as a rich text.

IXLRange InsertData (IEnumerable data)

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

Param data

The IEnumerable data.

IXLRange InsertData (IEnumerable data, Boolean transpose)

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

Param data

The IEnumerable data.

Param transpose

if set to true the data will be transposed before inserting.

IXLRange InsertData (DataTable dataTable)

Inserts the data of a data table.

Param dataTable

The data table.

Return

The range occupied by the inserted data

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

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

The new table will receive a generic name: Table#

Param data

The table data.

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

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

The new table will receive a generic name: Table#

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

Param data

The table data.

Param createTable

if set to true it will create an Excel table.

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

Creates an Excel table from the given IEnumerable data elements.

Param data

The table data.

Param tableName

Name of the table.

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

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

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

Param data

The table data.

Param tableName

Name of the table.

Param createTable

if set to true it will create an Excel table.

IXLTable InsertTable (DataTable data)

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

The new table will receive a generic name: Table#

Param data

The table data.

IXLTable InsertTable (DataTable data, Boolean createTable)

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

The new table will receive a generic name: Table#

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

Param data

The table data.

Param createTable

if set to true it will create an Excel table.

IXLTable InsertTable (DataTable data, String tableName)

Creates an Excel table from the given DataTable data elements.

Param data

The table data.

Param tableName

Name of the table.

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

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

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

Param data

The table data.

Param tableName

Name of the table.

Param createTable

if set to true it will create an Excel table.

void InvalidateFormula ()

Invalidate CachedValue so the formula will be re-evaluated next time Value is accessed. If cell does not contain formula nothing happens.

IXLCell SetValue (XLCellValue value)
Return

This cell.

string ToString (string format)

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

Param format

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

Properties

IXLAddress Address { get; set; }

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

The cell’s address.

XLCellValue CachedValue { get; set; }

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

Can be useful to decrease a number of formula evaluations.

IXLRange CurrentRegion { get; set; }

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

The current region.

XLDataType DataType { get; set; }

Gets the type of this cell’s data.

The type of the cell’s data.

String FormulaA1 { get; set; }

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

The formula with A1 references.

String FormulaR1C1 { get; set; }

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

The formula with R1C1 references.

Boolean NeedsRecalculation { get; set; }

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

Boolean ShareString { get; set; }

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

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

IXLStyle Style { get; set; }

Gets or sets the cell’s style.

XLCellValue Value { get; set; }

Gets or sets the cell’s value.

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

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