Examples with CellReference org.apache.poi.ss.util.CellReference used on opensource projects

Example 36 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestCellReference method testAbsRef2.

@Test
public void testAbsRef2() {
    CellReference cf = new CellReference(4, 1, true, true);
    confirmCell(cf, null, 4, 1, true, true, "$B$5");
}

Example 37 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestCellReference method testAbsRef6.

@Test
public void testAbsRef6() {
    CellReference cf = new CellReference(4, 1, false, true);
    confirmCell(cf, null, 4, 1, false, true, "$B5");
}

Example 38 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestCellReference method testAbsRef4.

@Test
public void testAbsRef4() {
    CellReference cf = new CellReference(4, 1, true, false);
    confirmCell(cf, null, 4, 1, true, false, "B$5");
}

Example 39 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestCellReference method testAbsRef7.

@Test
public void testAbsRef7() {
    CellReference cf = new CellReference("B5");
    confirmCell(cf, null, 4, 1, false, false, "B5");
}

Example 40 with CellReference

use of org.apache.poi.ss.util.CellReference in project poi by apache.

the class TestIfError method testEvaluate.

/**
     * =IFERROR(210/35,\"Error in calculation\")"  Divides 210 by 35 and returns 6.0
     * =IFERROR(55/0,\"Error in calculation\")"    Divides 55 by 0 and returns the error text
     * =IFERROR(C1,\"Error in calculation\")"      References the result of dividing 55 by 0 and returns the error text
     */
public static void testEvaluate() {
    Workbook wb = new HSSFWorkbook();
    Sheet sh = wb.createSheet();
    Row row1 = sh.createRow(0);
    Row row2 = sh.createRow(1);
    // Create cells
    row1.createCell(0, CellType.NUMERIC);
    row1.createCell(1, CellType.NUMERIC);
    row1.createCell(2, CellType.NUMERIC);
    row2.createCell(0, CellType.NUMERIC);
    row2.createCell(1, CellType.NUMERIC);
    // Create references
    CellReference a1 = new CellReference("A1");
    CellReference a2 = new CellReference("A2");
    CellReference b1 = new CellReference("B1");
    CellReference b2 = new CellReference("B2");
    CellReference c1 = new CellReference("C1");
    // Set values
    sh.getRow(a1.getRow()).getCell(a1.getCol()).setCellValue(210);
    sh.getRow(a2.getRow()).getCell(a2.getCol()).setCellValue(55);
    sh.getRow(b1.getRow()).getCell(b1.getCol()).setCellValue(35);
    sh.getRow(b2.getRow()).getCell(b2.getCol()).setCellValue(0);
    sh.getRow(c1.getRow()).getCell(c1.getCol()).setCellFormula("A1/B2");
    Cell cell1 = sh.createRow(3).createCell(0);
    cell1.setCellFormula("IFERROR(A1/B1,\"Error in calculation\")");
    Cell cell2 = sh.createRow(3).createCell(0);
    cell2.setCellFormula("IFERROR(A2/B2,\"Error in calculation\")");
    Cell cell3 = sh.createRow(3).createCell(0);
    cell3.setCellFormula("IFERROR(C1,\"error\")");
    double accuracy = 1E-9;
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    assertEquals("Checks that the cell is numeric", CellType.NUMERIC, evaluator.evaluate(cell1).getCellTypeEnum());
    assertEquals("Divides 210 by 35 and returns 6.0", 6.0, evaluator.evaluate(cell1).getNumberValue(), accuracy);
    assertEquals("Checks that the cell is numeric", CellType.STRING, evaluator.evaluate(cell2).getCellTypeEnum());
    assertEquals("Rounds -10 to a nearest multiple of -3 (-9)", "Error in calculation", evaluator.evaluate(cell2).getStringValue());
    assertEquals("Check that C1 returns string", CellType.STRING, evaluator.evaluate(cell3).getCellTypeEnum());
    assertEquals("Check that C1 returns string \"error\"", "error", evaluator.evaluate(cell3).getStringValue());
}