Examples with HSSFFormulaEvaluator org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator used on opensource projects

Example 11 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestFixed method setUp.

@Before
public void setUp() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    try {
        HSSFSheet sheet = wb.createSheet("new sheet");
        cell11 = sheet.createRow(0).createCell(0);
        cell11.setCellType(CellType.FORMULA);
        evaluator = new HSSFFormulaEvaluator(wb);
    } finally {
        wb.close();
    }
}

Example 12 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestFind method testFind.

@Test
public void testFind() throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
    // number args converted to text
    confirmResult(fe, cell, "find(7, 32768)", 3);
    confirmResult(fe, cell, "find(\"34\", 1341235233412, 3)", 10);
    confirmResult(fe, cell, "find(5, 87654)", 4);
    // Errors
    confirmError(fe, cell, "find(\"n\", \"haystack\")", FormulaError.VALUE);
    confirmError(fe, cell, "find(\"k\", \"haystack\",9)", FormulaError.VALUE);
    confirmError(fe, cell, "find(\"k\", \"haystack\",#REF!)", FormulaError.REF);
    confirmError(fe, cell, "find(\"k\", \"haystack\",0)", FormulaError.VALUE);
    confirmError(fe, cell, "find(#DIV/0!, #N/A, #REF!)", FormulaError.DIV0);
    confirmError(fe, cell, "find(2, #N/A, #REF!)", FormulaError.NA);
    wb.close();
}

Example 13 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestIPMT method testFromFile.

/**
     *  from http://office.microsoft.com/en-001/excel-help/ipmt-HP005209145.aspx
     */
public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("finance.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("IPMT");
    HSSFCell ex1cell1 = example1.getRow(6).getCell(0);
    fe.evaluate(ex1cell1);
    assertEquals(-22.41, ex1cell1.getNumericCellValue(), 0.1);
    HSSFCell ex1cell2 = example1.getRow(7).getCell(0);
    fe.evaluate(ex1cell2);
    assertEquals(-292.45, ex1cell2.getNumericCellValue(), 0.1);
}

Example 14 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestIsBlank method test3DArea.

public void test3DArea() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    wb.createSheet();
    wb.setSheetName(1, "Sheet2");
    HSSFRow row = sheet1.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("isblank(Sheet2!A1:A1)");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
    cell.setCellFormula("isblank(D7:D7)");
    result = fe.evaluate(cell);
    assertEquals(CellType.BOOLEAN, result.getCellTypeEnum());
    assertEquals(true, result.getBooleanValue());
}

Example 15 with HSSFFormulaEvaluator

use of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator in project poi by apache.

the class TestTime method setUp.

@Before
public void setUp() {
    wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    style = wb.createCellStyle();
    HSSFDataFormat fmt = wb.createDataFormat();
    style.setDataFormat(fmt.getFormat("hh:mm:ss"));
    cell11 = sheet.createRow(0).createCell(0);
    form = new HSSFDataFormatter();
    evaluator = new HSSFFormulaEvaluator(wb);
}