/** 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;
}
// 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;
}
// @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 "";
}
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 "";
}
// 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 "";
}
// 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 "";
}
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();
}
/** 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);
}
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();
}
/** 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;
}
@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);
}
// ZSS-725
public static String getRichTextEditCellHtml(SSheet sheet, int row, int column) {
final SCell cell = sheet.getCell(row, column);
String text = "";
if (!cell.isNull()) {
boolean wrap = cell.getCellStyle().isWrapText();
final FormatResult ft =
EngineFactory.getInstance()
.createFormatEngine()
.format(cell, new FormatContext(ZssContext.getCurrent().getLocale()));
if (ft.isRichText()) {
final SRichText rstr = ft.getRichText();
text = RichTextHelper.getCellRichTextHtml(rstr, wrap);
} else {
text =
RichTextHelper.getFontTextHtml(
escapeText(ft.getText(), wrap, true), cell.getCellStyle().getFont());
}
}
return text;
}
@Test
public void 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());
}
// 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();
}
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 "";
}
@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;
}
@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 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
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());
}