org.apache.poi.ss.util.CellReference Java Examples

/**
 *
 * @param sheetIden may be <code>null</code>
 * @param part1
 * @param part2 may be <code>null</code>
 */
private ParseNode createAreaRefParseNode(SheetIdentifier sheetIden, SimpleRangePart part1,
        SimpleRangePart part2) throws FormulaParseException {
    Ptg ptg;
    if (part2 == null) {
        CellReference cr = part1.getCellReference();
        if (sheetIden == null) {
            ptg = new RefPtg(cr);
        } else {
            ptg = _book.get3DReferencePtg(cr, sheetIden);
        }
    } else {
        AreaReference areaRef = createAreaRef(part1, part2);

        if (sheetIden == null) {
            ptg = new AreaPtg(areaRef);
        } else {
            ptg = _book.get3DReferencePtg(areaRef, sheetIden);
        }
    }
    return new ParseNode(ptg);
}
 

/**
 * @param cell needed for offsets from region anchor - may be null!
 * @param region for adjusting relative formulas
 * @return true/false using the same rules as Data Validation evaluations
 */
private boolean checkFormula(CellReference ref, CellRangeAddress region) {
    ValueEval comp = unwrapEval(workbookEvaluator.evaluate(rule.getFormula1(), ref, region));
    
    // Copied for now from DataValidationEvaluator.ValidationEnum.FORMULA#isValidValue()
    if (comp instanceof BlankEval) {
        return true;
    }
    if (comp instanceof ErrorEval) {
        return false;
    }
    if (comp instanceof BoolEval) {
        return ((BoolEval) comp).getBooleanValue();
    }
    // empirically tested in Excel - 0=false, any other number = true/valid
    // see test file DataValidationEvaluations.xlsx
    if (comp instanceof NumberEval) {
        return ((NumberEval) comp).getNumberValue() != 0;
    }
    return false; // anything else is false, such as text
}
 

protected void createName(HandlerState state, String bookmark, int row1, int col1, int row2, int col2 ) {
	CellReference crFirst = new CellReference( state.currentSheet.getSheetName(), row1, col1, true, true );
	CellReference crLast = new CellReference( row2, col2, true, true );
	String formula = crFirst.formatAsString() + ":" + crLast.formatAsString();

	Name name = state.currentSheet.getWorkbook().getName(bookmark);
	if( name == null ) {
		name = state.currentSheet.getWorkbook().createName();
		name.setNameName( bookmark ); 
		name.setRefersToFormula( formula );
	} else {
		String existingFormula = name.getRefersToFormula();
		try {
			name.setRefersToFormula(existingFormula + "," + formula);
		} catch( FormulaParseException ex ) {
			log.warn( 0, "Unable to add \"" + formula + "\" to name (\"" + bookmark + "\") with existing formula: " + existingFormula, ex );
		}
	}
}
 

/**
 * Use the validation returned by {@link #getValidationForCell(CellReference)} if you
 * want the error display details. This is the validation checked by this
 * method, which attempts to replicate Excel's data validation rules.
 * <p>
 * Note that to properly apply some validations, care must be taken to
 * offset the base validation formula by the relative position of the
 * current cell, or the wrong value is checked.
 * 
 * @param cellRef The reference of the cell to evaluate
 * @return true if the cell has no validation or the cell value passes the
 *         defined validation, false if it fails
 */
public boolean isValidCell(CellReference cellRef) {
    final DataValidationContext context = getValidationContextForCell(cellRef);

    if (context == null) return true;
    
    final Cell cell = SheetUtil.getCell(workbook.getSheet(cellRef.getSheetName()), cellRef.getRow(), cellRef.getCol());
    
    // now we can validate the cell
    
    // if empty, return not allowed flag
    if (   cell == null
        || isType(cell, CellType.BLANK)  
        || (isType(cell,CellType.STRING) 
            && (cell.getStringCellValue() == null || cell.getStringCellValue().isEmpty())
           )
       ) {
        return context.getValidation().getEmptyCellAllowed();
    }
    
    // cell has a value
    
    return ValidationEnum.isValid(cell, context);
}
 

