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

Example 1 with NamePtg

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

the class TestFormulaParser method testMacroFunction.

@Test
public void testMacroFunction() throws IOException {
    // testNames.xls contains a VB function called 'myFunc'
    final String testFile = "testNames.xls";
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(testFile);
    try {
        HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(wb);
        //Expected ptg stack: [NamePtg(myFunc), StringPtg(arg), (additional operands go here...), FunctionPtg(myFunc)]
        Ptg[] ptg = FormulaParser.parse("myFunc(\"arg\")", book, FormulaType.CELL, -1);
        assertEquals(3, ptg.length);
        // the name gets encoded as the first operand on the stack
        NamePtg tname = (NamePtg) ptg[0];
        assertEquals("myFunc", tname.toFormulaString(book));
        // the function's arguments are pushed onto the stack from left-to-right as OperandPtgs
        StringPtg arg = (StringPtg) ptg[1];
        assertEquals("arg", arg.getValue());
        // The external FunctionPtg is the last Ptg added to the stack
        // During formula evaluation, this Ptg pops off the the appropriate number of
        // arguments (getNumberOfOperands()) and pushes the result on the stack
        //FuncVarPtg
        AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[2];
        assertTrue(tfunc.isExternalFunction());
        // confirm formula parsing is case-insensitive
        FormulaParser.parse("mYfUnC(\"arg\")", book, FormulaType.CELL, -1);
        // confirm formula parsing doesn't care about argument count or type
        // this should only throw an error when evaluating the formula.
        FormulaParser.parse("myFunc()", book, FormulaType.CELL, -1);
        FormulaParser.parse("myFunc(\"arg\", 0, TRUE)", book, FormulaType.CELL, -1);
        // A completely unknown formula name (not saved in workbook) should still be parseable and renderable
        // but will throw an NotImplementedFunctionException or return a #NAME? error value if evaluated.
        FormulaParser.parse("yourFunc(\"arg\")", book, FormulaType.CELL, -1);
        // Verify that myFunc and yourFunc were successfully added to Workbook names
        HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb);
        try {
            // HSSFWorkbook/EXCEL97-specific side-effects user-defined function names must be added to Workbook's defined names in order to be saved.
            assertNotNull(wb2.getName("myFunc"));
            assertEqualsIgnoreCase("myFunc", wb2.getName("myFunc").getNameName());
            assertNotNull(wb2.getName("yourFunc"));
            assertEqualsIgnoreCase("yourFunc", wb2.getName("yourFunc").getNameName());
        // Manually check to make sure file isn't corrupted
        // TODO: develop a process for occasionally manually reviewing workbooks
        // to verify workbooks are not corrupted
        /*
                final File fileIn = HSSFTestDataSamples.getSampleFile(testFile);
                final File reSavedFile = new File(fileIn.getParentFile(), fileIn.getName().replace(".xls", "-saved.xls"));
                FileOutputStream fos = new FileOutputStream(reSavedFile);
                wb2.write(fos);
                fos.close();
                */
        } finally {
            wb2.close();
        }
    } finally {
        wb.close();
    }
}

Example 2 with NamePtg

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

the class TestNameRecord method testBug57923.

public void testBug57923() {
    NameRecord record = new NameRecord();
    assertEquals(0, record.getExternSheetNumber());
    record.setNameDefinition(new Ptg[] {});
    assertEquals(0, record.getExternSheetNumber());
    record.setNameDefinition(new Ptg[] { new NamePtg(1) });
    assertEquals(0, record.getExternSheetNumber());
    record.setNameDefinition(new Ptg[] { new Area3DPtg("area", 1) });
    assertEquals(1, record.getExternSheetNumber());
    record.setNameDefinition(new Ptg[] { new Ref3DPtg("A1", 1) });
    assertEquals(1, record.getExternSheetNumber());
}

Example 3 with NamePtg

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

the class TestFormulas method testSheetLevelFormulas.

/**
     * Test creation / evaluation of formulas with sheet-level names
     */
@Test
public void testSheetLevelFormulas() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFRow row;
    HSSFSheet sh1 = wb.createSheet("Sheet1");
    HSSFName nm1 = wb.createName();
    nm1.setNameName("sales_1");
    nm1.setSheetIndex(0);
    nm1.setRefersToFormula("Sheet1!$A$1");
    row = sh1.createRow(0);
    row.createCell(0).setCellValue(3);
    row.createCell(1).setCellFormula("sales_1");
    row.createCell(2).setCellFormula("sales_1*2");
    HSSFSheet sh2 = wb.createSheet("Sheet2");
    HSSFName nm2 = wb.createName();
    nm2.setNameName("sales_1");
    nm2.setSheetIndex(1);
    nm2.setRefersToFormula("Sheet2!$A$1");
    row = sh2.createRow(0);
    row.createCell(0).setCellValue(5);
    row.createCell(1).setCellFormula("sales_1");
    row.createCell(2).setCellFormula("sales_1*3");
    //check that NamePtg refers to the correct NameRecord
    Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0);
    NamePtg nPtg1 = (NamePtg) ptgs1[0];
    assertSame(nm1, wb.getNameAt(nPtg1.getIndex()));
    Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1);
    NamePtg nPtg2 = (NamePtg) ptgs2[0];
    assertSame(nm2, wb.getNameAt(nPtg2.getIndex()));
    //check that the formula evaluator returns the correct result
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0);
    assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0);
    assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0);
    assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0);
    wb.close();
}