ExcelCell Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

Excel cell provides access to a single worksheet cell or to a merged range if the cell is merged.

public sealed class ExcelCell : AbstractRange
Public NotInheritable Class ExcelCell
    Inherits AbstractRange
Inheritance:
System.Object
ExcelCell
Remarks

Merged range is created by using Merged property. See the property documentation for more information on merging.

Properties

BoolValue

Gets the boolean value of this cell or of merged range if this cell is merged.

public bool BoolValue { get; }
Public ReadOnly Property BoolValue As Boolean
Property Value
System.Boolean

The boolean value.

Column

Gets column of the cell.

public ExcelColumn Column { get; }
Public ReadOnly Property Column As ExcelColumn
Property Value
ExcelColumn

Comment

Gets or sets cell comment.

public override ExcelComment Comment { get; set; }
Public Overrides Property Comment As ExcelComment
Property Value
ExcelComment
Overrides

DateTimeValue

Gets the DateTime value of this cell or of merged range if this cell is merged.

public DateTime DateTimeValue { get; }
Public ReadOnly Property DateTimeValue As Date
Property Value
System.DateTime

The DateTime value.

DoubleValue

Gets the double value of this cell or of merged range if this cell is merged.

public double DoubleValue { get; }
Public ReadOnly Property DoubleValue As Double
Property Value
System.Double

The double value.

Formula

Gets or sets cell formula string.

public override string Formula { get; set; }
Public Overrides Property Formula As String
Property Value
System.String
Overrides
Remarks

In XLSX files, reading and writing of all formulas are supported.

In XLS files, reading and writing of all common and most of the advanced formulas are supported.

In XLSB files, reading and writing of all common and most of the advanced formulas are supported.

In ODS files, reading and writing of all formulas are supported, however, OpenOffice formulas don't have the same syntax as Excel formulas.

In CSV files, reading of all formulas is supported.

When adding formulas in new versions of Excel, it often automatically interprets formulas as dynamic array formulas. You can consider using the SetDynamicArrayFormula(String) method to set the formula instead of using this property.

For more information on formulas, consult Microsoft Excel documentation.

FormulaR1C1

Gets or sets a formula string in R1C1 notation.

public override string FormulaR1C1 { get; set; }
Public Overrides Property FormulaR1C1 As String
Property Value
System.String
Overrides

FormulaRange

Gets the range of cells that are affected by the formula stored in this cell.

public CellRange FormulaRange { get; }
Public ReadOnly Property FormulaRange As CellRange
Property Value
CellRange
Remarks

This property is only relevant for array formulas and dynamic array formulas.

When the formula of this cell was set using the SetDynamicArrayFormula(String) method, this property returns correct range only after the cell was calculated.

FormulaType

Gets the type of formula stored in this cell.

public FormulaType FormulaType { get; }
Public ReadOnly Property FormulaType As FormulaType
Property Value
FormulaType

Gets or sets cell hyperlink.

public override SpreadsheetHyperlink Hyperlink { get; set; }
Public Overrides Property Hyperlink As SpreadsheetHyperlink
Property Value
SpreadsheetHyperlink
Overrides

IntValue

Gets the integer value of this cell or of merged range if this cell is merged.

public int IntValue { get; }
Public ReadOnly Property IntValue As Integer
Property Value
System.Int32

The integer value.

MergedRange

Returns associated merged range if the cell is merged; otherwise, null.

public CellRange MergedRange { get; }
Public ReadOnly Property MergedRange As CellRange
Property Value
CellRange
See Also

Row

Gets row of the cell.

public ExcelRow Row { get; }
Public ReadOnly Property Row As ExcelRow
Property Value
ExcelRow

StringValue

Gets the string value of this cell or of merged range if this cell is merged.

public string StringValue { get; }
Public ReadOnly Property StringValue As String
Property Value
System.String

The string value.

Value

Gets or sets value of this cell or of merged range if this cell is merged.

public override object Value { get; set; }
Public Overrides Property Value As Object
Property Value
System.Object
Overrides
Remarks

Exception is thrown if value for the set is not of supported type (See SupportsType(Type) for details).

Note that the fact some type is supported doesn't mean it is written to Excel file in the native format. As Microsoft Excel has just few basic types, the object of supported type will be converted to a similar excel type. If similar excel type doesn't exist, value is written as a string value.