public ForkedEvaluationCell getOrCreateUpdatableCell(int rowIndex, int columnIndex) {
    RowColKey key = new RowColKey(rowIndex, columnIndex);

    ForkedEvaluationCell result = _sharedCellsByRowCol.get(key);
    if (result == null) {
        EvaluationCell mcell = _masterSheet.getCell(rowIndex, columnIndex);
        if (mcell == null) {
            CellReference cr = new CellReference(rowIndex, columnIndex);
            throw new UnsupportedOperationException("Underlying cell '"
                    + cr.formatAsString() + "' is missing in master sheet.");
        }
        result = new ForkedEvaluationCell(this, mcell);
        _sharedCellsByRowCol.put(key, result);
    }
    return result;
}
 

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

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

@Override
public CellRange<HSSFCell> removeArrayFormula(Cell cell) {
    if (cell.getSheet() != this) {
        throw new IllegalArgumentException("Specified cell does not belong to this sheet.");
    }
    CellValueRecordInterface rec = ((HSSFCell) cell).getCellValueRecord();
    if (!(rec instanceof FormulaRecordAggregate)) {
        String ref = new CellReference(cell).formatAsString();
        throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
    }
    FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
    CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());

    CellRange<HSSFCell> result = getCellRange(range);
    // clear all cells in the range
    for (Cell c : result) {
        c.setCellType(CellType.BLANK);
    }
    return result;
}
 

protected void updateFormulas(Area templateArea, Area dependentResultArea) {
    HSSFSheet templateSheet = getTemplateSheetForRangeName(templateWorkbook, templateArea.getName());
    HSSFSheet resultSheet = templateToResultSheetsMapping.get(templateSheet);

    AreaReference area = dependentResultArea.toAreaReference();
    for (CellReference cell : area.getAllReferencedCells()) {
        HSSFCell resultCell = getCellFromReference(cell, resultSheet);

        if (resultCell.getCellType() == CellType.FORMULA) {
            Ptg[] ptgs = HSSFFormulaParser.parse(resultCell.getCellFormula(), resultWorkbook);

            for (Ptg ptg : ptgs) {
                if (ptg instanceof AreaPtg) {
                    areaDependencyManager.updateAreaPtg(templateArea, dependentResultArea, (AreaPtg) ptg);
                } else if (ptg instanceof RefPtg) {
                    areaDependencyManager.updateRefPtg(templateArea, dependentResultArea, (RefPtg) ptg);
                }
            }

            String calculatedFormula = HSSFFormulaParser.toFormulaString(templateWorkbook, ptgs);
            resultCell.setCellFormula(calculatedFormula);
        }
    }
}
 

private static void common(Workbook wb, String filename) throws Throwable{
    Sheet sh = wb.createSheet();
    for(int rownum = 0; rownum < 1000; rownum++){
        Row row = sh.createRow(rownum);
        for(int cellnum = 0; cellnum < 10; cellnum++){
            Cell cell = row.createCell(cellnum);
            String address = new CellReference(cell).formatAsString();
            cell.setCellValue(address);
        }

    }
    // Rows with rownum < 900 are flushed and not accessible
    for(int rownum = 0; rownum < 900; rownum++){
        //Assert.assertNull(sh.getRow(rownum));
    }

    // ther last 100 rows are still in memory
    for(int rownum = 900; rownum < 1000; rownum++){
        //Assert.assertNotNull(sh.getRow(rownum));
    }

    FileOutputStream out = new FileOutputStream(filename);
    wb.write(out);
    out.close();
}
 

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

/**
 * @return <code>true</code> if the specified name is a valid cell reference
 */
