Java Code Examples for org.apache.poi.hssf.model.HSSFFormulaParser#parse()

@SuppressWarnings("resource")
   private Ptg[] createListFormula(HSSFSheet sheet) {

	if (_explicitListValues == null) {
           HSSFWorkbook wb = sheet.getWorkbook();
           // formula is parsed with slightly different RVA rules: (root node type must be 'reference')
		return HSSFFormulaParser.parse(_formula1, wb, FormulaType.DATAVALIDATION_LIST, wb.getSheetIndex(sheet));
		// To do: Excel places restrictions on the available operations within a list formula.
		// Some things like union and intersection are not allowed.
	}
	// explicit list was provided
	StringBuffer sb = new StringBuffer(_explicitListValues.length * 16);
	for (int i = 0; i < _explicitListValues.length; i++) {
		if (i > 0) {
			sb.append('\0'); // list delimiter is the nul char
		}
		sb.append(_explicitListValues[i]);
	
	}
	return new Ptg[] { new StringPtg(sb.toString()), };
}
 

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

/**
 * @return The parsed token array representing the formula or value specified. 
 * Empty array if both formula and value are <code>null</code>
 */
   @SuppressWarnings("resource")
private static Ptg[] convertDoubleFormula(String formula, Double value, HSSFSheet sheet) {
	if (formula == null) {
		if (value == null) {
			return Ptg.EMPTY_PTG_ARRAY;
		}
		return new Ptg[] { new NumberPtg(value.doubleValue()), };
	}
	if (value != null) {
		throw new IllegalStateException("Both formula and value cannot be present");
	}
       HSSFWorkbook wb = sheet.getWorkbook();
	return HSSFFormulaParser.parse(formula, wb, FormulaType.CELL, wb.getSheetIndex(sheet));
}
 

@Override
public CellRange<HSSFCell> setArrayFormula(String formula, CellRangeAddress range) {
    // make sure the formula parses OK first
    int sheetIndex = _workbook.getSheetIndex(this);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _workbook, FormulaType.ARRAY, sheetIndex);
    CellRange<HSSFCell> cells = getCellRange(range);

    for (HSSFCell c : cells) {
        c.setCellArrayFormula(range);
    }
    HSSFCell mainArrayFormulaCell = cells.getTopLeftCell();
    FormulaRecordAggregate agg = (FormulaRecordAggregate) mainArrayFormulaCell.getCellValueRecord();
    agg.setArrayFormula(range, ptgs);
    return cells;
}
 

public void setCellFormula(String formula) {
    if(isPartOfArrayFormulaGroup()){
        notifyArrayFormulaChanging();
    }

    int row=_record.getRow();
    short col=_record.getColumn();
    short styleIndex=_record.getXFIndex();

    if (formula==null) {
        notifyFormulaChanging();
        setCellType(CellType.BLANK, false, row, col, styleIndex);
        return;
    }
    int sheetIndex = _book.getSheetIndex(_sheet);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _book, FormulaType.CELL, sheetIndex);
    setCellType(CellType.FORMULA, false, row, col, styleIndex);
    FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
    FormulaRecord frec = agg.getFormulaRecord();
    frec.setOptions((short) 2);
    frec.setValue(0);

    //only set to default if there is no extended format index already set
    if (agg.getXFIndex() == (short)0) {
        agg.setXFIndex((short) 0x0f);
    }
    agg.setParsedExpression(ptgs);
}
 

public void setRefersToFormula(String formulaText) {
    Ptg[] ptgs = HSSFFormulaParser.parse(formulaText, _book, FormulaType.NAMEDRANGE, getSheetIndex());
    _definedNameRec.setNameDefinition(ptgs);
}
 

/**
 * TODO - parse conditional format formulas properly i.e. produce tRefN and tAreaN instead of tRef and tArea
 * this call will produce the wrong results if the formula contains any cell references
 * One approach might be to apply the inverse of SharedFormulaRecord.convertSharedFormulas(Stack, int, int)
 * Note - two extra parameters (rowIx & colIx) will be required. They probably come from one of the Region objects.
 *
 * @param formula  The formula to parse, excluding the leading equals sign.
 * @param sheet  The sheet that the formula is on.
 * @return <code>null</code> if <tt>formula</tt> was null.
 */
public static Ptg[] parseFormula(String formula, HSSFSheet sheet) {
    if(formula == null) {
        return null;
    }
    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    return HSSFFormulaParser.parse(formula, sheet.getWorkbook(), FormulaType.CELL, sheetIndex);
}