@Override public boolean isPartOfArrayFormulaGroup() { return getSheet().isCellInArrayFormulaContext(this); }
public XSSFEvaluationCell(XSSFCell cell) { this(cell, new XSSFEvaluationSheet(cell.getSheet())); }
/** * {@inheritDoc} */ @Override public void setAsActiveCell() { getSheet().setActiveCell(getAddress()); }
/** * Returns cell comment associated with this cell * * @return the cell comment associated with this cell or <code>null</code> */ @Override public XSSFComment getCellComment() { return getSheet().getCellComment(new CellAddress(this)); }
/** * Removes the hyperlink for this cell, if there is one. */ @Override public void removeHyperlink() { getSheet().removeHyperlink(_row.getRowNum(), _cellNum); }
/** * Returns hyperlink associated with this cell * * @return hyperlink associated with this cell or <code>null</code> if not found */ @Override public XSSFHyperlink getHyperlink() { return getSheet().getHyperlink(_row.getRowNum(), _cellNum); }
/** * Fired when a formula is deleted from this workbook, * for example when calling cell.setCellFormula(null) * * @see XSSFCell#setCellFormula(String) */ protected void onDeleteFormula(XSSFCell cell){ if(calcChain != null) { int sheetId = (int)cell.getSheet().sheet.getSheetId(); calcChain.removeItem(sheetId, cell.getReference()); } }
@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); }
/** * 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; }
@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); } }
/** * 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)); }
/** * Get the value of the cell as a date. * <p> * For strings we throw an exception. For blank cells we return a null. * </p> * @return the value of the cell as a date * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is {@link CellType#STRING} * @exception NumberFormatException if the cell value isn't a parsable <code>double</code>. * @see DataFormatter for formatting this date into a string similar to how excel does. */ @Override public Date getDateCellValue() { if (getCellType() == CellType.BLANK) { return null; } double value = getNumericCellValue(); boolean date1904 = getSheet().getWorkbook().isDate1904(); return DateUtil.getJavaDate(value, date1904); }
public void updateCellReferencesForShifting(String msg){ if(isPartOfArrayFormulaGroup()) notifyArrayFormulaChanging(msg); CalculationChain calcChain = getSheet().getWorkbook().getCalculationChain(); int sheetId = (int)getSheet().sheet.getSheetId(); //remove the reference in the calculation chain if(calcChain != null) calcChain.removeItem(sheetId, getReference()); CTCell ctCell = getCTCell(); String r = new CellReference(getRowIndex(), getColumnIndex()).formatAsString(); ctCell.setR(r); }
/** * Parse cell formula and re-assemble it back using the new sheet name * * @param cell the cell to update */ private void updateFormula(XSSFCell cell, String oldName, String newName) { CTCellFormula f = cell.getCTCell().getF(); if (f != null) { String formula = f.getStringValue(); if (formula != null && formula.length() > 0) { int sheetIndex = _wb.getSheetIndex(cell.getSheet()); Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.CELL, sheetIndex, cell.getRowIndex()); for (Ptg ptg : ptgs) { updatePtg(ptg, oldName, newName); } String updatedFormula = FormulaRenderer.toFormulaString(_fpwb, ptgs); if (!formula.equals(updatedFormula)) { f.setStringValue(updatedFormula); } } } }
/** * Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as * a date. * <p> * 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 <code>setCellValue(value.getTime())</code> which will * automatically shift the times to the default timezone. * </p> * * @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 )); }
/** * 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); }
/** * 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(); } }
/** * 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()); } }
/** * 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, BaseXSSFEvaluationWorkbook 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); }
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb); FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()), getRowIndex());