@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());
}
// 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;
}
@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());
}