예제 #1
0
  @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());
  }
예제 #2
0
  @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());
  }
예제 #3
0
  /** 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;
  }
예제 #4
0
  /** 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;
  }
예제 #5
0
  @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);
  }
예제 #6
0
  @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);
  }
예제 #8
0
 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();
 }
예제 #9
0
  @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());
  }
예제 #10
0
  @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);
  }
예제 #11
0
  @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());
  }
예제 #12
0
  @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);
  }
예제 #13
0
  @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());
  }
예제 #14
0
  /** 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;
  }
예제 #15
0
  /** 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;
  }
예제 #16
0
  // 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;
  }
예제 #17
0
  @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"));
  }
예제 #18
0
  @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;
  }
예제 #20
0
  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;
  }
예제 #21
0
  // 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;
  }
예제 #22
0
  /* 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());
    }
  }
예제 #23
0
  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;
  }
예제 #24
0
  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;
  }