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

Example 1 with Area3DPxg

use of org.apache.poi.ss.formula.ptg.Area3DPxg in project poi by apache.

the class OperationEvaluationContext method getExternalNameXEval.

private ValueEval getExternalNameXEval(ExternalName externName, String workbookName) {
    try {
        // Fetch the workbook this refers to, and the name as defined with that
        WorkbookEvaluator refWorkbookEvaluator = _bookEvaluator.getOtherWorkbookEvaluator(workbookName);
        EvaluationName evaluationName = refWorkbookEvaluator.getName(externName.getName(), externName.getIx() - 1);
        if (evaluationName != null && evaluationName.hasFormula()) {
            if (evaluationName.getNameDefinition().length > 1) {
                throw new RuntimeException("Complex name formulas not supported yet");
            }
            // Need to evaluate the reference in the context of the other book
            OperationEvaluationContext refWorkbookContext = new OperationEvaluationContext(refWorkbookEvaluator, refWorkbookEvaluator.getWorkbook(), -1, -1, -1, _tracker);
            Ptg ptg = evaluationName.getNameDefinition()[0];
            if (ptg instanceof Ref3DPtg) {
                Ref3DPtg ref3D = (Ref3DPtg) ptg;
                return refWorkbookContext.getRef3DEval(ref3D);
            } else if (ptg instanceof Ref3DPxg) {
                Ref3DPxg ref3D = (Ref3DPxg) ptg;
                return refWorkbookContext.getRef3DEval(ref3D);
            } else if (ptg instanceof Area3DPtg) {
                Area3DPtg area3D = (Area3DPtg) ptg;
                return refWorkbookContext.getArea3DEval(area3D);
            } else if (ptg instanceof Area3DPxg) {
                Area3DPxg area3D = (Area3DPxg) ptg;
                return refWorkbookContext.getArea3DEval(area3D);
            }
        }
        return ErrorEval.REF_INVALID;
    } catch (WorkbookNotFoundException wnfe) {
        return ErrorEval.REF_INVALID;
    }
}

Example 2 with Area3DPxg

use of org.apache.poi.ss.formula.ptg.Area3DPxg in project poi by apache.

the class TestXSSFFormulaParser method parseStructuredReferences.

