Package org.zkoss.poi.ss.usermodel

Examples of org.zkoss.poi.ss.usermodel.CellValue


    assertEquals(15, row7.getCell(4).getNumericCellValue(), 0.0000000000000001); //E7: 15
    assertEquals(16, row7.getCell(5).getNumericCellValue(), 0.0000000000000001); //F7: 16
   
    //A1: =SUM(C4:F7);
    Cell cellA1 = row1.getCell(0);
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(136, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(C4:F7)");
 
    //move left from D6:G8 to E7:H9
    BookHelper.moveRange(sheet1, 5, 3, 7, 6, 1, 1);
    _evaluator.notifySetFormula(cellA1);

    assertEquals(1, row4.getCell(2).getNumericCellValue(), 0.0000000000000001); //C4: 1
    assertEquals(2, row4.getCell(3).getNumericCellValue(), 0.0000000000000001); //D4: 2
    assertEquals(3, row4.getCell(4).getNumericCellValue(), 0.0000000000000001); //E4: 3
    assertEquals(4, row4.getCell(5).getNumericCellValue(), 0.0000000000000001); //F4: 4

    assertEquals(5, row5.getCell(2).getNumericCellValue(), 0.0000000000000001); //C5: 5
    assertEquals(6, row5.getCell(3).getNumericCellValue(), 0.0000000000000001); //D5: 6
    assertEquals(7, row5.getCell(4).getNumericCellValue(), 0.0000000000000001); //E5: 7
    assertEquals(8, row5.getCell(5).getNumericCellValue(), 0.0000000000000001); //F5: 8
   
    assertEquals(9, row6.getCell(2).getNumericCellValue(), 0.0000000000000001); //C6: 9
    assertNull(row6.getCell(3));
    assertNull(row6.getCell(4));
    assertNull(row6.getCell(5));
   
    assertEquals(13, row7.getCell(2).getNumericCellValue(), 0.0000000000000001); //C7: 13
    assertNull(row7.getCell(3));
   
    assertEquals(10, row7.getCell(4).getNumericCellValue(), 0.0000000000000001); //D6: 10
    assertEquals(11, row7.getCell(5).getNumericCellValue(), 0.0000000000000001); //E6: 11
    assertEquals(12, row7.getCell(6).getNumericCellValue(), 0.0000000000000001); //F6: 12
   
    Row row8 = sheet1.getRow(7);
    assertNull(row8.getCell(2));
    assertNull(row8.getCell(3));
    assertEquals(14, row8.getCell(4).getNumericCellValue(), 0.0000000000000001); //D7: 14
    assertEquals(15, row8.getCell(5).getNumericCellValue(), 0.0000000000000001); //E7: 15
    assertEquals(16, row8.getCell(6).getNumericCellValue(), 0.0000000000000001); //F7: 16
   
    //A1: =SUM(C4:F7) -> =SUM(C4:F7) //no change
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(79, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(C4:F7)");
  }
View Full Code Here


    assertNull(row.getCell(0));
   
   
    for(int col = 1; col < 13; ++col) {
      final Cell cell = row.getCell(col);
      CellValue value = _evaluator.evaluate(cell);
      assertEquals(0, value.getNumberValue(), 0.0000000000000001);
    }

    Row row4 = sheet1.getRow(3);
    final Cell cell = row4.getCell(0);
    CellValue value = _evaluator.evaluate(cell);
    assertEquals(0, value.getNumberValue(), 0.0000000000000001);
   
    Cell cellA1 = row.getCell(0);
    RichTextString rstr = _workbook.getCreationHelper().createRichTextString("10");
    new RangeImpl(0, 0, sheet1, sheet1).setRichEditText(rstr);
  }
