Example:
excel = Workbook() cells = excel.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(Color.getYellow()) # Set format of a cell style.getFont().setName("Courier New") style.setVerticalAlignment(TextAlignmentType.TOP) cell.setStyle(style)
Property Getters/Setters Summary | ||
---|---|---|
method | getBoolValue() | |
Gets the boolean value contained in the cell. | ||
method | getColumn() | |
Gets column number (zero based) of the cell. | ||
method | containsExternalLink() | |
Indicates whether this cell contains an external link. Only applies when the cell is a formula cell. | ||
method | getDateTimeValue() | |
Gets the DateTime value contained in the cell. | ||
method | getDisplayStringValue() | |
Gets the formatted string value of this cell. | ||
method | getDoubleValue() | |
Gets the double value contained in the cell. | ||
method | getFloatValue() | |
Gets the float value contained in the cell. | ||
method | getFormula() | |
method | setFormula(value) | |
Gets or sets a formula of the |
||
method | getFormulaLocal() | |
method | setFormulaLocal(value) | |
Get the locale formatted formula of the cell. | ||
method | getHtmlString() | |
method | setHtmlString(value) | |
Gets and sets the html string which contains data and some formats in this cell. | ||
method | getIntValue() | |
Gets the integer value contained in the cell. | ||
method | isArrayFormula() | |
Indicates whether the cell formula is an array formula. | ||
method | isArrayHeader() | |
Inidicates the cell's formula is and array formula and it is the first cell of the array. | ||
method | isErrorValue() | |
Checks if a formula can properly evaluate a result. | ||
method | isFormula() | |
Represents if the specified cell contains formula. | ||
method | isInArray() | |
Indicates whether the cell formula is an array formula. | ||
method | isInTable() | |
Indicates whether this cell is part of table formula. | ||
method | isMerged() | |
Checks if a cell is part of a merged range or not. | ||
method | isSharedFormula() | |
Indicates whether the cell formula is part of shared formula. | ||
method | isStyleSet() | |
Indicates if the cell's style is set. If return false, it means this cell has a default cell format. | ||
method | isTableFormula() | |
Indicates whether this cell is part of table formula. | ||
method | getName() | |
Gets the name of the cell. | ||
method | getNumberCategoryType() | |
Represents the category type of this cell's number formatting. The value of the property is NumberCategoryType integer constant. | ||
method | getR1C1Formula() | |
method | setR1C1Formula(value) | |
Gets or sets a R1C1 formula of the |
||
method | getRow() | |
Gets row number (zero based) of the cell. | ||
method | getSharedStyleIndex() | |
Gets cell's shared style index in the style pool. | ||
method | 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). | ||
method | getStringValueWithoutFormat() | |
Gets cell's value as string without any format. | ||
method | getType() | |
Represents cell value type. The value of the property is CellValueType integer constant. | ||
method | getValue() | |
method | setValue(value) | |
Gets the value contained in this cell. | ||
method | getWorksheet() | |
Gets the parent worksheet. |
Method Summary | ||
---|---|---|
method | calculate(options) | |
Calcaulate the formula of the cell. | ||
method | characters(startIndex, length) | |
Returns a Characters object that represents a range of characters within the cell text. | ||
method | copy(cell) | |
Copies data from a source cell. | ||
method | equals(cell) | |
Checks whether this object refers to the same cell with another cell object. | ||
method | equals(obj) | |
Checks whether this object refers to the same cell with another. | ||
method | getArrayRange() | |
Gets the array range if the cell's formula is an array formula. | ||
method | getCharacters() | |
Returns all Characters objects that represents a range of characters within the cell text. | ||
method | getCharacters(flag) | |
Returns all Characters objects that represents a range of characters within the cell text. | ||
method | getConditionalFormattingResult() | |
Get the result of the conditional formatting. | ||
method | getDependents(isAll) | |
Get all cells which refer to the specific cell. | ||
method | getDisplayStyle() | |
Gets the display style of the cell. If the cell is conditional formatted, the display style is not same as the cell.GetStyle(). | ||
method | 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(). | ||
method | getFormatConditions() | |
Gets format conditions which applies to this cell. | ||
method | getFormula(isR1C1, isLocal) | |
Get the formula of this cell. | ||
method | getHeightOfValue() | |
Gets the height of the value in unit of pixels. | ||
method | getHtmlString(html5) | |
Gets the html string which contains data and some formats in this cell. | ||
method | getLeafs() | |
Get all cells which will be updated when this cell is modified. This method can only work after calling Workbook.CalculateFormula. | ||
method | getMergedRange() | |
Returns a |
||
method | getPrecedents() | |
Gets all cells or ranges which this cell's formula depends on. | ||
method | getStringValue(formatStrategy) | |
Gets the string value by specific formatted strategy. | ||
method | getStyle() | |
Gets the cell style. | ||
method | getStyle(checkBorders) | |
If checkBorders is true, check whether other cells' borders will effect the style of this cell. | ||
method | getTable() | |
Gets the table which contains this cell. | ||
method | getValidation() | |
Gets the validation applied to this cell. | ||
method | getValidationValue() | |
Gets the value of validation which applied to this cell. | ||
method | getWidthOfValue() | |
Gets the width of the value in unit of pixels. | ||
method | hashCode() | |
Serves as a hash function for a particular type. | ||
method | isRichText() | |
Indicates whether the cell string value is a rich text. | ||
method | putValue(boolValue) | |
Puts an boolean value into the cell. | ||
method | putValue(dateTime) | |
Puts a DateTime value into the cell. | ||
method | putValue(doubleValue) | |
Puts a double value into the cell. | ||
method | putValue(intValue) | |
Puts an integer value into the cell. | ||
method | putValue(objectValue) | |
Puts an object value into the cell. | ||
method | putValue(stringValue) | |
Puts a string value into the cell. | ||
method | putValue(stringValue, isConverted) | |
Puts a string value into the cell and converts the value to other data type if appropriate. | ||
method | 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. | ||
method | removeArrayFormula(leaveNormalFormula) | |
Remove array formula. | ||
method | setAddInFormula(addInFileName, addInFunction) | |
Sets an Add-In formula to the cell. | ||
method | setArrayFormula(arrayFormula, rowNumber, columnNumber) | |
Sets an array formula to a range of cells. | ||
method | setArrayFormula(arrayFormula, rowNumber, columnNumber, isR1C1, isLocal) | |
Sets an array formula to a range of cells. | ||
method | setArrayFormula(arrayFormula, rowNumber, columnNumber, options) | |
Sets an array formula to a range of cells. | ||
method | setCharacters(characters) | |
Sets rich text format of the cell. | ||
method | setFormula(formula, isR1C1, isLocal, value) | |
Set the formula and the value of the formula. | ||
method | setFormula(formula, options, value) | |
Set the formula and the value of the formula. | ||
method | setFormula(formula, value) | |
Set the formula and the value of the formula. | ||
method | setSharedFormula(sharedFormula, rowNumber, columnNumber) | |
Sets a formula to a range of cells. | ||
method | setSharedFormula(sharedFormula, rowNumber, columnNumber, isR1C1, isLocal) | |
Sets a formula to a range of cells. | ||
method | setSharedFormula(sharedFormula, rowNumber, columnNumber, options) | |
Sets a formula to a range of cells. | ||
method | setStyle(style) | |
Sets the cell style. | ||
method | setStyle(style, explicitFlag) | |
Apply the cell style. | ||
method | setStyle(style, flag) | |
Apply the cell style. | ||
method | toString() | |
Returns a string represents the current Cell object. |
Property Getters/Setters Detail |
---|
getWorksheet : Worksheet | |
Worksheet getWorksheet() |
getDateTimeValue : DateTime | |
DateTime getDateTimeValue() |
getRow : int | |
int getRow() |
getColumn : int | |
int getColumn() |
isFormula : boolean | |
boolean isFormula() |
getType : int | |
int getType() |
getName : String | |
String getName() |
isErrorValue : boolean | |
boolean isErrorValue() |
getStringValue : String | |
String getStringValue() |
getStringValueWithoutFormat : String | |
String getStringValueWithoutFormat() |
getNumberCategoryType : int | |
int getNumberCategoryType() |
getDisplayStringValue : String | |
String getDisplayStringValue() |
getIntValue : int | |
int getIntValue() |
getDoubleValue : float | |
float getDoubleValue() |
getFloatValue : float | |
float getFloatValue() |
getBoolValue : boolean | |
boolean getBoolValue() |
getSharedStyleIndex : int | |
int getSharedStyleIndex() |
getFormula/setFormula : String | |
String getFormula() / setFormula(value) |
Example:
excel = Workbook() cells = excel.getWorksheets().get(0).getCells() cells.get("B6").setFormula("=SUM(B2:B5, E1) + sheet2!A1")
getFormulaLocal/setFormulaLocal : String | |
String getFormulaLocal() / setFormulaLocal(value) |
getR1C1Formula/setR1C1Formula : String | |
String getR1C1Formula() / setR1C1Formula(value) |
containsExternalLink : boolean | |
boolean containsExternalLink() |
isArrayHeader : boolean | |
boolean isArrayHeader() |
isArrayFormula : boolean | |
boolean isArrayFormula() |
isInArray : boolean | |
boolean isInArray() |
isSharedFormula : boolean | |
boolean isSharedFormula() |
isTableFormula : boolean | |
boolean isTableFormula() |
isInTable : boolean | |
boolean isInTable() |
getValue/setValue : Object | |
Object getValue() / 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 object type always.isStyleSet : boolean | |
boolean isStyleSet() |
isMerged : boolean | |
boolean isMerged() |
getHtmlString/setHtmlString : String | |
String getHtmlString() / setHtmlString(value) |
Method Detail |
---|
setArrayFormula | |
setArrayFormula(arrayFormula, rowNumber, columnNumber) |
arrayFormula: String
- Array formula.rowNumber: int
- Number of rows to populate result of the array formula.columnNumber: int
- Number of columns to populate result of the array formula.setSharedFormula | |
setSharedFormula(sharedFormula, rowNumber, columnNumber) |
sharedFormula: String
- Shared formula.rowNumber: int
- Number of rows to populate the formula.columnNumber: int
- Number of columns to populate the formula.removeArrayFormula | |
removeArrayFormula(leaveNormalFormula) |
leaveNormalFormula: boolean
- True represents converting the array formula to normal formula.setAddInFormula | |
setAddInFormula(addInFileName, addInFunction) |
addInFileName: String
- Add-In file name.addInFunction: String
- Add-In function name.Example:
workbook = Workbook() cells = workbook.getWorksheets().get(0).getCells() cells.get("h11").setAddInFormula("HRVSTTRK.xla", "=pct_overcut(F3:G3)") cells.get("h12").setAddInFormula("HRVSTTRK.xla", "=pct_overcut()")
copy | |
copy(cell) |
cell: Cell
- Source characters | |
FontSetting characters(startIndex, length) |
startIndex: int
- The index of the start of the character.length: int
- The number of characters.Example:
excel = Workbook() 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(Color.getBlue())
isRichText | |
boolean isRichText() |
getCharacters | |
FontSetting[] getCharacters() |
getCharacters | |
FontSetting[] getCharacters(flag) |
flag: boolean
- Indicates whether applying table style to the cell if the cell is in the table.setCharacters | |
setCharacters(characters) |
characters: FontSetting[]
- All Characters objects.getMergedRange | |
Range getMergedRange() |
getHtmlString | |
String getHtmlString(html5) |
html5: boolean
- Indicates whether the value is compatible for html5toString | |
String toString() |
equals | |
boolean equals(obj) |
obj: Object
- another objecthashCode | |
int hashCode() |
equals | |
boolean equals(cell) |
cell: Cell
- another cell objectgetConditionalFormattingResult | |
ConditionalFormattingResult getConditionalFormattingResult() |
getValidation | |
Validation getValidation() |
getValidationValue | |
boolean getValidationValue() |
getTable | |
ListObject getTable() |
calculate | |
calculate(options) |
options: CalculationOptions
- Options for calculationputValue | |
putValue(boolValue) |
boolValue: boolean
- putValue | |
putValue(intValue) |
intValue: int
- Input valueputValue | |
putValue(doubleValue) |
doubleValue: float
- Input valueputValue | |
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 typeputValue | |
putValue(stringValue, isConverted) |
stringValue: String
- Input valueisConverted: boolean
- True: converted to other data type if appropriate.putValue | |
putValue(stringValue) |
stringValue: String
- Input valueputValue | |
putValue(dateTime) |
dateTime: DateTime
- Input valueputValue | |
putValue(objectValue) |
objectValue: Object
- input valuegetStringValue | |
String getStringValue(formatStrategy) |
formatStrategy: int
- A getWidthOfValue | |
int getWidthOfValue() |
getHeightOfValue | |
int getHeightOfValue() |
getDisplayStyle | |
Style getDisplayStyle() |
getDisplayStyle | |
Style getDisplayStyle(includeMergedBorders) |
includeMergedBorders: boolean
- Indicates whether checking borders of the merged cells.
getFormatConditions | |
FormatConditionCollection[] getFormatConditions() |
getStyle | |
Style getStyle() |
getStyle | |
Style getStyle(checkBorders) |
checkBorders: boolean
- Check other cells' borderssetStyle | |
setStyle(style) |
style: Style
- The cell style.setStyle | |
setStyle(style, explicitFlag) |
style: Style
- The cell style.explicitFlag: boolean
- True, only overwriting formatting which is explicitly set.
setStyle | |
setStyle(style, flag) |
style: Style
- The cell style.flag: StyleFlag
- The style flag.setFormula | |
setFormula(formula, value) |
formula: String
- The formula.value: Object
- The value of the formula.getFormula | |
String 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.setFormula | |
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.setFormula | |
setFormula(formula, options, value) |
formula: String
- The formula.options: FormulaParseOptions
- Options for parsing the formula.value: Object
- The value of the formula.setArrayFormula | |
setArrayFormula(arrayFormula, rowNumber, columnNumber, isR1C1, isLocal) |
arrayFormula: String
- Array formula.rowNumber: int
- Number of rows to populate result of the array formula.columnNumber: int
- Number of columns to populate result of the array formula.isR1C1: boolean
- whether the formula is R1C1 formulaisLocal: boolean
- whether the formula is locale formattedsetArrayFormula | |
setArrayFormula(arrayFormula, rowNumber, columnNumber, options) |
arrayFormula: String
- Array formula.rowNumber: int
- Number of rows to populate result of the array formula.columnNumber: int
- Number of columns to populate result of the array formula.options: FormulaParseOptions
- Options for parsing the formula.setSharedFormula | |
setSharedFormula(sharedFormula, rowNumber, columnNumber, isR1C1, isLocal) |
sharedFormula: String
- Shared formula.rowNumber: int
- Number of rows to populate the formula.columnNumber: int
- Number of columns to populate the formula.isR1C1: boolean
- whether the formula is R1C1 formulaisLocal: boolean
- whether the formula is locale formattedsetSharedFormula | |
setSharedFormula(sharedFormula, rowNumber, columnNumber, options) |
sharedFormula: String
- Shared formula.rowNumber: int
- Number of rows to populate the formula.columnNumber: int
- Number of columns to populate the formula.options: FormulaParseOptions
- Options for parsing the formula.getPrecedents | |
ReferredAreaCollection getPrecedents() |
Example:
workbook = Workbook() cells = workbook.getWorksheets().get(0).getCells() cells.get("A1").setFormula("= B1 + SUM(B1:B10) + [Book2.xlsx]Chart!B2") areas = cells.get("A1").getPrecedents() for i in range(0, areas.getCount()): area = areas.get(i) stringBuilder = "" if area.isExternalLink(): stringBuilder += "[" stringBuilder += area.getExternalFileName() stringBuilder += "]" stringBuilder += area.getSheetName() stringBuilder += "!" stringBuilder += CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn()) if area.isArea(): stringBuilder += ":" stringBuilder += CellsHelper.cellIndexToName(area.getEndRow(), area.getEndColumn()) workbook.save("Book1.xlsx")
getDependents | |
Cell[] getDependents(isAll) |
isAll: boolean
- Indicates whether check other worksheetsgetLeafs | |
Iterator getLeafs() |
getArrayRange | |
CellArea getArrayRange() |