@Test
public void parseStructuredReferences() throws IOException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("StructuredReferences.xlsx");
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
    Ptg[] ptgs;
    /*
        The following cases are tested (copied from FormulaParser.parseStructuredReference)
           1 Table1[col]
           2 Table1[[#Totals],[col]]
           3 Table1[#Totals]
           4 Table1[#All]
           5 Table1[#Data]
           6 Table1[#Headers]
           7 Table1[#Totals]
           8 Table1[#This Row]
           9 Table1[[#All],[col]]
          10 Table1[[#Headers],[col]]
          11 Table1[[#Totals],[col]]
          12 Table1[[#All],[col1]:[col2]]
          13 Table1[[#Data],[col1]:[col2]]
          14 Table1[[#Headers],[col1]:[col2]]
          15 Table1[[#Totals],[col1]:[col2]]
          16 Table1[[#Headers],[#Data],[col2]]
          17 Table1[[#This Row], [col1]]
          18 Table1[ [col1]:[col2] ]
        */
    final String tbl = "\\_Prime.1";
    final String noTotalsRowReason = ": Tables without a Totals row should return #REF! on [#Totals]";
    ////// Case 1: Evaluate Table1[col] with apostrophe-escaped #-signs ////////
    ptgs = parse(fpb, "SUM(" + tbl + "[calc='#*'#])");
    assertEquals(2, ptgs.length);
    // Area3DPxg [sheet=Table ! A2:A7]
    assertTrue(ptgs[0] instanceof Area3DPxg);
    Area3DPxg ptg0 = (Area3DPxg) ptgs[0];
    assertEquals("Table", ptg0.getSheetName());
    assertEquals("A2:A7", ptg0.format2DRefAsString());
    // Note: structured references are evaluated and resolved to regular 3D area references.
    assertEquals("Table!A2:A7", ptg0.toFormulaString());
    // AttrPtg [sum ]
    assertTrue(ptgs[1] instanceof AttrPtg);
    AttrPtg ptg1 = (AttrPtg) ptgs[1];
    assertTrue(ptg1.isSum());
    ////// Case 1: Evaluate "Table1[col]" ////////
    ptgs = parse(fpb, tbl + "[Name]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[col]", "Table!B2:B7", ptgs[0].toFormulaString());
    ////// Case 2: Evaluate "Table1[[#Totals],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#Totals],[col]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Totals],[col]]" + noTotalsRowReason, ErrPtg.REF_INVALID, ptgs[0]);
    ////// Case 3: Evaluate "Table1[#Totals]" ////////
    ptgs = parse(fpb, tbl + "[#Totals]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#Totals]" + noTotalsRowReason, ErrPtg.REF_INVALID, ptgs[0]);
    ////// Case 4: Evaluate "Table1[#All]" ////////
    ptgs = parse(fpb, tbl + "[#All]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#All]", "Table!A1:C7", ptgs[0].toFormulaString());
    ////// Case 5: Evaluate "Table1[#Data]" (excludes Header and Data rows) ////////
    ptgs = parse(fpb, tbl + "[#Data]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#Data]", "Table!A2:C7", ptgs[0].toFormulaString());
    ////// Case 6: Evaluate "Table1[#Headers]" ////////
    ptgs = parse(fpb, tbl + "[#Headers]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#Headers]", "Table!A1:C1", ptgs[0].toFormulaString());
    ////// Case 7: Evaluate "Table1[#Totals]" ////////
    ptgs = parse(fpb, tbl + "[#Totals]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#Totals]" + noTotalsRowReason, ErrPtg.REF_INVALID, ptgs[0]);
    ////// Case 8: Evaluate "Table1[#This Row]" ////////
    ptgs = parse(fpb, tbl + "[#This Row]", 2);
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#This Row]", "Table!A3:C3", ptgs[0].toFormulaString());
    ////// Evaluate "Table1[@]" (equivalent to "Table1[#This Row]") ////////
    ptgs = parse(fpb, tbl + "[@]", 2);
    assertEquals(1, ptgs.length);
    assertEquals("Table!A3:C3", ptgs[0].toFormulaString());
    ////// Evaluate "Table1[#This Row]" when rowIndex is outside Table ////////
    ptgs = parse(fpb, tbl + "[#This Row]", 10);
    assertEquals(1, ptgs.length);
    assertEquals("Table1[#This Row]", ErrPtg.VALUE_INVALID, ptgs[0]);
    ////// Evaluate "Table1[@]" when rowIndex is outside Table ////////
    ptgs = parse(fpb, tbl + "[@]", 10);
    assertEquals(1, ptgs.length);
    assertEquals("Table1[@]", ErrPtg.VALUE_INVALID, ptgs[0]);
    ////// Evaluate "Table1[[#Data],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#Data], [Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Data],[col]]", "Table!C2:C7", ptgs[0].toFormulaString());
    ////// Case 9: Evaluate "Table1[[#All],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#All], [Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#All],[col]]", "Table!C1:C7", ptgs[0].toFormulaString());
    ////// Case 10: Evaluate "Table1[[#Headers],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#Headers], [Number]]");
    assertEquals(1, ptgs.length);
    // also acceptable: Table1!B1
    assertEquals("Table1[[#Headers],[col]]", "Table!C1:C1", ptgs[0].toFormulaString());
    ////// Case 11: Evaluate "Table1[[#Totals],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#Totals],[Name]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Totals],[col]]" + noTotalsRowReason, ErrPtg.REF_INVALID, ptgs[0]);
    ////// Case 12: Evaluate "Table1[[#All],[col1]:[col2]]" ////////
    ptgs = parse(fpb, tbl + "[[#All], [Name]:[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#All],[col1]:[col2]]", "Table!B1:C7", ptgs[0].toFormulaString());
    ////// Case 13: Evaluate "Table1[[#Data],[col]:[col2]]" ////////
    ptgs = parse(fpb, tbl + "[[#Data], [Name]:[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Data],[col]:[col2]]", "Table!B2:C7", ptgs[0].toFormulaString());
    ////// Case 14: Evaluate "Table1[[#Headers],[col1]:[col2]]" ////////
    ptgs = parse(fpb, tbl + "[[#Headers], [Name]:[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Headers],[col1]:[col2]]", "Table!B1:C1", ptgs[0].toFormulaString());
    ////// Case 15: Evaluate "Table1[[#Totals],[col]:[col2]]" ////////
    ptgs = parse(fpb, tbl + "[[#Totals], [Name]:[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Totals],[col]:[col2]]" + noTotalsRowReason, ErrPtg.REF_INVALID, ptgs[0]);
    ////// Case 16: Evaluate "Table1[[#Headers],[#Data],[col]]" ////////
    ptgs = parse(fpb, tbl + "[[#Headers],[#Data],[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[#Headers],[#Data],[col]]", "Table!C1:C7", ptgs[0].toFormulaString());
    ////// Case 17: Evaluate "Table1[[#This Row], [col1]]" ////////
    ptgs = parse(fpb, tbl + "[[#This Row], [Number]]", 2);
    assertEquals(1, ptgs.length);
    // also acceptable: Table!C3
    assertEquals("Table1[[#This Row], [col1]]", "Table!C3:C3", ptgs[0].toFormulaString());
    ////// Case 18: Evaluate "Table1[[col]:[col2]]" ////////
    ptgs = parse(fpb, tbl + "[[Name]:[Number]]");
    assertEquals(1, ptgs.length);
    assertEquals("Table1[[col]:[col2]]", "Table!B2:C7", ptgs[0].toFormulaString());
    wb.close();
}

