Examples of org.zkoss.zss.model.impl.FormulaImpl

org.zkoss.zss.model.impl.FormulaImpl
Implementation of a Spread Sheet Formula. @author henrichen

   * @param book the book which formula is belonged to
   * @param formula the formula
   * @return evaluted result
   */
  public static Object result(Book book, String formula) {
    return new FormulaImpl(formula).evaluate((XelContext)book);
  }

View Full Code Here

  }
  public void testFormulaEval() {
    Sheet sheet1 = ((BookImpl)_book).addSheet("Sheet1", 256, 64*1024);
    assertEquals(sheet1, _book.lookupSheet("Sheet1"));
    
    assertEquals(0d, ((Double)new FormulaImpl("Sheet1!B5 * 0.5").evaluate((XelContext)_book)).doubleValue());
    
    ((SheetImpl)sheet1).setCellEditText(4, 1, "1"); //B5
    assertEquals(0.5d, ((Double)new FormulaImpl("Sheet1!B5 * 0.5").evaluate((XelContext)_book)).doubleValue());
  
    ((SheetImpl)sheet1).setCellEditText(4, 1, "b"); //B5
    assertEquals(Boolean.FALSE, ((Boolean)new FormulaImpl("Sheet1!B5 < \"a\"").evaluate((XelContext)_book)));
  }

View Full Code Here

    assertEquals(66d, ((Number)result).doubleValue(), 0d);
    
    //test cascaded formula (a formula refer to a cell with formula)
    ((SheetImpl)sheet2).setCellValue(0, 0, new Integer(1)); //A1
    ((SheetImpl)sheet2).setCellValue(1, 0, new Integer(2)); //A2
    ((SheetImpl)sheet2).setCellValue(2, 0, new FormulaImpl("=SUM(A1,A2)")); //A3
/*    ((SheetImpl)sheet2).setCellValue(3, 0, new FormulaImpl("=SUM(A2,A3)")); //A4
    ((SheetImpl)sheet2).setCellValue(4, 0, new FormulaImpl("=SUM(A3,A4)")); //A5
    ((SheetImpl)sheet2).setCellValue(5, 0, new FormulaImpl("=SUM(A4,A5)")); //A6
    ((SheetImpl)sheet2).setCellValue(6, 0, new FormulaImpl("=SUM(A5,A6)")); //A7
    ((SheetImpl)sheet2).setCellValue(7, 0, new FormulaImpl("=SUM(A6,A7)")); //A8
    ((SheetImpl)sheet2).setCellValue(8, 0, new FormulaImpl("=SUM(A7,A8)")); //A9
    ((SheetImpl)sheet2).setCellValue(9, 0, new FormulaImpl("=SUM(A8,A9)")); //A10
    ((SheetImpl)sheet2).setCellValue(10, 0, new FormulaImpl("=SUM(A9,A10)")); //A11
*/
    ((SheetImpl)sheet2).setCellValue(3, 0, null); //A4
    ((SheetImpl)sheet2).setCellValue(4, 0, null); //A5
    ((SheetImpl)sheet2).setCellValue(5, 0, null); //A6
    ((SheetImpl)sheet2).setCellValue(6, 0, null); //A7
    ((SheetImpl)sheet2).setCellValue(7, 0, null); //A8
    ((SheetImpl)sheet2).setCellValue(8, 0, null); //A9
    ((SheetImpl)sheet2).setCellValue(9, 0, null); //A10
    ((SheetImpl)sheet2).setCellValue(10, 0, null); //A11
    
    //test Range.copy()
    new RangeSimple(sheet2, null, 0, 2, 0, 2).copy(new RangeSimple(sheet2, null, 0, 3, 0, 10)); //A3 -> A4:A11
/*    CellImpl cella3 = (CellImpl)sheet2.getCell(2,0);
    cella3.copy(sheet2.getCell(3,0)); //A4
    cella3.copy(sheet2.getCell(4,0)); //A5
    cella3.copy(sheet2.getCell(5,0)); //A6
    cella3.copy(sheet2.getCell(6,0)); //A7
    cella3.copy(sheet2.getCell(7,0)); //A8
    cella3.copy(sheet2.getCell(8,0)); //A9
    cella3.copy(sheet2.getCell(9,0)); //A10
    cella3.copy(sheet2.getCell(10,0)); //A11
*/    


    ((SheetImpl)sheet2).setCellValue(0, 1, new FormulaImpl("=SUM(A1,A2)")); //B1
    new RangeSimple(sheet2, null, 1, 0, 1, 0).copy(new RangeSimple(sheet2, null, 1, 1, 1, 10)); //B1 -> B2:B11
