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

Example 31 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());
}

Example 32 with CellReference

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

the class TestFormulaShifter method testShiftSheet2.

@Test
public void testShiftSheet2() {
    // 4 sheets, move a sheet from pos 1 to pos 2, i.e. current 2 becomes 1, current 1 becomes pos 2 
    FormulaShifter shifter = FormulaShifter.createForSheetShift(1, 2);
    Ptg[] ptgs = new Ptg[] { new Ref3DPtg(new CellReference("first", 0, 0, true, true), 0), new Ref3DPtg(new CellReference("second", 0, 0, true, true), 1), new Ref3DPtg(new CellReference("third", 0, 0, true, true), 2), new Ref3DPtg(new CellReference("fourth", 0, 0, true, true), 3) };
    shifter.adjustFormula(ptgs, -1);
    assertEquals("formula previously pointing to sheet 0 should be unchanged", 0, ((Ref3DPtg) ptgs[0]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 1 should now point to sheet 2", 2, ((Ref3DPtg) ptgs[1]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 2 should now point to sheet 1", 1, ((Ref3DPtg) ptgs[2]).getExternSheetIndex());
    assertEquals("formula previously pointing to sheet 3 should be unchanged", 3, ((Ref3DPtg) ptgs[3]).getExternSheetIndex());
}

Example 33 with CellReference

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

the class TestCountFuncs method testCountifExamples.

/**
     * Two COUNTIF examples taken from
     * http://office.microsoft.com/en-us/excel-help/countif-function-HP010069840.aspx?CTT=5&origin=HA010277524
     */
public void testCountifExamples() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("countifExamples.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet1 = wb.getSheet("MSDN Example 1");
    for (int rowIx = 7; rowIx <= 12; rowIx++) {
        HSSFRow row = sheet1.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at  " + new CellReference(cellA).formatAsString() + ": " + cellA.getCellFormula() + " :" + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
    HSSFSheet sheet2 = wb.getSheet("MSDN Example 2");
    for (int rowIx = 9; rowIx <= 14; rowIx++) {
        HSSFRow row = sheet2.getRow(rowIx - 1);
        // cell containing a formula with COUNTIF
        HSSFCell cellA = row.getCell(0);
        assertEquals(CellType.FORMULA, cellA.getCellTypeEnum());
        // cell with a reference value
        HSSFCell cellC = row.getCell(2);
        assertEquals(CellType.NUMERIC, cellC.getCellTypeEnum());
        CellValue cv = fe.evaluate(cellA);
        double actualValue = cv.getNumberValue();
        double expectedValue = cellC.getNumericCellValue();
        assertEquals("Problem with a formula at " + new CellReference(cellA).formatAsString() + "[" + cellA.getCellFormula() + "]: " + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ", expectedValue, actualValue, 0.0001);
    }
}

Example 34 with CellReference

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

the class TestCountFuncs method testCountifBug51498.

/**
    * Bug #51498 - Check that CountIf behaves correctly for GTE, LTE
    *  and NEQ cases
    */
public void testCountifBug51498() throws Exception {
    final int REF_COL = 4;
    final int EVAL_COL = 3;
    HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("51498.xls");
    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    HSSFSheet sheet = workbook.getSheetAt(0);
    // numeric criteria
    for (int i = 0; i < 8; i++) {
        CellValue expected = evaluator.evaluate(sheet.getRow(i).getCell(REF_COL));
        CellValue actual = evaluator.evaluate(sheet.getRow(i).getCell(EVAL_COL));
        assertEquals(expected.formatAsString(), actual.formatAsString());
    }
    // boolean criteria
    for (int i = 0; i < 8; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(8);
        HSSFCell cellRef = sheet.getRow(i).getCell(9);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
    // string criteria
    for (int i = 1; i < 9; i++) {
        HSSFCell cellFmla = sheet.getRow(i).getCell(13);
        HSSFCell cellRef = sheet.getRow(i).getCell(14);
        double expectedValue = cellRef.getNumericCellValue();
        double actualValue = evaluator.evaluate(cellFmla).getNumberValue();
        assertEquals("Problem with a formula at " + new CellReference(cellFmla).formatAsString() + "[" + cellFmla.getCellFormula() + "] ", expectedValue, actualValue, 0.0001);
    }
}

Example 35 with CellReference

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

the class BaseTestSheetUpdateArrayFormulas method testModifyArrayCells_setCellType.

@Test
public void testModifyArrayCells_setCellType() throws IOException {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();
    // single-cell array formulas behave just like normal cells -
    // changing cell type removes the array formula and associated cached result
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(CellType.FORMULA, scell.getCellTypeEnum());
    assertEquals(0.0, scell.getNumericCellValue(), 0);
    scell.setCellType(CellType.STRING);
    assertEquals(CellType.STRING, scell.getCellTypeEnum());
    scell.setCellValue("string cell");
    assertEquals("string cell", scell.getStringCellValue());
    //once you create a multi-cell array formula, you cannot change the type of its cells
    CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
        try {
            assertEquals(CellType.FORMULA, mcell.getCellTypeEnum());
            mcell.setCellType(CellType.NUMERIC);
            fail("expected exception");
        } catch (IllegalStateException e) {
            CellReference ref = new CellReference(mcell);
            String msg = "Cell " + ref.formatAsString() + " is part of a multi-cell array formula. You cannot change part of an array.";
            assertEquals(msg, e.getMessage());
        }
        // a failed invocation of Cell.setCellType leaves the cell
        // in the state that it was in prior to the invocation
        assertEquals(CellType.FORMULA, mcell.getCellTypeEnum());
        assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
    workbook.close();
}