Methods

Methods

calculate(options)

Calculates the formula of the cell.

Parameter

Name Type Optional Description

options

CalculationOptions

 

Options for calculation

characters(startIndex, length) → FontSetting

Returns a Characters object that represents a range of characters within the cell text. This method only works on cell with string value.

Example

excel.getWorksheets().get(0).getCells().get("A1").putValue("Helloworld");
excel.getWorksheets().get(0).getCells().get("A1").characters(5, 5).getFont().setBold(true);
excel.getWorksheets().get(0).getCells().get("A1").characters(5, 5).getFont().setColor(aspose.cells.Color.getBlue());

Parameters

Name Type Optional Description

startIndex

Number

 

The index of the start of the character.

length

Number

 

The number of characters.

Returns

FontSetting Characters object.

Indicates whether this cell contains an external link. Only applies when the cell is a formula cell.

copy(cell)

Copies data from a source cell.

Parameter

Name Type Optional Description

cell

Cell

 

Source

equals(obj) → boolean

Checks whether this object refers to the same cell with another.

Parameter

Name Type Optional Description

obj

Object

 

another object

Returns

boolean true if two objects refers to the same cell.

equals(cell) → boolean

Checks whether this object refers to the same cell with another cell object.

Parameter

Name Type Optional Description

cell

Cell

 

another cell object

Returns

boolean true if two cell objects refers to the same cell.

getArrayRange()

Gets the array range if the cell's formula is an array formula. Only applies when the cell's formula is an array formula@return {CellArea} The array range.

getBoolValue()

Gets the boolean value contained in the cell.

getCharacters() → Array of FontSetting

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

Returns

Array of FontSetting All Characters objects

getCharacters(flag) → Array of FontSetting

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

Parameter

Name Type Optional Description

flag

boolean

 

Indicates whether applying table style to the cell if the cell is in the table.

Returns

Array of FontSetting All Characters objects

getColumn()

Gets column number (zero based) of the cell.

getComment()

Gets the comment of this cell. If there is no comment applies to the cell, returns null.

getConditionalFormattingResult()

Get the result of the conditional formatting. Returns null if no conditional formatting is applied to this cell,

getDateTimeValue()

Gets the DateTime value contained in the cell.

getDependents(isAll)

Get all cells which refer to the specific cell.

Parameter

Name Type Optional Description

isAll

boolean

 

Indicates whether check other worksheets

getDisplayStringValue()

Gets the formatted string value of this cell by cell's display style.

getDisplayStyle()

Gets the display style of the cell. If this cell is also affected by other settings such as conditional formatting, list objects, etc., then the display style may be different from cell.GetStyle().

getDisplayStyle(includeMergedBorders)

Gets the display style of the cell. If the cell is conditional formatted, the display style is not same as the cell.GetStyle().

Parameter

Name Type Optional Description

includeMergedBorders

boolean

 

Indicates whether checking borders of the merged cells.

getDoubleValue()

Gets the double value contained in the cell.

getFloatValue()

Gets the float value contained in the cell.

getFormatConditions() → Array of FormatConditionCollection

Gets format conditions which applies to this cell.

Returns

Array of FormatConditionCollection Returns FormatConditionCollection object

getFormula()

Gets or sets a formula of the Cell. A formula string always begins with an equal sign (=). And please always use comma(,) as parameters delimiter, such as "=SUM(A1, E1, H2)".

Example

var excel = new aspose.cells.Workbook();
var cells = excel.getWorksheets().get(0).getCells();
cells.get("B6").setFormula("=SUM(B2:B5, E1) + sheet2!A1");

getFormula(isR1C1, isLocal) → String

Get the formula of this cell.

Parameters

Name Type Optional Description

isR1C1

boolean

 

Whether the formula needs to be formatted as R1C1.

isLocal

boolean

 

Whether the formula needs to be formatted by locale.

Returns

String the formula of this cell.

getFormulaLocal()