/*    ((SheetImpl)sheet2).setCellValue(1, 1, new FormulaImpl("=SUM(A2,A3)")); //B2
    ((SheetImpl)sheet2).setCellValue(2, 1, new FormulaImpl("=SUM(A3,A4)")); //B3
    ((SheetImpl)sheet2).setCellValue(3, 1, new FormulaImpl("=SUM(A4,A5)")); //B4
    ((SheetImpl)sheet2).setCellValue(4, 1, new FormulaImpl("=SUM(A5,A6)")); //B5

View Full Code Here

    final SimpleSSDataListener ssl = new SimpleSSDataListener();
    _book.addSSDataListener(ssl);
    assertEquals(0, ssl.getList().size());


    ssl.getList().clear();
    ((SheetImpl)sheet1).setCellValue(5,5, new FormulaImpl("=B1+D1")); //F6


    ssl.getList().clear();
    ((SheetImpl)sheet1).setCellValue(0,1,new Integer(1)); //B1
    assertCellEquals(sheet1.getCell(0, 1), 0, 1, new Integer(1));
    assertEquals(2, ssl.getList().size());

View Full Code Here


  public void testDouble15() {
    Sheet sheet1 = ((BookImpl)_book).addSheet("sheet1", 6, 6);
    assertEquals(sheet1, _book.lookupSheet("sheet1"));
    
    ((SheetImpl)sheet1).setCellValue(5,5, new FormulaImpl("=761.6+2732+20836.1+7262+48.1-10.3")); //F6
    final Cell cell = sheet1.getCell(5,5);
    final Object result = cell.getResult(); //F6
    assertEquals(31629.5d, ((Double)result).doubleValue(), 0d);
  }

View Full Code Here

  
  public void testCell2() {
    Sheet sheet1 = ((BookImpl)_book).addSheet("sheet1", 6, 6);
    assertEquals(sheet1, _book.lookupSheet("sheet1"));
    
    ((SheetImpl)sheet1).setCellValue(5,5, new FormulaImpl("=SUM(A1:E6)")); //F6
    final Cell cell = sheet1.getCell(5,5);
    
    ((SheetImpl)sheet1).setCellValue(0,1,new Integer(1)); //B1
    assertCellEquals(sheet1.getCell(0, 1), 0, 1, new Integer(1));


    ((SheetImpl)sheet1).setCellValue(0,2,new Integer(2)); //C1
    assertCellEquals(sheet1.getCell(0, 2), 0, 2, new Integer(2));
    
    ((SheetImpl)sheet1).setCellValue(0,3,new Integer(3)); //D1
    assertCellEquals(sheet1.getCell(0, 3), 0, 3, new Integer(3));


    final Object result = cell.getResult(); //F6
    assertEquals(6d, ((Double)result).doubleValue(), 0d);
    
    //test RefCell and Function
    final SimpleSSDataListener ssl = new SimpleSSDataListener();
    _book.addSSDataListener(ssl);
    assertEquals(0, ssl.getList().size());


    ((SheetImpl)sheet1).getCell(0,1).setValue(5); //change B1 (1 -> 5) => F6.
System.out.println(Objects.toString(ssl.getList()));
    assertEquals(2, ssl.getList().size());


    for(final Iterator it = ssl.getList().iterator(); it.hasNext();) {
      final SSDataEvent evt = (SSDataEvent) it.next();
      final Range rng = evt.getRange();
      if (rng.getLeft() == 1 && rng.getTop() == 0) {
        assertEquals(5d, ((Number)sheet1.getCell(0,1).getResult()).doubleValue(), 0d);
      } else  if (rng.getLeft() == 5 && rng.getTop() == 5) {
        assertEquals(10d, ((Number)sheet1.getCell(5,5).getResult()).doubleValue(), 0d);
      } else {
        assertFalse("should not come here", false);
      }
    }
    
    //test FormulaListener
    final SimpleFormulaListener sl = new SimpleFormulaListener("=sheet1!F6");
    _book.addFormulaListener(sl);
    assertNull("sl.getValue() must be null", sl.getValue());
    
    ((SheetImpl)sheet1).getCell(0,1).setValue(10); //make cascade dependent change B1 (5 -> 10) => F6.
    assertEquals(15d, ((Number)sl.getValue()).doubleValue(), 0d);


    //test UtilFns.getResult()
    assertEquals(15d, ((Number)UtilFns.result(_book, "=sheet1!F6")).doubleValue(), 0d);
    
    //test change F6 and change it back, and FormulaListner still work
    ((SheetImpl)sheet1).setCellValue(5,5, new FormulaImpl("=100")); //F6
    assertEquals(100d, ((Number)sl.getValue()).doubleValue(), 0d);


    ((SheetImpl)sheet1).setCellValue(5,5, new FormulaImpl("=SUM(A1:E6)")); //F6
    assertEquals(15d, ((Number)sl.getValue()).doubleValue(), 0d);
    
    ((SheetImpl)sheet1).getCell(0,1).setValue(5); //make cascade dependent change B1 (10 -> 5) => F6.
    assertEquals(10d, ((Number)sl.getValue()).doubleValue(), 0d);
  }

View Full Code Here

TOP

Related Classes of org.zkoss.zss.model.impl.FormulaImpl

org.zkoss.zss.engine.fn.UtilFns

org.zkoss.zss.engine.xel.SSExpression

org.zkoss.zss.model.impl.CellTest

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.