Example 3 with Area3DPxg

use of org.apache.poi.ss.formula.ptg.Area3DPxg in project poi by apache.

the class Indirect method evaluateIndirect.

private static ValueEval evaluateIndirect(final OperationEvaluationContext ec, String text, boolean isA1style) {
    // Search backwards for '!' because sheet names can contain '!'
    int plingPos = text.lastIndexOf('!');
    String workbookName;
    String sheetName;
    // whitespace around this gets trimmed OK
    String refText;
    if (plingPos < 0) {
        workbookName = null;
        sheetName = null;
        refText = text;
    } else {
        String[] parts = parseWorkbookAndSheetName(text.subSequence(0, plingPos));
        if (parts == null) {
            return ErrorEval.REF_INVALID;
        }
        workbookName = parts[0];
        sheetName = parts[1];
        refText = text.substring(plingPos + 1);
    }
    if (Table.isStructuredReference.matcher(refText).matches()) {
        // The argument is structured reference
        Area3DPxg areaPtg = null;
        try {
            areaPtg = FormulaParser.parseStructuredReference(refText, (FormulaParsingWorkbook) ec.getWorkbook(), ec.getRowIndex());
        } catch (FormulaParseException e) {
            return ErrorEval.REF_INVALID;
        }
        return ec.getArea3DEval(areaPtg);
    } else {
        // The argument is regular reference
        String refStrPart1;
        String refStrPart2;
        int colonPos = refText.indexOf(':');
        if (colonPos < 0) {
            refStrPart1 = refText.trim();
            refStrPart2 = null;
        } else {
            refStrPart1 = refText.substring(0, colonPos).trim();
            refStrPart2 = refText.substring(colonPos + 1).trim();
        }
        return ec.getDynamicReference(workbookName, sheetName, refStrPart1, refStrPart2, isA1style);
    }
}