if (formula == null) { wb.onDeleteFormula(this); if (_cell.isSetF()) { _row.getSheet().onDeleteFormula(this, null); _cell.unsetF(); if (_cell.isSetF()) { f = _cell.getF(); f.setStringValue(formula); if(f.getT() == STCellFormulaType.SHARED){ f = CTCellFormula.Factory.newInstance(); f.setStringValue(formula); _cell.setF(f); if(_cell.isSetV()) { _cell.unsetV();
/** * 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); }
break; case STRING: if (_cell.getT() == STCellType.INLINE_STR) { if(_cell.isSetIs()) { rt = new XSSFRichTextString(_cell.getIs()); } else if (_cell.isSetV()) { rt = new XSSFRichTextString(_cell.getV()); } else { rt = new XSSFRichTextString(""); } else if (_cell.getT() == STCellType.STR) { rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : ""); } else { if (_cell.isSetV()) { int idx = Integer.parseInt(_cell.getV()); rt = new XSSFRichTextString(_sharedStringSource.getEntryAt(idx)); case FORMULA: checkFormulaCachedValueType(CellType.STRING, getBaseCellType(false)); rt = new XSSFRichTextString(_cell.isSetV() ? _cell.getV() : ""); break; default:
/** * 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); }
/** * 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()); } }
if (_cell.isSetF()) { _row.getSheet().onDeleteFormula(this, evalWb); _cell.setT(STCellType.N); break; case STRING: rt.setStylesTableReference(_stylesSource); int sRef = _sharedStringSource.addSharedStringItem(rt); _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(); case BOOLEAN: String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING; _cell.setT(STCellType.B); _cell.setV(newVal); break; _cell.setT(STCellType.E); break;
public XLSXCellNode(XSSFSheet sheet,XSSFCell cell,int index,XLSXExpression expression) { super(sheet,cell,expression); assert cell != null : "null cell" ; if(cell.getCTCell().isSetF()){ formulaStringValue = cell.getCTCell().getF().getStringValue(); } colRef = CellReference.convertNumToColString(index); int rowRef = getCell().getRowIndex() + 1; absoluteRef = colRef + rowRef; if(cell.getCTCell().isSetS()){ s = cell.getCTCell().getS(); } workbookPr = getSheet().getWorkbook().getCTWorkbook().getWorkbookPr(); comment = cell.getCellComment(); if(comment != null){ commnetExpr = CellExpressionParser.parseExpression(comment.getString().getString()); } }
switch (cellType){ case FORMULA: _cell.setV(str.getString()); _cell.setT(STCellType.STR); break; default: if(_cell.getT() == STCellType.INLINE_STR) { _cell.setV(str.getString()); } else { _cell.setT(STCellType.S); XSSFRichTextString rt = (XSSFRichTextString)str; rt.setStylesTableReference(_stylesSource); int sRef = _sharedStringSource.addSharedStringItem(rt); _cell.setV(Integer.toString(sRef));
/** * POI currently supports these formula types: * <ul> * <li> {@link STCellFormulaType#NORMAL} * <li> {@link STCellFormulaType#SHARED} * <li> {@link STCellFormulaType#ARRAY} * </ul> * POI does not support {@link STCellFormulaType#DATA_TABLE} formulas. * @return true if the cell is of a formula type POI can handle */ private boolean isFormulaCell() { if ( (_cell.isSetF() && _cell.getF().getT() != STCellFormulaType.DATA_TABLE ) || getSheet().isCellInArrayFormulaContext(this)) { return true; } return false; }
/** * <p>Set the style for the cell. The style should be an XSSFCellStyle created/retreived from * the XSSFWorkbook.</p> * * <p>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)}</p> * * @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); } }
/** * Get the value of the cell as a boolean. * <p> * For strings, numbers, and errors, we throw an exception. For blank cells we return a false. * </p> * @return the value of the cell as a boolean * @throws IllegalStateException if the cell type returned by {@link #getCellType()} * is not {@link CellType#BOOLEAN}, {@link CellType#BLANK} or {@link CellType#FORMULA} */ @Override public boolean getBooleanCellValue() { CellType cellType = getCellType(); 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); } }
@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); }
/** * Returns the raw, underlying ooxml value for the cell * <p> * 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. * </p> * * @return the raw cell value as contained in the underlying CTCell bean, * <code>null</code> for blank cells. */ public String getRawValue() { return _cell.getV(); }
/** * 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; }
public CellRangeAddress getArrayFormulaRange() { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); if (cell == null) { throw new IllegalStateException("Cell " + _cell.getR() + " is not part of an array formula."); } String formulaRef = cell._cell.getF().getRef(); return CellRangeAddress.valueOf(formulaRef); }
private static XSSFColor getThemeOrStyleColor(XSSFCell cell) { long style = cell.getCTCell().getS(); StylesTable stylesSource = cell.getSheet().getWorkbook().getStylesSource(); int fillId = (int) stylesSource.getStyleAt((int) style).getStyleXf().getFillId(); return stylesSource.getFillAt(fillId).getFillForegroundColor(); } }
if (_cell.isSetF()) { _row.getSheet().onDeleteFormula(this, evalWb); _cell.setT(STCellType.N); break; case STRING: rt.setStylesTableReference(_stylesSource); int sRef = _sharedStringSource.addSharedStringItem(rt); _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(); case BOOLEAN: String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING; _cell.setT(STCellType.B); _cell.setV(newVal); break; _cell.setT(STCellType.E); break;
/** * 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()); }
switch(cellType){ case Cell.CELL_TYPE_FORMULA: _cell.setV(str.getString()); _cell.setT(STCellType.STR); break; default: if(_cell.getT() == STCellType.INLINE_STR) { _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));