private void setFormula(String formula, int formulaType) {
XSSFWorkbook wb = _row.getSheet().getWorkbook();
if (formula == null) {
wb.onDeleteFormula(this);
if (_cell.isSetF()) _cell.unsetF();
return;
}
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
// validate through the FormulaParser
FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()));
CTCellFormula f = CTCellFormula.Factory.newInstance();
f.setStringValue(formula);
_cell.setF(f);
if (_cell.isSetV()) _cell.unsetV();
}
/**
* 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) {
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);
XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex);
Ptg[] fmla =
sf.convertSharedFormulas(
ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
return FormulaRenderer.toFormulaString(fpb, fmla);
}
/**
* Parse a formula into a array of tokens
*
* @param formula the formula to parse
* @param workbook the parent workbook
* @param formulaType the type of the formula, see {@link FormulaType}
* @param sheetIndex the 0-based index of the sheet this formula belongs to. The sheet index is
* required to resolve sheet-level names. <code>-1</code> means that the scope of the name
* will be ignored and the parser will match names only by name
* @return array of parsed tokens
* @throws FormulaParseException if the formula has incorrect syntax or is otherwise invalid
*/
public static Ptg[] parse(
String formula, FormulaParsingWorkbook workbook, int formulaType, int sheetIndex) {
FormulaParser fp = new FormulaParser(formula, workbook, sheetIndex);
fp.parse();
return fp.getRPNPtg(formulaType);
}