public interface Cell
Cells can be numeric, formula-based or string-based (text). The cell type specifies this. String cells cannot conatin numbers and numeric cells cannot contain strings (at least according to our model). Client apps should do the conversions themselves. Formula cells have the formula string, as well as the formula result, which can be numeric or string.
Cells should have their number (0 based) before being added to a row.
Modifier and Type | Method and Description |
---|---|
CellAddress |
getAddress()
Gets the address of this cell
|
CellRangeAddress |
getArrayFormulaRange()
Only valid for array formula cells
|
boolean |
getBooleanCellValue()
Get the value of the cell as a boolean.
|
CellType |
getCachedFormulaResultType()
Only valid for formula cells
|
CellType |
getCachedFormulaResultTypeEnum()
Deprecated.
will be removed in 4.2
Will be renamed to
getCachedFormulaResultType() when we make the CellType enum transition in POI 4.0. See bug 59791. |
Comment |
getCellComment()
Returns comment associated with this cell
|
java.lang.String |
getCellFormula()
Return a formula for the cell, for example,
SUM(C4:E4) |
CellStyle |
getCellStyle()
Return the cell's style.
|
CellType |
getCellType()
Return the cell type.
|
CellType |
getCellTypeEnum()
Deprecated.
will be removed in 4.2
Will be renamed to
getCellType() when we make the CellType enum transition in POI 4.0. See bug 59791. |
int |
getColumnIndex()
Returns column index of this cell
|
java.util.Date |
getDateCellValue()
Get the value of the cell as a date.
|
byte |
getErrorCellValue()
Get the value of the cell as an error code.
|
Hyperlink |
getHyperlink() |
java.time.LocalDateTime |
getLocalDateTimeCellValue()
Get the value of the cell as a LocalDateTime.
|
double |
getNumericCellValue()
Get the value of the cell as a number.
|
RichTextString |
getRichStringCellValue()
Get the value of the cell as a XSSFRichTextString
|
Row |
getRow()
Returns the Row this cell belongs to
|
int |
getRowIndex()
Returns row index of a row in the sheet that contains this cell
|
Sheet |
getSheet()
Returns the sheet this cell belongs to
|
java.lang.String |
getStringCellValue()
Get the value of the cell as a string
|
boolean |
isPartOfArrayFormulaGroup() |
void |
removeCellComment()
Removes the comment for this cell, if there is one.
|
void |
removeFormula()
Removes formula, if any.
|
void |
removeHyperlink()
Removes the hyperlink for this cell, if there is one.
|
void |
setAsActiveCell()
Sets this cell as the active cell for the worksheet
|
void |
setBlank()
Removes formula and value from the cell, and sets its type to
CellType.BLANK . |
void |
setCellComment(Comment comment)
Assign a comment to this cell
|
void |
setCellErrorValue(byte value)
Set a error value for the cell
|
void |
setCellFormula(java.lang.String formula)
Sets formula for this cell.
|
void |
setCellStyle(CellStyle style)
Set the style for the cell.
|
void |
setCellType(CellType cellType)
Deprecated.
This method is deprecated and will be removed in POI 5.0.
Use explicit
setCellFormula(String) , setCellValue(...) or setBlank()
to get the desired result. |
void |
setCellValue(boolean value)
Set a boolean value for the cell
|
void |
setCellValue(java.util.Calendar value)
Set a date value for the cell.
|
void |
setCellValue(java.util.Date value)
Converts the supplied date to its equivalent Excel numeric value and sets
that into the cell.
|
void |
setCellValue(double value)
Set a numeric value for the cell.
|
default void |
setCellValue(java.time.LocalDate value)
Converts the supplied date to its equivalent Excel numeric value and sets
that into the cell.
|
void |
setCellValue(java.time.LocalDateTime value)
Converts the supplied date to its equivalent Excel numeric value and sets
that into the cell.
|
void |
setCellValue(RichTextString value)
Set a rich string value for the cell.
|
void |
setCellValue(java.lang.String value)
Set a string value for the cell.
|
void |
setHyperlink(Hyperlink link)
Assign a hyperlink to this cell
|
int getColumnIndex()
int getRowIndex()
Sheet getSheet()
Row getRow()
@Deprecated @Removal(version="5.0") void setCellType(CellType cellType)
setCellFormula(String)
, setCellValue(...)
or setBlank()
to get the desired result.If the cell currently contains a value, the value will be converted to match the new type, if possible. Formatting is generally lost in the process however.
Conversion rules:
to NUMERIC: numeric value is left as is. True converts to 1.0, false converts to 0. otherwise, the value is set to 0. Formula is removed.
If what you want to do is get a String value for your
numeric cell, stop! This is not the way to do it.
Instead, for fetching the string value of a numeric or boolean
or date cell, use DataFormatter
instead.
If cell is a member of an array formula group containing more than 1 cell, an IllegalStateException
is thrown. If the array formula group contains only this cell, it is removed.
Passing CellType.FORMULA
is illegal and will result in an IllegalArgumentException
.
java.lang.IllegalArgumentException
- if the specified cell type is invalid (null, _NONE or FORMULA)java.lang.IllegalStateException
- if the current value cannot be converted to the new type or
if the cell is a part of an array formula group containing other cellsvoid setBlank()
CellType.BLANK
.
Preserves comments and hyperlinks.
While setCellType(CellType)
exists, is an alias for setCellType(CellType.BLANK)
.CellType getCellType()
@Deprecated @Removal(version="4.2") CellType getCellTypeEnum()
getCellType()
when we make the CellType enum transition in POI 4.0. See bug 59791.CellType.FORMULA
for all cells, even though the formula is only defined
in the OOXML file for the top left cell of the array.
NOTE: POI does not support data table formulas. Cells in a data table appear to POI as plain cells typed from their cached value.
CellType getCachedFormulaResultType()
CellType.NUMERIC
, CellType.STRING
,
CellType.BOOLEAN
, CellType.ERROR
) depending
on the cached value of the formula@Deprecated @Removal(version="4.2") CellType getCachedFormulaResultTypeEnum()
getCachedFormulaResultType()
when we make the CellType enum transition in POI 4.0. See bug 59791.CellType.NUMERIC
, CellType.STRING
,
CellType.BOOLEAN
, CellType.ERROR
) depending
on the cached value of the formulavoid setCellValue(double value)
value
- the numeric value to set this cell to. For formulas we'll set the
precalculated value, for numerics we'll set its value. For other types we
will change the cell to a numeric cell and set its value.void setCellValue(java.util.Date value)
Converts the supplied date to its equivalent Excel numeric value and sets that into the cell.
Note - There is actually no 'DATE' cell type in Excel. In many
cases (when entering date values), Excel automatically adjusts the
cell style to some date format, creating the illusion that the cell
data type is now something besides CellType.NUMERIC
. POI
does not attempt to replicate this behaviour. To make a numeric cell
display as a date, use setCellStyle(CellStyle)
etc.
value
- the numeric value to set this cell to. For formulas we'll set the
precalculated value, for numerics we'll set its value. For other types we
will change the cell to a numerics cell and set its value.void setCellValue(java.time.LocalDateTime value)
Converts the supplied date to its equivalent Excel numeric value and sets that into the cell.
Note - There is actually no 'DATE' cell type in Excel. In many
cases (when entering date values), Excel automatically adjusts the
cell style to some date format, creating the illusion that the cell
data type is now something besides CellType.NUMERIC
. POI
does not attempt to replicate this behaviour. To make a numeric cell
display as a date, use setCellStyle(CellStyle)
etc.
value
- the numeric value to set this cell to. For formulas we'll set the
precalculated value, for numerics we'll set its value. For other types we
will change the cell to a numerics cell and set its value.default void setCellValue(java.time.LocalDate value)
Converts the supplied date to its equivalent Excel numeric value and sets that into the cell.
Note - There is actually no 'DATE' cell type in Excel. In many
cases (when entering date values), Excel automatically adjusts the
cell style to some date format, creating the illusion that the cell
data type is now something besides CellType.NUMERIC
. POI
does not attempt to replicate this behaviour. To make a numeric cell
display as a date, use setCellStyle(CellStyle)
etc.
value
- the numeric value to set this cell to. For formulas we'll set the
precalculated value, for numerics we'll set its value. For other types we
will change the cell to a numerics cell and set its value.void setCellValue(java.util.Calendar value)
Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as a date.
This will set the cell value based on the Calendar's timezone. As Excel
does not support timezones this means that both 20:00+03:00 and
20:00-03:00 will be reported as the same value (20:00) even that there
are 6 hours difference between the two times. This difference can be
preserved by using setCellValue(value.getTime())
which will
automatically shift the times to the default timezone.
value
- the date value to set this cell to. For formulas we'll set the
precalculated value, for numerics we'll set its value. For othertypes we
will change the cell to a numeric cell and set its value.void setCellValue(RichTextString value)
value
- value to set the cell to. For formulas we'll set the formula
string, for String cells we'll set its value. For other types we will
change the cell to a string cell and set its value.
If value is null then we will change the cell to a Blank cell.void setCellValue(java.lang.String value)
value
- value to set the cell to. For formulas we'll set the formula
string, for String cells we'll set its value. For other types we will
change the cell to a string cell and set its value.
If value is null then we will change the cell to a Blank cell.void setCellFormula(java.lang.String formula) throws FormulaParseException, java.lang.IllegalStateException
If formula
is not null, sets or updates the formula. If formula
is null, removes the formula.
Or use removeFormula()
to remove the formula.
Note, this method only sets the formula string and does not calculate the formula value.
To set the precalculated value use setCellValue(double)
.
If the cell was blank, sets value to 0. Otherwise, preserves the value as precalculated.
formula
- the formula to set, e.g. "SUM(C4:E4)"
.
If the argument is null
then the current formula is removed.java.lang.IllegalStateException
- if this cell is a part of an array formula group containing other cellsFormulaParseException
- if the formula has incorrect syntax or is otherwise invalidremoveFormula()
void removeFormula() throws java.lang.IllegalStateException
java.lang.IllegalStateException
- if cell is a part of an array formula group containing other cellsjava.lang.String getCellFormula()
SUM(C4:E4)
java.lang.IllegalStateException
- if the cell type returned by getCellType()
is not CellType.FORMULA
double getNumericCellValue()
For strings we throw an exception. For blank cells we return a 0. For formulas or error cells we return the precalculated value;
java.lang.IllegalStateException
- if the cell type returned by getCellType()
is CellType.STRING
java.lang.NumberFormatException
- if the cell value isn't a parsable double
.for turning this number into a string similar to that which Excel would render this number as.
java.util.Date getDateCellValue()
For strings we throw an exception. For blank cells we return a null.
java.lang.IllegalStateException
- if the cell type returned by getCellType()
is CellType.STRING
java.lang.NumberFormatException
- if the cell value isn't a parsable double
.for formatting this date into a string similar to how excel does.
java.time.LocalDateTime getLocalDateTimeCellValue()
For strings we throw an exception. For blank cells we return a null.
java.lang.IllegalStateException
- if the cell type returned by getCellType()
is CellType.STRING
java.lang.NumberFormatException
- if the cell value isn't a parsable double
.for formatting this date into a string similar to how excel does.
RichTextString getRichStringCellValue()
For numeric cells we throw an exception. For blank cells we return an empty string. For formula cells we return the pre-calculated value if a string, otherwise an exception.
java.lang.String getStringCellValue()
For numeric cells we throw an exception. For blank cells we return an empty string. For formulaCells that are not string Formulas, we throw an exception.
void setCellValue(boolean value)
value
- the boolean value to set this cell to. For formulas we'll set the
precalculated value, for booleans we'll set its value. For other types we
will change the cell to a boolean cell and set its value.void setCellErrorValue(byte value)
value
- the error value to set this cell to. For formulas we'll set the
precalculated value , for errors we'll set
its value. For other types we will change the cell to an error
cell and set its value.FormulaError
boolean getBooleanCellValue()
For strings, numbers, and errors, we throw an exception. For blank cells we return a false.
java.lang.IllegalStateException
- if the cell type returned by getCellType()
is not CellType.BOOLEAN
, CellType.BLANK
or CellType.FORMULA
byte getErrorCellValue()
For strings, numbers, and booleans, we throw an exception. For blank cells we return a 0.
java.lang.IllegalStateException
- if the cell type returned by getCellType()
isn't CellType.ERROR
for error codes
void setCellStyle(CellStyle style)
Set the style for the cell. The style should be an CellStyle created/retrieved from the Workbook.
To change the style of a cell without affecting other cells that use the same style,
use CellUtil.setCellStyleProperties(Cell, Map)
style
- reference contained in the workbook.
If the value is null then the style information is removed causing the cell to used the default workbook style.Workbook.createCellStyle()
CellStyle getCellStyle()
workbook.getCellStyleAt(0)
Workbook.getCellStyleAt(int)
void setAsActiveCell()
CellAddress getAddress()
A1
style address of this cellvoid setCellComment(Comment comment)
comment
- comment associated with this cellComment getCellComment()
null
if not foundvoid removeCellComment()
Hyperlink getHyperlink()
null
if not foundvoid setHyperlink(Hyperlink link)
link
- hyperlink associated with this cellvoid removeHyperlink()
CellRangeAddress getArrayFormulaRange()
boolean isPartOfArrayFormulaGroup()
true
if this cell is part of group of cells having a common array formula.Copyright 2020 The Apache Software Foundation or its licensors, as applicable.