Examples with CellValue org.apache.poi.ss.usermodel.CellValue used on opensource projects

Example 11 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestFormulaParserEval method testEvaluateFormulaWithRowBeyond32768_Bug44539.

public void testEvaluateFormulaWithRowBeyond32768_Bug44539() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    wb.setSheetName(0, "Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("SUM(A32769:A32770)");
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
        result = fe.evaluate(cell);
    } catch (FormulaParseException e) {
        if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
            throw new AssertionFailedError("Identifed bug 44539");
        }
        throw e;
    }
    assertEquals(CellType.NUMERIC, result.getCellTypeEnum());
    assertEquals(42.0, result.getNumberValue(), 0.0);
}

Example 12 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestBug42464 method process.

private static void process(HSSFRow row, HSSFFormulaEvaluator eval) {
    Iterator<Cell> it = row.cellIterator();
    while (it.hasNext()) {
        HSSFCell cell = (HSSFCell) it.next();
        if (cell.getCellTypeEnum() != CellType.FORMULA) {
            continue;
        }
        FormulaRecordAggregate record = (FormulaRecordAggregate) cell.getCellValueRecord();
        FormulaRecord r = record.getFormulaRecord();
        Ptg[] ptgs = r.getParsedExpression();
        String cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex(), false, false).formatAsString();
        //			if(false && cellRef.equals("BP24")) { // TODO - replace System.out.println()s with asserts
        //				System.out.print(cellRef);
        //				System.out.println(" - has " + ptgs.length + " ptgs:");
        //				for(int i=0; i<ptgs.length; i++) {
        //					String c = ptgs[i].getClass().toString();
        //					System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
        //				}
        //				System.out.println("-> " + cell.getCellFormula());
        //			}
        CellValue evalResult = eval.evaluate(cell);
        assertNotNull(evalResult);
    }
}

Example 13 with CellValue

use of org.apache.poi.ss.usermodel.CellValue 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 14 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestHSSFFormulaEvaluator method testEvaluateSimple.

/**
	 * Test that the HSSFFormulaEvaluator can evaluate simple named ranges
	 *  (single cells and rectangular areas)
	 */
@Test
public void testEvaluateSimple() throws IOException {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFCell cell = sheet.getRow(8).getCell(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = fe.evaluate(cell);
    assertEquals(CellType.NUMERIC, cv.getCellTypeEnum());
    assertEquals(3.72, cv.getNumberValue(), 0.0);
    wb.close();
}

Example 15 with CellValue

use of org.apache.poi.ss.usermodel.CellValue in project poi by apache.

the class TestHSSFFormulaEvaluator method testDefinedNameWithComplexFlag_bug47048.

/**
	 * When evaluating defined names, POI has to decide whether it is capable.  Currently
	 * (May2009) POI only supports simple cell and area refs.<br/>
	 * The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex'
	 * which contained a simple area ref.  It is not clear what the 'complex' flag is used
	 * for but POI should look elsewhere to decide whether it can evaluate the name.
	 */
@Test
public void testDefinedNameWithComplexFlag_bug47048() throws IOException {
    // Mock up a spreadsheet to match the critical details of the sample
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Input");
    HSSFName definedName = wb.createName();
    definedName.setNameName("Is_Multicar_Vehicle");
    definedName.setRefersToFormula("Input!$B$17:$G$17");
    // Set up some data and the formula
    HSSFRow row17 = sheet.createRow(16);
    row17.createCell(0).setCellValue(25.0);
    row17.createCell(1).setCellValue(1.33);
    row17.createCell(2).setCellValue(4.0);
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)");
    // Set the complex flag - POI doesn't usually manipulate this flag
    NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
    // 0x10 -> complex
    nameRec.setOptionFlag((short) 0x10);
    HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
    CellValue value;
    try {
        value = hsf.evaluate(cellA1);
        assertEquals(CellType.NUMERIC, value.getCellTypeEnum());
        assertEquals(5.33, value.getNumberValue(), 0.0);
    } catch (RuntimeException e) {
        if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) {
            fail("Identified bug 47048a");
        }
        throw e;
    } finally {
        wb.close();
    }
}