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

Example 6 with AreaPtg

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

the class TestFormulaShifter method confirmAreaCopy.

private static void confirmAreaCopy(AreaPtg aptg, int firstRowCopied, int lastRowCopied, int rowOffset, int expectedFirstRow, int expectedLastRow, boolean expectedChanged) {
    // clone so we can re-use aptg in calling method
    final AreaPtg copyPtg = (AreaPtg) aptg.copy();
    final Ptg[] ptgs = { copyPtg };
    final FormulaShifter fs = FormulaShifter.createForRowCopy(0, null, firstRowCopied, lastRowCopied, rowOffset, SpreadsheetVersion.EXCEL2007);
    final boolean actualChanged = fs.adjustFormula(ptgs, 0);
    // DeletedAreaRef
    if (expectedFirstRow < 0 || expectedLastRow < 0) {
        assertEquals("Reference should have shifted off worksheet, producing #REF! error: " + ptgs[0], AreaErrPtg.class, ptgs[0].getClass());
        return;
    }
    assertEquals("Should this AreaPtg change due to row copy?", expectedChanged, actualChanged);
    // expected to change in place (although this is not a strict requirement)
    assertEquals("AreaPtgs should be modified in-place when a row containing the AreaPtg is copied", copyPtg, ptgs[0]);
    assertEquals("AreaPtg first row", expectedFirstRow, copyPtg.getFirstRow());
    assertEquals("AreaPtg last row", expectedLastRow, copyPtg.getLastRow());
}

Example 7 with AreaPtg

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

the class TestFormulaEvaluatorBugs method test44410.

/**
     * Bug 44410: SUM(C:C) is valid in excel, and means a sum
     *  of all the rows in Column C
     */
@Test
public void test44410() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("SingleLetterRanges.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(wb);
    // =index(C:C,2,1) -> 2
    HSSFRow rowIDX = sheet.getRow(3);
    // =sum(C:C) -> 6
    HSSFRow rowSUM = sheet.getRow(4);
    // =sum(C:D) -> 66
    HSSFRow rowSUM2D = sheet.getRow(5);
    // Test the sum
    HSSFCell cellSUM = rowSUM.getCell(0);
    FormulaRecordAggregate frec = (FormulaRecordAggregate) cellSUM.getCellValueRecord();
    Ptg[] ops = frec.getFormulaRecord().getParsedExpression();
    assertEquals(2, ops.length);
    assertEquals(AreaPtg.class, ops[0].getClass());
    assertEquals(FuncVarPtg.class, ops[1].getClass());
    // Actually stored as C1 to C65536
    // (last row is -1 === 65535)
    AreaPtg ptg = (AreaPtg) ops[0];
    assertEquals(2, ptg.getFirstColumn());
    assertEquals(2, ptg.getLastColumn());
    assertEquals(0, ptg.getFirstRow());
    assertEquals(65535, ptg.getLastRow());
    assertEquals("C:C", ptg.toFormulaString());
    // Will show as C:C, but won't know how many
    // rows it covers as we don't have the sheet
    // to hand when turning the Ptgs into a string
    assertEquals("SUM(C:C)", cellSUM.getCellFormula());
    // But the evaluator knows the sheet, so it
    // can do it properly
    assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0);
    // Test the index
    // Again, the formula string will be right but
    // lacking row count, evaluated will be right
    HSSFCell cellIDX = rowIDX.getCell(0);
    assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula());
    assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0);
    // Across two colums
    HSSFCell cellSUM2D = rowSUM2D.getCell(0);
    assertEquals("SUM(C:D)", cellSUM2D.getCellFormula());
    assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0);
    wb.close();
}

Example 8 with AreaPtg

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

the class XSSFRowShifter method shiftRange.

private static CellRangeAddress shiftRange(FormulaShifter shifter, CellRangeAddress cra, int currentExternSheetIx) {
    // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here
    AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false);
    Ptg[] ptgs = { aptg };
    if (!shifter.adjustFormula(ptgs, currentExternSheetIx)) {
        return cra;
    }
    Ptg ptg0 = ptgs[0];
    if (ptg0 instanceof AreaPtg) {
        AreaPtg bptg = (AreaPtg) ptg0;
        return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn());
    }
    if (ptg0 instanceof AreaErrPtg) {
        return null;
    }
    throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")");
}

Example 9 with AreaPtg

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

the class TestLbsDataSubRecord method test_47701.

/**
     * test read-write round trip
     * test data was taken from 47701.xls
     */
public void test_47701() {
    byte[] data = HexRead.readFromString("15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " + "00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " + "00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " + "00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " + "00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " + "10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " + "00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " + "00, 00, 08, 00, 00, 00");
    RecordInputStream in = TestcaseRecordInputStream.create(ObjRecord.sid, data);
    // check read OK
    ObjRecord record = new ObjRecord(in);
    assertEquals(3, record.getSubRecords().size());
    SubRecord sr = record.getSubRecords().get(2);
    assertTrue(sr instanceof LbsDataSubRecord);
    LbsDataSubRecord lbs = (LbsDataSubRecord) sr;
    assertEquals(4, lbs.getNumberOfItems());
    assertTrue(lbs.getFormula() instanceof AreaPtg);
    AreaPtg ptg = (AreaPtg) lbs.getFormula();
    CellRangeAddress range = new CellRangeAddress(ptg.getFirstRow(), ptg.getLastRow(), ptg.getFirstColumn(), ptg.getLastColumn());
    assertEquals("H10:H13", range.formatAsString());
    // check that it re-serializes to the same data
    byte[] ser = record.serialize();
    TestcaseRecordInputStream.confirmRecordEncoding(ObjRecord.sid, data, ser);
}

Example 10 with AreaPtg

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

the class TestFormulaShifter method testCopyAreasSourceRowsRelAbs.

@Test
public void testCopyAreasSourceRowsRelAbs() {
    // all these operations are on an area ref spanning rows 10 to 20
    final AreaPtg aptg = createAreaPtg(10, 20, true, false);
    // Only first row should move
    confirmAreaCopy(aptg, 0, 30, 20, 20, 30, true);
    //DeletedRef
    confirmAreaCopy(aptg, 15, 25, -15, -1, -1, true);
}