@Test
public void movePicture() {
SBook book = SBooks.createBook("book1");
SSheet sheet = book.createSheet("Picture");
assertEquals(0, sheet.getPictures().size());
try {
AImage zklogo = new AImage(RangeTest.class.getResource("zklogo.png"));
ViewAnchor anchor = new ViewAnchor(0, 1, zklogo.getWidth() / 2, zklogo.getHeight() / 2);
SPicture picture =
SRanges.range(sheet).addPicture(anchor, zklogo.getByteData(), SPicture.Format.PNG);
assertEquals(1, sheet.getPictures().size());
assertEquals(Format.PNG, picture.getFormat());
assertEquals(zklogo.getWidth() / 2, picture.getAnchor().getWidth());
ViewAnchor newAnchor = new ViewAnchor(3, 4, zklogo.getWidth() / 2, zklogo.getHeight() / 2);
SRanges.range(sheet).movePicture(picture, newAnchor);
assertEquals(3, picture.getAnchor().getRowIndex());
assertEquals(4, picture.getAnchor().getColumnIndex());
// ImExpTestUtil.write(book, Type.XLSX); //human checking
} catch (IOException e) {
e.printStackTrace();
fail();
}
}
@Test
public void testDataValidationHelperDate() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = initialDataGrid(book.createSheet("Sheet1"));
SRanges.range(sheet1, 0, 0).setEditText("2013/1/10"); // day start
SRanges.range(sheet1, 0, 1).setEditText("2013/2/1"); // day end
SRanges.range(sheet1, 0, 2).setEditText("12:00"); // time start
SRanges.range(sheet1, 0, 3).setEditText("14:00"); // time end
SDataValidation dv1 = sheet1.addDataValidation(new CellRegion(1, 1));
// test integer
dv1.setValidationType(ValidationType.DATE);
dv1.setFormula("A1", "B1"); // 2013/1/10 - 2013/2/1
dv1.setOperatorType(OperatorType.BETWEEN);
String format = "yyyy/m/d";
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/2/1", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/9", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/2/2", format));
dv1.setOperatorType(OperatorType.NOT_BETWEEN);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/2/1", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/9", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/2/2", format));
dv1.setOperatorType(OperatorType.EQUAL);
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/2/1", format));
dv1.setOperatorType(OperatorType.NOT_EQUAL);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/2/1", format));
dv1.setOperatorType(OperatorType.GREATER_OR_EQUAL);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/9", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/15", format));
dv1.setOperatorType(OperatorType.GREATER_THAN);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/9", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/15", format));
dv1.setOperatorType(OperatorType.LESS_OR_EQUAL);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/9", format));
dv1.setOperatorType(OperatorType.LESS_THAN);
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/15", format));
Assert.assertFalse(new DataValidationHelper(dv1).validate("2013/1/10", format));
Assert.assertTrue(new DataValidationHelper(dv1).validate("2013/1/9", format));
}
@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 testDataValidationHelperDecimal() {
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.DECIMAL);
dv1.setFormula("A1", "SUM(A1:B1)"); // 1-3
dv1.setOperatorType(OperatorType.BETWEEN);
Assert.assertTrue(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"));
}
@Test
public void testFormulaDependency() {
SBook book = SBooks.createBook("book1");
SSheet sheet = book.createSheet("Sheet 1");
SRanges.range(sheet, 0, 0).setEditText("999");
SRanges.range(sheet, 0, 1).setValue("=SUM(A1)");
SCell cell = sheet.getCell(0, 0);
Assert.assertEquals(CellType.NUMBER, cell.getType());
Assert.assertEquals(999, cell.getNumberValue().intValue());
cell = sheet.getCell(0, 1);
Assert.assertEquals(CellType.FORMULA, cell.getType());
Assert.assertEquals(CellType.NUMBER, cell.getFormulaResultType());
Assert.assertEquals("SUM(A1)", cell.getFormulaValue());
Assert.assertEquals(999D, cell.getValue());
final AtomicInteger a0counter = new AtomicInteger(0);
final AtomicInteger b0counter = new AtomicInteger(0);
final AtomicInteger unknowcounter = new AtomicInteger(0);
book.addEventListener(
new ModelEventListener() {
public void onEvent(ModelEvent event) {
if (event.getName().equals(ModelEvents.ON_CELL_CONTENT_CHANGE)) {
CellRegion region = event.getRegion();
if (region.getRow() == 0 && region.getColumn() == 0) {
a0counter.incrementAndGet();
} else if (region.getRow() == 0 && region.getColumn() == 1) {
b0counter.incrementAndGet();
} else {
unknowcounter.incrementAndGet();
}
}
}
});
SRanges.range(sheet, 0, 0).setEditText("888");
Assert.assertEquals(1, b0counter.intValue());
Assert.assertEquals(1, a0counter.intValue());
Assert.assertEquals(0, unknowcounter.intValue());
SRanges.range(sheet, 0, 0).setEditText("777");
Assert.assertEquals(2, b0counter.intValue());
Assert.assertEquals(2, a0counter.intValue());
Assert.assertEquals(0, unknowcounter.intValue());
SRanges.range(sheet, 0, 0)
.setEditText("777"); // in last update, set edit text is always notify cell change
Assert.assertEquals(3, b0counter.intValue());
Assert.assertEquals(3, a0counter.intValue());
Assert.assertEquals(0, unknowcounter.intValue());
}
@Test
public void testGeneralCellValue1() {
SBook book = SBooks.createBook("book1");
SSheet sheet = book.createSheet("Sheet 1");
Date now = new Date();
ErrorValue err = new ErrorValue(ErrorValue.INVALID_FORMULA);
SCell cell = sheet.getCell(1, 1);
Assert.assertEquals(CellType.BLANK, cell.getType());
Assert.assertNull(cell.getValue());
SRanges.range(sheet, 1, 1).setEditText("abc");
Assert.assertEquals(CellType.STRING, cell.getType());
Assert.assertEquals("abc", cell.getValue());
SRanges.range(sheet, 1, 1).setEditText("123");
Assert.assertEquals(CellType.NUMBER, cell.getType());
Assert.assertEquals(123, cell.getNumberValue().intValue());
SRanges.range(sheet, 1, 1).setEditText("2013/01/01");
Assert.assertEquals(CellType.NUMBER, cell.getType());
Assert.assertEquals(
"2013/01/01", new SimpleDateFormat("yyyy/MM/dd").format((Date) cell.getDateValue()));
SRanges.range(sheet, 1, 1).setEditText("tRue");
Assert.assertEquals(CellType.BOOLEAN, cell.getType());
Assert.assertEquals(Boolean.TRUE, cell.getBooleanValue());
SRanges.range(sheet, 1, 1).setEditText("FalSe");
Assert.assertEquals(CellType.BOOLEAN, cell.getType());
Assert.assertEquals(Boolean.FALSE, cell.getBooleanValue());
SRanges.range(sheet, 1, 1).setEditText("=SUM(999)");
Assert.assertEquals(CellType.FORMULA, cell.getType());
Assert.assertEquals(CellType.NUMBER, cell.getFormulaResultType());
Assert.assertEquals("SUM(999)", cell.getFormulaValue());
Assert.assertEquals(999D, cell.getValue());
try {
SRanges.range(sheet, 1, 1).setEditText("=SUM)((999)");
Assert.fail("not here");
} catch (InvalidModelOpException x) {
// old value
Assert.assertEquals(CellType.FORMULA, cell.getType());
Assert.assertEquals(CellType.NUMBER, cell.getFormulaResultType());
Assert.assertEquals("SUM(999)", cell.getFormulaValue());
Assert.assertEquals(999D, cell.getValue());
}
SRanges.range(sheet, 1, 1).setEditText("");
Assert.assertEquals(CellType.BLANK, cell.getType());
Assert.assertEquals(null, cell.getValue());
}
@Test
public void testSetStyle() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
SCellStyle style1 = book.createCellStyle(true);
SRanges.range(sheet1, "A1:B2").setCellStyle(style1);
Assert.assertEquals(style1, SRanges.range(sheet1, "A1").getCellStyle());
Assert.assertEquals(style1, SRanges.range(sheet1, "A2").getCellStyle());
Assert.assertEquals(style1, SRanges.range(sheet1, "B1").getCellStyle());
Assert.assertEquals(style1, SRanges.range(sheet1, "B2").getCellStyle());
Assert.assertEquals(book.getDefaultCellStyle(), SRanges.range(sheet1, "C1").getCellStyle());
Assert.assertEquals(book.getDefaultCellStyle(), SRanges.range(sheet1, "C2").getCellStyle());
}
@Test
public void testDeleteSheet() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
SSheet sheet2 = book.createSheet("Sheet2");
sheet1.getCell("A1").setValue(11);
sheet1.getCell("B1").setValue("=A1");
sheet2.getCell("B1").setValue("=Sheet1!A1");
Assert.assertEquals(11D, sheet1.getCell("B1").getValue());
Assert.assertEquals(11D, sheet2.getCell("B1").getValue());
SRanges.range(sheet1).deleteSheet();
Assert.assertEquals("#REF!", sheet2.getCell("B1").getErrorValue().getErrorString());
}
@Test
@Ignore("incomplete")
public void bookCreatedInRuntimeTest() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
SCell cell1 = sheet1.getCell(1, 1);
SCell cell2 = sheet1.getCell(1, 2);
SCell cell3 = sheet1.getCell(1, 3);
cell1.setStringValue("hair");
cell2.setStringValue("dot");
cell3.setStringValue("dash");
SCellStyle style1 = book.createCellStyle(true);
style1.setBorderBottom(BorderType.HAIR);
cell1.setCellStyle(style1);
SCellStyle style2 = book.createCellStyle(true);
style2.setBorderBottom(BorderType.DOTTED);
cell2.setCellStyle(style2);
SCellStyle style3 = book.createCellStyle(true);
style3.setBorderBottom(BorderType.DASHED);
cell3.setCellStyle(style3);
SCell cell21 = sheet1.getCell(2, 1);
SCell cell22 = sheet1.getCell(2, 2);
SCell cell23 = sheet1.getCell(2, 3);
SCellStyle style21 = book.createCellStyle(true);
style21.setBorderTop(BorderType.NONE);
cell21.setCellStyle(style21);
SCellStyle style22 = book.createCellStyle(true);
style22.setBorderTop(BorderType.NONE);
cell22.setCellStyle(style22);
SCellStyle style23 = book.createCellStyle(true);
style23.setBorderTop(BorderType.NONE);
cell23.setCellStyle(style23);
// File file = ImExpTestUtil.write(book);
// FIXME assert it
// confirm
// cellBorderTest(inBook);
}
@Test
public void testGeneralCellValue2() {
SBook book = SBooks.createBook("book1");
SSheet sheet = book.createSheet("Sheet 1");
Date now = new Date();
ErrorValue err = new ErrorValue(ErrorValue.INVALID_FORMULA);
SCell cell = sheet.getCell(1, 1);
Assert.assertEquals(CellType.BLANK, cell.getType());
Assert.assertNull(cell.getValue());
SRanges.range(sheet, 1, 1).setValue("abc");
Assert.assertEquals(CellType.STRING, cell.getType());
Assert.assertEquals("abc", cell.getValue());
SRanges.range(sheet, 1, 1).setValue(123D);
Assert.assertEquals(CellType.NUMBER, cell.getType());
Assert.assertEquals(123D, cell.getValue());
SRanges.range(sheet, 1, 1).setValue(now);
Assert.assertEquals(CellType.NUMBER, cell.getType());
Assert.assertEquals(now, cell.getDateValue());
SRanges.range(sheet, 1, 1).setValue(Boolean.TRUE);
Assert.assertEquals(CellType.BOOLEAN, cell.getType());
Assert.assertEquals(Boolean.TRUE, cell.getValue());
SRanges.range(sheet, 1, 1).setValue("=SUM(999)");
Assert.assertEquals(CellType.FORMULA, cell.getType());
Assert.assertEquals(CellType.NUMBER, cell.getFormulaResultType());
Assert.assertEquals("SUM(999)", cell.getFormulaValue());
Assert.assertEquals(999D, cell.getValue());
try {
SRanges.range(sheet, 1, 1).setValue("=SUM)((999)");
Assert.fail("not here");
} catch (InvalidModelOpException x) {
Assert.assertEquals(CellType.FORMULA, cell.getType());
Assert.assertEquals(CellType.NUMBER, cell.getFormulaResultType());
Assert.assertEquals("SUM(999)", cell.getFormulaValue());
Assert.assertEquals(999D, cell.getValue());
}
SRanges.range(sheet, 1, 1).setValue("");
Assert.assertEquals(CellType.STRING, cell.getType());
Assert.assertEquals("", cell.getValue());
}
@Test
public void richTextTest() {
SBook book = SBooks.createBook("rich");
SSheet sheet = book.createSheet("first");
SCell cell = sheet.getCell(0, 0);
SRichText rText = cell.setupRichTextValue();
SFont font1 = book.createFont(true);
font1.setColor(book.createColor("#0000FF"));
font1.setStrikeout(true);
rText.addSegment("abc", font1);
SFont font2 = book.createFont(true);
font2.setColor(book.createColor("#FF0000"));
font2.setBoldweight(Boldweight.BOLD);
rText.addSegment("123", font2);
SFont font3 = book.createFont(true);
font3.setColor(book.createColor("#C78548"));
font3.setUnderline(Underline.SINGLE);
rText.addSegment("xyz", font3);
cell = sheet.getCell(0, 1);
rText = cell.setupRichTextValue();
font1 = book.createFont(true);
font1.setColor(book.createColor("#FFFF00"));
font1.setItalic(true);
rText.addSegment("Hello", font1);
font2 = book.createFont(true);
font2.setColor(book.createColor("#FF33FF"));
font2.setBoldweight(Boldweight.BOLD);
rText.addSegment("World", font2);
font3 = book.createFont(true);
font3.setColor(book.createColor("#CCCC99"));
font3.setName("HGPSoeiKakupoptai");
rText.addSegment("000", font3);
File temp = Setup.getTempFile("pdfExportTest", ".pdf");
exportBook(book, temp);
Util.open(temp);
}
@Test
public void testSetHyperlink() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
SRange range = SRanges.range(sheet1, "A1:B2");
range.setHyperlink(HyperlinkType.URL, "http://www.google.com", "Google");
SHyperlink link = range.getHyperlink();
Assert.assertEquals(HyperlinkType.URL, link.getType());
Assert.assertEquals("http://www.google.com", link.getAddress());
Assert.assertEquals("Google", link.getLabel());
Assert.assertEquals("Google", sheet1.getCell("A1").getStringValue());
link = SRanges.range(sheet1, "B2").getHyperlink();
Assert.assertEquals(HyperlinkType.URL, link.getType());
Assert.assertEquals("http://www.google.com", link.getAddress());
Assert.assertEquals("Google", link.getLabel());
Assert.assertEquals("Google", sheet1.getCell("B2").getStringValue());
}
@Test
public void richTextModelTest() {
SBook book = SBooks.createBook("rich");
SSheet sheet = book.createSheet("first");
SCell cell = sheet.getCell(0, 0);
SRichText rText = cell.setupRichTextValue();
SFont font1 = book.createFont(true);
font1.setColor(book.createColor("#0000FF"));
font1.setStrikeout(true);
rText.addSegment("abc", font1);
SFont font2 = book.createFont(true);
font2.setColor(book.createColor("#FF0000"));
font2.setBoldweight(Boldweight.BOLD);
rText.addSegment("123", font2);
SFont font3 = book.createFont(true);
font3.setColor(book.createColor("#C78548"));
font3.setUnderline(Underline.SINGLE);
rText.addSegment("xyz", font3);
cell = sheet.getCell(0, 1);
rText = cell.setupRichTextValue();
font1 = book.createFont(true);
font1.setColor(book.createColor("#FFFF00"));
font1.setItalic(true);
rText.addSegment("Hello", font1);
font2 = book.createFont(true);
font2.setColor(book.createColor("#FF33FF"));
font2.setBoldweight(Boldweight.BOLD);
rText.addSegment("World", font2);
font3 = book.createFont(true);
font3.setColor(book.createColor("#CCCC99"));
font3.setName("HGPSoeiKakupoptai");
rText.addSegment("000", font3);
ImExpTestUtil.write(book, ExcelExportFactory.Type.XLSX);
}
@SuppressWarnings("unused")
@Test
public void hyperlinkModelTest() {
SBook book = SBooks.createBook("hyperlink");
SSheet sheet = book.createSheet("link");
SCell cell = sheet.getCell(0, 0);
SHyperlink hyperlink =
cell.setupHyperlink(
SHyperlink.HyperlinkType.URL,
"http://www.zkoss.org/download/zkspreadsheet",
"Download ZK Spreadsheet");
cell.setStringValue("Go to ZK Spreadsheet Download Page");
SCell cellB4 = sheet.getCell("B4");
SRichText rText = cellB4.setupRichTextValue();
SFont font1 = book.createFont(true);
font1.setColor(book.createColor("#0000FF"));
font1.setStrikeout(true);
rText.addSegment("abc", font1);
SFont font2 = book.createFont(true);
font2.setColor(book.createColor("#FF0000"));
font2.setBoldweight(Boldweight.BOLD);
rText.addSegment("123", font2);
SFont font3 = book.createFont(true);
font3.setColor(book.createColor("#C78548"));
font3.setUnderline(Underline.SINGLE);
rText.addSegment("xyz", font3);
SHyperlink linkB4 =
cellB4.setupHyperlink(
SHyperlink.HyperlinkType.URL, "http://www.yahoo.com.tw/", "Hyperlink Label B4");
ImExpTestUtil.write(book, ExcelExportFactory.Type.XLSX);
}
@Test
public void testGetRange() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
SRange r1 = SRanges.range(sheet1);
Assert.assertEquals(0, r1.getRow());
Assert.assertEquals(0, r1.getColumn());
Assert.assertEquals(book.getMaxRowIndex(), r1.getLastRow());
Assert.assertEquals(book.getMaxColumnIndex(), r1.getLastColumn());
r1 = SRanges.range(sheet1, 3, 4);
Assert.assertEquals(3, r1.getRow());
Assert.assertEquals(4, r1.getColumn());
Assert.assertEquals(3, r1.getLastRow());
Assert.assertEquals(4, r1.getLastColumn());
r1 = SRanges.range(sheet1, 3, 4, 5, 6);
Assert.assertEquals(3, r1.getRow());
Assert.assertEquals(4, r1.getColumn());
Assert.assertEquals(5, r1.getLastRow());
Assert.assertEquals(6, r1.getLastColumn());
}
@Test
public void testShiftChartFormula() {
SBook book = SBooks.createBook("Book1");
SSheet sheet = book.createSheet("Sheet1");
SChart chart = sheet.addChart(ChartType.LINE, new ViewAnchor(0, 0, 100, 100));
SGeneralChartData data = (SGeneralChartData) chart.getData();
String nameF = "Sheet1:A1";
String areaF = "Sheet1:A2:A3";
data.setCategoriesFormula(areaF);
SSeries series = data.addSeries();
series.setXYZFormula(nameF, areaF, areaF, areaF);
// extend
SRanges.range(sheet, "3")
.getRows()
.insert(InsertShift.DEFAULT, InsertCopyOrigin.FORMAT_NONE); // whole row
areaF = "Sheet1:A2:A4";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// shrink
SRanges.range(sheet, "A3:A4").delete(DeleteShift.UP); // area
areaF = "Sheet1:A2:A2";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// move series name
SRanges.range(sheet, "1").getRows().insert(InsertShift.DEFAULT, InsertCopyOrigin.FORMAT_NONE);
nameF = "Sheet1:A2";
areaF = "Sheet1:A3:A3";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// change data to column direction
nameF = "Sheet1:A1";
areaF = "Sheet1:B1:F1";
data.setCategoriesFormula(areaF);
series.setXYZFormula(nameF, areaF, areaF, areaF);
// extend
SRanges.range(sheet, "C:D")
.getColumns()
.insert(InsertShift.DEFAULT, InsertCopyOrigin.FORMAT_NONE); // whole column
areaF = "Sheet1:B1:H1";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// shrink
SRanges.range(sheet, "B1:F1").delete(DeleteShift.LEFT);
areaF = "Sheet1:B1:C1";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// move series name in column direction
SRanges.range(sheet, "A1:C1").insert(InsertShift.RIGHT, InsertCopyOrigin.FORMAT_NONE);
nameF = "Sheet1:D1";
areaF = "Sheet1:E1:F1";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
// special cases
// delete all
SRanges.range(sheet, "B1:F1").delete(DeleteShift.LEFT);
nameF = "Sheet1:#REF!";
areaF = "Sheet1:#REF!";
assertEquals(areaF, data.getCategoriesFormula());
assertEquals(nameF, series.getNameFormula());
assertEquals(areaF, series.getXValuesFormula());
assertEquals(areaF, series.getYValuesFormula());
assertEquals(areaF, series.getZValuesFormula());
}
@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());
}
/*
* import sheet scope content from POI Sheet.
*/
protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) {
SSheet sheet = book.createSheet(poiSheet.getSheetName());
sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight()));
// ZSS-952
importSheetDefaultColumnWidth(poiSheet, sheet);
// reference FreezeInfoLoaderImpl.getRowFreeze()
sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet));
sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet));
sheet
.getViewInfo()
.setDisplayGridlines(
poiSheet
.isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are
// different
sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks());
sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks());
SPrintSetup sps = sheet.getPrintSetup();
SHeader header = sheet.getViewInfo().getHeader();
if (header != null) {
header.setCenterText(poiSheet.getHeader().getCenter());
header.setLeftText(poiSheet.getHeader().getLeft());
header.setRightText(poiSheet.getHeader().getRight());
sps.setHeader(header);
}
SFooter footer = sheet.getViewInfo().getFooter();
if (footer != null) {
footer.setCenterText(poiSheet.getFooter().getCenter());
footer.setLeftText(poiSheet.getFooter().getLeft());
footer.setRightText(poiSheet.getFooter().getRight());
sps.setFooter(footer);
}
if (poiSheet.isDiffOddEven()) {
Header poiEvenHeader = poiSheet.getEvenHeader();
if (poiEvenHeader != null) {
SHeader evenHeader = new HeaderFooterImpl();
evenHeader.setCenterText(poiEvenHeader.getCenter());
evenHeader.setLeftText(poiEvenHeader.getLeft());
evenHeader.setRightText(poiEvenHeader.getRight());
sps.setEvenHeader(evenHeader);
}
Footer poiEvenFooter = poiSheet.getEvenFooter();
if (poiEvenFooter != null) {
SFooter evenFooter = new HeaderFooterImpl();
evenFooter.setCenterText(poiEvenFooter.getCenter());
evenFooter.setLeftText(poiEvenFooter.getLeft());
evenFooter.setRightText(poiEvenFooter.getRight());
sps.setEvenFooter(evenFooter);
}
}
if (poiSheet.isDiffFirst()) {
Header poiFirstHeader = poiSheet.getFirstHeader();
if (poiFirstHeader != null) {
SHeader firstHeader = new HeaderFooterImpl();
firstHeader.setCenterText(poiFirstHeader.getCenter());
firstHeader.setLeftText(poiFirstHeader.getLeft());
firstHeader.setRightText(poiFirstHeader.getRight());
sps.setFirstHeader(firstHeader);
}
Footer poiFirstFooter = poiSheet.getFirstFooter();
if (poiFirstFooter != null) {
SFooter firstFooter = new HeaderFooterImpl();
firstFooter.setCenterText(poiFirstFooter.getCenter());
firstFooter.setLeftText(poiFirstFooter.getLeft());
firstFooter.setRightText(poiFirstFooter.getRight());
sps.setFirstFooter(firstFooter);
}
}
PrintSetup poips = poiSheet.getPrintSetup();
sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin));
sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin));
sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin));
sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin));
sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin));
sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin));
sps.setAlignWithMargins(poiSheet.isAlignMargins());
sps.setErrorPrintMode(poips.getErrorsMode());
sps.setFitHeight(poips.getFitHeight());
sps.setFitWidth(poips.getFitWidth());
sps.setHCenter(poiSheet.getHorizontallyCenter());
sps.setLandscape(poips.getLandscape());
sps.setLeftToRight(poips.getLeftToRight());
sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0);
sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize()));
sps.setCommentsMode(poips.getCommentsMode());
sps.setPrintGridlines(poiSheet.isPrintGridlines());
sps.setPrintHeadings(poiSheet.isPrintHeadings());
sps.setScale(poips.getScale());
sps.setScaleWithDoc(poiSheet.isScaleWithDoc());
sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven());
sps.setDifferentFirstPage(poiSheet.isDiffFirst());
sps.setVCenter(poiSheet.getVerticallyCenter());
Workbook poiBook = poiSheet.getWorkbook();
String area = poiBook.getPrintArea(poiSheetIndex);
if (area != null) {
sps.setPrintArea(area);
}
CellRangeAddress rowrng = poiSheet.getRepeatingRows();
if (rowrng != null) {
sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow());
}
CellRangeAddress colrng = poiSheet.getRepeatingColumns();
if (colrng != null) {
sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn());
}
sheet.setPassword(poiSheet.getProtect() ? "" : null);
// import hashed password directly
importPassword(poiSheet, sheet);
// ZSS-832
// import sheet visible
if (poiBook.isSheetHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.HIDDEN);
} else if (poiBook.isSheetVeryHidden(poiSheetIndex)) {
sheet.setSheetVisible(SheetVisible.VERY_HIDDEN);
} else {
sheet.setSheetVisible(SheetVisible.VISIBLE);
}
// ZSS-1130
// import conditionalFormatting
importConditionalFormatting(sheet, poiSheet);
return sheet;
}
/* from D7
A 1 4 5 =SUM(E7:F7)
B 2 5 7 =SUM(E8:F8)
C 3 6 9 =SUM(E9:F9)
*/
@Test
public void testAutoFilterRange() {
SBook book = SBooks.createBook("book1");
SSheet sheet = book.createSheet("Sheet 1");
SRange range = SRanges.range(sheet, "D4").findAutoFilterRange();
Assert.assertNull(range);
sheet.getCell("D7").setValue("A");
sheet.getCell("D8").setValue("B");
sheet.getCell("D9").setValue("C");
sheet.getCell("E7").setValue(1);
sheet.getCell("E8").setValue(2);
sheet.getCell("E9").setValue(3);
sheet.getCell("F7").setValue(4);
sheet.getCell("F8").setValue(5);
sheet.getCell("F9").setValue(6);
sheet.getCell("G7").setValue("=SUM(E7:F7)");
sheet.getCell("G8").setValue("=SUM(E8:F8)");
sheet.getCell("G9").setValue("=SUM(E9:F9)");
Assert.assertEquals(5D, sheet.getCell("G7").getValue());
Assert.assertEquals(7D, sheet.getCell("G8").getValue());
Assert.assertEquals(9D, sheet.getCell("G9").getValue());
String nullLoc[] =
new String[] {
"C5", "D5", "G5", "H5", "B6", "B7", "B9", "B10", "J6", "J7", "J9", "J10", "C11", "D11",
"G11", "H11"
};
for (String loc : nullLoc) {
Assert.assertNull("at " + loc, SRanges.range(sheet, loc).findAutoFilterRange());
}
String inSideLoc[] =
new String[] {"D7", "D8", "D9", "E7", "E8", "E9", "F7", "F8", "F9", "G7", "G8", "G9"};
for (String loc : inSideLoc) {
range = SRanges.range(sheet, loc).findAutoFilterRange();
String at = "at " + loc;
Assert.assertNotNull(at, range);
Assert.assertEquals(at, 6, range.getRow());
Assert.assertEquals(at, 3, range.getColumn());
Assert.assertEquals(at, 8, range.getLastRow());
Assert.assertEquals(at, 6, range.getLastColumn());
}
Object inCornerLoc[][] =
new Object[][] {
new Object[] {"C6", -1, -1, 0, 0}, // area, row,column,lastRow,lastColumn offset
new Object[] {"C7", 0, -1, 0, 0},
new Object[] {"C9", 0, -1, 0, 0},
new Object[] {"C10", 0, -1, 1, 0},
new Object[] {"H6", -1, 0, 0, 1},
new Object[] {"H7", 0, 0, 0, 1},
new Object[] {"H9", 0, 0, 0, 1},
new Object[] {"H10", 0, 0, 1, 1},
new Object[] {"D6", -1, 0, 0, 0},
new Object[] {"E6", -1, 0, 0, 0},
new Object[] {"F6", -1, 0, 0, 0},
new Object[] {"G6", -1, 0, 0, 0},
new Object[] {"D10", 0, 0, 1, 0},
new Object[] {"E10", 0, 0, 1, 0},
new Object[] {"F10", 0, 0, 1, 0},
new Object[] {"G10", 0, 0, 1, 0}
};
for (Object loc[] : inCornerLoc) {
range = SRanges.range(sheet, (String) loc[0]).findAutoFilterRange();
String at = "at " + loc[0];
Assert.assertNotNull(at, range);
Assert.assertEquals(at, 6 + (Integer) loc[1], range.getRow());
Assert.assertEquals(at, 3 + (Integer) loc[2], range.getColumn());
Assert.assertEquals(at, 8 + (Integer) loc[3], range.getLastRow());
Assert.assertEquals(at, 6 + (Integer) loc[4], range.getLastColumn());
}
}
@Test
public void exportWidthSplitTest() {
SBook book = SBooks.createBook("book1");
SSheet sheet1 = book.createSheet("Sheet1");
int defaultWidth = 100;
sheet1.setDefaultColumnWidth(defaultWidth);
sheet1.setDefaultRowHeight(200);
Assert.assertEquals(defaultWidth, sheet1.getDefaultColumnWidth());
Assert.assertEquals(200, sheet1.getDefaultRowHeight());
Iterator<SColumnArray> arrays = sheet1.getColumnArrayIterator();
Assert.assertFalse(arrays.hasNext());
Assert.assertNull(sheet1.getColumnArray(0));
sheet1.setupColumnArray(0, 8).setWidth(10);
sheet1.setupColumnArray(11, 255);
arrays = sheet1.getColumnArrayIterator();
SColumnArray array = arrays.next();
Assert.assertEquals(0, array.getIndex());
Assert.assertEquals(8, array.getLastIndex());
Assert.assertEquals(10, array.getWidth());
array = arrays.next();
Assert.assertEquals(9, array.getIndex());
Assert.assertEquals(10, array.getLastIndex());
Assert.assertEquals(defaultWidth, array.getWidth());
array = arrays.next();
Assert.assertEquals(11, array.getIndex());
Assert.assertEquals(255, array.getLastIndex());
Assert.assertEquals(defaultWidth, array.getWidth());
///////////// first export
File outFile =
ImExpTestUtil.writeBookToFile(
book,
ImExpTestUtil.DEFAULT_EXPORT_TARGET_PATH + ImExpTestUtil.DEFAULT_EXPORT_FILE_NAME_XLSX,
EXPORTER_TYPE);
SBook outBook = ImExpTestUtil.loadBook(outFile, "OutBook");
sheet1 = outBook.getSheet(0);
// default width become 104px
// Assert.assertEquals(defaultWidth, sheet1.getDefaultColumnWidth());
Assert.assertEquals(200, sheet1.getDefaultRowHeight());
arrays = sheet1.getColumnArrayIterator();
Assert.assertTrue(arrays.hasNext());
arrays = sheet1.getColumnArrayIterator();
array = arrays.next();
Assert.assertEquals(0, array.getIndex());
Assert.assertEquals(8, array.getLastIndex());
Assert.assertEquals(10, array.getWidth());
array = arrays.next();
Assert.assertEquals(9, array.getIndex());
Assert.assertEquals(10, array.getLastIndex());
Assert.assertEquals(defaultWidth, array.getWidth());
array = arrays.next();
Assert.assertEquals(11, array.getIndex());
Assert.assertEquals(255, array.getLastIndex());
Assert.assertEquals(defaultWidth, array.getWidth());
///////////// second export
File outFile2 =
ImExpTestUtil.writeBookToFile(
outBook,
ImExpTestUtil.DEFAULT_EXPORT_TARGET_PATH + ImExpTestUtil.DEFAULT_EXPORT_FILE_NAME_XLSX,
EXPORTER_TYPE);
SBook outBook2 = ImExpTestUtil.loadBook(outFile2, "OutBook");
sheet1 = outBook2.getSheet(0);
// default width become 104px
// Assert.assertEquals(100, sheet1.getDefaultColumnWidth());
Assert.assertEquals(200, sheet1.getDefaultRowHeight());
arrays = sheet1.getColumnArrayIterator();
Assert.assertTrue(arrays.hasNext());
arrays = sheet1.getColumnArrayIterator();
array = arrays.next();
Assert.assertEquals(0, array.getIndex());
Assert.assertEquals(8, array.getLastIndex());
Assert.assertEquals(10, array.getWidth());
array = arrays.next();
Assert.assertEquals(9, array.getIndex());
Assert.assertEquals(10, array.getLastIndex());
Assert.assertEquals(100, array.getWidth());
array = arrays.next();
Assert.assertEquals(11, array.getIndex());
Assert.assertEquals(255, array.getLastIndex());
Assert.assertEquals(100, array.getWidth());
}
@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"));
}
