public int getSheetIndex() { return dve.getWorkbookEvaluator().getSheetIndex(target.getSheetName()); }
/** * @param reference * @return the cell the refernce points to */ private Cell getCellFromReference( String reference ) { CellReference cellRef = new CellReference( reference ); String sheetName = cellRef.getSheetName(); Sheet sheet = data.sheet; if ( !Utils.isEmpty( sheetName ) ) { sheet = data.wb.getSheet( sheetName ); } if ( sheet == null ) { return null; } // reference is assumed to be absolute Row xlsRow = sheet.getRow( cellRef.getRow() ); if ( xlsRow == null ) { return null; } Cell styleCell = xlsRow.getCell( cellRef.getCol() ); return styleCell; }
if (cellRef.getSheetName() != null) { sheet = workbook.getSheet(cellRef.getSheetName()); } else { sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
/** * Returns a reference to every cell covered by this area */ public CellReference[] getAllReferencedCells() { // Special case for single cell reference if(_isSingleCell) { return new CellReference[] { _firstCell, }; } // Interpolate between the two int minRow = Math.min(_firstCell.getRow(), _lastCell.getRow()); int maxRow = Math.max(_firstCell.getRow(), _lastCell.getRow()); int minCol = Math.min(_firstCell.getCol(), _lastCell.getCol()); int maxCol = Math.max(_firstCell.getCol(), _lastCell.getCol()); String sheetName = _firstCell.getSheetName(); List<CellReference> refs = new ArrayList<>(); for(int row=minRow; row<=maxRow; row++) { for(int col=minCol; col<=maxCol; col++) { CellReference ref = new CellReference(sheetName, row, col, _firstCell.isRowAbsolute(), _firstCell.isColAbsolute()); refs.add(ref); } } return refs.toArray(new CellReference[refs.size()]); }
if(!_isSingleCell) { sb.append(CELL_DELIMITER); if(_lastCell.getSheetName() == null) { sb.append(_lastCell.formatAsString()); } else {
/** * Create a pivot table using the AreaReference range, at the given position. * If the source reference contains a sheet name, that sheet is used, otherwise this sheet is assumed as the source sheet. * @param source location of pivot data * @param position A reference to the top left cell where the pivot table will start * @return The pivot table */ @Beta public XSSFPivotTable createPivotTable(AreaReference source, CellReference position){ final String sourceSheetName = source.getFirstCell().getSheetName(); if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(this.getSheetName())) { final XSSFSheet sourceSheet = getWorkbook().getSheet(sourceSheetName); return createPivotTable(source, position, sourceSheet); } return createPivotTable(source, position, this); }
/** * A range is returned only for the part of the table matching this enum instance and containing the given cell reference. * Null is returned for all other cases, such as: * <ul> * <li>Cell on a different sheet than the table * <li>Cell outside the table * <li>this Enum part is not included in the table (i.e. no header/totals row) * <li>this Enum is for a table part not yet implemented in POI, such as pivot table elements * </ul> * The returned range can be used to determine how style options may or may not apply to this cell. * For example, {@link #wholeTable} borders only apply to the outer boundary of a table, while the * rest of the styling, such as font and color, could apply to all the interior cells as well. * * @param table table to evaluate * @param cell CellReference to evaluate * @return range in the table representing this class of cells, if it contains the given cell, or null if not applicable. * Stripe style types return only the stripe range containing the given cell, or null. */ public CellRangeAddressBase appliesTo(Table table, CellReference cell) { if (table == null || cell == null) return null; if ( ! cell.getSheetName().equals(table.getSheetName())) return null; if ( ! table.contains(cell)) return null; final CellRangeAddressBase range = getRange(table, cell); if (range != null && range.isInRange(cell.getRow(), cell.getCol())) return range; // else return null; }
/** * Get the sheets name which this named range is referenced to * * @return sheet name, which this named range referred to. * Empty string if the referenced sheet name was not found. */ public String getSheetName() { if (_ctName.isSetLocalSheetId()) { // Given as explicit sheet id int sheetId = (int)_ctName.getLocalSheetId(); return _workbook.getSheetName(sheetId); } String ref = getRefersToFormula(); AreaReference areaRef = new AreaReference(ref, SpreadsheetVersion.EXCEL2007); return areaRef.getFirstCell().getSheetName(); }
/** * @see org.apache.poi.ss.usermodel.Table#contains(org.apache.poi.ss.usermodel.Cell) * @since 3.17 beta 1 */ public boolean contains(CellReference cell) { if (cell == null) return false; // check if cell is on the same sheet as the table if ( ! getSheetName().equals(cell.getSheetName())) return false; // check if the cell is inside the table if (cell.getRow() >= getStartRowIndex() && cell.getRow() <= getEndRowIndex() && cell.getCol() >= getStartColIndex() && cell.getCol() <= getEndColIndex()) { return true; } return false; }
/** * Create a pivot table using the AreaReference range on sourceSheet, at the given position. * If the source reference contains a sheet name, it must match the sourceSheet * @param source location of pivot data * @param position A reference to the top left cell where the pivot table will start * @param sourceSheet The sheet containing the source data, if the source reference doesn't contain a sheet name * @throws IllegalArgumentException if source references a sheet different than sourceSheet * @return The pivot table */ @Beta public XSSFPivotTable createPivotTable(final AreaReference source, CellReference position, Sheet sourceSheet) { final String sourceSheetName = source.getFirstCell().getSheetName(); if(sourceSheetName != null && !sourceSheetName.equalsIgnoreCase(sourceSheet.getSheetName())) { throw new IllegalArgumentException("The area is referenced in another sheet than the " + "defined source sheet " + sourceSheet.getSheetName() + "."); } return createPivotTable(position, sourceSheet, wsSource -> { final String[] firstCell = source.getFirstCell().getCellRefParts(); final String firstRow = firstCell[1]; final String firstCol = firstCell[2]; final String[] lastCell = source.getLastCell().getCellRefParts(); final String lastRow = lastCell[1]; final String lastCol = lastCell[2]; final String ref = firstCol+firstRow+':'+lastCol+lastRow; //or just source.formatAsString() wsSource.setRef(ref); }); }
/** * Finds and returns the {@link DataValidationContext} for the cell, if there is * one. Lookup is based on the first match from * {@link DataValidation#getRegions()} for the cell's sheet. DataValidation * regions must be in the same sheet as the DataValidation. Allowed values * expressions may reference other sheets, however. * * @param cell reference to check * @return the DataValidationContext applicable to the given cell, or null if no * validation applies */ public DataValidationContext getValidationContextForCell(CellReference cell) { final Sheet sheet = workbook.getSheet(cell.getSheetName()); if (sheet == null) return null; final List<? extends DataValidation> dataValidations = getValidations(sheet); if (dataValidations == null) return null; for (DataValidation dv : dataValidations) { final CellRangeAddressList regions = dv.getRegions(); if (regions == null) return null; // current implementation can't return null for (CellRangeAddressBase range : regions.getCellRangeAddresses()) { if (range.isInRange(cell)) { return new DataValidationContext(dv, this, range, cell); } } } return null; }
private ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region, FormulaType formulaType) { final String sheetName = target == null ? null : target.getSheetName(); if (sheetName == null) throw new IllegalArgumentException("Sheet name is required"); final int sheetIndex = getWorkbook().getSheetIndex(sheetName); Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), formulaType, sheetIndex, target.getRow()); adjustRegionRelativeReference(ptgs, target, region); final OperationEvaluationContext ec = new OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(), target.getCol(), new EvaluationTracker(_cache), formulaType.isSingleValue()); return evaluateNameFormula(ptgs, ec); }
String areaSheetName = tableArea.getFirstCell().getSheetName(); if (areaSheetName != null && !areaSheetName.equals(getXSSFSheet().getSheetName())) {
final Cell cell = SheetUtil.getCell(workbook.getSheet(cellRef.getSheetName()), cellRef.getRow(), cellRef.getCol());
/** * Evaluate a formula outside a cell value, e.g. conditional format rules or data validation expressions * * @param formula to evaluate * @param ref defines the optional sheet and row/column base for the formula, if it is relative * @return value */ public ValueEval evaluate(String formula, CellReference ref) { final String sheetName = ref == null ? null : ref.getSheetName(); int sheetIndex; if (sheetName == null) { sheetIndex = -1; // workbook scope only } else { sheetIndex = getWorkbook().getSheetIndex(sheetName); } int rowIndex = ref == null ? -1 : ref.getRow(); short colIndex = ref == null ? -1 : ref.getCol(); final OperationEvaluationContext ec = new OperationEvaluationContext( this, getWorkbook(), sheetIndex, rowIndex, colIndex, new EvaluationTracker(_cache) ); Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), FormulaType.CELL, sheetIndex, rowIndex); return evaluateNameFormula(ptgs, ec); }
private CellRef(String cellRef) { this.cellRef = cellRef; CellReference cellReference = new CellReference(cellRef); rowNum = cellReference.getRow(); colNum = cellReference.getCol(); sheetName = cellReference.getSheetName(); }
public void update(String newCellRef) { cellRef = newCellRef; CellReference cellReference = new CellReference(cellRef); rowNum = cellReference.getRow(); colNum = cellReference.getCol(); sheetName = cellReference.getSheetName(); }
private CellRef(String cellRef) { this.cellRef = cellRef; CellReference cellReference = new CellReference(cellRef); rowNum = cellReference.getRow(); colNum = cellReference.getCol(); sheetName = cellReference.getSheetName(); }
public void update(String newCellRef) { cellRef = newCellRef; CellReference cellReference = new CellReference(cellRef); rowNum = cellReference.getRow(); colNum = cellReference.getCol(); sheetName = cellReference.getSheetName(); }
private ValueEval evaluate(String formula, CellReference target, CellRangeAddressBase region, FormulaType formulaType) { final String sheetName = target == null ? null : target.getSheetName(); if (sheetName == null) throw new IllegalArgumentException("Sheet name is required"); final int sheetIndex = getWorkbook().getSheetIndex(sheetName); Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook) getWorkbook(), formulaType, sheetIndex, target.getRow()); adjustRegionRelativeReference(ptgs, target, region); final OperationEvaluationContext ec = new OperationEvaluationContext(this, getWorkbook(), sheetIndex, target.getRow(), target.getCol(), new EvaluationTracker(_cache), formulaType.isSingleValue()); return evaluateNameFormula(ptgs, ec); }