Get the locale formatted formula of the cell.

getHeightOfValue() → Number

Gets the height of the value in unit of pixels.

Returns

Number 

getHtmlString()

Gets and sets the html string which contains data and some formats in this cell.

getHtmlString(html5) → String

Gets the html string which contains data and some formats in this cell.

Parameter

Name Type Optional Description

html5

boolean

 

Indicates whether the value is compatible for html5

Returns

String 

getIntValue()

Gets the integer value contained in the cell.

getLeafs()

Get all cells which will be updated when this cell is modified. This method can only work after calling Workbook.CalculateFormula with WorkbookSettings.CreateCalcChain of value true.

getMergedRange() → Range

Returns a Range object which represents a merged range.

Returns

Range Range object. Null if this cell is not merged.

getName()

Gets the name of the cell. A cell name includes its column letter and row number. For example, the name of a cell in row 0 and column 0 is A1.

getNumberCategoryType()

Represents the category type of this cell's number formatting. The value of the property is NumberCategoryType integer constant.

getPrecedents()

Gets all cells or ranges which this cell's formula depends on. Returns null if this is not a formula cell.@return {ReferredAreaCollection} Returns all cells or ranges.

Example

var workbook = new aspose.cells.Workbook();
var cells = workbook.getWorksheets().get(0).getCells();
cells.get("A1").setFormula("= B1 + SUM(B1:B10) + [Book1.xls]Sheet1!A1");
var areas = cells.get("A1").getPrecedents();
for (var i = 0; i < areas.getCount(); i++)
{
var area = areas.get(i);
var stringBuilder = "";
if (area.isExternalLink())
{
stringBuilder += "[";
stringBuilder += area.getExternalFileName();
stringBuilder += "]";
}
stringBuilder += area.getSheetName();
stringBuilder += "!";
stringBuilder += aspose.cells.CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn());
if (area.isArea())
{
stringBuilder += ":";
stringBuilder += aspose.cells.CellsHelper.cellIndexToName(area.getEndRow(), area.getEndColumn());
}
console.log(stringBuilder);
}
workbook.save("Book2.xls");

getR1C1Formula()

Gets or sets a R1C1 formula of the Cell.

getRow()

Gets row number (zero based) of the cell. Cell row number

getSharedStyleIndex()

Gets cell's shared style index in the style pool.

getStringValue()

Gets the string value contained in the cell. If the type of this cell is string, then return the string value itself. For other cell types, the formatted string value (formatted with the specified style of this cell) will be returned. The formatted cell value is same with what you can get from excel when copying a cell as text(such as copying cell to text editor or exporting to csv).

getStringValue(formatStrategy) → String

Gets the string value by specific formatted strategy.

Parameter

Name Type Optional Description

formatStrategy

Number

 

CellValueFormatStrategy

Returns

String 

getStringValueWithoutFormat()

Gets cell's value as string without any format. NOTE: This method is now obsolete. Instead, User should get the value object and format it according to the value type and the specific requirement. This property will be removed 12 months later since December 2020. Aspose apologizes for any inconvenience you may have experienced.

getStyle() → Style

Gets the cell style. To change the style of the cell, please call Cell.SetStyle() method after changing the style.

Returns

Style Style object.

getStyle(checkBorders) → Style

If checkBorders is true, check whether other cells' borders will effect the style of this cell.

Parameter

Name Type Optional Description

checkBorders

boolean

 

Check other cells' borders

Returns

Style Style object.

getTable() → ListObject

Gets the table which contains this cell.

Returns

ListObject 

getType()

Represents cell value type. The value of the property is CellValueType integer constant.

getValidation() → Validation

Gets the validation applied to this cell.

Returns

Validation 

getValidationValue() → boolean

Gets the value of validation which applied to this cell.

Returns

boolean 

getValue()

Gets the value contained in this cell. Possible type: null,Boolean,DateTime,Double,IntegerString. For int value, it may be returned as an Integer object or a Double object. And there is no guarantee that the returned value will be kept as the same type of object always.

