/** * Return a formula for the cell, for example, <code>SUM(C4:E4)</code> * * @return a formula for the cell * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not {@link CellType#FORMULA} */ @Override public String getCellFormula() { // existing behavior - create a new XSSFEvaluationWorkbook for every call return getCellFormula(null); }
@Override public Ptg[] getFormulaTokens(EvaluationCell evalCell) { final XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell(); final int sheetIndex = _uBook.getSheetIndex(cell.getSheet()); final int rowIndex = cell.getRowIndex(); return FormulaParser.parse(cell.getCellFormula(this), this, FormulaType.CELL, sheetIndex, rowIndex); } }
return getRichStringCellValue().toString(); case FORMULA: return getCellFormula(); case BLANK: return "";
/** * 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 #getCellType()} is not {@link CellType#FORMULA} */ protected String getCellFormula(BaseXSSFEvaluationWorkbook fpb) { CellType cellType = getCellType(); if(cellType != CellType.FORMULA) { throw typeMismatch(CellType.FORMULA, cellType, false); } CTCellFormula f = _cell.getF(); if (isPartOfArrayFormulaGroup()) { /* In an excel generated array formula, the formula property might be set, but the string is empty in slave cells */ if (f == null || f.getStringValue().isEmpty()) { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); return cell.getCellFormula(fpb); } } if (f == null) { return null; } else if (f.getT() == STCellFormulaType.SHARED) { return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb); } else { return f.getStringValue(); } }
/** * when a cell with a 'master' shared formula is removed, the next cell in the range becomes the master * @param cell The cell that is removed * @param evalWb BaseXSSFEvaluationWorkbook in use, if one exists */ protected void onDeleteFormula(XSSFCell cell, BaseXSSFEvaluationWorkbook evalWb){ CTCellFormula f = cell.getCTCell().getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { CellRangeAddress ref = CellRangeAddress.valueOf(f.getRef()); if(ref.getNumberOfCells() > 1){ DONE: for(int i = cell.getRowIndex(); i <= ref.getLastRow(); i++){ XSSFRow row = getRow(i); if(row != null) for(int j = cell.getColumnIndex(); j <= ref.getLastColumn(); j++){ XSSFCell nextCell = row.getCell(j); if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA){ CTCellFormula nextF = nextCell.getCTCell().getF(); nextF.setStringValue(nextCell.getCellFormula(evalWb)); CellRangeAddress nextRef = new CellRangeAddress( nextCell.getRowIndex(), ref.getLastRow(), nextCell.getColumnIndex(), ref.getLastColumn()); nextF.setRef(nextRef.formatAsString()); sharedFormulas.put((int)nextF.getSi(), nextF); break DONE; } } } } } }
/** * Return a formula for the cell, for example, <code>SUM(C4:E4)</code> * * @return a formula for the cell * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not {@link CellType#FORMULA} */ @Override public String getCellFormula() { // existing behavior - create a new XSSFEvaluationWorkbook for every call return getCellFormula(null); }
break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); cellTempList.add(cell.getCellFormula()); break;
break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); cellTempList.add(cell.getCellFormula()); break;
break; case Cell.CELL_TYPE_FORMULA: value=cell.getCellFormula(); break;
case 2: cellTypeDesc = "FORMULA"; cellValue = cell.getCellFormula(); break; case 3:
@Override public Ptg[] getFormulaTokens(EvaluationCell evalCell) { final XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell(); final int sheetIndex = _uBook.getSheetIndex(cell.getSheet()); final int rowIndex = cell.getRowIndex(); return FormulaParser.parse(cell.getCellFormula(this), this, FormulaType.CELL, sheetIndex, rowIndex); } }
public Ptg[] getFormulaTokens(EvaluationCell evalCell) { XSSFCell cell = ((XSSFEvaluationCell)evalCell).getXSSFCell(); XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook); return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); }
value[r][c] = cell.getCellFormula(); break;
return getRichStringCellValue().toString(); case FORMULA: return getCellFormula(); case BLANK: return "";
/** * Returns a string representation of the cell * <p> * 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> * </p> */ public String toString() { switch (getCellType()) { case CELL_TYPE_BLANK: return ""; case CELL_TYPE_BOOLEAN: return getBooleanCellValue() ? "TRUE" : "FALSE"; case CELL_TYPE_ERROR: return ErrorEval.getText(getErrorCellValue()); case CELL_TYPE_FORMULA: return getCellFormula(); case CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(this)) { DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy"); return sdf.format(getDateCellValue()); } return getNumericCellValue() + ""; case CELL_TYPE_STRING: return getRichStringCellValue().toString(); default: return "Unknown Cell Type: " + getCellType(); } }
/** * Return a formula for the cell, for example, <code>SUM(C4:E4)</code> * * @return a formula for the cell * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA */ public String getCellFormula() { int cellType = getCellType(); if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false); CTCellFormula f = _cell.getF(); if (isPartOfArrayFormulaGroup() && f == null) { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); return cell.getCellFormula(); } if (f.getT() == STCellFormulaType.SHARED) { return convertSharedFormula((int)f.getSi()); } return f.getStringValue(); }
/** * 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 #getCellType()} is not {@link CellType#FORMULA} */ protected String getCellFormula(BaseXSSFEvaluationWorkbook fpb) { CellType cellType = getCellType(); if(cellType != CellType.FORMULA) { throw typeMismatch(CellType.FORMULA, cellType, false); } CTCellFormula f = _cell.getF(); if (isPartOfArrayFormulaGroup()) { /* In an excel generated array formula, the formula property might be set, but the string is empty in slave cells */ if (f == null || f.getStringValue().isEmpty()) { XSSFCell cell = getSheet().getFirstCellInArrayFormula(this); return cell.getCellFormula(fpb); } } if (f == null) { return null; } else if (f.getT() == STCellFormulaType.SHARED) { return convertSharedFormula((int)f.getSi(), fpb == null ? XSSFEvaluationWorkbook.create(getSheet().getWorkbook()) : fpb); } else { return f.getStringValue(); } }
break; case HSSFCell.CELL_TYPE_FORMULA: targetCell.setCellFormula(sourceCell.getCellFormula()); break; default:
/** * when a cell with a 'master' shared formula is removed, the next cell in the range becomes the master * @param cell The cell that is removed * @param evalWb BaseXSSFEvaluationWorkbook in use, if one exists */ protected void onDeleteFormula(XSSFCell cell, BaseXSSFEvaluationWorkbook evalWb){ CTCellFormula f = cell.getCTCell().getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { CellRangeAddress ref = CellRangeAddress.valueOf(f.getRef()); if(ref.getNumberOfCells() > 1){ DONE: for(int i = cell.getRowIndex(); i <= ref.getLastRow(); i++){ XSSFRow row = getRow(i); if(row != null) for(int j = cell.getColumnIndex(); j <= ref.getLastColumn(); j++){ XSSFCell nextCell = row.getCell(j); if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA){ CTCellFormula nextF = nextCell.getCTCell().getF(); nextF.setStringValue(nextCell.getCellFormula(evalWb)); CellRangeAddress nextRef = new CellRangeAddress( nextCell.getRowIndex(), ref.getLastRow(), nextCell.getColumnIndex(), ref.getLastColumn()); nextF.setRef(nextRef.formatAsString()); sharedFormulas.put((int)nextF.getSi(), nextF); break DONE; } } } } } }
break; case Cell.CELL_TYPE_FORMULA: String cellFormula = tplSheetCell.getCellFormula(); if (cellFormula != null) sheetCell.setCellFormula(cellFormula);