Java Code Examples for org.apache.poi.ss.util.CellReference#getSheetName()

/**
 * @param reference
 * @return the cell the reference 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;
}
 

/**
 * 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);
}
 

/**
 * @param reference
 * @return the cell the reference points to
 */
private Cell getCellFromReference( String reference ) {

  CellReference cellRef = new CellReference( reference );
  String sheetName = cellRef.getSheetName();

  Sheet sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( data.realSheetname ) : data.sheet;
  if ( !Utils.isEmpty( sheetName ) ) {
    sheet = meta.isStreamingData() ? data.xssfWorkbook.getSheet( sheetName ) : 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;
}
 

/**
 * This checks all applicable {@link ConditionalFormattingRule}s for the cell's sheet, 
 * in defined "priority" order, returning the matches if any.  This is a property currently
 * not exposed from <code>CTCfRule</code> in <code>XSSFConditionalFormattingRule</code>.  
 * <p>
 * Most cells will have zero or one applied rule, but it is possible to define multiple rules
 * that apply at the same time to the same cell, thus the List result.
 * <p>
 * Note that to properly apply conditional rules, care must be taken to offset the base 
 * formula by the relative position of the current cell, or the wrong value is checked.
 * This is handled by {@link WorkbookEvaluator#evaluate(String, CellReference, CellRangeAddressBase)}.
 * 
 * @param cellRef NOTE: if no sheet name is specified, this uses the workbook active sheet
 * @return Unmodifiable List of {@link EvaluationConditionalFormatRule}s that apply to the current cell value,
 *         in priority order, as evaluated by Excel (smallest priority # for XSSF, definition order for HSSF), 
 *         or null if none apply
 */
public List<EvaluationConditionalFormatRule> getConditionalFormattingForCell(final CellReference cellRef) {
    List<EvaluationConditionalFormatRule> rules = values.get(cellRef);
    
    if (rules == null) {
        // compute and cache them
        rules = new ArrayList<EvaluationConditionalFormatRule>();
        
        Sheet sheet = null;
        if (cellRef.getSheetName() != null) sheet = workbook.getSheet(cellRef.getSheetName());
        else sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
        
        /*
         * Per Excel help:
         * https://support.office.com/en-us/article/Manage-conditional-formatting-rule-precedence-e09711a3-48df-4bcb-b82c-9d8b8b22463d#__toc269129417
         * stopIfTrue is true for all rules from HSSF files, and an explicit value for XSSF files.
         * thus the explicit ordering of the rule lists in #getFormattingRulesForSheet(Sheet)
         */
        boolean stopIfTrue = false;
        for (EvaluationConditionalFormatRule rule : getRules(sheet)) {
            
            if (stopIfTrue) {
                continue; // a previous rule matched and wants no more evaluations
            }

            if (rule.matches(cellRef)) {
                rules.add(rule);
                stopIfTrue = rule.getRule().getStopIfTrue();
            }
        }
        Collections.sort(rules);
        values.put(cellRef, rules);
    }
    
    return Collections.unmodifiableList(rules);
}
 

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);
}