getWidthOfValue() → Number

Gets the width of the value in unit of pixels.

Returns

Number 

getWorksheet()

Gets the parent worksheet.

hashCode() → Number

Serves as a hash function for a particular type.

Returns

Number A hash code for current Cell object.

isArrayFormula()

Indicates whether the cell formula is an array formula.

isArrayHeader()

Indicates the cell's formula is and array formula and it is the first cell of the array.

isErrorValue()

Checks if a formula can properly evaluate a result. Also applies to formula cell to check the calculated result

isFormula()

Represents if the specified cell contains formula.

isInArray()

Indicates whether the cell formula is an array formula. NOTE: This class is now obsolete. Instead, please use Cell.IsArrayFormula to check whether the cell formula is an array formula. This property will be removed 12 months later since May 2018. Aspose apologizes for any inconvenience you may have experienced.

isInTable()

Indicates whether this cell is part of table formula. NOTE: This class is now obsolete. Instead, please use Cell.IsTableFormula to check whether the cell formula is part of table formula. This property will be removed 12 months later since May 2018. Aspose apologizes for any inconvenience you may have experienced.

isMerged()

Checks if a cell is part of a merged range or not.

isNumericValue()

Indicates whether the inner value of this cell is numeric(int, double and datetime) Also applies to formula cell to check the calculated result

isRichText()

Indicates whether the cell string value is a rich text.

isSharedFormula()

Indicates whether the cell formula is part of shared formula.

isStyleSet()

Indicates if the cell's style is set. If return false, it means this cell has a default cell format.

isTableFormula()

Indicates whether this cell is part of table formula.

putValue(boolValue)

Puts an boolean value into the cell.

Parameter

Name Type Optional Description

boolValue

boolean

 

putValue(intValue)

Puts an integer value into the cell.

Parameter

Name Type Optional Description

intValue

Number

 

Input value

putValue(doubleValue)

Puts a double value into the cell.

Parameter

Name Type Optional Description

doubleValue

Number

 

Input value

putValue(stringValue, isConverted, setStyle)

Puts a value into the cell, if appropriate the value will be converted to other data type and cell's number format will be reset.

Parameters

Name Type Optional Description

stringValue

String

 

Input value

isConverted

boolean

 

True: converted to other data type if appropriate.

setStyle

boolean

 

True: set the number format to cell's style when converting to other data type

putValue(stringValue, isConverted)

Puts a string value into the cell and converts the value to other data type if appropriate.

Parameters

Name Type Optional Description

stringValue

String

 

Input value

isConverted

boolean

 

True: converted to other data type if appropriate.

putValue(stringValue)

Puts a string value into the cell.

Parameter

Name Type Optional Description

stringValue

String

 

Input value

putValue(dateTime)

Puts a DateTime value into the cell.

Parameter

Name Type Optional Description

dateTime

DateTime

 

Input value

putValue(objectValue)

Puts an object value into the cell.

Parameter

Name Type Optional Description

objectValue

Object

 

input value

removeArrayFormula(leaveNormalFormula)

Remove array formula.

Parameter

Name Type Optional Description

leaveNormalFormula

boolean

 

True represents converting the array formula to normal formula.

setAddInFormula(addInFileName, addInFunction)

Sets an Add-In formula to the cell. Add-In file should be placed in the directory or sub-directory of Workbook Add-In library. For example, file name can be "Eurotool.xla" or "solver\solver.xla". NOTE: This class is now obsolete. Instead, please use Cell.Formula/Cell.SetFormula() to set cell formula with the Add-In functions after registering it by WorksheetCollection.RegisterAddInFunction(). This property will be removed 12 months later since January 2019. Aspose apologizes for any inconvenience you may have experienced.

Example

cells.get("h11").setAddInFormula("HRVSTTRK.xla", "=pct_overcut(F3:G3)");
cells.get("h12").setAddInFormula("HRVSTTRK.xla", "=pct_overcut()");

