Example:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); //Put a string into a cell $cell =$cells->get(0, 0); $cell->putValue("Hello"); $first = $cell->getStringValue(); //Put an integer into a cell $cell =$cells->get("B1"); $cell->putValue(12); $second = $cell->getIntValue(); //Put a double into a cell $cell =$cells->get(0, 2); $cell->putValue(-1.234); $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 $style = $cell->getStyle(); //Set background color $style->setBackgroundColor(cells\Color::getYellow()); //Set format of a cell $style->getFont()->setName("Courier New"); $style->setVerticalAlignment(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 by cell's display style.
|
||
function | getDoubleValue() | |
Gets the double value contained in the cell.
|
||
function | getEmbeddedImage() | |
function | setEmbeddedImage(value) | |
Gets and sets the embeddedn image 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 | hasCustomStyle() | |
Indicates whether this cell has custom style settings(different from the default one inherited
from corresponding row, column, or workbook).
|
||
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 an array formula
and it is the first cell of the array.
|
||
function | isDynamicArrayFormula() | |
Indicates whether the cell's formula is dynamic array formula(true) or legacy array formula(false).
|
||
function | isErrorValue() | |
Checks if the value of this cell is an error.
|
||
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 | isNumericValue() | |
Indicates whether the value of this cell is numeric(int, double and datetime)
|
||
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/sets 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 whose formula references to this cell directly.
|
||
function | getDependentsInCalculation(recursive) | |
Gets all cells whose calculated result depends on this cell.
|
||
function | 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().
|
||
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 reference to this cell directly and need to be updated when this cell is modified.
|
||
function | getLeafs(recursive) | |
Get all cells which will be updated when this cell is modified.
|
||
function | getMergedRange() | |
Returns a |
||
function | getPrecedents() | |
Gets all references appearing in this cell's formula.
|
||
function | getPrecedentsInCalculation() | |
Gets all precedents(reference to cells in current workbook) used by this cell's formula while calculating it.
|
||
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 | insertText(index, text) | |
Insert some characters to the cell.
If the cell is rich formatted, this method could keep the original formatting.
|
||
function | isRichText() | |
Indicates whether the string value of this cell is a rich formatted text.
|
||
function | putValue(boolValue) | |
Puts a 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 | replace(placeHolder, newValue, options) | |
Replace text of the cell with options.
|
||
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 | setArrayFormula(arrayFormula, rowNumber, columnNumber, options, values) | |
Sets an array formula to a range of cells.
|
||
function | setCharacters(characters) | |
Sets rich text format of the cell.
|
||
function | setDynamicArrayFormula(arrayFormula, options, calculateValue) | |
Sets dynamic array formula and make the formula spill into neighboring cells if possible.
|
||
function | setDynamicArrayFormula(arrayFormula, options, values, calculateRange, calculateValue) | |
Sets dynamic array formula and make the formula spill into neighboring cells if possible.
|
||
function | setDynamicArrayFormula(arrayFormula, options, values, calculateRange, calculateValue, copts) | |
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(calculated result) of the formula.
|
||
function | setFormula(formula, value) | |
Set the formula and the value(calculated result) of the formula.
|
||
function | setSharedFormula(sharedFormula, rowNumber, columnNumber) | |
Sets shared formulas 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 shared formulas to a range of cells.
|
||
function | setSharedFormula(sharedFormula, rowNumber, columnNumber, options, values) | |
Sets shared formulas to a range of cells.
|
||
function | setStyle(style) | |
Sets the cell style.
|
||
function | setStyle(style, explicitFlag) | |
Apply the changed property of style to the cell.
|
||
function | setStyle(style, flag) | |
Apply the cell style based on flags.
|
||
function | setTableFormula(rowNumber, columnNumber, rowIndexOfInputCell, columnIndexOfInputCell, isRowInput, values) | |
Create one-variable data table for given range starting from this cell.
|
||
function | setTableFormula(rowNumber, columnNumber, rowIndexOfRowInputCell, columnIndexOfRowInputCell, rowIndexOfColumnInputCell, columnIndexOfColumnInputCell, values) | |
Create two-variable data table for given range starting from this cell.
|
||
function | setTableFormula(rowNumber, columnNumber, inputCell, isRowInput, values) | |
Create one-variable data table for given range starting from this cell.
|
||
function | setTableFormula(rowNumber, columnNumber, rowInputCell, columnInputCell, values) | |
Create two-variable data table for given range starting from this cell.
|
||
function | toJson() | |
Convert |
||
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 isNumericValue()
function getStringValue()
function getStringValueWithoutFormat()
function getNumberCategoryType()
function getDisplayStringValue()
function getIntValue()
function getDoubleValue()
function getFloatValue()
function getBoolValue()
function hasCustomStyle()
function getSharedStyleIndex()
function getFormula() / function setFormula(value)
Example:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get("B6")->setFormula("=SUM(B2:B5, E1) + sheet1!A1");
function getFormulaLocal() / function setFormulaLocal(value)
function getR1C1Formula() / function setR1C1Formula(value)
function containsExternalLink()
function isArrayHeader()
function isDynamicArrayFormula()
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 getEmbeddedImage() / function setEmbeddedImage(value)
function setDynamicArrayFormula(arrayFormula, options, calculateValue)
arrayFormula: String
- the formula expressionoptions: FormulaParseOptions
- options to parse formula.
"Parse" option will be ignored and the formula will always be parsed immediatelycalculateValue: boolean
- whether calculate this dynamic array formula for those cells in the spilled range.function setDynamicArrayFormula(arrayFormula, options, values, calculateRange, calculateValue)
arrayFormula: String
- the formula expressionoptions: FormulaParseOptions
- options to parse formula.
"Parse" option will be ignored and the formula will always be parsed immediatelyvalues: Object[][]
- values(calculated results) for those cells with given dynamic array formulacalculateRange: 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.
function setDynamicArrayFormula(arrayFormula, options, values, calculateRange, calculateValue, copts)
arrayFormula: String
- the formula expressionoptions: FormulaParseOptions
- options to parse formula.
"Parse" option will be ignored and the formula will always be parsed immediatelyvalues: Object[][]
- values(calculated results) for those cells with given dynamic array formulacalculateRange: 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.
copts: CalculationOptions
- The options for calculating formula.
Commonly, for performance consideration, the function setTableFormula(rowNumber, columnNumber, rowInputCell, columnInputCell, values)
rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.rowInputCell: String
- the row input cellcolumnInputCell: String
- the column input cellvalues: Object[][]
- values for cells in table formula rangefunction setTableFormula(rowNumber, columnNumber, inputCell, isRowInput, values)
rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.inputCell: String
- the input cellisRowInput: boolean
- Indicates whether the input cell is a row input cell(true) or a column input cell(false).values: Object[][]
- values for cells in table formula rangefunction setTableFormula(rowNumber, columnNumber, rowIndexOfRowInputCell, columnIndexOfRowInputCell, rowIndexOfColumnInputCell, columnIndexOfColumnInputCell, values)
rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.rowIndexOfRowInputCell: Number
- row index of the row input cellcolumnIndexOfRowInputCell: Number
- column index of the row input cellrowIndexOfColumnInputCell: Number
- row index of the column input cellcolumnIndexOfColumnInputCell: Number
- column index of the column input cellvalues: Object[][]
- values for cells in table formula rangefunction setTableFormula(rowNumber, columnNumber, rowIndexOfInputCell, columnIndexOfInputCell, isRowInput, values)
rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.rowIndexOfInputCell: Number
- row index of the input cellcolumnIndexOfInputCell: Number
- column index of the input cellisRowInput: boolean
- Indicates whether the input cell is a row input cell(true) or a column input cell(false).values: Object[][]
- values for cells in table formula rangefunction removeArrayFormula(leaveNormalFormula)
leaveNormalFormula: boolean
- True represents converting the array formula to normal formula.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:
$workbook = new cells\Workbook(); $cell = $workbook->getWorksheets()->get(0)->getCells()->get("A1"); $cell->putValue("Helloworld"); $cell->characters(5, 5)->getFont()->setBold(true); $cell->characters(5, 5)->getFont()->setColor(cells\Color::getBlue());
function replace(placeHolder, newValue, options)
placeHolder: String
- Cell placeholdernewValue: String
- String value to replaceoptions: ReplaceOptions
- The replace optionsfunction insertText(index, text)
index: Number
- The index.text: String
- Inserted text.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 toJson()
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(calculated result) 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(calculated result) 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)
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 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 setArrayFormula(arrayFormula, rowNumber, columnNumber, options, values)
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: Object[][]
- values for those cells with given array formulafunction 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)
sharedFormula: String
- Shared formula.rowNumber: Number
- Number of rows to populate the formula.columnNumber: Number
- Number of columns to populate the formula.function 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 setSharedFormula(sharedFormula, rowNumber, columnNumber, options, values)
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: Object[][]
- values for those cells with given shared formulafunction getPrecedents()
Example:
$workbook = new cells\Workbook(); $cells = $workbook->getWorksheets()->get(0)->getCells(); $cells->get("A1")->setFormula("= B1 + SUM(B1:B10) + [Book1.xls]Sheet1!A1"); $areas =$cells->get("A1")->getPrecedents(); for ($i = 0; $i < java_values($areas->getCount()); $i++) { $area = $areas->get($i); $stringBuilder = ""; if (java_values($area->isExternalLink())) { $stringBuilder .= "["; $stringBuilder .= $area->getExternalFileName(); $stringBuilder .= "]"; } $stringBuilder .= $area->getSheetName(); $stringBuilder .= "!"; $stringBuilder .= cells\CellsHelper::cellIndexToName($area->getStartRow(), $area->getStartColumn()); if (java_values($area->isArea())) { $stringBuilder .= ":"; $stringBuilder .= cells\CellsHelper::cellIndexToName($area->getEndRow(), $area->getEndColumn()); } echo "Precedent ".$i.": ".$stringBuilder."\n"; }
function getDependents(isAll)
isAll: boolean
- Indicates whether check formulas in other worksheetsfunction getPrecedentsInCalculation()
function getDependentsInCalculation(recursive)
recursive: boolean
- Whether returns those dependents which do not reference to this cell directly
but reference to other leafs of this cell function getLeafs()
function getLeafs(recursive)
recursive: boolean
- Whether returns those leafs that do not reference to this cell directly
but reference to other leafs of this cellfunction getArrayRange()