示例#1
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;
  }
示例#2
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;
  }
示例#3
0
  // Halignment determined by style alignment, text format and value type
  public static Alignment getRealAlignment(SCell cell) {
    final SCellStyle style = cell.getCellStyle();
    CellType type = cell.getType();
    Alignment align = style.getAlignment();
    if (align == Alignment.GENERAL) {
      // ZSS-918: vertical text default to horizontal center; no matter the type
      final boolean vtxt = style.getRotation() == 255;
      if (vtxt) return Alignment.CENTER;

      // ZSS-1020: 90 degree text default to horizontal right; no matter the type
      final boolean deg90 = style.getRotation() == 90;
      if (deg90) return Alignment.RIGHT;

      final String format = style.getDataFormat();
      if (format != null && format.startsWith("@")) // a text format
      type = CellType.STRING;
      else if (type == CellType.FORMULA) type = cell.getFormulaResultType();
      switch (type) {
        case BLANK:
          return align;
        case BOOLEAN:
          return Alignment.CENTER;
        case ERROR:
          return Alignment.CENTER;
        case NUMBER:
          return Alignment.RIGHT;
        case STRING:
        default:
          return Alignment.LEFT;
      }
    }
    return align;
  }
示例#4
0
  // @since 3.8.0
  public String getRealHtmlStyle(FormatResult ft, SCellStyle tbCellStyle) { // ZSS-977
    if (!_cell.isNull()) {
      final StringBuffer sb = new StringBuffer();
      sb.append(
          getFontHtmlStyle(
              _sheet, _cell, _cell.getCellStyle(), ft, tbCellStyle)); // ZSS-977, ZSS-1018
      sb.append(getIndentCSSStyle(_cell));
      return sb.toString();
    }

    return "";
  }
示例#5
0
 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 "";
 }
示例#6
0
  // ZSS-725: separate inner and font style to avoid the conflict between
  // vertical alignment, subscript and superscript.
  public String getFontHtmlStyle() {
    if (!_cell.isNull()) {

      final StringBuffer sb = new StringBuffer();
      final SFont font = _cellStyle.getFont();

      // sb.append(BookHelper.getFontCSSStyle(_book, font));
      sb.append(getFontCSSStyle(_cell, font));

      // condition color
      final FormatResult ft =
          _formatEngine.format(_cell, new FormatContext(ZssContext.getCurrent().getLocale()));
      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 "";
  }
示例#7
0
  // 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 "";
  }
示例#8
0
  public static String getTextCSSStyle(SCell cell) {
    final SCellStyle style = cell.getCellStyle();

    final StringBuffer sb = new StringBuffer();
    Alignment textHAlign = getRealAlignment(cell);

    switch (textHAlign) {
      case RIGHT:
        sb.append("text-align:").append("right").append(";");
        break;
      case CENTER:
      case CENTER_SELECTION:
        sb.append("text-align:").append("center").append(";");
        break;
      default:
        break;
    }
    // ZSS-944: when rotate 90 degree, wrap must be false
    final int rotate90 = style.getRotation();
    boolean textWrap = style.isWrapText() && rotate90 != 90 && rotate90 != -90; // ZSS-1020
    if (textWrap) {
      sb.append("white-space:").append("pre-wrap").append(";"); // ZSS-1118
    } /*else{ sb.append("white-space:").append("nowrap").append(";"); }*/

    return sb.toString();
  }
示例#9
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;
  }
  @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);
  }
示例#11
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();
 }
示例#12
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;
  }
示例#13
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);
  }
示例#14
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;
  }
示例#15
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());
  }
示例#16
0
  // 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();
  }
示例#17
0
  public String getInnerHtmlStyle() {
    if (!_cell.isNull()) {

      final StringBuffer sb = new StringBuffer();
      sb.append(getTextCSSStyle(_cell));

      // vertical alignment
      VerticalAlignment verticalAlignment = _cellStyle.getVerticalAlignment();
      sb.append("display: table-cell;");
      switch (verticalAlignment) {
        case TOP:
          sb.append("vertical-align: top;");
          break;
        case CENTER:
          sb.append("vertical-align: middle;");
          break;
        case BOTTOM:
        default:
          sb.append("vertical-align: bottom;");
          break;
      }

      // final SFont font = _cellStyle.getFont();

      // sb.append(BookHelper.getFontCSSStyle(_book, font));
      // sb.append(getFontCSSStyle(_cell, font));

      // condition color
      // final FormatResult ft = _formatEngine.format(_cell, new
      // FormatContext(ZssContext.getCurrent().getLocale()));
      // 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 "";
  }
示例#18
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);
  }
  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
  @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);
  }
示例#21
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());
  }
示例#22
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());
  }