If the value of this property is of System.DateTime type and Style number format is not set, ISO date/time format will be used as NumberFormat value.

Exceptions
System.NotSupportedException

Value is not supported.

See Also

ValueType

Gets the cell value type.

public CellValueType ValueType { get; }
Public ReadOnly Property ValueType As CellValueType
Property Value
CellValueType

Worksheet

Gets the parent worksheet.

public ExcelWorksheet Worksheet { get; }
Public ReadOnly Property Worksheet As ExcelWorksheet
Property Value
ExcelWorksheet

Methods

Calculate()

Calculates formula for this excel cell.

public override void Calculate()
Public Overrides Sub Calculate
Overrides

ConvertDateTimeToExcelNumber(DateTime, Boolean)

Converts System.DateTime structure to Excel floating-point number.

public static double ConvertDateTimeToExcelNumber(DateTime date, bool use1904DateSystem)
Public Shared Function ConvertDateTimeToExcelNumber(date As Date, use1904DateSystem As Boolean) As Double
Parameters
date
System.DateTime

DateTime structure.

use1904DateSystem
System.Boolean

True to use 1904 date system.

Returns
System.Double

Converted Excel floating-point number.

ConvertExcelNumberToDateTime(Double, Boolean)

Converts Excel floating-point number to System.DateTime structure.

public static DateTime ConvertExcelNumberToDateTime(double num, bool use1904DateSystem)
Public Shared Function ConvertExcelNumberToDateTime(num As Double, use1904DateSystem As Boolean) As Date
Parameters
num
System.Double

Excel floating-point number.

use1904DateSystem
System.Boolean

True to use 1904 date system.

Returns
System.DateTime

Converted DateTime structure.

Remarks

Excel file format doesn't have a separate data type for date and time. System.DateTime value is stored as IEEE number encoded in a special way. When reading Excel file, NumberFormat is examined and if it matches some of date/time number formats cell value is interpreted as System.DateTime.

However, if some non-standard date/time number format is used, cell value will not be recognized as System.DateTime but as ordinary number. In such cases (when you know that specific cell holds System.DateTime value but you get a number when reading Excel file) use this method to convert IEEE number to System.DateTime structure.

Equals(Object)

Determines whether the specified System.Object is equal to this ExcelCell instance.

public override bool Equals(object obj)
Public Overrides Function Equals(obj As Object) As Boolean
Parameters
obj
System.Object

The System.Object to compare with this ExcelCell instance.

Returns
System.Boolean

true if the specified System.Object is a ExcelCell and is equal to this ExcelCell instance; otherwise, false.

Overrides
System.Object.Equals(System.Object)

GetCurrentRegion()

Selects a worksheet region to which the cell belongs to.

public CellRange GetCurrentRegion()
Public Function GetCurrentRegion As CellRange
Returns
CellRange

The worksheet region to which the cell belongs to.

GetFormattedValue()

Converts Value of this ExcelCell instance to string representation using NumberFormat that is specified on this ExcelCell instance.

public string GetFormattedValue()
Public Function GetFormattedValue As String
Returns
System.String

Formatted Value

GetHashCode()

Returns a hash code for this ExcelCell instance.

public override int GetHashCode()
Public Overrides Function GetHashCode As Integer
Returns
System.Int32

An integer value that specifies a hash value for this ExcelCell instance.

Overrides
System.Object.GetHashCode()

Insert(InsertShiftDirection)

Inserts a cell by shifting cells in the specified direction.

public override void Insert(InsertShiftDirection direction)
Public Overrides Sub Insert(direction As InsertShiftDirection)
Parameters
direction
InsertShiftDirection

The shift direction.

Overrides

Remove(RemoveShiftDirection)

Removes a cell by shifting cells in the specified direction.

public override void Remove(RemoveShiftDirection direction)
Public Overrides Sub Remove(direction As RemoveShiftDirection)
Parameters
direction
RemoveShiftDirection

The shift direction.

Overrides

SetArrayFormula(String, Int32, Int32)

Sets the array formula (also known as CSE formula) to the range of cells.

public void SetArrayFormula(string formula, int rowCount, int columnCount)
Public Sub SetArrayFormula(formula As String, rowCount As Integer, columnCount As Integer)
Parameters
formula
System.String

The array formula.

rowCount
System.Int32

