Package org.zkoss.zss.model

Examples of org.zkoss.zss.model.Sheet


    ((SheetImpl)sheet1).setCellEditText(1,0,"=ERROR.TYPE(A1)"); //A2
    assertEquals(new Integer(7), cellA2.getResult());
  }

  public void testCell1() {
    Sheet sheet1 = ((BookImpl)_book).addSheet("sheet1", 256, 64*1024);
    assertEquals(sheet1, _book.lookupSheet("sheet1"));
   
    ((SheetImpl)sheet1).setCellValue(2,0,null); //A3
    final Cell cell = sheet1.getCell(2,0);
    assertCellEquals(cell, 2, 0, null);
   
    ((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));
   

    cell.setFormula("=SUM(B1)");
    Object result = cell.getResult();
    assertEquals(new Integer(1).doubleValue(), ((Number)result).doubleValue(), 0d);
   
    cell.setFormula("=SUM(C1)");
    result = cell.getResult();
    assertEquals(new Integer(2).doubleValue(), ((Number)result).doubleValue(), 0d);

    cell.setFormula("=SUM(D1)");
    result = cell.getResult();
    assertEquals(new Integer(3).doubleValue(), ((Number)result).doubleValue(), 0d);

    cell.setFormula("=SUM(E1)");
    result = cell.getResult();
    assertEquals(new Integer(0).doubleValue(), ((Number)result).doubleValue(), 0d);

    //given String and do SUM()
    ((SheetImpl)sheet1).setCellValue(0,1,"1"); //B1
    assertCellEquals(sheet1.getCell(0, 1), 0, 1, "1");

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

    cell.setFormula("=SUM(B1)");
    result = cell.getResult();
    assertEquals(1d, ((Number)result).doubleValue(), 0d);
   
    cell.setFormula("=SUM(C1)");
    result = cell.getResult();
    assertEquals(2d, ((Number)result).doubleValue(), 0d);

    cell.setFormula("=SUM(D1)");
    result = cell.getResult();
    assertEquals(3d, ((Number)result).doubleValue(), 0d);
   
    //test one dimension range
    cell.setFormula("=SUM(B1:D1)");
    result = cell.getResult();
    assertEquals(0d, ((Number)result).doubleValue(), 0d);
   
    cell.setFormula("=SUM(B1:D1,\"1\")");
    result = cell.getResult();
    assertEquals(1d, ((Number)result).doubleValue(), 0d);

    cell.setFormula("=SUM(B1:D1,TRUE)");
    result = cell.getResult();
    assertEquals(1d, ((Number)result).doubleValue(), 0d);

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

    cell.setFormula("=SUM(B1:D1)");
    result = cell.getResult();
    assertEquals(3d, ((Number)result).doubleValue(), 0d);

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

    cell.setFormula("=SUM(B1:D1)");
    result = cell.getResult();
    assertEquals(5d, ((Number)result).doubleValue(), 0d);

    ((SheetImpl)sheet1).setCellValue(0,1, Boolean.TRUE); //B1
    assertCellEquals(sheet1.getCell(0, 1), 0, 1, Boolean.TRUE);

    cell.setFormula("=SUM(B1:D1)");
    result = cell.getResult();
    assertEquals(5d, ((Number)result).doubleValue(), 0d);

    ((SheetImpl)sheet1).setCellValue(0,1, new Integer(1)); //B1
    assertCellEquals(sheet1.getCell(0, 1), 0, 1, new Integer(1));

    cell.setFormula("=SUM(B1:D1)");
    result = cell.getResult();
    assertEquals(6d, ((Number)result).doubleValue(), 0d);

    //test one row range
    cell.setFormula("=SUM(1:1)");
    result = cell.getResult();
    assertEquals(6d, ((Number)result).doubleValue(), 0d);
   
    //test two dimension range
    ((SheetImpl)sheet1).setCellValue(1,3,new Integer(100)); //D2
    assertCellEquals(sheet1.getCell(1, 3), 1, 3, new Integer(100));

    ((SheetImpl)sheet1).setCellValue(2,3,new Integer(50)); //D3
    assertCellEquals(sheet1.getCell(2, 3), 2, 3, new Integer(50));
   
    cell.setFormula("=SUM(B1:D3)");
    result = cell.getResult();
    assertEquals(156d, ((Number)result).doubleValue(), 0d);

    ((SheetImpl)sheet1).setCellValue(1, 10,""); //K2
    cell.setFormula("=SUM(B1:D3)-K2");
    result = cell.getResult();
    assertEquals(156d, ((Number)result).doubleValue(), 0d);
   
   
    //test range3d
    Sheet sheet2 = ((BookImpl)_book).addSheet("sheet2", 256, 64*1024);
   
    ((SheetImpl)sheet2).setCellValue(0,1,new Integer(10)); //B1
    assertCellEquals(sheet2.getCell(0, 1), 0, 1, new Integer(10));

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

    cell.setFormula("=SUM(sheet1:sheet2!B1:D1)");
    result = cell.getResult();
    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
    ((SheetImpl)sheet2).setCellValue(5, 1, new FormulaImpl("=SUM(A6,A7)")); //B6
    ((SheetImpl)sheet2).setCellValue(6, 1, new FormulaImpl("=SUM(A7,A8)")); //B7
    ((SheetImpl)sheet2).setCellValue(7, 1, new FormulaImpl("=SUM(A8,A9)")); //B8
    ((SheetImpl)sheet2).setCellValue(8, 1, new FormulaImpl("=SUM(A9,A10)")); //B9
    ((SheetImpl)sheet2).setCellValue(9, 1, new FormulaImpl("=SUM(A10,A11)")); //B10
    ((SheetImpl)sheet2).setCellValue(10, 1, new FormulaImpl("=SUM(A11,A12)")); //B11
*/   
    //A1 B1 ~ A11 B11
    result = sheet2.getCell(0, 0).getResult(); //A1
    assertEquals(1d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(0, 1).getResult(); //B1
    assertEquals(3d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(1, 0).getResult(); //A2
    assertEquals(2d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(1, 1).getResult(); //B2
    assertEquals(5d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(2, 0).getResult(); //A3
    assertEquals(3d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(2, 1).getResult(); //B3
    assertEquals(8d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(3, 0).getResult(); //A4
    assertEquals(5d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(3, 1).getResult(); //B4
    assertEquals(13d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(4, 0).getResult(); //A5
    assertEquals(8d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(4, 1).getResult(); //B5
    assertEquals(21d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(5, 0).getResult(); //A6
    assertEquals(13d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(5, 1).getResult(); //B6
    assertEquals(34d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(6, 0).getResult(); //A7
    assertEquals(21d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(6, 1).getResult(); //B7
    assertEquals(55d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(7, 0).getResult(); //A8
    assertEquals(34d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(7, 1).getResult(); //B8
    assertEquals(89d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(8, 0).getResult(); //A9
    assertEquals(55d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(8, 1).getResult(); //B9
    assertEquals(144d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(9, 0).getResult(); //A10
    assertEquals(89d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(9, 1).getResult(); //B10
    assertEquals(233d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(10, 0).getResult(); //A11
    assertEquals(144d, ((Number)result).doubleValue(), 0d);
    result = sheet2.getCell(10, 1).getResult(); //B11
    assertEquals(144d, ((Number)result).doubleValue(), 0d);

    //A1 ~ A11
    result = sheet2.getCell(0, 0).getResult(); //A1
    assertEquals(1d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(1, 0).getResult(); //A2
    assertEquals(2d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(2, 0).getResult(); //A3
    assertEquals(3d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(3, 0).getResult(); //A4
    assertEquals(5d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(4, 0).getResult(); //A5
    assertEquals(8d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(5, 0).getResult(); //A6
    assertEquals(13d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(6, 0).getResult(); //A7
    assertEquals(21d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(7, 0).getResult(); //A8
    assertEquals(34d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(8, 0).getResult(); //A9
    assertEquals(55d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(9, 0).getResult(); //A10
    assertEquals(89d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(10, 0).getResult(); //A11
    assertEquals(144d, ((Number)result).doubleValue(), 0d);

    //B1 ~ B11
    result = sheet2.getCell(0, 1).getResult(); //B1
    assertEquals(3d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(1, 1).getResult(); //B2
    assertEquals(5d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(2, 1).getResult(); //B3
    assertEquals(8d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(3, 1).getResult(); //B4
    assertEquals(13d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(4, 1).getResult(); //B5
    assertEquals(21d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(5, 1).getResult(); //B6
    assertEquals(34d, ((Number)result).doubleValue(), 0d);
   
    result = sheet2.getCell(6, 1).getResult(); //B7
    assertEquals(55d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(7, 1).getResult(); //B8
    assertEquals(89d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(8, 1).getResult(); //B9
    assertEquals(144d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(9, 1).getResult(); //B10
    assertEquals(233d, ((Number)result).doubleValue(), 0d);

    result = sheet2.getCell(10, 1).getResult(); //B11
    assertEquals(144d, ((Number)result).doubleValue(), 0d);
   
    //test Range.cut()
    new RangeSimple(sheet2, null, 1, 0, 1, 0).cut(new RangeSimple(sheet2, null, 2, 1, 2, 1)); //B1 -> C2
    result = sheet2.getCell(1, 2).getResult(); //C2 == B1
    assertEquals(3d, ((Number)result).doubleValue(), 0d);
    assertNotNull(((SheetImpl)sheet2).getMatrix().getRef(sheet2, null, 2,1,2,1));
    assertNull(sheet2.getCell(0, 1)); //B1 -> no cell
   
   
    //test dependent graph
    sheet2.getCell(0, 0).setValue(new Integer(5));
   
    //test logic functions
    cell.setFormula("=TRUE()");
    result = cell.getResult();
    assertEquals(Boolean.TRUE, result);
View Full Code Here


 
  public void testImportedColor(){
    if(true) return;//never success now;
    final String nm = "B-1982904.xls";
    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
   
    int[][] cells = new int[][]{{1,1},{2,1},{3,1},{4,1},{5,1},{6,1}};
    String[] colors = new String[]{"#CC0000","#CC9900","#008000","#006699","#3333FF","#FF00FF",};
   
   
    Cell cell;
    Format format;
    String color;
    for(int i=0;i<cells.length;i++){
      cell = sheet1.getCell(cells[i][0],cells[i][1]);
      assertNotNull(cell);
      format = cell.getFormat();
      assertNotNull(format);
      color = format.getFillColor();
      assertNotNull(color);
View Full Code Here

   * Test method for {@link jxl.read.biff.WorkbookParser#parse()}.
   */
  public void testVariableResolving() {
    final String nm = "var1.xls";
    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
    assertEquals("=ABCD.value.text", sheet1.getCell(0,0).getEditText());
    assertEquals("=ivan", sheet1.getCell(1,0).getEditText());
    assertEquals("=ivnulla", sheet1.getCell(2,0).getEditText());
   
    assertEquals("#NAME?", sheet1.getCell(0,0).getText());
   
    Map map = new HashMap();
    SimpleResolver resolver = new SimpleResolver(map);
    map.put("ABCD", new VarObject("Hello ZK"));
    book.addVariableResolver(resolver);
   
    SimpleSSDataListener listener = new SimpleSSDataListener();
    book.addSSDataListener(listener);
   
    book.notifyChange(new String[] {"ABCE"});
    List events = listener.getList();
    assertEquals(0, events.size());
   
    book.notifyChange(new String[] {"ABCD"});
    events = listener.getList();
    assertEquals(1, events.size());

    SSDataEvent event = (SSDataEvent) events.get(0);
    Range rng = event.getRange();
   
    assertEquals(0, rng.getTop()); //row
    assertEquals(0, rng.getLeft()); //column
   
    assertEquals("Hello ZK", sheet1.getCell(rng.getTop(),rng.getLeft()).getText());
   
  }
View Full Code Here

 
  public void testFunctionMapper() {
    final String nm = "fun1.xls";

    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
    assertEquals("#NAME?", sheet1.getCell(0,0).getText());

    Map map = new HashMap();
    SimpleResolver resolver = new SimpleResolver(map);
    map.put("DEF", "Hello ZK");
    book.addVariableResolver(resolver);

    FunctionMapper mapper = new MyMapper();
    book.addFunctionMapper(mapper);
    Cell cellA1 = sheet1.getCell(0,0);
System.out.println(cellA1.getValue());
   
Cell cellA2 = sheet1.getCell(1,0);
System.out.println(cellA2.getValue());

    assertEquals("Hello ZK", cellA1.getText());
  }
View Full Code Here

 
  public void testSpecialFunction2() {
    final String nm = "fun2.xls";

    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
    Cell cellA1 = sheet1.getCell(0,0);
    assertEquals("=MROUND(10.0,3.0)", cellA1.getEditText());
    assertEquals("9", cellA1.getText());
   
    Cell cellB1 = sheet1.getCell(0,1);
    assertEquals("=LCM(5.0,2.0)", cellB1.getEditText());
    assertEquals("10", cellB1.getText());
  }
View Full Code Here

 
  public void testSpecialFunction3() {
    final String nm = "fun3.xls";

    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
    Cell cellA1 = sheet1.getCell(0,0);
    assertEquals("=DB(3000000.0,200000.0,10.0,2.0,9.0)", cellA1.getEditText());
    Cell cellA2 = sheet1.getCell(1,0);
    assertEquals("=PMT(0.1/12.0,6.0,100000.0,2000.0,1.0)", cellA2.getEditText());
//    assertEquals("9", cellA1.getText());
  }
View Full Code Here

        doCellChange(tbxval.getValue());
      }
    });
  }
  void onCellEvent(CellEvent event){
    Sheet sheet = event.getSheet();
    lastRow = event.getRow();
    lastCol = event.getColumn();
    Label lbpos = (Label)getFellow("lbpos");
    Textbox tbxval = (Textbox)getFellow("tbxval");
   
    Cell cell = sheet.getCell(lastRow, lastCol);
    lbpos.setValue(Indexes.toA1(lastRow,lastCol,false,false));
    tbxval.setValue(cell == null ? "" : cell.getEditText());
  }
View Full Code Here

   
  void doCellChange(String value){
    if(lastRow == -1){
      return;
    }
    Sheet sheet = (Sheet)book.getSheets().get(0);
    Cell cell = sheet.getCell(lastRow, lastCol);
    if(cell==null){
      sheet.setCellValue(lastRow, lastCol, "");
      cell = (Cell)sheet.getCell(lastRow,lastCol);
    }
    cell.setEditText(value);
  }
View Full Code Here

  }

  public void testImage() {
    final String nm = "image.xls";
    Book book = new ExcelImporter().imports(getClass().getResource("/"+nm));
    Sheet sheet1 = (Sheet) book.getSheets().get(0);
  }
View Full Code Here

  public void testRefARange() {
    Book book = new ExcelImporter().imports(getClass().getResource("/refrange.xls"));
    String[] names = book.getNameRangeNames();
    System.out.println("names="+Objects.toString(names));
    Sheet sheet = (Sheet) book.getSheets().get(0);

    //test single column range
    for (int j = 1; j < 8; ++j) { //A1 ~ A7
      Cell cell = sheet.getCell(j-1,0);
      assertEquals(""+j, cell.getText());
    }
    {
      Cell cell = sheet.getCell(7,0); //A8
      assertEquals("#VALUE!", cell.getText());
    }

    //test single row range
    for (int j = 1; j < 7; ++j) { //A9 ~ F9
      Cell cell = sheet.getCell(8, j-1);
      assertEquals(""+(j*10), cell.getText());
    }
    {
      Cell cell = sheet.getCell(8,6); //G9
      assertEquals("#VALUE!", cell.getText());
    }
   
    //test multiple row and column range
    {
      Cell cell = sheet.getCell(11,0); //A12
      assertEquals("#VALUE!", cell.getText());
    }
   
    //test single cell range
    {
      Cell cell = sheet.getCell(12,0); //A13
      assertEquals("100", cell.getText());
    }
  }
View Full Code Here

TOP

Related Classes of org.zkoss.zss.model.Sheet

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.