@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()); }
// ZSS-952 protected void importSheetDefaultColumnWidth(Sheet poiSheet, SSheet sheet) { // reference XUtils.getDefaultColumnWidthInPx() int defaultWidth = UnitUtil.defaultColumnWidthToPx( poiSheet.getDefaultColumnWidth(), ((AbstractBookAdv) book).getCharWidth()); // ZSS-1132 sheet.setDefaultColumnWidth(defaultWidth); }
// ZSS-945, ZSS-1018 // @since 3.8.0 // @Internal public static String getFontHtmlStyle( SSheet sheet, SCell cell, SCellStyle cellStyle, FormatResult ft, SCellStyle tbCellStyle) { // ZSS-977 if (!cell.isNull()) { final StringBuffer sb = new StringBuffer(); // ZSS-977 SFont font = StyleUtil.getFontStyle(sheet.getBook(), cellStyle, tbCellStyle); ; sb.append(getFontCSSStyle(cell, font)); // condition color final boolean isRichText = ft.isRichText(); if (!isRichText) { final SColor color = ft.getColor(); if (color != null) { final String htmlColor = color.getHtmlColor(); sb.append("color:").append(htmlColor).append(";"); } } return sb.toString(); } return ""; }
/** Gets Cell text by given row and column, it handling */ public static String getRichCellHtmlText( SSheet sheet, int row, int column, FormatResult ft, SCellStyle tbStyle) { // ZSS-1018 final SCell cell = sheet.getCell(row, column); String text = ""; if (!cell.isNull()) { final SCellStyle style = cell.getCellStyle(); boolean wrap = style.isWrapText(); boolean vtxt = style.getRotation() == 255; // ZSS-918 if (ft.isRichText()) { final SRichText rstr = ft.getRichText(); text = vtxt ? getVRichTextHtml(rstr, wrap) : getRichTextHtml(rstr, wrap); // ZSS-918 } else { text = vtxt ? escapeVText(ft.getText(), wrap) : escapeText(ft.getText(), wrap, true); // ZSS-918 } final SHyperlink hlink = cell.getHyperlink(); if (hlink != null) { text = getHyperlinkHtml(text, hlink, sheet, cell, style, ft, tbStyle); // ZSS-1018 } } return text; }
/** Gets Cell text by given row and column, it handling */ public static String getRichCellHtmlText(SSheet sheet, int row, int column) { final SCell cell = sheet.getCell(row, column); String text = ""; if (!cell.isNull()) { final SCellStyle style = cell.getCellStyle(); boolean wrap = style.isWrapText(); boolean vtxt = style.getRotation() == 255; // ZSS-918 final FormatResult ft = EngineFactory.getInstance() .createFormatEngine() .format(cell, new FormatContext(ZssContext.getCurrent().getLocale())); if (ft.isRichText()) { final SRichText rstr = ft.getRichText(); text = vtxt ? getVRichTextHtml(rstr, wrap) : getRichTextHtml(rstr, wrap); // ZSS-918 } else { text = vtxt ? escapeVText(ft.getText(), wrap) : escapeText(ft.getText(), wrap, true); // ZSS-918 } final SHyperlink hlink = cell.getHyperlink(); if (hlink != null) { text = getHyperlinkHtml(text, hlink, sheet, cell, style, ft, null); // ZSS-1018 } } return text; }
@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 @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 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); }
public CellFormatHelper(SSheet sheet, int row, int col, MergeMatrixHelper mmhelper) { _sheet = sheet; _row = row; _col = col; _cell = sheet.getCell(row, col); _cellStyle = _cell.getCellStyle(); _mmHelper = mmhelper; _formatEngine = EngineFactory.getInstance().createFormatEngine(); }
/** * POI AutoFilter.getFilterColumn(i) sometimes returns null. A POI FilterColumn object only exists * when we have set a criteria on that column. For example, if we enable auto filter on 2 columns, * but we only set criteria on 2nd column. Thus, the size of filter column is 1. There is only one * FilterColumn object and its column id is 1. Only getFilterColumn(1) will return a FilterColumn, * other get null. * * @param poiSheet source POI sheet * @param sheet destination sheet */ protected void importAutoFilter(Sheet poiSheet, SSheet sheet) { AutoFilter poiAutoFilter = poiSheet.getAutoFilter(); if (poiAutoFilter != null) { CellRangeAddress filteringRange = poiAutoFilter.getRangeAddress(); SAutoFilter autoFilter = sheet.createAutoFilter(new CellRegion(filteringRange.formatAsString())); int numberOfColumn = filteringRange.getLastColumn() - filteringRange.getFirstColumn() + 1; importAutoFilterColumns(poiAutoFilter, autoFilter, numberOfColumn); // ZSS-1019 } }
@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); }
@Test public void picture() { File outFile = ImExpTestUtil.write( ImExpTestUtil.loadBook(PICTURE_IMPORT_FILE_UNDER_TEST, "XSSFBook"), EXPORTER_TYPE); SBook book = ImExpTestUtil.loadBook(outFile, DEFAULT_BOOK_NAME); picture(book); SSheet sheet2 = book.getSheet(1); assertEquals(2, sheet2.getPictures().size()); SPicture flowerJpg = sheet2.getPicture(0); assertEquals(Format.JPG, flowerJpg.getFormat()); assertEquals(569, flowerJpg.getAnchor().getWidth()); assertEquals(427, flowerJpg.getAnchor().getHeight()); // different spec in XLS SPicture rainbowGif = sheet2.getPicture(1); assertEquals(Format.GIF, rainbowGif.getFormat()); assertEquals(613, rainbowGif.getAnchor().getWidth()); assertEquals(345, rainbowGif.getAnchor().getHeight()); }
protected void importPicture(List<Picture> poiPictures, Sheet poiSheet, SSheet sheet) { for (Picture poiPicture : poiPictures) { PictureData poiPicData = poiPicture.getPictureData(); Integer picDataIx = importedPictureData.get(poiPicData); // ZSS-735 if (picDataIx != null) { sheet.addPicture( picDataIx.intValue(), toViewAnchor(poiSheet, poiPicture.getClientAnchor())); } else { Format format = Format.valueOfFileExtension(poiPicData.suggestFileExtension()); if (format != null) { SPicture pic = sheet.addPicture( format, poiPicData.getData(), toViewAnchor(poiSheet, poiPicture.getClientAnchor())); importedPictureData.put(poiPicData, pic.getPictureData().getIndex()); } else { // TODO log we ignore a picture with unsupported format } } } }
@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); }
private String getIndentCSSStyle(SCell cell) { final int indention = _cell.getCellStyle().getIndention(); final boolean wrap = _cell.getCellStyle().isWrapText(); if (indention > 0) { if (wrap) { // ZSS-1016 return "float:right; width: " + (_sheet.getColumn(_cell.getColumnIndex()).getWidth() - (indention * 8.5) - RESERVE_CELL_MARGIN) + "px;"; } else return "text-indent:" + (indention * 8.5) + "px;"; } return ""; }
protected SRow importRow(Row poiRow, SSheet sheet) { SRow row = sheet.getRow(poiRow.getRowNum()); row.setHeight(UnitUtil.twipToPx(poiRow.getHeight())); row.setCustomHeight(poiRow.isCustomHeight()); row.setHidden(poiRow.getZeroHeight()); CellStyle rowStyle = poiRow.getRowStyle(); if (rowStyle != null) { row.setCellStyle(importCellStyle(rowStyle)); } for (Cell poiCell : poiRow) { importCell(poiCell, poiRow.getRowNum(), sheet); } return row; }
/** Gets Cell text by given row and column */ public static String getCellHtmlText( SSheet sheet, int row, int column, FormatResult ft, SCellStyle tbStyle) { // ZSS-1018 final SCell cell = sheet.getCell(row, column); String text = ""; if (cell != null) { boolean wrap = cell.getCellStyle().isWrapText(); if (ft.isRichText()) { final SRichText rstr = ft.getRichText(); text = rstr.getText(); } else { text = ft.getText(); } text = escapeText(text, wrap, true); } return text; }
@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()); }
/** Gets Cell text by given row and column */ public static String getCellHtmlText(SSheet sheet, int row, int column) { final SCell cell = sheet.getCell(row, column); String text = ""; if (cell != null) { boolean wrap = cell.getCellStyle().isWrapText(); final FormatResult ft = EngineFactory.getInstance() .createFormatEngine() .format(cell, new FormatContext(ZssContext.getCurrent().getLocale())); if (ft.isRichText()) { final SRichText rstr = ft.getRichText(); text = rstr.getText(); } else { text = ft.getText(); } text = escapeText(text, wrap, true); } return text; }
// ZSS-901 public String getAutoFilterBorder() { StringBuffer sb = new StringBuffer(); final SAutoFilter filter = _sheet.getAutoFilter(); if (filter == null) return "____"; // empty // must check in top/left/bottom/right order final CellRegion rgn = filter.getRegion(); final int t = rgn.getRow(); final int l = rgn.getColumn(); final int b = rgn.getLastRow(); final int r = rgn.getLastColumn(); final int r0 = _cell.getRowIndex(); final int c0 = _cell.getColumnIndex(); sb.append(r0 == t && l <= c0 && c0 <= r ? "t" : "_"); sb.append(c0 == l && t <= r0 && r0 <= b ? "l" : "_"); sb.append(r0 == b && l <= c0 && c0 <= r ? "b" : "_"); sb.append(c0 == r && t <= r0 && r0 <= b ? "r" : "_"); return sb.toString(); }
// ZSS-725 public static String getRichTextEditCellHtml(SSheet sheet, int row, int column) { final SCell cell = sheet.getCell(row, column); String text = ""; if (!cell.isNull()) { boolean wrap = cell.getCellStyle().isWrapText(); final FormatResult ft = EngineFactory.getInstance() .createFormatEngine() .format(cell, new FormatContext(ZssContext.getCurrent().getLocale())); if (ft.isRichText()) { final SRichText rstr = ft.getRichText(); text = RichTextHelper.getCellRichTextHtml(rstr, wrap); } else { text = RichTextHelper.getFontTextHtml( escapeText(ft.getText(), wrap, true), cell.getCellStyle().getFont()); } } return text; }
private boolean processRightBorder( StringBuffer sb, StringBuffer db, SCellStyle fillStyle, SCellStyle tbStyle) { // ZSS-977 boolean hitRight = false; MergedRect rect = null; boolean hitMerge = false; // find right border of target cell rect = _mmHelper.getMergeRange(_row, _col); int right = _col; if (rect != null) { hitMerge = true; right = rect.getLastColumn(); } BorderType bb = null; SCellStyle nextStyle = StyleUtil.getRightStyle(_sheet.getCell(_row, right).getCellStyle(), tbStyle); // ZSS-977 if (nextStyle != null) { bb = nextStyle.getBorderRight(); String color = nextStyle.getBorderRightColor().getHtmlColor(); hitRight = appendBorderStyle(sb, "right", bb, color); } // if no border for target cell,then check is this cell in a merge range // if(true) then try to get next cell after this merge range // else get next cell of this cell SCellStyle nextFillStyle = null; // ZSS-977 if (!hitRight) { right = hitMerge ? rect.getLastColumn() + 1 : _col + 1; // ZSS-919: merge more than 2 rows; must use left border of right cell if (!hitMerge || rect.getRow() == rect.getLastRow()) { nextFillStyle = nextStyle = _sheet.getCell(_row, right).getCellStyle(); // ZSS-977 SCellStyle nextTbStyle = null; if (nextStyle.getBorderLeft() == BorderType.NONE) { final STable table0 = ((AbstractSheetAdv) _sheet).getTableByRowCol(_row, right); nextTbStyle = table0 == null ? null : ((AbstractTableAdv) table0).getCellStyle(_row, right); nextStyle = StyleUtil.getLeftStyle(nextStyle, nextTbStyle); } if (nextStyle != null) { bb = nextStyle.getBorderLeft(); // get left here // String color = BookHelper.indexToRGB(_book, style.getLeftBorderColor()); // ZSS-34 cell background color does not show in excel String color = nextStyle.getBorderLeftColor().getHtmlColor(); hitRight = appendBorderStyle(sb, "right", bb, color); } // ZSS-977 if (!hitRight) { nextFillStyle = StyleUtil.getFillStyle(nextFillStyle, nextTbStyle); } } } // border depends on next cell's background color (why? dennis, 20131118) if (!hitRight && nextFillStyle != null) { // String bgColor = BookHelper.indexToRGB(_book, style.getFillForegroundColor()); // ZSS-34 cell background color does not show in excel String bgColor = nextFillStyle.getFillPattern() == FillPattern.SOLID ? nextFillStyle.getBackColor().getHtmlColor() : null; if (bgColor != null) { hitRight = appendBorderStyle(sb, "right", BorderType.THIN, bgColor); } else if (nextFillStyle.getFillPattern() != FillPattern.NONE) { // ZSS-841 sb.append("border-right:none;"); // no grid line either hitRight = true; } } // border depends on current cell's background color if (!hitRight && fillStyle != null) { // String bgColor = BookHelper.indexToRGB(_book, style.getFillForegroundColor()); // ZSS-34 cell background color does not show in excel String bgColor = fillStyle.getFillPattern() == FillPattern.SOLID ? fillStyle.getBackColor().getHtmlColor() : null; if (bgColor != null) { hitRight = appendBorderStyle(sb, "right", BorderType.THIN, bgColor); } else if (fillStyle.getFillPattern() != FillPattern.NONE) { // ZSS-841 sb.append("border-right:none;"); // no grid line either hitRight = true; } } db.append(hitRight && bb == BorderType.DOUBLE ? "r" : "_"); return hitRight; }
// ZSS-568 private boolean processTopBorder( StringBuffer sb, StringBuffer db, SCellStyle fillStyle, SCellStyle tbStyle) { // ZSS-977 boolean hitTop = false; MergedRect rect = null; boolean hitMerge = false; // ZSS-259: should apply the top border from the cell of merged range's top // as processRightBorder() does. rect = _mmHelper.getMergeRange(_row, _col); int top = _row; if (rect != null) { hitMerge = true; top = rect.getRow(); } SCellStyle nextStyle = StyleUtil.getTopStyle(_sheet.getCell(top, _col).getCellStyle(), tbStyle); // ZSS-977 if (nextStyle != null) { BorderType bb = nextStyle.getBorderTop(); if (bb == BorderType.DOUBLE) { String color = nextStyle.getBorderTopColor().getHtmlColor(); hitTop = appendBorderStyle(sb, "top", bb, color); } else if (bb != BorderType.NONE) { // ZSS-919: check if my top is a merged cell top = hitMerge ? rect.getRow() - 1 : _row - 1; if (top >= 0) { final MergedRect rectT = _mmHelper.getMergeRange(top, _col); // my top merge more than 2 columns if (rectT != null && rectT.getColumn() < rectT.getLastColumn()) { String color = nextStyle.getBorderTopColor().getHtmlColor(); // support only solid line but position correctly return appendMergedBorder(sb, "top", color); // //offset 1px to bottom but support more line styles // return hitTop = appendBorderStyle(sb, "top", bb, color); } } } } // ZSS-259: should check and apply the bottom border from the top cell // of merged range's top as processRightBorder() does. if (!hitTop) { top = hitMerge ? rect.getRow() - 1 : _row - 1; if (top >= 0) { nextStyle = _sheet.getCell(top, _col).getCellStyle(); // ZSS-977 if (nextStyle.getBorderBottom() == BorderType.NONE) { final STable table0 = ((AbstractSheetAdv) _sheet).getTableByRowCol(top, _col); final SCellStyle tbStyle0 = table0 == null ? null : ((AbstractTableAdv) table0).getCellStyle(top, _col); nextStyle = StyleUtil.getBottomStyle(nextStyle, tbStyle0); } if (nextStyle != null) { BorderType bb = nextStyle.getBorderBottom(); // get bottom border of if (bb == BorderType.DOUBLE) { String color = nextStyle.getBorderBottomColor().getHtmlColor(); // set next row top border as cell's top border; hitTop = appendBorderStyle(sb, "top", bb, color); } } } } db.append(hitTop ? "t" : "_"); return hitTop; }
private boolean processLeftBorder( StringBuffer sb, StringBuffer db, SCellStyle fillStyle, SCellStyle tbStyle) { // ZSS-977 boolean hitLeft = false; MergedRect rect = null; boolean hitMerge = false; // find left border of target cell rect = _mmHelper.getMergeRange(_row, _col); int left = _col; if (rect != null) { hitMerge = true; left = rect.getColumn(); } SCellStyle nextStyle = StyleUtil.getLeftStyle(_sheet.getCell(_row, left).getCellStyle(), tbStyle); // ZSS-977 if (nextStyle != null) { BorderType bb = nextStyle.getBorderLeft(); if (bb == BorderType.DOUBLE) { String color = nextStyle.getBorderLeftColor().getHtmlColor(); hitLeft = appendBorderStyle(sb, "left", bb, color); } else if (bb != BorderType.NONE) { // ZSS-919: check if my left is a merged cell left = hitMerge ? rect.getColumn() - 1 : _col - 1; if (left >= 0) { final MergedRect rectT = _mmHelper.getMergeRange(_row, left); // my left merged more than 2 rows if (rectT != null && rectT.getRow() < rectT.getLastRow()) { String color = nextStyle.getBorderLeftColor().getHtmlColor(); // support only solid line but position correctly return appendMergedBorder(sb, "left", color); // //offset 1px to right but support more line styles // return hitLeft = appendBorderStyle(sb, "left", bb, color); } } } } // if no border for target cell,then check if this cell is in a merge range // if(true) then try to get next cell after this merge range // else get next cell of this cell if (!hitLeft) { left = hitMerge ? rect.getColumn() - 1 : _col - 1; if (left >= 0) { nextStyle = _sheet.getCell(_row, left).getCellStyle(); // ZSS-977 if (nextStyle.getBorderRight() == BorderType.NONE) { final STable table0 = ((AbstractSheetAdv) _sheet).getTableByRowCol(_row, left); final SCellStyle tbStyle0 = table0 == null ? null : ((AbstractTableAdv) table0).getCellStyle(_row, left); nextStyle = StyleUtil.getRightStyle(nextStyle, tbStyle0); } if (nextStyle != null) { BorderType bb = nextStyle.getBorderRight(); // get right here // String color = BookHelper.indexToRGB(_book, style.getLeftBorderColor()); // ZSS-34 cell background color does not show in excel if (bb == BorderType.DOUBLE) { String color = nextStyle.getBorderRightColor().getHtmlColor(); hitLeft = appendBorderStyle(sb, "left", bb, color); } } } } db.append(hitLeft ? "l" : "_"); return hitLeft; }