View Full Code Here

    assertEquals(11, row2.getCell(9).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(E2:E3)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0);
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(E2:E3)");

    //A2: =SUM(E2:J2)
    Cell cellA2 = row2.getCell(0);
    CellValue valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(36, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(E2:J2)");
   
    //A3: =SUM(H2:J2)
    Cell cellA3 = row3.getCell(0);
    CellValue valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(H2:J2)");
 
    //G3: =G2
    Cell cellG3 = row3.getCell(6);
    CellValue valueG3 = _evaluator.evaluate(cellG3);
    assertEquals(5, valueG3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueG3.getCellType());
    testToFormulaString(cellG3, "G2");
   
    //remove column D ~ F
    BookHelper.deleteColumns(sheet1, 3, 3);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellA2);
    _evaluator.notifySetFormula(cellA3);
    _evaluator.notifySetFormula(cellG3);
   
    //D2: 5, E2:7, E3: empty, F2: 9, G2: 11, H ~ J empty
    assertNull(row3.getCell(4)); //E3 not exist
    assertNull(row2.getCell(7));
    assertNull(row2.getCell(8));
    assertNull(row2.getCell(9));
   
    assertEquals(5, row2.getCell(3).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row2.getCell(4).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row2.getCell(5).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row2.getCell(6).getNumericCellValue(), 0.0000000000000001);
   
    //A3: =SUM(E2:G2)
    valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(E2:G2)");
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
   
    //A2: =SUM(D2:G2)
    valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(32, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(D2:G2)");
   
    //A1: =SUM(#REF!)
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_ERROR, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(#REF!)");

    //G3 -> D3: =G2 -> =D2
    assertNull(row3.getCell(6)); //G3 not exist
   
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(5, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "D2");
  }
View Full Code Here

    assertEquals(11, row2.getCell(9).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(E2:E3)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0);
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(E2:E3)");

    //A2: =SUM(E2:J2)
    Cell cellA2 = row2.getCell(0);
    CellValue valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(36, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(E2:J2)");
   
    //A3: =SUM(H2:J2)
    Cell cellA3 = row3.getCell(0);
    CellValue valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(H2:J2)");
 
    Cell cellG3 = row3.getCell(6);
    CellValue valueG3 = _evaluator.evaluate(cellG3);
    assertEquals(5, valueG3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueG3.getCellType());
    testToFormulaString(cellG3, "G2");
   
    //remove column D ~ F
    BookHelper.deleteRange(sheet1, 0, 3, 2, 5, true);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellA2);
    _evaluator.notifySetFormula(cellA3);
    _evaluator.notifySetFormula(cellG3);
   
    //D2: 5, E2:7, E3: empty, F2: 9, G2: 11, H ~ J empty
    assertNull(row3.getCell(4)); //E3 not exist
    assertNull(row2.getCell(7));
    assertNull(row2.getCell(8));
    assertNull(row2.getCell(9));
   
    assertEquals(5, row2.getCell(3).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row2.getCell(4).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row2.getCell(5).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row2.getCell(6).getNumericCellValue(), 0.0000000000000001);
   
    //A3: =SUM(E2:G2)
    valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(E2:G2)");
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
   
    //A2: =SUM(D2:G2)
    valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(32, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(D2:G2)");
   
    //A1: =SUM(#REF!)
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_ERROR, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(#REF!)");

    //G3 -> D3: =G2 -> =D2
    assertNull(row3.getCell(6)); //G3 not exist
   
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(5, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "D2");
  }
View Full Code Here

    assertEquals(11, row2.getCell(9).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(E2:E3)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0);
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(E2:E3)");

    //A2: =SUM(E2:J2)
    Cell cellA2 = row2.getCell(0);
    CellValue valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(36, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(E2:J2)");
   
    //A3: =SUM(H2:J2)
    Cell cellA3 = row3.getCell(0);
    CellValue valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(H2:J2)");
 
    //G3: =G2
    Cell cellG3 = row3.getCell(6);
    CellValue valueG3 = _evaluator.evaluate(cellG3);
    assertEquals(5, valueG3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueG3.getCellType());
    testToFormulaString(cellG3, "G2");
   
    //remove column D ~ F
    BookHelper.deleteColumns(sheet1, 3, 3);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellA2);
    _evaluator.notifySetFormula(cellA3);
    _evaluator.notifySetFormula(cellG3);
   
    //D2: 5, E2:7, E3: empty, F2: 9, G2: 11, H ~ J empty
    assertNull(row3.getCell(4)); //E3 not exist
    assertNull(row2.getCell(7));
    assertNull(row2.getCell(8));
    assertNull(row2.getCell(9));
   
    assertEquals(5, row2.getCell(3).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row2.getCell(4).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row2.getCell(5).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row2.getCell(6).getNumericCellValue(), 0.0000000000000001);
   
    //A3: =SUM(E2:G2)
    valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(E2:G2)");
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
   
    //A2: =SUM(D2:G2)
    valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(32, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(D2:G2)");
   
    //A1: =SUM(#REF!)
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_ERROR, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(#REF!)");

    //G3 -> D3: =G2 -> =D2
    assertNull(row3.getCell(6)); //G3 not exist
   
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(5, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "D2");
  }
View Full Code Here

    assertEquals(11, row2.getCell(9).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(E2:E3)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0);
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(E2:E3)");

    //A2: =SUM(E2:J2)
    Cell cellA2 = row2.getCell(0);
    CellValue valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(36, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(E2:J2)");
   
    //A3: =SUM(H2:J2)
    Cell cellA3 = row3.getCell(0);
    CellValue valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(H2:J2)");
 
    Cell cellG3 = row3.getCell(6);
    CellValue valueG3 = _evaluator.evaluate(cellG3);
    assertEquals(5, valueG3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueG3.getCellType());
    testToFormulaString(cellG3, "G2");
   
    //remove column D ~ F
    BookHelper.deleteRange(sheet1, 0, 3, 2, 5, true);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellA2);
    _evaluator.notifySetFormula(cellA3);
    _evaluator.notifySetFormula(cellG3);
   
    //D2: 5, E2:7, E3: empty, F2: 9, G2: 11, H ~ J empty
    assertNull(row3.getCell(4)); //E3 not exist
    assertNull(row2.getCell(7));
    assertNull(row2.getCell(8));
    assertNull(row2.getCell(9));
   
    assertEquals(5, row2.getCell(3).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row2.getCell(4).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row2.getCell(5).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row2.getCell(6).getNumericCellValue(), 0.0000000000000001);
   
    //A3: =SUM(E2:G2)
    valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(E2:G2)");
    assertEquals(27, valueA3.getNumberValue(), 0.0000000000000001);
   
    //A2: =SUM(D2:G2)
    valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(32, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(D2:G2)");
   
    //A1: =SUM(#REF!)
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_ERROR, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(#REF!)");

    //G3 -> D3: =G2 -> =D2
    assertNull(row3.getCell(6)); //G3 not exist
   
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(5, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "D2");
  }
View Full Code Here

    assertEquals(11, row10.getCell(1).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(B5:C5)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0); //A1: =SUM(B5:C5)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B5:C5)");

    //B1: =SUM(B5:B10)
    Cell cellB1 = row1.getCell(1); //B1: =SUM(B5:B10)
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(36, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B10)");
   
    //C1: =SUM(B8:B10)
    Cell cellC1 = row1.getCell(2); //C1: =SUM(B8:B10)
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B8:B10)");
   
    //6 merge area
    assertEquals(6, sheet1.getNumMergedRegions());
    for (int j = 0; j < 6; ++j) {
      CellRangeAddress rng = sheet1.getMergedRegion(j);
      switch(j) {
      case 0:
        assertEquals("E6:F8", rng.formatAsString());
        break;
      case 1:
        assertEquals("E10:F12", rng.formatAsString());
        break;
      case 2:
        assertEquals("G5:H5", rng.formatAsString());
        break;
      case 3:
        assertEquals("E3:F4", rng.formatAsString());
        break;
      case 4:
        assertEquals("I3:J7", rng.formatAsString());
        break;
      case 5:
        assertEquals("E14:F15", rng.formatAsString());
        break;
      }
    }
 
    //remove rows 4 ~ 6
    BookHelper.deleteRows(sheet1, 3, 3); //remove rows 4 ~ 6
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellB1);
    _evaluator.notifySetFormula(cellC1);
   
    //B4: 5, B5:7, C5: empty, B6: 9, B7: 11, row 8 ~ row 10 empty
    Row row4 = sheet1.getRow(3);
    row5 = sheet1.getRow(4);
    row6 = sheet1.getRow(5);
    row7 = sheet1.getRow(6);
   
    row8 = sheet1.getRow(7);
    row9 = sheet1.getRow(8);
    row10 = sheet1.getRow(9);
   
    assertNull(row5.getCell(2)); //C5 not exist
    assertNull(row8.getCell(1));
    assertNull(row9.getCell(1));
    assertNull(row10);
   
    assertEquals(5, row4.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row5.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row6.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row7.getCell(1).getNumericCellValue(), 0.0000000000000001);
   
    //C1: =SUM(B5:B7)
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B5:B7)");
   
    //B1: =SUM(B4:B7)
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(32, valueB1.getNumberValue(), 0.0000000000000001);
View Full Code Here

    assertEquals(11, row10.getCell(1).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(B5:C5)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0); //A1: =SUM(B5:C5)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B5:C5)");

    //B1: =SUM(B5:B10)
    Cell cellB1 = row1.getCell(1); //B1: =SUM(B5:B10)
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(36, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B10)");
   
    //C1: =SUM(B8:B10)
    Cell cellC1 = row1.getCell(2); //C1: =SUM(B8:B10)
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B8:B10)");
   
    //6 merge area
    assertEquals(6, sheet1.getNumMergedRegions());
    for (int j = 0; j < 6; ++j) {
      CellRangeAddress rng = sheet1.getMergedRegion(j);
      switch(j) {
      case 0:
        assertEquals("E6:F8", rng.formatAsString());
        break;
      case 1:
        assertEquals("E10:F12", rng.formatAsString());
        break;
      case 2:
        assertEquals("G5:H5", rng.formatAsString());
        break;
      case 3:
        assertEquals("E3:F4", rng.formatAsString());
        break;
      case 4:
        assertEquals("I3:J7", rng.formatAsString());
        break;
      case 5:
        assertEquals("E14:F15", rng.formatAsString());
        break;
      }
    }
 
    //remove A4:J6
    BookHelper.deleteRange(sheet1, 3, 0, 5, 9, false);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellB1);
    _evaluator.notifySetFormula(cellC1);
   
    //B4: 5, B5:7, C5: empty, B6: 9, B7: 11, row 8 ~ row 10 empty
    Row row4 = sheet1.getRow(3);
    row5 = sheet1.getRow(4);
    row6 = sheet1.getRow(5);
    row7 = sheet1.getRow(6);
   
    row8 = sheet1.getRow(7);
    row9 = sheet1.getRow(8);
    row10 = sheet1.getRow(9);
   
    assertNull(row5.getCell(2)); //C5 not exist
    assertNull(row8.getCell(1));
    assertNull(row9.getCell(1));
    assertNull(row10.getCell(1));
   
    assertEquals(5, row4.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row5.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row6.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row7.getCell(1).getNumericCellValue(), 0.0000000000000001);
   
    //C1: =SUM(B5:B7)
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B5:B7)");
   
    //B1: =SUM(B4:B7)
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(32, valueB1.getNumberValue(), 0.0000000000000001);
View Full Code Here

    assertNull(row.getCell(0));
   
   
    for(int col = 1; col < 13; ++col) {
      final Cell cell = row.getCell(col);
      CellValue value = _evaluator.evaluate(cell);
      assertEquals(0, value.getNumberValue(), 0.0000000000000001);
    }

    Row row4 = sheet1.getRow(3);
    final Cell cell = row4.getCell(0);
    CellValue value = _evaluator.evaluate(cell);
    assertEquals(0, value.getNumberValue(), 0.0000000000000001);
   
    Cell cellA1 = row.getCell(0);
    RichTextString rstr = _workbook.getCreationHelper().createRichTextString("10");
    new RangeImpl(0, 0, sheet1, sheet1).setRichEditText(rstr);
  }
