org.apache.poi.xssf.usermodel.XSSFCell Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of poi-ooxml Show documentation
Show all versions of poi-ooxml Show documentation
Apache POI - Java API To Access Microsoft Format Files
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.xssf.usermodel;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaRenderer;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.SharedFormula;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.Beta;
import org.apache.poi.util.Internal;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellFormulaType;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
/**
* High level representation of a cell in a row of a spreadsheet.
*
* Cells can be numeric, formula-based or string-based (text). The cell type
* specifies this. String cells cannot contain 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. Only
* cells that have values should be added.
*
*/
public final class XSSFCell implements Cell {
private static final String FALSE_AS_STRING = "0";
private static final String TRUE_AS_STRING = "1";
private static final String FALSE = "FALSE";
private static final String TRUE = "TRUE";
/**
* the xml bean containing information about the cell's location, value,
* data type, formatting, and formula
*/
private CTCell _cell;
/**
* the XSSFRow this cell belongs to
*/
private final XSSFRow _row;
/**
* 0-based column index
*/
private int _cellNum;
/**
* Table of strings shared across this workbook.
* If two cells contain the same string, then the cell value is the same index into SharedStringsTable
*/
private SharedStringsTable _sharedStringSource;
/**
* Table of cell styles shared across all cells in a workbook.
*/
private StylesTable _stylesSource;
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean containing information about the cell.
*/
protected XSSFCell(XSSFRow row, CTCell cell) {
_cell = cell;
_row = row;
if (cell.getR() != null) {
_cellNum = new CellReference(cell.getR()).getCol();
} else {
int prevNum = row.getLastCellNum();
if(prevNum != -1){
_cellNum = row.getCell(prevNum-1, MissingCellPolicy.RETURN_NULL_AND_BLANK).getColumnIndex() + 1;
}
}
_sharedStringSource = row.getSheet().getWorkbook().getSharedStringSource();
_stylesSource = row.getSheet().getWorkbook().getStylesSource();
}
/**
* Copy cell value, formula and style, from srcCell per cell copy policy
* If srcCell is null, clears the cell value and cell style per cell copy policy
*
* This does not shift references in formulas. Use {@link org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter} to shift references in formulas.
*
* @param srcCell The cell to take value, formula and style from
* @param policy The policy for copying the information, see {@link CellCopyPolicy}
* @throws IllegalArgumentException if copy cell style and srcCell is from a different workbook
*/
@Beta
@Internal
public void copyCellFrom(Cell srcCell, CellCopyPolicy policy) {
// Copy cell value (cell type is updated implicitly)
if (policy.isCopyCellValue()) {
if (srcCell != null) {
CellType copyCellType = srcCell.getCellTypeEnum();
if (copyCellType == CellType.FORMULA && !policy.isCopyCellFormula()) {
// Copy formula result as value
// FIXME: Cached value may be stale
copyCellType = srcCell.getCachedFormulaResultTypeEnum();
}
switch (copyCellType) {
case NUMERIC:
// DataFormat is not copied unless policy.isCopyCellStyle is true
if (DateUtil.isCellDateFormatted(srcCell)) {
setCellValue(srcCell.getDateCellValue());
}
else {
setCellValue(srcCell.getNumericCellValue());
}
break;
case STRING:
setCellValue(srcCell.getStringCellValue());
break;
case FORMULA:
setCellFormula(srcCell.getCellFormula());
break;
case BLANK:
setBlank();
break;
case BOOLEAN:
setCellValue(srcCell.getBooleanCellValue());
break;
case ERROR:
setCellErrorValue(srcCell.getErrorCellValue());
break;
default:
throw new IllegalArgumentException("Invalid cell type " + srcCell.getCellTypeEnum());
}
} else { //srcCell is null
setBlank();
}
}
// Copy CellStyle
if (policy.isCopyCellStyle()) {
if (srcCell != null) {
setCellStyle(srcCell.getCellStyle());
}
else {
// clear cell style
setCellStyle(null);
}
}
if (policy.isMergeHyperlink()) {
// if srcCell doesn't have a hyperlink and destCell has a hyperlink, don't clear destCell's hyperlink
final Hyperlink srcHyperlink = srcCell.getHyperlink();
if (srcHyperlink != null) {
setHyperlink(new XSSFHyperlink(srcHyperlink));
}
}
else if (policy.isCopyHyperlink()) {
// overwrite the hyperlink at dest cell with srcCell's hyperlink
// if srcCell doesn't have a hyperlink, clear the hyperlink (if one exists) at destCell
final Hyperlink srcHyperlink = srcCell.getHyperlink();
if (srcHyperlink == null) {
setHyperlink(null);
}
else {
setHyperlink(new XSSFHyperlink(srcHyperlink));
}
}
}
/**
* @return table of strings shared across this workbook
*/
protected SharedStringsTable getSharedStringSource() {
return _sharedStringSource;
}
/**
* @return table of cell styles shared across this workbook
*/
protected StylesTable getStylesSource() {
return _stylesSource;
}
/**
* Returns the sheet this cell belongs to
*
* @return the sheet this cell belongs to
*/
@Override
public XSSFSheet getSheet() {
return getRow().getSheet();
}
/**
* Returns the row this cell belongs to
*
* @return the row this cell belongs to
*/
@Override
public XSSFRow getRow() {
return _row;
}
/**
* Get the value of the cell as a boolean.
*
* For strings, numbers, and errors, we throw an exception. For blank cells we return a false.
*
* @return the value of the cell as a boolean
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()}
* is not {@link CellType#BOOLEAN}, {@link CellType#BLANK} or {@link CellType#FORMULA}
*/
@Override
public boolean getBooleanCellValue() {
CellType cellType = getCellTypeEnum();
switch(cellType) {
case BLANK:
return false;
case BOOLEAN:
return _cell.isSetV() && TRUE_AS_STRING.equals(_cell.getV());
case FORMULA:
//YK: should throw an exception if requesting boolean value from a non-boolean formula
return _cell.isSetV() && TRUE_AS_STRING.equals(_cell.getV());
default:
throw typeMismatch(CellType.BOOLEAN, cellType, false);
}
}
/**
* Set a boolean value for the cell
*
* @param 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.
*/
@Override
public void setCellValue(boolean value) {
_cell.setT(STCellType.B);
_cell.setV(value ? TRUE_AS_STRING : FALSE_AS_STRING);
}
/**
* Get the value of the cell as a number.
*
* For strings we throw an exception. For blank cells we return a 0.
* For formulas or error cells we return the precalculated value;
*
* @return the value of the cell as a number
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} is {@link CellType#STRING}
* @exception NumberFormatException if the cell value isn't a parsable double.
* @see DataFormatter for turning this number into a string similar to that which Excel would render this number as.
*/
@Override
public double getNumericCellValue() {
CellType cellType = getCellTypeEnum();
switch(cellType) {
case BLANK:
return 0.0;
case FORMULA:
// fall-through
case NUMERIC:
if(_cell.isSetV()) {
String v = _cell.getV();
if (v.isEmpty()) return 0.0;
try {
return Double.parseDouble(v);
} catch(NumberFormatException e) {
throw typeMismatch(CellType.NUMERIC, CellType.STRING, false);
}
} else {
return 0.0;
}
default:
throw typeMismatch(CellType.NUMERIC, cellType, false);
}
}
/**
* Set a numeric value for the cell
*
* @param 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.
*/
@Override
public void setCellValue(double value) {
if(Double.isInfinite(value)) {
// Excel does not support positive/negative infinities,
// rather, it gives a #DIV/0! error in these cases.
_cell.setT(STCellType.E);
_cell.setV(FormulaError.DIV0.getString());
} else if (Double.isNaN(value)){
// Excel does not support Not-a-Number (NaN),
// instead it immediately generates an #NUM! error.
_cell.setT(STCellType.E);
_cell.setV(FormulaError.NUM.getString());
} else {
_cell.setT(STCellType.N);
_cell.setV(String.valueOf(value));
}
}
/**
* Get the value of the cell as a string
*
* 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
*
* @return the value of the cell as a string
*/
@Override
public String getStringCellValue() {
return getRichStringCellValue().getString();
}
/**
* Get the value of the cell as a XSSFRichTextString
*
* 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
*
* @return the value of the cell as a XSSFRichTextString
*/
@Override
public XSSFRichTextString getRichStringCellValue() {
CellType cellType = getCellTypeEnum();
XSSFRichTextString rt;
switch (cellType) {
case BLANK:
rt = new XSSFRichTextString("");
break;
case STRING:
if (_cell.getT() == STCellType.INLINE_STR) {
if(_cell.isSetIs()) {
//string is expressed directly in the cell definition instead of implementing the shared string table.
rt = new XSSFRichTextString(_cell.getIs());
} else if (_cell.isSetV()) {
//cached result of a formula
rt = new XSSFRichTextString(_cell.getV());
} else {
rt = new XSSFRichTextString("");
}
} else if (_cell.getT() == STCellType.STR) {
//cached formula value
rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : "");
} else {
if (_cell.isSetV()) {
int idx = Integer.parseInt(_cell.getV());
rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(idx));
}
else {
rt = new XSSFRichTextString("");
}
}
break;
case FORMULA:
checkFormulaCachedValueType(CellType.STRING, getBaseCellType(false));
rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : "");
break;
default:
throw typeMismatch(CellType.STRING, cellType, false);
}
rt.setStylesTableReference(_stylesSource);
return rt;
}
private static void checkFormulaCachedValueType(CellType expectedTypeCode, CellType cachedValueType) {
if (cachedValueType != expectedTypeCode) {
throw typeMismatch(expectedTypeCode, cachedValueType, true);
}
}
/**
* Set a string value for the cell.
*
* @param str value to set the cell to. For formulas we'll set the formula
* cached string result, 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.
*/
@Override
public void setCellValue(String str) {
setCellValue(str == null ? null : new XSSFRichTextString(str));
}
/**
* Set a string value for the cell.
*
* @param str value to set the cell to. For formulas we'll set the 'pre-evaluated result 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.
*/
@Override
public void setCellValue(RichTextString str) {
if(str == null || str.getString() == null){
setCellType(CellType.BLANK);
return;
}
if(str.length() > SpreadsheetVersion.EXCEL2007.getMaxTextLength()){
throw new IllegalArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
CellType cellType = getCellTypeEnum();
switch (cellType){
case FORMULA:
_cell.setV(str.getString());
_cell.setT(STCellType.STR);
break;
default:
if(_cell.getT() == STCellType.INLINE_STR) {
//set the 'pre-evaluated result
_cell.setV(str.getString());
} else {
_cell.setT(STCellType.S);
XSSFRichTextString rt = (XSSFRichTextString)str;
rt.setStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.addEntry(rt.getCTRst());
_cell.setV(Integer.toString(sRef));
}
break;
}
}
/**
* Return a formula for the cell, for example, SUM(C4:E4)
*
* @return a formula for the cell
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} is not {@link CellType#FORMULA}
*/
@Override
public String getCellFormula() {
// existing behavior - create a new XSSFEvaluationWorkbook for every call
return getCellFormula(null);
}
/**
* package/hierarchy use only - reuse an existing evaluation workbook if available for caching
*
* @param fpb evaluation workbook for reuse, if available, or null to create a new one as needed
* @return a formula for the cell
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} is not {@link CellType#FORMULA}
*/
protected String getCellFormula(XSSFEvaluationWorkbook fpb) {
CellType cellType = getCellTypeEnum();
if(cellType != CellType.FORMULA) throw typeMismatch(CellType.FORMULA, cellType, false);
CTCellFormula f = _cell.getF();
if (isPartOfArrayFormulaGroup() && f == null) {
XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
return cell.getCellFormula(fpb);
}
if (f.getT() == STCellFormulaType.SHARED) {
return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb);
}
return f.getStringValue();
}
/**
* Creates a non shared formula from the shared formula counterpart
*
* @param si Shared Group Index
* @return non shared formula created for the given shared formula and this cell
*/
private String convertSharedFormula(int si, XSSFEvaluationWorkbook fpb){
XSSFSheet sheet = getSheet();
CTCellFormula f = sheet.getSharedFormula(si);
if(f == null) throw new IllegalStateException(
"Master cell of a shared formula with sid="+si+" was not found");
String sharedFormula = f.getStringValue();
//Range of cells which the shared formula applies to
String sharedFormulaRange = f.getRef();
CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange);
int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex, getRowIndex());
Ptg[] fmla = sf.convertSharedFormulas(ptgs,
getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
return FormulaRenderer.toFormulaString(fpb, fmla);
}
/**
* Sets formula for this cell.
*
* Note, this method only sets the formula string and does not calculate the formula value.
* To set the precalculated value use {@link #setCellValue(double)} or {@link #setCellValue(String)}
*
*
* @param formula the formula to set, e.g. "SUM(C4:E4)".
* If the argument is null then the current formula is removed.
* @throws org.apache.poi.ss.formula.FormulaParseException if the formula has incorrect syntax or is otherwise invalid
* @throws IllegalStateException if the operation is not allowed, for example,
* when the cell is a part of a multi-cell array formula
*/
@Override
public void setCellFormula(String formula) {
if(isPartOfArrayFormulaGroup()){
notifyArrayFormulaChanging();
}
setFormula(formula, FormulaType.CELL);
}
/* package */ void setCellArrayFormula(String formula, CellRangeAddress range) {
setFormula(formula, FormulaType.ARRAY);
CTCellFormula cellFormula = _cell.getF();
cellFormula.setT(STCellFormulaType.ARRAY);
cellFormula.setRef(range.formatAsString());
}
private void setFormula(String formula, FormulaType formulaType) {
XSSFWorkbook wb = _row.getSheet().getWorkbook();
if (formula == null) {
wb.onDeleteFormula(this);
if(_cell.isSetF()) _cell.unsetF();
return;
}
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
//validate through the FormulaParser
FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex());
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setStringValue(formula);
_cell.setF(f);
if(_cell.isSetV()) _cell.unsetV();
}
/**
* Returns column index of this cell
*
* @return zero-based column index of a column in a sheet.
*/
@Override
public int getColumnIndex() {
return this._cellNum;
}
/**
* Returns row index of a row in the sheet that contains this cell
*
* @return zero-based row index of a row in the sheet that contains this cell
*/
@Override
public int getRowIndex() {
return _row.getRowNum();
}
/**
* Returns an A1 style reference to the location of this cell
*
* @return A1 style reference to the location of this cell
*/
public String getReference() {
String ref = _cell.getR();
if(ref == null) {
return getAddress().formatAsString();
}
return ref;
}
/**
* {@inheritDoc}
*/
@Override
public CellAddress getAddress() {
return new CellAddress(this);
}
/**
* Return the cell's style.
*
* @return the cell's style.
*/
@Override
public XSSFCellStyle getCellStyle() {
XSSFCellStyle style = null;
if(_stylesSource.getNumCellStyles() > 0){
long idx = _cell.isSetS() ? _cell.getS() : 0;
style = _stylesSource.getStyleAt((int)idx);
}
return style;
}
/**
* Set the style for the cell. The style should be an XSSFCellStyle created/retreived from
* the XSSFWorkbook.
*
* To change the style of a cell without affecting other cells that use the same style,
* use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(Cell, java.util.Map)}
*
* @param 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.
* @throws IllegalArgumentException if style belongs to a different styles source (most likely because style is from a different Workbook)
*/
@Override
public void setCellStyle(CellStyle style) {
if(style == null) {
if(_cell.isSetS()) _cell.unsetS();
} else {
XSSFCellStyle xStyle = (XSSFCellStyle)style;
xStyle.verifyBelongsToStylesSource(_stylesSource);
long idx = _stylesSource.putStyle(xStyle);
_cell.setS(idx);
}
}
private boolean isFormulaCell() {
if (_cell.getF() != null || getSheet().isCellInArrayFormulaContext(this)) {
return true;
}
return false;
}
/**
* Return the cell type.
*
* Will return {@link CellType} in a future version of POI.
* For forwards compatibility, do not hard-code cell type literals in your code.
*
* @return the cell type
* @deprecated 3.15. Will return a {@link CellType} enum in the future.
*/
@Override
public int getCellType() {
return getCellTypeEnum().getCode();
}
/**
* Return the cell type.
*
* @return the cell type
* @since POI 3.15 beta 3
* Will be deleted when we make the CellType enum transition. See bug 59791.
*/
@Override
public CellType getCellTypeEnum() {
if (isFormulaCell()) return CellType.FORMULA;
return getBaseCellType(true);
}
/**
* Only valid for formula cells
*
* Will return {@link CellType} in a future version of POI.
* For forwards compatibility, do not hard-code cell type literals in your code.
*
* @return one of ({@link CellType#NUMERIC}, {@link CellType#STRING},
* {@link CellType#BOOLEAN}, {@link CellType#ERROR}) depending
* on the cached value of the formula
* @deprecated 3.15. Will return a {@link CellType} enum in the future.
*/
@Override
public int getCachedFormulaResultType() {
return getCachedFormulaResultTypeEnum().getCode();
}
/**
* Only valid for formula cells
* @return one of ({@link CellType#NUMERIC}, {@link CellType#STRING},
* {@link CellType#BOOLEAN}, {@link CellType#ERROR}) depending
* on the cached value of the formula
* @since POI 3.15 beta 3
* Will be deleted when we make the CellType enum transition. See bug 59791.
*/
@Override
public CellType getCachedFormulaResultTypeEnum() {
if (! isFormulaCell()) {
throw new IllegalStateException("Only formula cells have cached results");
}
return getBaseCellType(false);
}
/**
* Detect cell type based on the "t" attribute of the CTCell bean
*/
private CellType getBaseCellType(boolean blankCells) {
switch (_cell.getT().intValue()) {
case STCellType.INT_B:
return CellType.BOOLEAN;
case STCellType.INT_N:
if (!_cell.isSetV() && blankCells) {
// ooxml does have a separate cell type of 'blank'. A blank cell gets encoded as
// (either not present or) a numeric cell with no value set.
// The formula evaluator (and perhaps other clients of this interface) needs to
// distinguish blank values which sometimes get translated into zero and sometimes
// empty string, depending on context
return CellType.BLANK;
}
return CellType.NUMERIC;
case STCellType.INT_E:
return CellType.ERROR;
case STCellType.INT_S: // String is in shared strings
case STCellType.INT_INLINE_STR: // String is inline in cell
case STCellType.INT_STR:
return CellType.STRING;
default:
throw new IllegalStateException("Illegal cell type: " + this._cell.getT());
}
}
/**
* Get the value of the cell as a date.
*
* For strings we throw an exception. For blank cells we return a null.
*
* @return the value of the cell as a date
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} is {@link CellType#STRING}
* @exception NumberFormatException if the cell value isn't a parsable double.
* @see DataFormatter for formatting this date into a string similar to how excel does.
*/
@Override
public Date getDateCellValue() {
if (getCellTypeEnum() == CellType.BLANK) {
return null;
}
double value = getNumericCellValue();
boolean date1904 = getSheet().getWorkbook().isDate1904();
return DateUtil.getJavaDate(value, date1904);
}
/**
* Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
* a date.
*
* @param 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 other types we
* will change the cell to a numeric cell and set its value.
*/
@Override
public void setCellValue(Date value) {
if(value == null) {
setCellType(CellType.BLANK);
return;
}
boolean date1904 = getSheet().getWorkbook().isDate1904();
setCellValue(DateUtil.getExcelDate(value, date1904));
}
/**
* 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.
*
*
* @param 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.
*/
@Override
public void setCellValue(Calendar value) {
if(value == null) {
setCellType(CellType.BLANK);
return;
}
boolean date1904 = getSheet().getWorkbook().isDate1904();
setCellValue( DateUtil.getExcelDate(value, date1904 ));
}
/**
* Returns the error message, such as #VALUE!
*
* @return the error message such as #VALUE!
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} isn't {@link CellType#ERROR}
* @see FormulaError
*/
public String getErrorCellString() throws IllegalStateException {
CellType cellType = getBaseCellType(true);
if(cellType != CellType.ERROR) throw typeMismatch(CellType.ERROR, cellType, false);
return _cell.getV();
}
/**
* Get the value of the cell as an error code.
*
* For strings, numbers, and booleans, we throw an exception.
* For blank cells we return a 0.
*
*
* @return the value of the cell as an error code
* @throws IllegalStateException if the cell type returned by {@link #getCellTypeEnum()} isn't {@link CellType #ERROR}
* @see FormulaError
*/
@Override
public byte getErrorCellValue() throws IllegalStateException {
String code = getErrorCellString();
if (code == null) {
return 0;
}
try {
return FormulaError.forString(code).getCode();
} catch (final IllegalArgumentException e) {
throw new IllegalStateException("Unexpected error code", e);
}
}
/**
* Set a error value for the cell
*
* @param errorCode 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.
* @see FormulaError
*/
@Override
public void setCellErrorValue(byte errorCode) {
FormulaError error = FormulaError.forInt(errorCode);
setCellErrorValue(error);
}
/**
* Set a error value for the cell
*
* @param error 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.
*/
public void setCellErrorValue(FormulaError error) {
_cell.setT(STCellType.E);
_cell.setV(error.getString());
}
/**
* {@inheritDoc}
*/
@Override
public void setAsActiveCell() {
getSheet().setActiveCell(getAddress());
}
/**
* Blanks this cell. Blank cells have no formula or value but may have styling.
* This method erases all the data previously associated with this cell.
*/
private void setBlank(){
CTCell blank = CTCell.Factory.newInstance();
blank.setR(_cell.getR());
if(_cell.isSetS()) blank.setS(_cell.getS());
_cell.set(blank);
}
/**
* Sets column index of this cell
*
* @param num column index of this cell
*/
protected void setCellNum(int num) {
checkBounds(num);
_cellNum = num;
String ref = new CellReference(getRowIndex(), getColumnIndex()).formatAsString();
_cell.setR(ref);
}
/**
* Set the cells type (numeric, formula or string)
*
* @throws IllegalArgumentException if the specified cell type is invalid
* @see CellType#NUMERIC
* @see CellType#STRING
* @see CellType#FORMULA
* @see CellType#BLANK
* @see CellType#BOOLEAN
* @see CellType#ERROR
* @deprecated POI 3.15 beta 3. Use {@link #setCellType(CellType)} instead.
*/
@Override
public void setCellType(int cellType) {
setCellType(CellType.forInt(cellType));
}
/**
* Set the cells type (numeric, formula or string)
*
* @throws IllegalArgumentException if the specified cell type is invalid
*/
@Override
public void setCellType(CellType cellType) {
CellType prevType = getCellTypeEnum();
if(isPartOfArrayFormulaGroup()){
notifyArrayFormulaChanging();
}
if(prevType == CellType.FORMULA && cellType != CellType.FORMULA) {
getSheet().getWorkbook().onDeleteFormula(this);
}
switch (cellType) {
case NUMERIC:
_cell.setT(STCellType.N);
break;
case STRING:
if(prevType != CellType.STRING){
String str = convertCellValueToString();
XSSFRichTextString rt = new XSSFRichTextString(str);
rt.setStylesTableReference(_stylesSource);
int sRef = _sharedStringSource.addEntry(rt.getCTRst());
_cell.setV(Integer.toString(sRef));
}
_cell.setT(STCellType.S);
break;
case FORMULA:
if(!_cell.isSetF()){
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setStringValue("0");
_cell.setF(f);
if(_cell.isSetT()) _cell.unsetT();
}
break;
case BLANK:
setBlank();
break;
case BOOLEAN:
String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING;
_cell.setT(STCellType.B);
_cell.setV(newVal);
break;
case ERROR:
_cell.setT(STCellType.E);
break;
default:
throw new IllegalArgumentException("Illegal cell type: " + cellType);
}
if (cellType != CellType.FORMULA && _cell.isSetF()) {
_cell.unsetF();
}
}
/**
* Returns a string representation of the cell
*
* Formula cells return the formula string, rather than the formula result.
* Dates are displayed in dd-MMM-yyyy format
* Errors are displayed as #ERR<errIdx>
*
*/
@Override
public String toString() {
switch (getCellTypeEnum()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(this)) {
DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", LocaleUtil.getUserLocale());
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
return sdf.format(getDateCellValue());
}
return Double.toString(getNumericCellValue());
case STRING:
return getRichStringCellValue().toString();
case FORMULA:
return getCellFormula();
case BLANK:
return "";
case BOOLEAN:
return getBooleanCellValue() ? TRUE : FALSE;
case ERROR:
return ErrorEval.getText(getErrorCellValue());
default:
return "Unknown Cell Type: " + getCellTypeEnum();
}
}
/**
* Returns the raw, underlying ooxml value for the cell
*
* If the cell contains a string, then this value is an index into
* the shared string table, pointing to the actual string value. Otherwise,
* the value of the cell is expressed directly in this element. Cells containing formulas express
* the last calculated result of the formula in this element.
*
*
* @return the raw cell value as contained in the underlying CTCell bean,
* null for blank cells.
*/
public String getRawValue() {
return _cell.getV();
}
/**
* Used to help format error messages
*/
private static RuntimeException typeMismatch(CellType expectedType, CellType actualType, boolean isFormulaCell) {
String msg = "Cannot get a " + expectedType + " value from a " + actualType+ " " + (isFormulaCell ? "formula " : "") + "cell";
return new IllegalStateException(msg);
}
/**
* @throws RuntimeException if the bounds are exceeded.
*/
private static void checkBounds(int cellIndex) {
SpreadsheetVersion v = SpreadsheetVersion.EXCEL2007;
int maxcol = SpreadsheetVersion.EXCEL2007.getLastColumnIndex();
if (cellIndex < 0 || cellIndex > maxcol) {
throw new IllegalArgumentException("Invalid column index (" + cellIndex
+ "). Allowable column range for " + v.name() + " is (0.."
+ maxcol + ") or ('A'..'" + v.getLastColumnName() + "')");
}
}
/**
* Returns cell comment associated with this cell
*
* @return the cell comment associated with this cell or null
*/
@Override
public XSSFComment getCellComment() {
return getSheet().getCellComment(new CellAddress(this));
}
/**
* Assign a comment to this cell. If the supplied comment is null,
* the comment for this cell will be removed.
*
* @param comment the XSSFComment associated with this cell
*/
@Override
public void setCellComment(Comment comment) {
if(comment == null) {
removeCellComment();
return;
}
comment.setAddress(getRowIndex(), getColumnIndex());
}
/**
* Removes the comment for this cell, if there is one.
*/
@Override
public void removeCellComment() {
XSSFComment comment = getCellComment();
if(comment != null){
CellAddress ref = new CellAddress(getReference());
XSSFSheet sh = getSheet();
sh.getCommentsTable(false).removeComment(ref);
sh.getVMLDrawing(false).removeCommentShape(getRowIndex(), getColumnIndex());
}
}
/**
* Returns hyperlink associated with this cell
*
* @return hyperlink associated with this cell or null if not found
*/
@Override
public XSSFHyperlink getHyperlink() {
return getSheet().getHyperlink(_row.getRowNum(), _cellNum);
}
/**
* Assign a hyperlink to this cell. If the supplied hyperlink is null, the
* hyperlink for this cell will be removed.
*
* @param hyperlink the hyperlink to associate with this cell
*/
@Override
public void setHyperlink(Hyperlink hyperlink) {
if (hyperlink == null) {
removeHyperlink();
return;
}
XSSFHyperlink link = (XSSFHyperlink)hyperlink;
// Assign to us
link.setCellReference( new CellReference(_row.getRowNum(), _cellNum).formatAsString() );
// Add to the lists
getSheet().addHyperlink(link);
}
/**
* Removes the hyperlink for this cell, if there is one.
*/
@Override
public void removeHyperlink() {
getSheet().removeHyperlink(_row.getRowNum(), _cellNum);
}
/**
* Returns the xml bean containing information about the cell's location (reference), value,
* data type, formatting, and formula
*
* @return the xml bean containing information about this cell
*/
@Internal
public CTCell getCTCell(){
return _cell;
}
/**
* Set a new internal xml bean. This is only for internal use, do not call this from outside!
*
* This is necessary in some rare cases to work around XMLBeans specialties.
*/
@Internal
public void setCTCell(CTCell cell) {
_cell = cell;
}
/**
* Chooses a new boolean value for the cell when its type is changing.
*
* Usually the caller is calling setCellType() with the intention of calling
* setCellValue(boolean) straight afterwards. This method only exists to give
* the cell a somewhat reasonable value until the setCellValue() call (if at all).
* TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
*/
private boolean convertCellValueToBoolean() {
CellType cellType = getCellTypeEnum();
if (cellType == CellType.FORMULA) {
cellType = getBaseCellType(false);
}
switch (cellType) {
case BOOLEAN:
return TRUE_AS_STRING.equals(_cell.getV());
case STRING:
int sstIndex = Integer.parseInt(_cell.getV());
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(sstIndex));
String text = rt.getString();
return Boolean.parseBoolean(text);
case NUMERIC:
return Double.parseDouble(_cell.getV()) != 0;
case ERROR:
// fall-through
case BLANK:
return false;
default:
throw new RuntimeException("Unexpected cell type (" + cellType + ")");
}
}
private String convertCellValueToString() {
CellType cellType = getCellTypeEnum();
switch (cellType) {
case BLANK:
return "";
case BOOLEAN:
return TRUE_AS_STRING.equals(_cell.getV()) ? TRUE : FALSE;
case STRING:
int sstIndex = Integer.parseInt(_cell.getV());
XSSFRichTextString rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(sstIndex));
return rt.getString();
case NUMERIC:
case ERROR:
return _cell.getV();
case FORMULA:
// should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new IllegalStateException("Unexpected cell type (" + cellType + ")");
}
cellType = getBaseCellType(false);
String textValue = _cell.getV();
switch (cellType) {
case BOOLEAN:
if (TRUE_AS_STRING.equals(textValue)) {
return TRUE;
}
if (FALSE_AS_STRING.equals(textValue)) {
return FALSE;
}
throw new IllegalStateException("Unexpected boolean cached formula value '"
+ textValue + "'.");
case STRING:
// fall-through
case NUMERIC:
// fall-through
case ERROR:
return textValue;
default:
throw new IllegalStateException("Unexpected formula result type (" + cellType + ")");
}
}
@Override
public CellRangeAddress getArrayFormulaRange() {
XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
if (cell == null) {
throw new IllegalStateException("Cell " + getReference()
+ " is not part of an array formula.");
}
String formulaRef = cell._cell.getF().getRef();
return CellRangeAddress.valueOf(formulaRef);
}
@Override
public boolean isPartOfArrayFormulaGroup() {
return getSheet().isCellInArrayFormulaContext(this);
}
/**
* The purpose of this method is to validate the cell state prior to modification
*
* @see #notifyArrayFormulaChanging()
*/
void notifyArrayFormulaChanging(String msg){
if(isPartOfArrayFormulaGroup()){
CellRangeAddress cra = getArrayFormulaRange();
if(cra.getNumberOfCells() > 1) {
throw new IllegalStateException(msg);
}
//un-register the single-cell array formula from the parent XSSFSheet
getRow().getSheet().removeArrayFormula(this);
}
}
/**
* Called when this cell is modified.
*
* The purpose of this method is to validate the cell state prior to modification.
*
*
* @see #setCellType(int)
* @see #setCellFormula(String)
* @see XSSFRow#removeCell(org.apache.poi.ss.usermodel.Cell)
* @see org.apache.poi.xssf.usermodel.XSSFSheet#removeRow(org.apache.poi.ss.usermodel.Row)
* @see org.apache.poi.xssf.usermodel.XSSFSheet#shiftRows(int, int, int)
* @see org.apache.poi.xssf.usermodel.XSSFSheet#addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)
* @throws IllegalStateException if modification is not allowed
*/
void notifyArrayFormulaChanging(){
CellReference ref = new CellReference(this);
String msg = "Cell "+ref.formatAsString()+" is part of a multi-cell array formula. " +
"You cannot change part of an array.";
notifyArrayFormulaChanging(msg);
}
}