((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);