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

Example 1 with DefaultUDFFinder

use of org.apache.poi.ss.formula.udf.DefaultUDFFinder in project poi by apache.

the class UserDefinedFunctionExample method main.

public static void main(String[] args) throws Exception {
    if (args.length != 2) {
        // e.g. src/examples/src/org/apache/poi/ss/examples/formula/mortgage-calculation.xls Sheet1!B4
        System.out.println("usage: UserDefinedFunctionExample fileName cellId");
        return;
    }
    System.out.println("fileName: " + args[0]);
    System.out.println("cell: " + args[1]);
    File workbookFile = new File(args[0]);
    Workbook workbook = WorkbookFactory.create(workbookFile, null, true);
    try {
        String[] functionNames = { "calculatePayment" };
        FreeRefFunction[] functionImpls = { new CalculateMortgage() };
        UDFFinder udfToolpack = new DefaultUDFFinder(functionNames, functionImpls);
        // register the user-defined function in the workbook
        workbook.addToolPack(udfToolpack);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellReference cr = new CellReference(args[1]);
        String sheetName = cr.getSheetName();
        Sheet sheet = workbook.getSheet(sheetName);
        int rowIdx = cr.getRow();
        int colIdx = cr.getCol();
        Row row = sheet.getRow(rowIdx);
        Cell cell = row.getCell(colIdx);
        CellValue value = evaluator.evaluate(cell);
        System.out.println("returns value: " + value);
    } finally {
        workbook.close();
    }
}

Example 2 with DefaultUDFFinder

use of org.apache.poi.ss.formula.udf.DefaultUDFFinder in project poi by apache.

the class TestWorkbook method testAddNameX.

@Test
public void testAddNameX() throws IOException {
    HSSFWorkbook hwb = new HSSFWorkbook();
    InternalWorkbook wb = TestHSSFWorkbook.getInternalWorkbook(hwb);
    assertNotNull(wb.getNameXPtg("ISODD", AggregatingUDFFinder.DEFAULT));
    FreeRefFunction NotImplemented = new FreeRefFunction() {

        @Override
        public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
            throw new RuntimeException("not implemented");
        }
    };
    /*
         * register the two test UDFs in a UDF finder, to be passed to the evaluator
         */
    UDFFinder udff1 = new DefaultUDFFinder(new String[] { "myFunc" }, new FreeRefFunction[] { NotImplemented });
    UDFFinder udff2 = new DefaultUDFFinder(new String[] { "myFunc2" }, new FreeRefFunction[] { NotImplemented });
    UDFFinder udff = new AggregatingUDFFinder(udff1, udff2);
    assertNotNull(wb.getNameXPtg("myFunc", udff));
    assertNotNull(wb.getNameXPtg("myFunc2", udff));
    // myFunc3 is unknown
    assertNull(wb.getNameXPtg("myFunc3", udff));
    hwb.close();
}

Example 3 with DefaultUDFFinder

use of org.apache.poi.ss.formula.udf.DefaultUDFFinder in project poi by apache.

the class ExcelAntWorkbookUtil method getFunctions.

/**
     * returns a UDFFinder that contains all of the functions added.
     *
     * @return
     */
protected UDFFinder getFunctions() {
    String[] names = new String[xlsMacroList.size()];
    FreeRefFunction[] functions = new FreeRefFunction[xlsMacroList.size()];
    int x = 0;
    for (Map.Entry<String, FreeRefFunction> entry : xlsMacroList.entrySet()) {
        names[x] = entry.getKey();
        functions[x] = entry.getValue();
    }
    UDFFinder udff1 = new DefaultUDFFinder(names, functions);
    UDFFinder udff = new AggregatingUDFFinder(udff1);
    return udff;
}

Example 4 with DefaultUDFFinder

use of org.apache.poi.ss.formula.udf.DefaultUDFFinder in project poi by apache.

the class TestExternalFunction method testInvoke.

/**
	 * Checks that an external function can get invoked from the formula
	 * evaluator.
	 */
public void testInvoke() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    /**
		 * register the two test UDFs in a UDF finder, to be passed to the evaluator
		 */
    UDFFinder udff1 = new DefaultUDFFinder(new String[] { "myFunc" }, new FreeRefFunction[] { new MyFunc() });
    UDFFinder udff2 = new DefaultUDFFinder(new String[] { "myFunc2" }, new FreeRefFunction[] { new MyFunc2() });
    UDFFinder udff = new AggregatingUDFFinder(udff1, udff2);
    HSSFRow row = sheet.getRow(0);
    // =myFunc("_")
    HSSFCell myFuncCell = row.getCell(1);
    // =myFunc2("_")
    HSSFCell myFunc2Cell = row.getCell(2);
    HSSFFormulaEvaluator fe = HSSFFormulaEvaluator.create(wb, null, udff);
    assertEquals("_abc", fe.evaluate(myFuncCell).getStringValue());
    assertEquals("_abc2", fe.evaluate(myFunc2Cell).getStringValue());
}