@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")); }