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