private boolean isValidCellReference(String str) {
    //check range bounds against grid max
    boolean result = CellReference.classifyCellReference(str, _ssVersion) == NameType.CELL;

    if(result){
        /*
         * Check if the argument is a function. Certain names can be either a cell reference or a function name
         * depending on the contenxt. Compare the following examples in Excel 2007:
         * (a) LOG10(100) + 1
         * (b) LOG10 + 1
         * In (a) LOG10 is a name of a built-in function. In (b) LOG10 is a cell reference
         */
        boolean isFunc = FunctionMetadataRegistry.getFunctionByName(str.toUpperCase(Locale.ROOT)) != null;
        if(isFunc){
            int savePointer = _pointer;
            resetPointer(_pointer + str.length());
            SkipWhite();
            // open bracket indicates that the argument is a function,
            // the returning value should be false, i.e. "not a valid cell reference"
            result = look != '(';
            resetPointer(savePointer);
        }
    }
    return result;
}
 

/**
 * Removes the {@link ArrayRecord} for the cell group containing the specified cell.
 * The caller should clear (set blank) all cells in the returned range.
 * @return the range of the array formula which was just removed. Never <code>null</code>.
 */
public CellRangeAddress8Bit removeArrayFormula(int rowIndex, int columnIndex) {
	for (ArrayRecord ar : _arrayRecords) {
		if (ar.isInRange(rowIndex, columnIndex)) {
			_arrayRecords.remove(ar);
			return ar.getRange();
		}
	}
	String ref = new CellReference(rowIndex, columnIndex, false, false).formatAsString();
	throw new IllegalArgumentException("Specified cell " + ref
			+ " is not part of an array formula.");
}
 

@Override
      public String toString() {
	StringBuffer sb = new StringBuffer(64);
	CellReference crA = new CellReference(_firstRowIndex, _firstColumnIndex, false, false);
	CellReference crB = new CellReference(_lastRowIndex, _lastColumnIndex, false, false);
	sb.append(getClass().getName());
	sb.append(" [").append(crA.formatAsString()).append(':').append(crB.formatAsString()).append("]");
	return sb.toString();
}
 

public String toString() {
	CellReference cr = new CellReference(getRow(), getColumn());
	return getClass().getName() + "[" +
			_evaluator.getSheetNameRange() +
			'!' +
			cr.formatAsString() +
			"]";
}
 

protected static String prepareName( String name ) {
	char c = name.charAt(0);
	boolean requirePreparation = (!(c == '_' || Character.isLetter(c)) || name.indexOf(' ') != -1);
	if( !requirePreparation ) {
		for( int i = 1; i < name.length(); ++i ) {
			c = name.charAt(i);
			if(!(Character.isLetter(c) || Character.isDigit(c) || c == '_' )) {
				requirePreparation = true;
				break;
			}
		}
	}
	
	if( requirePreparation ) {
		name = name.trim();
		char chars[] = name.toCharArray();
		for( int i = 0; i < name.length(); ++i ) {
			c = chars[i];
			if(!(Character.isLetter(c) || Character.isDigit(c) || c == '_' )) {
				chars[i] = '_';
			}
		}
		name = new String(chars);
	}
	
	NameType refType = CellReference.classifyCellReference( name, SpreadsheetVersion.EXCEL2007 );
	if( ( NameType.CELL == refType ) || ( NameType.COLUMN == refType ) || ( NameType.ROW == refType ) ) {
		name = "_" + name;
	}
	
	return name;
}
 

public SharedFormulaGroup(SharedFormulaRecord sfr, CellReference firstCell) {
	if (!sfr.isInRange(firstCell.getRow(), firstCell.getCol())) {
		throw new IllegalArgumentException("First formula cell " + firstCell.formatAsString()
				+ " is not shared formula range " + sfr.getRange() + ".");
	}
	_sfr = sfr;
	_firstCell = firstCell;
	int width = sfr.getLastColumn() - sfr.getFirstColumn() + 1;
	int height = sfr.getLastRow() - sfr.getFirstRow() + 1;
	_frAggs = new FormulaRecordAggregate[width * height];
	_numberOfFormulas = 0;
}
 