Parameters

Name Type Optional Description

addInFileName

String

 

Add-In file name.

addInFunction

String

 

Add-In function name.

setArrayFormula(arrayFormula, rowNumber, columnNumber, isR1C1, isLocal)

Sets an array formula to a range of cells. NOTE: This class is now obsolete. Instead, please use Cell.SetArrayFormula(string,int,int,FormulaParseOptions). This property will be removed 12 months later since December 2019. Aspose apologizes for any inconvenience you may have experienced.

Parameters

Name Type Optional Description

arrayFormula

String

 

Array formula.

rowNumber

Number

 

Number of rows to populate result of the array formula.

columnNumber

Number

 

Number of columns to populate result of the array formula.

isR1C1

boolean

 

whether the formula is R1C1 formula

isLocal

boolean

 

whether the formula is locale formatted

setArrayFormula(arrayFormula, rowNumber, columnNumber, options)

Sets an array formula to a range of cells.

Parameters

Name Type Optional Description

arrayFormula

String

 

Array formula.

rowNumber

Number

 

Number of rows to populate result of the array formula.

columnNumber

Number

 

Number of columns to populate result of the array formula.

options

FormulaParseOptions

 

Options for parsing the formula.

setArrayFormula(arrayFormula, rowNumber, columnNumber, options, values)

Sets an array formula to a range of cells.

Parameters

Name Type Optional Description

arrayFormula

String

 

Array formula.

rowNumber

Number

 

Number of rows to populate result of the array formula.

columnNumber

Number

 

Number of columns to populate result of the array formula.

options

FormulaParseOptions

 

Options for parsing the formula.

values

Array of Array of Object

 

values for those cells with given array formula

setArrayFormula(arrayFormula, rowNumber, columnNumber)

Sets an array formula(legacy array formula entered via CTRL+SHIFT+ENTER in ms excel) to a range of cells.

Parameters

Name Type Optional Description

arrayFormula

String

 

Array formula.

rowNumber

Number

 

Number of rows to populate result of the array formula.

columnNumber

Number

 

Number of columns to populate result of the array formula.

setCharacters(characters)

Sets rich text format of the cell.

Parameter

Name Type Optional Description

characters

Array of FontSetting

 

All Characters objects.

setDynamicArrayFormula(arrayFormula, options, calculateValue) → CellArea

Sets dynamic array formula and make the formula spill into neighboring cells if possible.

Parameters

Name Type Optional Description

arrayFormula

String

 

the formula expression

options

FormulaParseOptions

 

options to parse formula. "Parse" option will be ignored and the formula will always be parsed immediately

calculateValue

boolean

 

whether calculate this dynamic array formula for those cells in the spilled range.

Returns

CellArea the range that the formula should spill into.

setDynamicArrayFormula(arrayFormula, options, values, calculateRange, calculateValue) → CellArea

Sets dynamic array formula and make the formula spill into neighboring cells if possible.

Parameters

Name Type Optional Description

arrayFormula

String

 

the formula expression

options

FormulaParseOptions

 

options to parse formula. "Parse" option will be ignored and the formula will always be parsed immediately

values

Array of Array of Object

 

values for those cells with given dynamic array formula

calculateRange

boolean

 

Whether calculate the spilled range for this dynamic array formula. If the "values" parameter is not null and this flag is false, then the spilled range's height will be values.Length and width will be values[0].Length.

calculateValue

boolean

 

whether calculate this dynamic array formula for those cells in the spilled range when "values" is null or corresponding item in "values" for one cell is null.

Returns

CellArea the range that the formula should spill into.

setFormula()

Gets or sets a formula of the Cell. A formula string always begins with an equal sign (=). And please always use comma(,) as parameters delimiter, such as "=SUM(A1, E1, H2)".

Example

