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