public CellRangeAddress getArrayFormulaRange() {
	if (_sharedFormulaRecord != null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	CellReference expRef = _formulaRecord.getFormula().getExpReference();
	if (expRef == null) {
		throw new IllegalStateException("not an array formula cell.");
	}
	ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
	if (arec == null) {
		throw new IllegalStateException("ArrayRecord was not found for the locator " + expRef.formatAsString());
	}
	CellRangeAddress8Bit a = arec.getRange();
	return new CellRangeAddress(a.getFirstRow(), a.getLastRow(), a.getFirstColumn(),a.getLastColumn());
}
 

/**
 * POI calls this with the value parsed from the cell.
 * @param cellReference The cell reference
 * @param formattedValue The value of the cell
 * @param comment a comment
 */
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    this.output.valueParsed(formattedValue);
    int thisCol = (new CellReference(cellReference)).getCol();

    //Fill missing columns
    int missedCols = thisCol - currentCol - 1;
    for (int i=0; i<missedCols; i++) {
        this.output.valueParsed("");
    }
    currentCol = thisCol;
}
 

public void cell(String cellReference, String formattedValue, XSSFComment comment) {
    if (firstCellOfRow) {
        firstCellOfRow = false;
    } else {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }

    // gracefully handle missing CellRef here in a similar way as
    // XSSFCell does
    if (cellReference == null) {
        cellReference = new CellAddress(currentRow, currentCol).formatAsString();
    }

    // Did we miss any cells?
    int thisCol = (new CellReference(cellReference)).getCol();
    int missedCols = thisCol - currentCol - 1;
    for (int i = 0; i < missedCols; i++) {
        _resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
    }
    currentCol = thisCol;

    // Number or string?
    try {
        Double.parseDouble(formattedValue);
        _resultRowTmp.append(formattedValue);
    } catch (NumberFormatException e) {
        _resultRowTmp.append(formattedValue);
    }
}
 

public boolean isPartOfArrayFormula() {
	if (_sharedFormulaRecord != null) {
		return false;
	}
       CellReference expRef = _formulaRecord.getFormula().getExpReference();
       ArrayRecord arec = expRef == null ? null : _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
	return arec != null;
}
 

public Ptg[] getFormulaTokens() {
	if (_sharedFormulaRecord != null) {
		return _sharedFormulaRecord.getFormulaTokens(_formulaRecord);
	}
	CellReference expRef = _formulaRecord.getFormula().getExpReference();
	if (expRef != null) {
		ArrayRecord arec = _sharedValueManager.getArrayRecord(expRef.getRow(), expRef.getCol());
		return arec.getFormulaTokens();
	}
	return _formulaRecord.getParsedExpression();
}
 

/**
 * return the column number from a cell reference (AA242)
 *
 * @param lastCell
 * @return
 */
public static int getColumnNumberFromCellRef(String lastCell) {

    StringBuilder letters = new StringBuilder();
    // get all letters to remove row number
    StringCharacterIterator iter = new StringCharacterIterator(lastCell);
    for (char c = iter.first(); c != StringCharacterIterator.DONE; c = iter.next()) {
        if (!NumberUtils.isNumber(String.valueOf(c))) {
            letters.append(c);
        }
    }
    // use poi api to calculate column number from an excell column format
    return CellReference.convertColStringToIndex(letters.toString());

}
 

/**
 * Adds the current cell reference to the exception for easier debugging.
 * Would be nice to get the formula text as well, but that seems to require
 * too much digging around and casting to get the FormulaRenderingWorkbook.
 */
private NotImplementedException addExceptionInfo(NotImplementedException inner, int sheetIndex, int rowIndex, int columnIndex) {

    try {
        String sheetName = _workbook.getSheetName(sheetIndex);
        CellReference cr = new CellReference(sheetName, rowIndex, columnIndex, false, false);
        String msg =  "Error evaluating cell " + cr.formatAsString();
        return new NotImplementedException(msg, inner);
    } catch (Exception e) {
        // avoid bombing out during exception handling
        LOG.log(POILogger.ERROR, "Can't add exception info", e);
        return inner; // preserve original exception
    }
}
 