View Full Code Here

    byte[] fbg = ((XSSFColor)row1.getCell(1).getCellStyle().getFillBackgroundColorColor()).getRgb();
    assertColors(YELLOWColor, fbg);
   
    //A1: =SUM(F1:F3)
    Cell cellA1 = row1.getCell(0); //A1: =SUM(F1:F3)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(F1:F3)");
   
    //A2: =SUM(D2:XFD2) XFD: 16384
    Cell cellA2 = row2.getCell(0);
    CellValue valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(2, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(D2:XFD2)");

    //A3: =SUM(XFC3:XFD3) XFC: 16383, XFD: 16384
    Cell cellA3 = row3.getCell(0);
    CellValue valueA3 = _evaluator.evaluate(cellA3);
    assertEquals(0, valueA3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA3.getCellType());
    testToFormulaString(cellA3, "SUM(XFC3:XFD3)");
   
    //A5: =SUM(C1:D1)
    Cell cellA5 = row5.getCell(0);
    CellValue valueA5 = _evaluator.evaluate(cellA5);
    assertEquals(20, valueA5.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA5.getCellType());
    testToFormulaString(cellA5, "SUM(C1:D1)");
   
    //A6: =SUM(B1:D1)
    Cell cellA6 = row6.getCell(0);
    CellValue valueA6 = _evaluator.evaluate(cellA6);
    assertEquals(20, valueA6.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA6.getCellType());
    testToFormulaString(cellA6, "SUM(B1:D1)");
   
    //H1: =SUM(F1:F3)
    Cell cellH1 = row1.getCell(7);
    CellValue valueH1 = _evaluator.evaluate(cellH1);
    assertEquals(6, valueH1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueH1.getCellType());
    testToFormulaString(cellH1, "SUM(F1:F3)");

    //H4: =A4
    Cell cellH4 = row4.getCell(7);
    CellValue valueH4 = _evaluator.evaluate(cellH4);
    assertEquals(7, valueH4.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueH4.getCellType());
    testToFormulaString(cellH4, "A4");
   
    //Insert before column C
    BookHelper.insertColumns(sheet1, 2, 1, Range.FORMAT_LEFTABOVE);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellA2);
    _evaluator.notifySetFormula(cellA3);
    _evaluator.notifySetFormula(cellA5);
    _evaluator.notifySetFormula(cellA6);
   
    assertEquals(sheet1.getColumnWidth(1), sheet1.getColumnWidth(2)); //column c width == column b width
    assertColors(REDColor, ((XSSFColor)row1.getCell(1).getCellStyle().getFillForegroundColorColor()).getRgb());
    assertColors(YELLOWColor, ((XSSFColor)row1.getCell(1).getCellStyle().getFillBackgroundColorColor()).getRgb());
    assertColors(REDColor, ((XSSFColor)row1.getCell(2).getCellStyle().getFillForegroundColorColor()).getRgb());
    assertColors(YELLOWColor, ((XSSFColor)row1.getCell(2).getCellStyle().getFillBackgroundColorColor()).getRgb());
   
    assertEquals(1, row1.getCell(6).getNumericCellValue(), 0.0000000000000001); //G1: 1
    assertEquals(2, row2.getCell(6).getNumericCellValue(), 0.0000000000000001)//G2: 2
    assertEquals(3, row3.getCell(6).getNumericCellValue(), 0.0000000000000001); //G3: 3
    assertEquals(7, row4.getCell(0).getNumericCellValue(), 0.0000000000000001); //A4: 7

    //A1: =SUM(G1:G3)
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(G1:G3)");
   
    //A2: =SUM(E2:XFD2) XFD: 16384
    valueA2 = _evaluator.evaluate(cellA2);
    assertEquals(2, valueA2.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA2.getCellType());
    testToFormulaString(cellA2, "SUM(E2:XFD2)");

    //A3: =SUM(XFD3:XFD3) XFD: 16384
    cellA3 = row3.getCell(0); //A3: =SUM(IU3:IV3)
    valueA6 = _evaluator.evaluate(cellA3);
    assertEquals(0, valueA6.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA6.getCellType());
    testToFormulaString(cellA3, "SUM(XFD3:XFD3)");
   
    //A5: =SUM(C1:D1) -> =SUM(D1:E1)
    cellA5 = row5.getCell(0);
    valueA5 = _evaluator.evaluate(cellA5);
    assertEquals(20, valueA5.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA5.getCellType());
    testToFormulaString(cellA5, "SUM(D1:E1)");
   
    //A6: =SUM(B1:D1) -> =SUM(B1:E1)
    cellA6 = row6.getCell(0);
    valueA6 = _evaluator.evaluate(cellA6);
    assertEquals(20, valueA6.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA6.getCellType());
    testToFormulaString(cellA6, "SUM(B1:E1)");
   
    //I1: =SUM(G1:G3)
    Cell cellI1 = row1.getCell(8);
    CellValue valueI1 = _evaluator.evaluate(cellI1);
    assertEquals(6, valueI1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueI1.getCellType());
    testToFormulaString(cellI1, "SUM(G1:G3)");

    //I4: =A4
    Cell cellI4 = row4.getCell(8);
    CellValue valueI4 = _evaluator.evaluate(cellI4);
    assertEquals(7, valueI4.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueI4.getCellType());
    testToFormulaString(cellI4, "A4");
   
  }
View Full Code Here

TOP

Related Classes of org.zkoss.poi.ss.usermodel.CellValue

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.