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

Example 31 with HSSFFormulaEvaluator

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

the class TestFormulasFromSpreadsheet method data.

@Parameters(name = "{0}")
public static Collection<Object[]> data() throws Exception {
    // Function "Text" uses custom-formats which are locale specific
    // can't set the locale on a per-testrun execution, as some settings have been
    // already set, when we would try to change the locale by then
    userLocale = LocaleUtil.getUserLocale();
    LocaleUtil.setUserLocale(Locale.ROOT);
    workbook = HSSFTestDataSamples.openSampleWorkbook(SS.FILENAME);
    sheet = workbook.getSheetAt(0);
    evaluator = new HSSFFormulaEvaluator(workbook);
    List<Object[]> data = new ArrayList<Object[]>();
    processFunctionGroup(data, SS.START_OPERATORS_ROW_INDEX, null);
    processFunctionGroup(data, SS.START_FUNCTIONS_ROW_INDEX, null);
    return data;
}

Example 32 with HSSFFormulaEvaluator

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

the class TestMultiSheetEval method processFunctionGroup.

/**
	 * @param startRowIndex row index in the spreadsheet where the first function/operator is found
	 * @param testFocusFunctionName name of a single function/operator to test alone.
	 * Typically pass <code>null</code> to test all functions
	 */
private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
    Collection<String> funcs = FunctionEval.getSupportedFunctionNames();
    int rowIndex = startRowIndex;
    while (true) {
        Row r = sheet.getRow(rowIndex);
        // only evaluate non empty row
        if (r != null) {
            String targetFunctionName = getTargetFunctionName(r);
            String targetTestName = getTargetTestName(r);
            if (targetFunctionName == null) {
                throw new AssertionFailedError("Test spreadsheet cell empty on row (" + (rowIndex + 1) + "). Expected function name or '" + SS.FUNCTION_NAMES_END_SENTINEL + "'");
            }
            if (targetFunctionName.equals(SS.FUNCTION_NAMES_END_SENTINEL)) {
                // found end of functions list
                break;
            }
            if (testFocusFunctionName == null || targetFunctionName.equalsIgnoreCase(testFocusFunctionName)) {
                // expected results are on the row below
                Cell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_VALUE);
                if (expectedValueCell == null) {
                    int missingRowNum = rowIndex + 1;
                    throw new AssertionFailedError("Missing expected values cell for function '" + targetFunctionName + ", test" + targetTestName + " (row " + missingRowNum + ")");
                }
                switch(processFunctionRow(evaluator, targetFunctionName, targetTestName, r, expectedValueCell)) {
                    case Result.ALL_EVALUATIONS_SUCCEEDED:
                        _functionSuccessCount++;
                        break;
                    case Result.SOME_EVALUATIONS_FAILED:
                        _functionFailureCount++;
                        break;
                    default:
                        throw new RuntimeException("unexpected result");
                    case // do nothing
                    Result.NO_EVALUATIONS_FOUND:
                        String uname = targetFunctionName.toUpperCase(Locale.ROOT);
                        if (startRowIndex >= SS.START_FUNCTIONS_ROW_INDEX && funcs.contains(uname)) {
                            logger.log(POILogger.WARN, uname + ": function is supported but missing test data");
                        }
                        break;
                }
            }
        }
        rowIndex++;
    }
}

Example 33 with HSSFFormulaEvaluator

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

the class TestPercentEval method testInSpreadSheet.

public void testInSpreadSheet() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("B1%");
    row.createCell(1).setCellValue(50.0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
        cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
        if (e.getCause() instanceof NullPointerException) {
            throw new AssertionFailedError("Identified bug 44608");
        }
        // else some other unexpected error
        throw e;
    }
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(0.5, cv.getNumberValue(), 0.0);
}

Example 34 with HSSFFormulaEvaluator

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

the class TestDate method setUp.

@Override
public void setUp() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    cell11 = sheet.createRow(0).createCell(0);
    cell11.setCellType(CellType.FORMULA);
    evaluator = new HSSFFormulaEvaluator(wb);
}

Example 35 with HSSFFormulaEvaluator

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

the class TestClean method testClean.

public void testClean() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCell cell = wb.createSheet().createRow(0).createCell(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    String[] asserts = { "aniket", "aniket", "aniket", "aniket", "aniket", "aniket", "№aniket∑ﭞ‹", "№aniket∑ﭞ‹" };
    for (int i = 0; i < asserts.length; i += 2) {
        String formulaText = "CLEAN(\"" + asserts[i] + "\")";
        confirmResult(fe, cell, formulaText, asserts[i + 1]);
    }
    asserts = new String[] { "CHAR(7)&\"text\"&CHAR(7)", "text", "CHAR(7)&\"text\"&CHAR(17)", "text", "CHAR(181)&\"text\"&CHAR(190)", "µtext¾", "\"text\"&CHAR(160)&\"'\"", "text '" };
    for (int i = 0; i < asserts.length; i += 2) {
        String formulaText = "CLEAN(" + asserts[i] + ")";
        confirmResult(fe, cell, formulaText, asserts[i + 1]);
    }
}