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