@Test
  public void testDataValidationHelperFormula() {
    SBook book = SBooks.createBook("book1");
    SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
    SRanges.range(sheet1, 0, 0).setEditText("A");
    SRanges.range(sheet1, 0, 1).setEditText("B");
    SRanges.range(sheet1, 0, 2).setEditText("C");
    SRanges.range(sheet1, 0, 3).setEditText("D");
    SRanges.range(sheet1, 1, 0).setEditText("1");
    SRanges.range(sheet1, 1, 1).setEditText("2");
    SRanges.range(sheet1, 1, 2).setEditText("3");
    SRanges.range(sheet1, 1, 3).setEditText("4");
    SRanges.range(sheet1, 2, 0).setEditText("2013/1/1");
    SRanges.range(sheet1, 2, 1).setEditText("2013/1/2");
    SRanges.range(sheet1, 2, 2).setEditText("2013/1/3");
    SRanges.range(sheet1, 2, 3).setEditText("2013/1/4");

    SDataValidation dv0 = sheet1.addDataValidation(new CellRegion(0, 4));

    String dateFormat = "yyyy/m/d";
    String numberFormat = "General";
    // test integer
    dv0.setValidationType(ValidationType.LIST);
    dv0.setFormula("{1,2,3}");
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=A2", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=B2", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=C2", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=D2", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=E2", numberFormat));

    dv0.setFormula("{\"A\",\"B\",\"C\"}");
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=A1", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=B1", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=C1", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=D1", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=E1", numberFormat));

    dv0.setFormula("A1:C1");
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=A1", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=B1", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=C1", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=D1", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=E1", numberFormat));

    dv0.setFormula("A2:C2");
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=A2", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=B2", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=C2", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=D2", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=E2", numberFormat));

    dv0.setFormula("A3:C3");
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=A3", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=B3", numberFormat));
    Assert.assertTrue(new DataValidationHelper(dv0).validate("=C3", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=D3", numberFormat));
    Assert.assertFalse(new DataValidationHelper(dv0).validate("=E3", numberFormat));
  }
  @Test
  public void testDataValidation() {
    SBook book = SBooks.createBook("book1");
    SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
    sheet1.getCell(0, 0).setValue(1D);
    sheet1.getCell(0, 1).setValue(2D);
    sheet1.getCell(0, 2).setValue(3D);

    SDataValidation dv1 = sheet1.addDataValidation(new CellRegion(1, 1));
    SDataValidation dv2 = sheet1.addDataValidation(new CellRegion(1, 2));
    SDataValidation dv3 = sheet1.addDataValidation(new CellRegion(1, 3));
    // LIST
    dv1.setValidationType(ValidationType.LIST);
    dv1.setFormula("A1:C1");

    Assert.assertEquals(3, dv1.getNumOfValue1());
    Assert.assertEquals(0, dv1.getNumOfValue2());
    Assert.assertEquals(1D, dv1.getValue1(0));
    Assert.assertEquals(2D, dv1.getValue1(1));
    Assert.assertEquals(3D, dv1.getValue1(2));

    dv2.setValidationType(ValidationType.INTEGER);
    dv2.setFormula("A1", "C1");
    Assert.assertEquals(1, dv2.getNumOfValue1());
    Assert.assertEquals(1, dv2.getNumOfValue2());
    Assert.assertEquals(1D, dv2.getValue1(0));
    Assert.assertEquals(3D, dv2.getValue2(0));

    dv3.setValidationType(ValidationType.INTEGER);
    dv3.setFormula("AVERAGE(A1:C1)", "SUM(A1:C1)");
    Assert.assertEquals(1, dv3.getNumOfValue1());
    Assert.assertEquals(1, dv3.getNumOfValue2());
    Assert.assertEquals(2D, dv3.getValue1(0));
    Assert.assertEquals(6D, dv3.getValue2(0));

    SRanges.range(sheet1, 0, 0).setEditText("2");
    SRanges.range(sheet1, 0, 1).setEditText("4");
    SRanges.range(sheet1, 0, 2).setEditText("6");

    Assert.assertEquals(3, dv1.getNumOfValue1());
    Assert.assertEquals(0, dv1.getNumOfValue2());
    Assert.assertEquals(2D, dv1.getValue1(0));
    Assert.assertEquals(4D, dv1.getValue1(1));
    Assert.assertEquals(6D, dv1.getValue1(2));

    Assert.assertEquals(1, dv2.getNumOfValue1());
    Assert.assertEquals(1, dv2.getNumOfValue2());
    Assert.assertEquals(2D, dv2.getValue1(0));
    Assert.assertEquals(6D, dv2.getValue2(0));

    Assert.assertEquals(1, dv3.getNumOfValue1());
    Assert.assertEquals(1, dv3.getNumOfValue2());
    Assert.assertEquals(4D, dv3.getValue1(0));
    Assert.assertEquals(12D, dv3.getValue2(0));

    DependencyTable table = ((AbstractBookSeriesAdv) book.getBookSeries()).getDependencyTable();

    Set<Ref> refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 0)));
    Assert.assertEquals(3, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 1)));
    Assert.assertEquals(2, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 2)));
    Assert.assertEquals(3, refs.size());

    sheet1.deleteDataValidation(dv1);
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 0)));
    Assert.assertEquals(2, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 1)));
    Assert.assertEquals(1, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 2)));
    Assert.assertEquals(2, refs.size());

    sheet1.deleteDataValidation(dv2);
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 0)));
    Assert.assertEquals(1, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 1)));
    Assert.assertEquals(1, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 2)));
    Assert.assertEquals(1, refs.size());

    sheet1.deleteDataValidation(dv3);
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 0)));
    Assert.assertEquals(0, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 1)));
    Assert.assertEquals(0, refs.size());
    refs = table.getDependents(new RefImpl((AbstractCellAdv) sheet1.getCell(0, 2)));
    Assert.assertEquals(0, refs.size());
  }
  @Test
  public void testDataValidationHelperTextlength() {
    SBook book = SBooks.createBook("book1");
    SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
    sheet1.getCell(0, 0).setValue(1D); // min
    sheet1.getCell(0, 1).setValue(2D); // max SUM(A1:A2)

    SDataValidation dv1 = sheet1.addDataValidation(new CellRegion(1, 1));

    // test integer
    dv1.setValidationType(ValidationType.TEXT_LENGTH);
    dv1.setFormula("A1", "SUM(A1:B1)"); // 1-3
    dv1.setOperatorType(OperatorType.BETWEEN);

    Assert.assertTrue(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AAA", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AAAA", "General"));

    dv1.setOperatorType(OperatorType.NOT_BETWEEN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AAA", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AAAA", "General"));

    dv1.setOperatorType(OperatorType.EQUAL);
    Assert.assertTrue(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AAA", "General"));

    dv1.setOperatorType(OperatorType.NOT_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AAA", "General"));

    dv1.setOperatorType(OperatorType.GREATER_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AA", "General"));

    dv1.setOperatorType(OperatorType.GREATER_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("AA", "General"));

    dv1.setOperatorType(OperatorType.LESS_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("", "General"));

    dv1.setOperatorType(OperatorType.LESS_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("AA", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("A", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("", "General"));
  }
  @Test
  public void testDataValidationHelperTime() {
    SBook book = SBooks.createBook("book1");
    SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
    SRanges.range(sheet1, 0, 0).setEditText("12:00"); // time start
    SRanges.range(sheet1, 0, 1).setEditText("14:00"); // time end

    SDataValidation dv1 = sheet1.addDataValidation(new CellRegion(1, 1));

    // test integer
    dv1.setValidationType(ValidationType.TIME);
    dv1.setFormula("A1", "B1"); // 12:00 - 14:00
    dv1.setOperatorType(OperatorType.BETWEEN);
    String format = "h:mm";
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("14:00", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("11:59", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("14:01", format));

    dv1.setOperatorType(OperatorType.NOT_BETWEEN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("14:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("11:59", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("14:01", format));

    dv1.setOperatorType(OperatorType.EQUAL);
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("14:00", format));

    dv1.setOperatorType(OperatorType.NOT_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("14:00", format));

    dv1.setOperatorType(OperatorType.GREATER_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("11:59", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:01", format));

    dv1.setOperatorType(OperatorType.GREATER_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("11:59", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:01", format));

    dv1.setOperatorType(OperatorType.LESS_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("11:59", format));

    dv1.setOperatorType(OperatorType.LESS_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:01", format));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("12:00", format));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("11:59", format));
  }
  @Test
  public void testDataValidationHelperInteger() {
    SBook book = SBooks.createBook("book1");
    SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
    sheet1.getCell(0, 0).setValue(1D); // min
    sheet1.getCell(0, 1).setValue(3D); // max
    SRanges.range(sheet1, 0, 2).setEditText("2013/1/1"); // day start
    SRanges.range(sheet1, 0, 3).setEditText("2013/2/1"); // day end
    SRanges.range(sheet1, 0, 4).setEditText("12:00"); // time start
    SRanges.range(sheet1, 0, 5).setEditText("14:00"); // time end

    Assert.assertEquals(CellType.NUMBER, sheet1.getCell(0, 2).getType());
    Assert.assertEquals("2013/1/1", SRanges.range(sheet1, 0, 2).getCellFormatText());
    Assert.assertEquals(CellType.NUMBER, sheet1.getCell(0, 3).getType());
    Assert.assertEquals("2013/2/1", SRanges.range(sheet1, 0, 3).getCellFormatText());
    Assert.assertEquals(CellType.NUMBER, sheet1.getCell(0, 4).getType());
    Assert.assertEquals("12:00", SRanges.range(sheet1, 0, 4).getCellFormatText());
    Assert.assertEquals(CellType.NUMBER, sheet1.getCell(0, 5).getType());
    Assert.assertEquals("14:00", SRanges.range(sheet1, 0, 5).getCellFormatText());

    SDataValidation dv1 = sheet1.addDataValidation(new CellRegion(1, 1));
    // test any
    Assert.assertTrue(new DataValidationHelper(dv1).validate("123", "General"));

    // test integer
    dv1.setValidationType(ValidationType.INTEGER);
    dv1.setFormula("1", "3");
    dv1.setOperatorType(OperatorType.BETWEEN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("1.3", "General")); // not integer

    Assert.assertTrue(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("3", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("0", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("4", "General"));

    dv1.setOperatorType(OperatorType.NOT_BETWEEN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("3", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("0", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("4", "General"));

    dv1.setOperatorType(OperatorType.EQUAL);
    Assert.assertTrue(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("3", "General"));

    dv1.setOperatorType(OperatorType.NOT_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("3", "General"));

    dv1.setOperatorType(OperatorType.GREATER_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("0", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("2", "General"));

    dv1.setOperatorType(OperatorType.GREATER_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("0", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("2", "General"));

    dv1.setOperatorType(OperatorType.LESS_OR_EQUAL);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("0", "General"));

    dv1.setOperatorType(OperatorType.LESS_THAN);
    Assert.assertFalse(new DataValidationHelper(dv1).validate("2", "General"));
    Assert.assertFalse(new DataValidationHelper(dv1).validate("1", "General"));
    Assert.assertTrue(new DataValidationHelper(dv1).validate("0", "General"));
  }