public final class ExcelCell extends AbstractRange implements com.gembox.spreadsheet.internal.ExcelEditor<com.gembox.spreadsheet.internal.ExcelCellData>, com.gembox.spreadsheet.internal.styles.CellFormatDataFormatHolder
Merged range is created by using CellRange.setMerged(boolean)
method.
See the method documentation for more information on merging.
Modifier and Type | Method and Description |
---|---|
void |
calculate()
Calculates formula for this excel cell.
|
static double |
convertDateTimeToExcelNumber(LocalDateTime date,
boolean use1904DateSystem)
Converts LocalDateTime to Excel floating-point number.
|
static LocalDateTime |
convertExcelNumberToDateTime(double num,
boolean use1904DateSystem)
Converts Excel floating-point number to
java.time.LocalDateTime . |
boolean |
equals(Object obj)
Determines whether the specified
Object is equal to this ExcelCell instance. |
boolean |
getBooleanValue()
Gets the boolean value of this cell or of merged range if this cell is merged.
|
ExcelColumn |
getColumn()
Gets column of the cell.
|
ExcelComment |
getComment()
Gets cell comment.
|
CellRange |
getCurrentRegion()
Selects a worksheet region to which the cell belongs to.
|
LocalDateTime |
getDateTimeValue()
Gets the DateTime value of this cell or of merged range if this cell is merged.
|
double |
getDoubleValue()
Gets the double value of this cell or of merged range if this cell is merged.
|
String |
getFormattedValue()
Converts
cell value of this ExcelCell instance to string representation using number format
that is specified on this ExcelCell instance. |
String |
getFormula()
Gets cell formula string.
|
SpreadsheetHyperlink |
getHyperlink()
Gets cell hyperlink.
|
int |
getIntValue()
Gets the integer value of this cell or of merged range if this cell is merged.
|
CellRange |
getMergedRange()
Returns associated merged range if the cell is merged; otherwise,
null . |
String |
getName(boolean displayWorksheet,
boolean rowAbsolute,
boolean columnAbsolute) |
ExcelRow |
getRow()
Gets row of the cell.
|
String |
getStringValue()
Gets the string value of this cell or of merged range if this cell is merged.
|
Object |
getValue()
Gets value of this cell or of merged range if this cell is merged.
|
CellValueType |
getValueType()
Gets the cell value type.
|
ExcelWorksheet |
getWorksheet()
Gets the parent worksheet.
|
int |
hashCode()
Returns a hash code for this
ExcelCell instance. |
void |
insert(InsertShiftDirection direction)
Inserts a cell by shifting cells in the specified direction.
|
void |
remove(RemoveShiftDirection direction)
Removes a cell by shifting cells in the specified direction.
|
void |
setComment(ExcelComment value)
Sets cell comment.
|
void |
setFormula(String value)
Sets cell formula string.
|
void |
setHyperlink(SpreadsheetHyperlink value)
Sets cell hyperlink.
|
void |
setValue(boolean value)
Sets the boolean value to this cell or to merged range if this cell is merged.
|
void |
setValue(byte value)
Sets the byte value to this cell or to merged range if this cell is merged.
|
void |
setValue(char value)
Sets the char value to this cell or to merged range if this cell is merged.
|
void |
setValue(double value)
Sets the double value to this cell or to merged range if this cell is merged.
|
void |
setValue(float value)
Sets the float value to this cell or to merged range if this cell is merged.
|
void |
setValue(int value)
Sets the integer value to this cell or to merged range if this cell is merged.
|
void |
setValue(LocalDateTime value)
Sets the DateTime value to this cell or to merged range if this cell is merged.
|
void |
setValue(long value)
Sets the long value to this cell or to merged range if this cell is merged.
|
void |
setValue(Object value)
Sets value of this cell or of merged range if this cell is merged.
|
void |
setValue(short value)
Sets the short value to this cell or to merged range if this cell is merged.
|
void |
setValue(String value)
Sets the string value to this cell or to merged range if this cell is merged.
|
void |
setValue(String html,
HtmlLoadOptions options)
Loads the specified HTML text as the value of this
ExcelCell . |
clear, getCharacterRanges, getCharacters, getCharacters, getName, getStyle, replaceText, replaceText, replaceText, replaceText, setStyle, toString
createData, getData, getParentSyncIndex, setData
public void calculate()
calculate
in class AbstractRange
public static double convertDateTimeToExcelNumber(LocalDateTime date, boolean use1904DateSystem)
date
- DateTime structure.use1904DateSystem
- True to use 1904 date system.public static LocalDateTime convertExcelNumberToDateTime(double num, boolean use1904DateSystem)
java.time.LocalDateTime
.
Excel file format doesn't have a separate data type for date and time.
java.time.LocalDateTime
value is
stored as IEEE number encoded in a special way. When reading Excel file,
CellStyle numberFormat
is examined and if it matches
some of date/time number formats cell value is interpreted as java.time.LocalDateTime
.
However, if some non-standard date/time number format is used, cell value will not be recognized
as java.time.LocalDateTime
but as ordinary number. In such cases (when you know that
specific cell holds java.time.LocalDateTime
value but you get a number when reading
Excel file) use this method to convert IEEE number to java.time.LocalDateTime
structure.
num
- Excel floating-point number.use1904DateSystem
- True to use 1904 date system.public boolean equals(Object obj)
Object
is equal to this ExcelCell
instance.public boolean getBooleanValue()
public ExcelColumn getColumn()
public ExcelComment getComment()
getComment
in class AbstractRange
public CellRange getCurrentRegion()
public LocalDateTime getDateTimeValue()
public double getDoubleValue()
public String getFormattedValue()
cell value
of this ExcelCell
instance to string representation using number format
that is specified on this ExcelCell
instance.cell value.
public String getFormula()
getFormula
in class AbstractRange
setFormula(String)
public SpreadsheetHyperlink getHyperlink()
getHyperlink
in class AbstractRange
public int getIntValue()
public CellRange getMergedRange()
null
.CellRange.isMerged()
public String getName(boolean displayWorksheet, boolean rowAbsolute, boolean columnAbsolute)
public ExcelRow getRow()
public String getStringValue()
public Object getValue()
getValue
in class AbstractRange
public CellValueType getValueType()
public ExcelWorksheet getWorksheet()
getWorksheet
in interface com.gembox.spreadsheet.internal.inlinestyles.FormattedStringProvider
getWorksheet
in class AbstractRange
public int hashCode()
ExcelCell
instance.public void insert(InsertShiftDirection direction)
insert
in class AbstractRange
direction
- The shift direction.public void remove(RemoveShiftDirection direction)
remove
in class AbstractRange
direction
- The shift direction.public void setComment(ExcelComment value)
You can set comment text, set whether comment will be visible when opening xlsx file or not. Additionally you can get column or row of the excel cell to which this comment is assigned.
Supported only in xlsx and xls.
setComment
in class AbstractRange
Excel comment for an example on how to use comments
public void setFormula(String value)
New XLSX (Open XML) format stores formulas as strings and leaves formula parsing to applications that read XLSX documents. Therefore, ALL formulas are supported when writing/reading XLSX files.
Depending on ExcelFile.isDelayFormulaParse()
ExcelFile.DelayFormulaParse property, formula string can be parsed when this method is used
or when one of {@link com.gembox.spreadsheet.ExcelFile#save(String)"> methods is called.
GemBox.Spreadsheet formula parser will use English culture to parse numbers.
Currently unsupported formula features are:
For more information on formulas, consult Microsoft Excel documentation.
Following code demonstrates how to use formulas and named ranges. It shows following features: cell references (both absolute and relative), unary and binary operators, constant operands (integer and floating point), functions and named cell ranges. At the end, code shows how to calculate worksheet formulas.
ws.getCell("A1").setValue(5);
ws.getCell("A2").setValue(6);
ws.getCell("A3").setValue(10);
ws.getCell("C1").setFormula("=A1+A2");
ws.getCell("C2").setFormula("=$A$1-A3");
ws.getCell("C3").setFormula("=COUNT(A1:A3)");
ws.getCell("C4").setFormula("=AVERAGE($A$1:$A$3)");
ws.getCell("C5").setFormula("=SUM(A1:A3,2,3)");
ws.getCell("C7").setFormula("= 123 - (-(-(23.5)))");
ws.addNamedRange("DataRange", ws.getCells().getSubrange("A1", "A3"));
ws.getCell("C8").setFormula("=MAX(DataRange)");
CellRange cr = ws.getCells().getSubrange("B9", "C10");
cr.setMerged(true);
cr.setFormula("=A1*25");
ws.calculate();
setFormula
in class AbstractRange
NamedRangeCollection.add
public void setHyperlink(SpreadsheetHyperlink value)
setHyperlink
in class AbstractRange
public void setValue(boolean value)
value
- The boolean value.public void setValue(byte value)
value
- The double value.public void setValue(char value)
value
- The double value.public void setValue(double value)
value
- The double value.public void setValue(float value)
value
- The double value.public void setValue(int value)
value
- The integer value.public void setValue(LocalDateTime value)
value
- The DateTime value.public void setValue(long value)
value
- The double value.public void setValue(Object value)
Exception is thrown if value for the set is not of supported type (See
ExcelFile.SupportsType
for details).
Note that the fact some type is supported doesn't mean it is written to Excel file in the native format. As Microsoft Excel has just few basic types, the object of supported type will be converted to a similar excel type. If similar excel type doesn't exist, value is written as a string value.
If the value of this property is of java.time.LocalDateTime
type and
style
number format is not set, ISO date/time
format will be used as CellStyle's number format
value.
setValue
in class AbstractRange
UnsupportedOperationException
- Value is not supported.CellRange.getMerged
,
getMergedRange()
,
ExcelFile.supportsType
,
CellStyle.getNumberFormat
public void setValue(short value)
value
- The double value.public void setValue(String value)
value
- The string value.public final void setValue(String html, HtmlLoadOptions options) throws IOException
ExcelCell
.html
- The HTML text which should be loaded as the value of this ExcelCell
.options
- The options used to define settings for load operation.IOException
© GemBox d.o.o. — All rights reserved.