Package org.zkoss.poi.ss.usermodel

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


  private Object getValue0(Cell cell) {
    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
      final Book book = (Book)cell.getSheet().getWorkbook();
      final CellValue cv = BookHelper.evaluate(book, cell);
      return BookHelper.getValueByCellValue(cv);
    } else {
      final Object obj = BookHelper.getCellValue(cell);
      return obj instanceof RichTextString ?
          ((RichTextString)obj).getString() : obj;
View Full Code Here


    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3: =A1+7
    Cell cellC3 = row3.getCell(2);
    CellValue valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //Copy cell (C3 -> D4)
    BookHelper.copyCell(cellC3, sheet1, 3, 3, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellC3);

    //A1,A2,A2,B2 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001)//B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3 stay as is
    valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //D4: =B2+7
    Row row4 = sheet1.getRow(3);
    Cell cellD4 = row4.getCell(3);
    CellValue valueD4 = _evaluator.evaluate(cellD4);
    assertEquals(11, valueD4.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD4.getCellType());
    testToFormulaString(cellD4, "B2+7");
  }
View Full Code Here

    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3: =A1+7
    Cell cellC3 = row3.getCell(2);
    CellValue valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //Copy cell (C3 -> C2)
    BookHelper.copyCell(cellC3, sheet1, 1, 2, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellC3);

    //A1,A2,A2,B2 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001)//B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3 stay as is
    valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //C2: #REF!
    Cell cellC2 = row2.getCell(2);
    CellValue valueC2 = _evaluator.evaluate(cellC2);
    assertEquals(ErrorConstants.ERROR_REF, valueC2.getErrorValue());
    assertEquals(Cell.CELL_TYPE_ERROR, valueC2.getCellType());
    testToFormulaString(cellC2, "#REF!+7");
  }
View Full Code Here

    assertEquals(5, row1.getCell(4).getNumericCellValue(), 0.0000000000000001); //E1: 5
    assertEquals(6, row1.getCell(5).getNumericCellValue(), 0.0000000000000001); //F1: 6
   
    //D3: =SUM(E1:F1)
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(11, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "SUM(E1:F1)");
   
    //Copy cell (D3 -> IV3)
    BookHelper.copyCell(cellD3, sheet1, 2, 255, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellD3);

    //A1,A2,A2,B2,E1,F1 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001)//B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
    assertEquals(5, row1.getCell(4).getNumericCellValue(), 0.0000000000000001); //E1: 5
    assertEquals(6, row1.getCell(5).getNumericCellValue(), 0.0000000000000001); //F1: 6
   
    //D3 stay as is
    valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(11, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "SUM(E1:F1)");
   
    //IV3: #REF!
    Cell cellIV3 = row3.getCell(255);
    CellValue valueIV3 = _evaluator.evaluate(cellIV3);
    assertEquals(ErrorConstants.ERROR_REF, valueIV3.getErrorValue());
    assertEquals(Cell.CELL_TYPE_ERROR, valueIV3.getCellType());
    testToFormulaString(cellIV3, "SUM(#REF!)");
  }
View Full Code Here

    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 right from E4:F7 to F4:G7
    BookHelper.moveRange(sheet1, 3, 4, 6, 5, 0, 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(5).getNumericCellValue(), 0.0000000000000001); //E4: 3
    assertEquals(4, row4.getCell(6).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(5).getNumericCellValue(), 0.0000000000000001); //E5: 7
    assertEquals(8, row5.getCell(6).getNumericCellValue(), 0.0000000000000001); //F5: 8
   
    assertEquals(9, row6.getCell(2).getNumericCellValue(), 0.0000000000000001); //C6: 9
    assertEquals(10, row6.getCell(3).getNumericCellValue(), 0.0000000000000001); //D6: 10
    assertEquals(11, row6.getCell(5).getNumericCellValue(), 0.0000000000000001); //E6: 11
    assertEquals(12, row6.getCell(6).getNumericCellValue(), 0.0000000000000001); //F6: 12
   
    assertEquals(13, row7.getCell(2).getNumericCellValue(), 0.0000000000000001); //C7: 13
    assertEquals(14, row7.getCell(3).getNumericCellValue(), 0.0000000000000001); //D7: 14
    assertEquals(15, row7.getCell(5).getNumericCellValue(), 0.0000000000000001); //E7: 15
    assertEquals(16, row7.getCell(6).getNumericCellValue(), 0.0000000000000001); //F7: 16
   
    //A1: =SUM(C4:G7)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(136, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(C4:G7)");
  }
View Full Code Here

    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 C4:D7 to B4:C7
    BookHelper.moveRange(sheet1, 3, 2, 6, 3, 0, -1);
    _evaluator.notifySetFormula(cellA1);
   
    assertEquals(1, row4.getCell(1).getNumericCellValue(), 0.0000000000000001); //B4: 1
    assertEquals(2, row4.getCell(2).getNumericCellValue(), 0.0000000000000001); //C4: 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(1).getNumericCellValue(), 0.0000000000000001); //B5: 5
    assertEquals(6, row5.getCell(2).getNumericCellValue(), 0.0000000000000001); //C5: 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(1).getNumericCellValue(), 0.0000000000000001); //B6: 9
    assertEquals(10, row6.getCell(2).getNumericCellValue(), 0.0000000000000001); //C6: 10
    assertEquals(11, row6.getCell(4).getNumericCellValue(), 0.0000000000000001); //E6: 11
    assertEquals(12, row6.getCell(5).getNumericCellValue(), 0.0000000000000001); //F6: 12
   
    assertEquals(13, row7.getCell(1).getNumericCellValue(), 0.0000000000000001); //B7: 13
    assertEquals(14, row7.getCell(2).getNumericCellValue(), 0.0000000000000001); //C7: 14
    assertEquals(15, row7.getCell(4).getNumericCellValue(), 0.0000000000000001); //E7: 15
    assertEquals(16, row7.getCell(5).getNumericCellValue(), 0.0000000000000001); //F7: 16
   
    //A1: =SUM(C4:F7) -> =SUM(B4:F7)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(136, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B4:F7)");
  }
