@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 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, 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;
}
/** 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;
}
@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 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 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();
}
@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 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());
}
@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 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());
}
/** 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;
}
/** 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-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;
}
@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"));
}
@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());
}
protected SCell importCell(Cell poiCell, int row, SSheet sheet) {
SCell cell = sheet.getCell(row, poiCell.getColumnIndex());
cell.setCellStyle(importCellStyle(poiCell.getCellStyle()));
switch (poiCell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
cell.setNumberValue(poiCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
RichTextString poiRichTextString = poiCell.getRichStringCellValue();
if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
SRichText richText = cell.setupRichTextValue();
importRichText(poiCell, poiRichTextString, richText);
} else {
cell.setStringValue(poiCell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cell.setBooleanValue(poiCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cell.setFormulaValue(poiCell.getCellFormula());
// ZSS-873
if (isImportCache() && !poiCell.isCalcOnLoad() && !mustCalc(cell)) {
ValueEval val = null;
switch (poiCell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
val = new NumberEval(poiCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
RichTextString poiRichTextString0 = poiCell.getRichStringCellValue();
if (poiRichTextString0 != null && poiRichTextString0.numFormattingRuns() > 0) {
SRichText richText = new RichTextImpl();
importRichText(poiCell, poiRichTextString0, richText);
val = new StringEval(richText.getText());
} else {
val = new StringEval(poiCell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
val = BoolEval.valueOf(poiCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
val = ErrorEval.valueOf(poiCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_BLANK:
default:
// do nothing
}
if (val != null) {
((AbstractCellAdv) cell).setFormulaResultValue(val);
}
}
break;
case Cell.CELL_TYPE_ERROR:
cell.setErrorValue(PoiEnumConversion.toErrorCode(poiCell.getErrorCellValue()));
break;
case Cell.CELL_TYPE_BLANK:
// do nothing because spreadsheet model auto creates blank cells
default:
// TODO log: leave an unknown cell type as a blank cell.
break;
}
Hyperlink poiHyperlink = poiCell.getHyperlink();
if (poiHyperlink != null) {
String addr = poiHyperlink.getAddress();
String label = poiHyperlink.getLabel();
SHyperlink hyperlink =
cell.setupHyperlink(
PoiEnumConversion.toHyperlinkType(poiHyperlink.getType()),
addr == null ? "" : addr,
label == null ? "" : label);
cell.setHyperlink(hyperlink);
}
Comment poiComment = poiCell.getCellComment();
if (poiComment != null) {
SComment comment = cell.setupComment();
comment.setAuthor(poiComment.getAuthor());
comment.setVisible(poiComment.isVisible());
RichTextString poiRichTextString = poiComment.getString();
if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
importRichText(poiCell, poiComment.getString(), comment.setupRichText());
} else {
comment.setText(poiComment.toString());
}
}
return cell;
}
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;
}
// 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;
}
/* 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());
}
}
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;
}
private boolean processBottomBorder(
StringBuffer sb, StringBuffer db, SCellStyle fillStyle, SCellStyle tbStyle) { // ZSS-977
boolean hitBottom = false;
MergedRect rect = null;
boolean hitMerge = false;
// ZSS-259: should apply the bottom border from the cell of merged range's bottom
// as processRightBorder() does.
rect = _mmHelper.getMergeRange(_row, _col);
int bottom = _row;
if (rect != null) {
hitMerge = true;
bottom = rect.getLastRow();
}
SCellStyle nextStyle =
StyleUtil.getBottomStyle(_sheet.getCell(bottom, _col).getCellStyle(), tbStyle); // ZSS-977
BorderType bb = null;
if (nextStyle != null) {
bb = nextStyle.getBorderBottom();
String color = nextStyle.getBorderBottomColor().getHtmlColor();
hitBottom = appendBorderStyle(sb, "bottom", bb, color);
}
// ZSS-259: should check and apply the top border from the bottom cell
// of merged range's bottom as processRightBorder() does.
SCellStyle nextFillStyle = null; // ZSS-977
if (!hitBottom) {
bottom = hitMerge ? rect.getLastRow() + 1 : _row + 1;
/*if(next == null){ // don't search into merge ranges
//check is _row+1,_col in merge range
MergedRect rect = _mmHelper.getMergeRange(_row+1, _col);
if(rect !=null){
next = _sheet.getCell(rect.getTop(),rect.getLeft());
}
}*/
// ZSS-919: merge more than 2 columns; must use top border of bottom cell
if (!hitMerge || rect.getColumn() == rect.getLastColumn()) {
nextFillStyle = nextStyle = _sheet.getCell(bottom, _col).getCellStyle();
// ZSS-977
SCellStyle nextTbStyle = null;
if (nextStyle.getBorderTop() == BorderType.NONE) {
final STable table0 = ((AbstractSheetAdv) _sheet).getTableByRowCol(bottom, _col);
nextTbStyle =
table0 == null ? null : ((AbstractTableAdv) table0).getCellStyle(bottom, _col);
nextStyle = StyleUtil.getTopStyle(nextStyle, nextTbStyle);
}
if (nextStyle != null) {
bb = nextStyle.getBorderTop(); // get top border of
String color = nextStyle.getBorderTopColor().getHtmlColor();
// set next row top border as cell's bottom border;
hitBottom = appendBorderStyle(sb, "bottom", bb, color);
}
// ZSS-977
if (!hitBottom) {
nextFillStyle = StyleUtil.getFillStyle(nextFillStyle, nextTbStyle);
}
}
}
// border depends on next cell's fill color if solid pattern
if (!hitBottom && 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; // ZSS-857
if (bgColor != null) {
hitBottom = appendBorderStyle(sb, "bottom", BorderType.THIN, bgColor);
} else if (nextFillStyle.getFillPattern() != FillPattern.NONE) { // ZSS-841
sb.append("border-bottom:none;"); // no grid line either
hitBottom = true;
}
}
// border depends on current cell's background color
if (!hitBottom && 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) {
hitBottom = appendBorderStyle(sb, "bottom", BorderType.THIN, bgColor);
} else if (fillStyle.getFillPattern() != FillPattern.NONE) { // ZSS-841
sb.append("border-bottom:none;"); // no grid line either
hitBottom = true;
}
}
db.append(hitBottom && bb == BorderType.DOUBLE ? "b" : "_");
return hitBottom;
}