The number of rows that should be populated with the result of the array formula.

columnCount
System.Int32

The number of columns that should be populated with the result of the array formula.

See Also

SetDynamicArrayFormula(String)

Sets the dynamic array formula. Dynamic array formulas spill the result of the formula to neighbouring cells and are recommended as a replacement for array formulas in newer versions of MS Excel.

public void SetDynamicArrayFormula(string formula)
Public Sub SetDynamicArrayFormula(formula As String)
Parameters
formula
System.String

The dynamic array formula.

Remarks

Dynamic array formulas are fully supported only in Excel 365, Excel 2021, and newer versions. Dynamic array formulas opened in Excel 2019 or older will be shown as legacy array formulas (also known as CSE formulas).

See Also

SetValue(Boolean)

Sets the boolean value to this cell or to merged range if this cell is merged.

public void SetValue(bool value)
Public Sub SetValue(value As Boolean)
Parameters
value
System.Boolean

The boolean value.

SetValue(DateTime)

Sets the DateTime value to this cell or to merged range if this cell is merged.

public void SetValue(DateTime value)
Public Sub SetValue(value As Date)
Parameters
value
System.DateTime

The DateTime value.

SetValue(Double)

Sets the double value to this cell or to merged range if this cell is merged.

public void SetValue(double value)
Public Sub SetValue(value As Double)
Parameters
value
System.Double

The double value.

SetValue(Int32)

Sets the integer value to this cell or to merged range if this cell is merged.

public void SetValue(int value)
Public Sub SetValue(value As Integer)
Parameters
value
System.Int32

The integer value.

SetValue(String)

Sets the string value to this cell or to merged range if this cell is merged.

public void SetValue(string value)
Public Sub SetValue(value As String)
Parameters
value
System.String

The string value.

SetValue(String, HtmlLoadOptions)

Loads the specified HTML text as the value of this ExcelCell.

public void SetValue(string html, HtmlLoadOptions options)
Public Sub SetValue(html As String, options As HtmlLoadOptions)
Parameters
html
System.String

The HTML text which should be loaded as the value of this ExcelCell.

options
HtmlLoadOptions

The options used to define settings for load operation.

Operators

Equality(ExcelCell, ExcelCell)

Determines whether first and second ExcelCells are equal.

public static bool operator ==(ExcelCell first, ExcelCell second)
Public Shared Operator =(first As ExcelCell, second As ExcelCell) As Boolean
Parameters
first
ExcelCell

The first ExcelCell.

second
ExcelCell

The second ExcelCell.

Returns
System.Boolean

true if first and second ExcelCells are equal; otherwise, false.

Inequality(ExcelCell, ExcelCell)

Determines whether first and second ExcelCells are not equal.

public static bool operator !=(ExcelCell first, ExcelCell second)
Public Shared Operator <>(first As ExcelCell, second As ExcelCell) As Boolean
Parameters
first
ExcelCell

The first ExcelCell.

second
ExcelCell

The second ExcelCell.

Returns
System.Boolean

true if first and second ExcelCells are not equal; otherwise, false.

Inherited Properties

CharacterRanges

Returns all FormattedCharacterRange objects that represents a range of characters within the cell text.

(Inherited from AbstractRange)

Name

Gets the name of AbstractRange instance.

(Inherited from AbstractRange)

Style

Gets or sets the cell formatting of one or more cells.

(Inherited from AbstractRange)

Inherited Methods

Clear(ClearOptions)

Clears the range (content, formatting, comment, hyperlink or all).

(Inherited from AbstractRange)

GetCharacters(System.Int32)

Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position.

(Inherited from AbstractRange)

GetCharacters(System.Int32, System.Int32)

Returns a FormattedCharacterRange object that represents a range of characters within the cell text. The range starts at a specified character position and has a specified length.

(Inherited from AbstractRange)

ReplaceText(System.Text.RegularExpressions.Regex, System.String)

Replaces all matches of the specified System.Text.RegularExpressions.Regex with specified text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(System.String, System.String)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(System.String, System.String, System.Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ReplaceText(System.String, System.String, System.Boolean, System.Boolean)

Replaces all occurrences of the specified text with another text in the current AbstractRange.

(Inherited from AbstractRange)

ToString()

Returns a System.String that represents this AbstractRange instance.

(Inherited from AbstractRange)

Examples

See Also