View Full Code Here

    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 E4:G7 to D4:F7
    BookHelper.moveRange(sheet1, 3, 4, 6, 6, 0, -1);
    _evaluator.notifySetFormula(cellA1);
   
    assertEquals(1, row4.getCell(2).getNumericCellValue(), 0.0000000000000001); //C4: 1
    assertEquals(3, row4.getCell(3).getNumericCellValue(), 0.0000000000000001); //D4: 3
    assertEquals(4, row4.getCell(4).getNumericCellValue(), 0.0000000000000001); //E4: 4
    assertNull(row4.getCell(6)); //F4: null

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

    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 B4:D7 to C4:E7
    BookHelper.moveRange(sheet1, 3, 1, 6, 3, 0, 1);
    _evaluator.notifySetFormula(cellA1);
   
    assertNull(row4.getCell(1)); //C4: null
    assertEquals(1, row4.getCell(3).getNumericCellValue(), 0.0000000000000001); //D4: 1
    assertEquals(2, row4.getCell(4).getNumericCellValue(), 0.0000000000000001); //E4: 2
    assertEquals(4, row4.getCell(5).getNumericCellValue(), 0.0000000000000001); //F4: 4

    assertNull(row5.getCell(1)); //C5: null
    assertEquals(5, row5.getCell(3).getNumericCellValue(), 0.0000000000000001); //D5: 5
    assertEquals(6, row5.getCell(4).getNumericCellValue(), 0.0000000000000001); //E5: 6
    assertEquals(8, row5.getCell(5).getNumericCellValue(), 0.0000000000000001); //F5: 8
   
    assertNull(row6.getCell(1)); //C6: null
    assertEquals(9, row6.getCell(3).getNumericCellValue(), 0.0000000000000001); //D6: 10
    assertEquals(10, row6.getCell(4).getNumericCellValue(), 0.0000000000000001); //E6: 11
    assertEquals(12, row6.getCell(5).getNumericCellValue(), 0.0000000000000001); //F6: 12
   
    assertNull(row7.getCell(1)); //B7: 13
    assertEquals(13, row7.getCell(3).getNumericCellValue(), 0.0000000000000001); //D7: 13
    assertEquals(14, row7.getCell(4).getNumericCellValue(), 0.0000000000000001); //E7: 14
    assertEquals(16, row7.getCell(5).getNumericCellValue(), 0.0000000000000001); //F7: 16
   
    //A1: =SUM(C4:F7) -> =SUM(D4:F7)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(100, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(D4:F7)");
  }
View Full Code Here

    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 D4:D7 to F4:F7
    BookHelper.moveRange(sheet1, 3, 3, 6, 3, 0, 2);
    _evaluator.notifySetFormula(cellA1);
   
    assertEquals(1, row4.getCell(2).getNumericCellValue(), 0.0000000000000001); //C4: 1
    assertNull(row4.getCell(3));
    assertEquals(3, row4.getCell(4).getNumericCellValue(), 0.0000000000000001); //E4: 3
    assertEquals(2, row4.getCell(5).getNumericCellValue(), 0.0000000000000001); //F4: 2

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

    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 F4:G7 to B4:C7
    BookHelper.moveRange(sheet1, 3, 5, 6, 6, 0, -4);
    _evaluator.notifySetFormula(cellA1);
   

    assertEquals(4, row4.getCell(1).getNumericCellValue(), 0.0000000000000001); //B4: 4
    assertNull(row4.getCell(2));
    assertEquals(2, row4.getCell(3).getNumericCellValue(), 0.0000000000000001); //D4: 2
    assertEquals(3, row4.getCell(4).getNumericCellValue(), 0.0000000000000001); //E4: 3

    assertEquals(8, row5.getCell(1).getNumericCellValue(), 0.0000000000000001); //B5: 8
    assertNull(row5.getCell(2));
    assertEquals(6, row5.getCell(3).getNumericCellValue(), 0.0000000000000001); //D5: 6
    assertEquals(7, row5.getCell(4).getNumericCellValue(), 0.0000000000000001); //E5: 7
   
    assertEquals(12, row6.getCell(1).getNumericCellValue(), 0.0000000000000001); //B6: 12
    assertNull(row6.getCell(2));
    assertEquals(10, row6.getCell(3).getNumericCellValue(), 0.0000000000000001); //D6: 10
    assertEquals(11, row6.getCell(4).getNumericCellValue(), 0.0000000000000001); //E6: 11
   
    assertEquals(16, row7.getCell(1).getNumericCellValue(), 0.0000000000000001); //B7: 16
    assertNull(row7.getCell(2));
    assertEquals(14, row7.getCell(3).getNumericCellValue(), 0.0000000000000001); //D7: 14
    assertEquals(15, row7.getCell(4).getNumericCellValue(), 0.0000000000000001); //E7: 15
   
    //A1: =SUM(C4:F7) -> =SUM(B4:E7)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(108, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B4:E7)");
  }
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.