/**
 * @param stringRec may be <code>null</code> if this formula does not have a cached text
 * value.
 * @param svm the {@link SharedValueManager} for the current sheet
 */
public FormulaRecordAggregate(FormulaRecord formulaRec, StringRecord stringRec, SharedValueManager svm) {
	if (svm == null) {
		throw new IllegalArgumentException("sfm must not be null");
	}
	if (formulaRec.hasCachedResultString()) {
		if (stringRec == null) {
			throw new RecordFormatException("Formula record flag is set but String record was not found");
		}
		_stringRecord = stringRec;
	} else {
		// Usually stringRec is null here (in agreement with what the formula rec says).
		// In the case where an extra StringRecord is erroneously present, Excel (2007)
		// ignores it (see bug 46213).
		_stringRecord = null;
	}

	_formulaRecord = formulaRec;
	_sharedValueManager = svm;
	if (formulaRec.isSharedFormula()) {
		CellReference firstCell = formulaRec.getFormula().getExpReference();
		if (firstCell == null) {
			handleMissingSharedFormulaRecord(formulaRec);
		} else {
			_sharedFormulaRecord = svm.linkSharedFormulaRecord(firstCell, this);
		}
	}
}
 

public CellRangeAddress getArrayFormulaRange() {
    if (_cellType != CellType.FORMULA) {
        String ref = new CellReference(this).formatAsString();
        throw new IllegalStateException("Cell " + ref
                + " is not part of an array formula.");
    }
    return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
}
 

public void cell(String cellReference, String formattedValue, XSSFComment comment) {
	if (firstCellOfRow) {
		firstCellOfRow = false;
	} else {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}

	// gracefully handle missing CellRef here in a similar way as
	// XSSFCell does
	if (cellReference == null) {
		cellReference = new CellAddress(currentRow, currentCol).formatAsString();
	}

	// Did we miss any cells?
	int thisCol = (new CellReference(cellReference)).getCol();
	int missedCols = thisCol - currentCol - 1;
	for (int i = 0; i < missedCols; i++) {
		_resultRowTmp.append(ExcelValidator.FIELD_SPLIT);
	}
	currentCol = thisCol;

	// Number or string?
	try {
		Double.parseDouble(formattedValue);
		_resultRowTmp.append(formattedValue);
	} catch (NumberFormatException e) {
		_resultRowTmp.append(formattedValue);
	}
}
 

protected final String formatReferenceAsString() {
    CellReference topLeft = new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative());
    CellReference botRight = new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative());

    if(AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
        return (new AreaReference(topLeft, botRight, SpreadsheetVersion.EXCEL97)).formatAsString();
    }
    return topLeft.formatAsString() + ":" + botRight.formatAsString();
}
 

private Map<String, Integer> toColNamesMap(Row headerRow) {
  if (headerRow == null) return null;

  Map<String, Integer> columnIdx = new LinkedHashMap<>();
  int i = 0;
  for (Iterator<Cell> it = headerRow.cellIterator(); it.hasNext(); ) {
    try {
      String header =
          AbstractCellProcessor.processCell(ExcelUtils.toValue(it.next()), true, cellProcessors);
      if (header != null) {
        if (columnIdx.containsKey(header)) {
          throw new MolgenisDataException(
              format(
                  "Duplicate column header '%s' in sheet '%s' not allowed",
                  header, headerRow.getSheet().getSheetName()));
        }
        columnIdx.put(header, i++);
      }
    } catch (final IllegalStateException ex) {
      final int row = headerRow.getRowNum();
      final String column = CellReference.convertNumToColString(i);
      throw new IllegalStateException(
          "Invalid value at [" + sheet.getSheetName() + "] " + column + row + 1, ex);
    }
  }
  return columnIdx;
}
 

/**
 * 文字列の形式のセルのアドレスを、Point形式に変換する。
 * @param address
 * @return
 */
public static Point toPointAddress(final String address) {
    ArgUtils.notEmpty(address, "address");

    CellReference ref = new CellReference(address);
    return new Point(ref.getCol(), ref.getRow());
}