Example:
var excel = new aspose.cells.Workbook(); var cells = excel.getWorksheets().get(0).getCells(); //Put a string into a cell var cell = cells.get(0, 0); cell.putValue("Hello"); var first = cell.getStringValue(); //Put an integer into a cell cell = cells.get("B1"); cell.putValue(12); var second = cell.getIntValue(); //Put a double into a cell cell = cells.get(0, 2); cell.putValue(-1.234); var third = cell.getDoubleValue(); //Put a formula into a cell cell = cells.get("D1"); cell.setFormula("=B1 + C1"); //Put a combined formula: "sum(average(b1,c1), b1)" to cell at b2 cell = cells.get("b2"); cell.setFormula("=sum(average(b1,c1), b1)"); //Set style of a cell var style = cell.getStyle(); //Set background color style.setBackgroundColor(aspose.cells.Color.getYellow()); //Set format of a cell style.getFont().setName("Courier New"); style.setVerticalAlignment(aspose.cells.TextAlignmentType.TOP); cell.setStyle(style);
Property Getters/Setters Summary | ||
---|---|---|
function | getBoolValue() | |
Gets the boolean value contained in the cell.
|
||
function | getColumn() | |
Gets column number (zero based) of the cell.
|
||
function | getComment() | |
Gets the comment of this cell.
|
||
function | containsExternalLink() | |
Indicates whether this cell contains an external link.
Only applies when the cell is a formula cell.
|
||
function | getDateTimeValue() | |
Gets the DateTime value contained in the cell.
|
||
function | getDisplayStringValue() | |
Gets the formatted string value of this cell.
|
||
function | getDoubleValue() | |
Gets the double value contained in the cell.
|
||
function | getFloatValue() | |
Gets the float value contained in the cell.
|
||
function | getFormula() | |
function | setFormula(value) | |
Gets or sets a formula of the |
||
function | getFormulaLocal() | |
function | setFormulaLocal(value) | |
Get the locale formatted formula of the cell. | ||
function | getHtmlString() | |
function | setHtmlString(value) | |
Gets and sets the html string which contains data and some formats in this cell. | ||
function | getIntValue() | |
Gets the integer value contained in the cell.
|
||
function | isArrayFormula() | |
Indicates whether the cell formula is an array formula.
|
||
function | isArrayHeader() | |
Indicates the cell's formula is and array formula
and it is the first cell of the array.
|
||
function | isErrorValue() | |
Checks if a formula can properly evaluate a result.
|
||
function | isFormula() | |
Represents if the specified cell contains formula.
|
||
function | isInArray() | |
Indicates whether the cell formula is an array formula.
|
||
function | isInTable() | |
Indicates whether this cell is part of table formula.
|
||
function | isMerged() | |
Checks if a cell is part of a merged range or not.
|
||
function | isSharedFormula() | |
Indicates whether the cell formula is part of shared formula.
|
||
function | isStyleSet() | |
Indicates if the cell's style is set. If return false, it means this cell has a default cell format.
|
||
function | isTableFormula() | |
Indicates whether this cell is part of table formula.
|
||
function | getName() | |
Gets the name of the cell.
|
||
function | getNumberCategoryType() | |
Represents the category type of this cell's number formatting.
The value of the property is NumberCategoryType integer constant. |
||
function | getR1C1Formula() | |
function | setR1C1Formula(value) | |
Gets or sets a R1C1 formula of the |
||
function | getRow() | |
Gets row number (zero based) of the cell.
|
||
function | getSharedStyleIndex() | |
Gets cell's shared style index in the style pool.
|
||
function | 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).
|
||
function | getStringValueWithoutFormat() | |
Gets cell's value as string without any format.
|
||
function | getType() | |
Represents cell value type.
The value of the property is CellValueType integer constant. |
||
function | getValue() | |
function | setValue(value) | |
Gets the value contained in this cell. | ||
function | getWorksheet() | |
Gets the parent worksheet.
|
Method Summary | ||
---|---|---|
function | calculate(options) | |
Calculates the formula of the cell.
|
||
function | characters(startIndex, length) | |
Returns a Characters object that represents a range of characters within the cell text.
|
||
function | copy(cell) | |
Copies data from a source cell.
|
||
function | equals(cell) | |
Checks whether this object refers to the same cell with another cell object.
|
||
function | equals(obj) | |
Checks whether this object refers to the same cell with another.
|
||
function | getArrayRange() | |
Gets the array range if the cell's formula is an array formula.
|
||
function | getCharacters() | |
Returns all Characters objects
that represents a range of characters within the cell text.
|
||
function | getCharacters(flag) | |
Returns all Characters objects
that represents a range of characters within the cell text.
|
||
function | getConditionalFormattingResult() | |
Get the result of the conditional formatting.
|
||
function | getDependents(isAll) | |
Get all cells which refer to the specific cell.
|
||
function | getDisplayStyle() | |
Gets the display style of the cell.
If the cell is conditional formatted, the display style is not same as the cell.GetStyle().
|
||
function | 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().
|
||
function | getFormatConditions() | |
Gets format conditions which applies to this cell.
|
||
function | getFormula(isR1C1, isLocal) | |
Get the formula of this cell.
|
||
function | getHeightOfValue() | |
Gets the height of the value in unit of pixels.
|
||
function | getHtmlString(html5) | |
Gets the html string which contains data and some formats in this cell.
|
||
function | 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.
|
||
function | getMergedRange() | |
Returns a |
||
function | getPrecedents() | |
Gets all cells or ranges which this cell's formula depends on.
|
||
function | getStringValue(formatStrategy) | |
Gets the string value by specific formatted strategy.
|
||
function | getStyle() | |
Gets the cell style.
|
||
function | getStyle(checkBorders) | |
If checkBorders is true, check whether other cells' borders will effect the style of this cell.
|
||
function | getTable() | |
Gets the table which contains this cell.
|
||
function | getValidation() | |
Gets the validation applied to this cell.
|
||
function | getValidationValue() | |
Gets the value of validation which applied to this cell.
|
||
function | getWidthOfValue() | |
Gets the width of the value in unit of pixels.
|
||
function | hashCode() | |
Serves as a hash function for a particular type.
|
||
function | isRichText() | |
Indicates whether the cell string value is a rich text.
|
||
function | putValue(boolValue) | |
Puts an boolean value into the cell.
|
||
function | putValue(dateTime) | |
Puts a DateTime value into the cell.
|
||
function | putValue(doubleValue) | |
Puts a double value into the cell.
|
||
function | putValue(intValue) | |
Puts an integer value into the cell.
|
||
function | putValue(objectValue) | |
Puts an object value into the cell.
|
||
function | putValue(stringValue) | |
Puts a string value into the cell.
|
||
function | putValue(stringValue, isConverted) | |
Puts a string value into the cell and converts the value to other data type if appropriate.
|
||
function | 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.
|
||
function | removeArrayFormula(leaveNormalFormula) | |
Remove array formula.
|
||
function | setAddInFormula(addInFileName, addInFunction) | |
Sets an Add-In formula to the cell.
|
||
function | setArrayFormula(arrayFormula, rowNumber, columnNumber) | |
Sets an array formula(legacy array formula entered via CTRL+SHIFT+ENTER in ms excel) to a range of cells.
|
||
function | setArrayFormula(arrayFormula, rowNumber, columnNumber, isR1C1, isLocal) | |
Sets an array formula to a range of cells.
|
||
function | setArrayFormula(arrayFormula, rowNumber, columnNumber, options) | |
Sets an array formula to a range of cells.
|
||
function | setCharacters(characters) | |
Sets rich text format of the cell.
|
||
function | setDynamicArrayFormula(arrayFormula, options, calculate) | |
Sets dynamic array formula and make the formula spill into neighboring cells if possible.
|
||
function | setFormula(formula, isR1C1, isLocal, value) | |
Set the formula and the value of the formula.
|
||
function | setFormula(formula, options, value) | |
Set the formula and the value of the formula.
|
||
function | setFormula(formula, value) | |
Set the formula and the value of the formula.
|
||
function | setSharedFormula(sharedFormula, rowNumber, columnNumber) | |
Sets a formula to a range of cells.
|
||
function | setSharedFormula(sharedFormula, rowNumber, columnNumber, isR1C1, isLocal) | |
Sets a formula to a range of cells.
|
||
function | setSharedFormula(sharedFormula, rowNumber, columnNumber, options) | |
Sets a formula to a range of cells.
|
||
function | setStyle(style) | |
Sets the cell style.
|
||
function | setStyle(style, explicitFlag) | |
Apply the cell style.
|
||
function | setStyle(style, flag) | |
Apply the cell style.
|
||
function | toString() | |
Returns a string represents the current Cell object.
|
function getWorksheet()
function getDateTimeValue()
function getRow()
function getColumn()
function isFormula()
function getType()
function getName()
function isErrorValue()
function getStringValue()
function getStringValueWithoutFormat()
function getNumberCategoryType()
function getDisplayStringValue()
function getIntValue()
function getDoubleValue()
function getFloatValue()
function getBoolValue()
function getSharedStyleIndex()
function getFormula() / function setFormula(value)
Example:
var excel = new aspose.cells.Workbook(); var cells = excel.getWorksheets().get(0).getCells(); cells.get("B6").setFormula("=SUM(B2:B5, E1) + sheet2!A1");
function getFormulaLocal() / function setFormulaLocal(value)
function getR1C1Formula() / function setR1C1Formula(value)
function containsExternalLink()
function isArrayHeader()
function isArrayFormula()
function isInArray()
function isSharedFormula()
function isTableFormula()
function isInTable()
function getValue() / function setValue(value)
null,
Boolean,
DateTime,
Double,
Integer
String.
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.function isStyleSet()
function isMerged()
function getComment()
function getHtmlString() / function setHtmlString(value)
function setAddInFormula(addInFileName, addInFunction)
addInFileName: String
- Add-In file name.addInFunction: String
- Add-In function name.Example:
cells.get("h11").setAddInFormula("HRVSTTRK.xla", "=pct_overcut(F3:G3)"); cells.get("h12").setAddInFormula("HRVSTTRK.xla", "=pct_overcut()");
function copy(cell)
cell: Cell
- Source function characters(startIndex, length)
startIndex: Number
- The index of the start of the character.length: Number
- The number of characters.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());
function isRichText()
function getCharacters()
function getCharacters(flag)
flag: boolean
- Indicates whether applying table style to the cell if the cell is in the table.function setCharacters(characters)
characters: FontSetting[]
- All Characters objects.function getMergedRange()
function getHtmlString(html5)
html5: boolean
- Indicates whether the value is compatible for html5function toString()
function equals(obj)
obj: Object
- another objectfunction hashCode()
function equals(cell)
cell: Cell
- another cell objectfunction getConditionalFormattingResult()
function getValidation()
function getValidationValue()
function getTable()
function calculate(options)
options: CalculationOptions
- Options for calculationfunction putValue(boolValue)
boolValue: boolean
- function putValue(intValue)
intValue: Number
- Input valuefunction putValue(doubleValue)
doubleValue: Number
- Input valuefunction putValue(stringValue, isConverted, setStyle)
stringValue: String
- Input valueisConverted: 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 typefunction putValue(stringValue, isConverted)
stringValue: String
- Input valueisConverted: boolean
- True: converted to other data type if appropriate.function putValue(stringValue)
stringValue: String
- Input valuefunction putValue(dateTime)
dateTime: DateTime
- Input valuefunction putValue(objectValue)
objectValue: Object
- input valuefunction getStringValue(formatStrategy)
formatStrategy: Number
- A function getWidthOfValue()
function getHeightOfValue()
function getDisplayStyle()
function getDisplayStyle(includeMergedBorders)
includeMergedBorders: boolean
- Indicates whether checking borders of the merged cells.
function getFormatConditions()
function getStyle()
function getStyle(checkBorders)
checkBorders: boolean
- Check other cells' bordersfunction setStyle(style)
style: Style
- The cell style.function setStyle(style, explicitFlag)
style: Style
- The cell style.explicitFlag: boolean
- True, only overwriting formatting which is explicitly set.
function setStyle(style, flag)
style: Style
- The cell style.flag: StyleFlag
- The style flag.function setFormula(formula, value)
formula: String
- The formula.value: Object
- The value of the formula.function getFormula(isR1C1, isLocal)
isR1C1: boolean
- Whether the formula needs to be formatted as R1C1.isLocal: boolean
- Whether the formula needs to be formatted by locale.function setFormula(formula, isR1C1, isLocal, value)
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.function setFormula(formula, options, value)
formula: String
- The formula.options: FormulaParseOptions
- Options for parsing the formula.value: Object
- The value of the formula.function setArrayFormula(arrayFormula, rowNumber, columnNumber, isR1C1, isLocal)
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 formulaisLocal: boolean
- whether the formula is locale formattedfunction setArrayFormula(arrayFormula, rowNumber, columnNumber, options)
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.function setSharedFormula(sharedFormula, rowNumber, columnNumber, isR1C1, isLocal)
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 formulaisLocal: boolean
- whether the formula is locale formattedfunction setSharedFormula(sharedFormula, rowNumber, columnNumber, options)
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.function getPrecedents()
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");
function getDependents(isAll)
isAll: boolean
- Indicates whether check other worksheetsfunction getLeafs()
function getArrayRange()
function setDynamicArrayFormula(arrayFormula, options, calculate)
arrayFormula: String
- the formula expressionoptions: FormulaParseOptions
- options to parse formula.
"Parse" option will be ignored and the formula will always be parsed immediatelycalculate: boolean
- Whether calculate this dynamic array formula for those cells in the spilled range.function setArrayFormula(arrayFormula, rowNumber, columnNumber)
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.function setSharedFormula(sharedFormula, rowNumber, columnNumber)
sharedFormula: String
- Shared formula.rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.function removeArrayFormula(leaveNormalFormula)
leaveNormalFormula: boolean
- True represents converting the array formula to normal formula.