var excel = new aspose.cells.Workbook();
var cells = excel.getWorksheets().get(0).getCells();
cells.get("B6").setFormula("=SUM(B2:B5, E1) + sheet2!A1");

setFormula(formula, value)

Set the formula and the value of the formula.

Parameters

Name Type Optional Description

formula

String

 

The formula.

value

Object

 

The value of the formula.

setFormula(formula, isR1C1, isLocal, value)

Set the formula and the value of the formula. NOTE: This class is now obsolete. Instead, please use Cell.SetFormula(string,FormulaParseOptions,object). This property will be removed 12 months later since December 2019. Aspose apologizes for any inconvenience you may have experienced.

Parameters

Name Type Optional Description

formula

String

 

The formula.

isR1C1

boolean

 

Whether the formula is R1C1 formula.

isLocal

boolean

 

Whether the formula is locale formatted.

value

Object

 

The value of the formula.

setFormula(formula, options, value)

Set the formula and the value of the formula.

Parameters

Name Type Optional Description

formula

String

 

The formula.

options

FormulaParseOptions

 

Options for parsing the formula.

value

Object

 

The value of the formula.

setFormulaLocal()

Get the locale formatted formula of the cell.

setHtmlString()

Gets and sets the html string which contains data and some formats in this cell.

setR1C1Formula()

Gets or sets a R1C1 formula of the Cell.

setSharedFormula(sharedFormula, rowNumber, columnNumber, isR1C1, isLocal)

Sets a formula to a range of cells. NOTE: This class is now obsolete. Instead, please use Cell.SetSharedFormula(string,int,int,FormulaParseOptions). This property will be removed 12 months later since December 2019. Aspose apologizes for any inconvenience you may have experienced.

Parameters

Name Type Optional Description

sharedFormula

String

 

Shared formula.

rowNumber

Number

 

Number of rows to populate the formula.

columnNumber

Number

 

Number of columns to populate the formula.

isR1C1

boolean

 

whether the formula is R1C1 formula

isLocal

boolean

 

whether the formula is locale formatted

setSharedFormula(sharedFormula, rowNumber, columnNumber, options)

Sets a formula to a range of cells.

Parameters

Name Type Optional Description

sharedFormula

String

 

Shared formula.

rowNumber

Number

 

Number of rows to populate the formula.

columnNumber

Number

 

Number of columns to populate the formula.

options

FormulaParseOptions

 

Options for parsing the formula.

setSharedFormula(sharedFormula, rowNumber, columnNumber, options, values)

Sets a formula to a range of cells.

Parameters

Name Type Optional Description

sharedFormula

String

 

Shared formula.

rowNumber

Number

 

Number of rows to populate the formula.

columnNumber

Number

 

Number of columns to populate the formula.

options

FormulaParseOptions

 

Options for parsing the formula.

values

Array of Array of Object

 

values for those cells with given shared formula

setSharedFormula(sharedFormula, rowNumber, columnNumber)

Sets a formula to a range of cells.

Parameters

Name Type Optional Description

sharedFormula

String

 

Shared formula.

rowNumber

Number

 

Number of rows to populate the formula.

columnNumber

Number

 

Number of columns to populate the formula.

setStyle(style)

Sets the cell style. If the border settings are changed, the border of adjust cells will be updated too.

Parameter

Name Type Optional Description

style

Style

 

The cell style.

setStyle(style, explicitFlag)

Apply the cell style.

Parameters

Name Type Optional Description

style

Style

 

The cell style.

explicitFlag

boolean

 

True, only overwriting formatting which is explicitly set.

setStyle(style, flag)

Apply the cell style.

Parameters

Name Type Optional Description

style

Style

 

The cell style.

flag

StyleFlag

 

The style flag.

setValue()

Gets the value contained in this cell. Possible type: null,Boolean,DateTime,Double,IntegerString. For int value, it may be returned as an Integer object or a Double object. And there is no guarantee that the returned value will be kept as the same type of object always.

toString() → String

Returns a string represents the current Cell object.

Returns

String