Examples with SharedFormula org.apache.poi.ss.formula.SharedFormula used on opensource projects

Example 1 with SharedFormula

use of org.apache.poi.ss.formula.SharedFormula in project poi by apache.

the class XSSFCell method convertSharedFormula.

/**
     * 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, XSSFEvaluationWorkbook fpb) {
    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);
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
    Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex, getRowIndex());
    Ptg[] fmla = sf.convertSharedFormulas(ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
    return FormulaRenderer.toFormulaString(fpb, fmla);
}

Example 2 with SharedFormula

use of org.apache.poi.ss.formula.SharedFormula in project poi by apache.

the class SharedFormulaRecord method getFormulaTokens.

/**
     * @return the equivalent {@link Ptg} array that the formula would have, were it not shared.
     */
public Ptg[] getFormulaTokens(FormulaRecord formula) {
    int formulaRow = formula.getRow();
    int formulaColumn = formula.getColumn();
    //Sanity checks
    if (!isInRange(formulaRow, formulaColumn)) {
        throw new RuntimeException("Shared Formula Conversion: Coding Error");
    }
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
    return sf.convertSharedFormulas(field_7_parsed_expr.getTokens(), formulaRow, formulaColumn);
}

Example 3 with SharedFormula

use of org.apache.poi.ss.formula.SharedFormula in project poi by apache.

the class TestSharedFormulaRecord method testConvertSharedFormulas.

public void testConvertSharedFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFEvaluationWorkbook fpb = HSSFEvaluationWorkbook.create(wb);
    Ptg[] sharedFormula, convertedFormula;
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
    sharedFormula = FormulaParser.parse("A2", fpb, FormulaType.CELL, -1);
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 0, 0);
    confirmOperandClasses(sharedFormula, convertedFormula);
    //conversion relative to [0,0] should return the original formula
    assertEquals("A2", FormulaRenderer.toFormulaString(fpb, convertedFormula));
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 1, 0);
    confirmOperandClasses(sharedFormula, convertedFormula);
    //one row down
    assertEquals("A3", FormulaRenderer.toFormulaString(fpb, convertedFormula));
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 1, 1);
    confirmOperandClasses(sharedFormula, convertedFormula);
    //one row down and one cell right
    assertEquals("B3", FormulaRenderer.toFormulaString(fpb, convertedFormula));
    sharedFormula = FormulaParser.parse("SUM(A1:C1)", fpb, FormulaType.CELL, -1);
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 0, 0);
    confirmOperandClasses(sharedFormula, convertedFormula);
    assertEquals("SUM(A1:C1)", FormulaRenderer.toFormulaString(fpb, convertedFormula));
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 1, 0);
    confirmOperandClasses(sharedFormula, convertedFormula);
    assertEquals("SUM(A2:C2)", FormulaRenderer.toFormulaString(fpb, convertedFormula));
    convertedFormula = sf.convertSharedFormulas(sharedFormula, 1, 1);
    confirmOperandClasses(sharedFormula, convertedFormula);
    assertEquals("SUM(B2:D2)", FormulaRenderer.toFormulaString(fpb, convertedFormula));
}

Example 4 with SharedFormula

use of org.apache.poi.ss.formula.SharedFormula in project poi by apache.

the class TestSharedFormulaRecord method testConvertSharedFormulasOperandClasses_bug45123.

/**
     * The method <tt>SharedFormulaRecord.convertSharedFormulas()</tt> converts formulas from
     * 'shared formula' to 'single cell formula' format.  It is important that token operand
     * classes are preserved during this transformation, because Excel may not tolerate the
     * incorrect encoding.  The formula here is one such example (Excel displays #VALUE!).
     */
public void testConvertSharedFormulasOperandClasses_bug45123() {
    LittleEndianInput in = TestcaseRecordInputStream.createLittleEndian(SHARED_FORMULA_WITH_REF_ARRAYS_DATA);
    int encodedLen = in.readUShort();
    Ptg[] sharedFormula = Ptg.readTokens(encodedLen, in);
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL97);
    Ptg[] convertedFormula = sf.convertSharedFormulas(sharedFormula, 100, 200);
    RefPtg refPtg = (RefPtg) convertedFormula[1];
    assertEquals("$C101", refPtg.toFormulaString());
    if (refPtg.getPtgClass() == Ptg.CLASS_REF) {
        throw new AssertionFailedError("Identified bug 45123");
    }
    confirmOperandClasses(sharedFormula, convertedFormula);
}