@Listen("onHeaderUpdate = #ss")
public void onHeaderUpdate(HeaderUpdateEvent event) {
StringBuilder info = new StringBuilder();
info.append("Header ").append(event.getAction()).append(" on ").append(event.getType());
switch (event.getType()) {
case COLUMN:
info.append(" ").append(Ranges.getColumnRefString(event.getIndex()));
break;
case ROW:
info.append(" ").append(Ranges.getRowRefString(event.getIndex()));
break;
}
switch (event.getAction()) {
case RESIZE:
if (event.isHidden()) {
info.append(" hides ");
} else {
info.append(" changes to ").append(event.getSize());
}
break;
}
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Test
public void testZSS595_Column() {
Book book = Util.loadBook(this, "book/blank.xlsx");
Sheet sheet = book.getSheet("Sheet1");
String[] columns = {"A", "B", "C", "D", "E"};
Ranges.range(sheet).setFreezePanel(0, 5);
// === Insert
for (int i = 1; i < 6; i++) {
try {
Ranges.range(sheet, columns[i - 1] + ":G")
.toColumnRange()
.insert(InsertShift.DEFAULT, InsertCopyOrigin.FORMAT_LEFT_ABOVE);
} catch (InvalidModelOpException e) {
continue;
}
fail(); // if doesn't continue, it fail!
}
// == Delete
for (int i = 1; i < 6; i++) {
try {
Ranges.range(sheet, columns[i - 1] + ":G").toColumnRange().delete(DeleteShift.DEFAULT);
} catch (InvalidModelOpException e) {
continue;
}
fail(); // if doesn't continue, it fail!
}
}
@Test
public void testZSS595_Row() {
Book book = Util.loadBook(this, "book/blank.xlsx");
Sheet sheet = book.getSheet("Sheet1");
Ranges.range(sheet).setFreezePanel(5, 0);
// === Insert
for (int i = 1; i < 6; i++) {
try {
Ranges.range(sheet, i + ":6")
.toRowRange()
.insert(InsertShift.DEFAULT, InsertCopyOrigin.FORMAT_LEFT_ABOVE);
} catch (InvalidModelOpException e) {
continue;
}
fail(); // if doesn't continue, it fail!
}
// == Delete
for (int i = 1; i < 6; i++) {
try {
Ranges.range(sheet, i + ":6").toRowRange().delete(DeleteShift.DEFAULT);
} catch (InvalidModelOpException e) {
continue;
}
fail(); // if doesn't continue, it fail!
}
}
private void testZSS547_DeleteSheet(Book book) {
Ranges.range(book.getSheetAt(0), "A1").getCellValue(); // eval. Sheet1 A1
Ranges.range(book.getSheetAt(0)).deleteSheet(); // delete Sheet1
Ranges.range(book.getSheetAt(2)).deleteSheet(); // delete Sheet4
// eval Sheet3 F1
Assert.assertEquals(new Double(3.0), Ranges.range(book.getSheetAt(1), "F1").getCellValue());
// NOTE, there should not be any exception
}
private void initModel() {
// Available events
// It is just for showing message, event is always listened in this demo.
eventFilterModel.setMultiple(true);
addEventFilter(Events.ON_START_EDITING, true);
addEventFilter(Events.ON_EDITBOX_EDITING, true);
addEventFilter(Events.ON_STOP_EDITING, true);
addEventFilter(Events.ON_AFTER_CELL_CHANGE, true);
addEventFilter(Events.ON_CTRL_KEY, true);
addEventFilter(Events.ON_CELL_CLICK, false);
addEventFilter(Events.ON_CELL_DOUBLE_CLICK, true);
addEventFilter(Events.ON_CELL_RIGHT_CLICK, true);
addEventFilter(Events.ON_HEADER_UPDATE, true);
addEventFilter(Events.ON_HEADER_CLICK, true);
addEventFilter(Events.ON_HEADER_RIGHT_CLICK, true);
addEventFilter(Events.ON_HEADER_DOUBLE_CLICK, true);
addEventFilter(Events.ON_AUX_ACTION, true);
addEventFilter(Events.ON_CELL_FOUCS, false);
addEventFilter(Events.ON_CELL_SELECTION, false);
addEventFilter(Events.ON_CELL_SELECTION_UPDATE, true);
addEventFilter(Events.ON_CELL_FILTER, true); // useless
addEventFilter(Events.ON_CELL_VALIDATOR, true); // useless
addEventFilter(Events.ON_WIDGET_UPDATE, true);
addEventFilter(Events.ON_WIDGET_CTRL_KEY, true);
addEventFilter(Events.ON_AFTER_SHEET_CREATE, true);
addEventFilter(Events.ON_AFTER_SHEET_DELETE, true);
addEventFilter(Events.ON_AFTER_SHEET_NAME_CHANGE, true);
addEventFilter(Events.ON_AFTER_SHEET_ORDER_CHANGE, true);
addEventFilter(Events.ON_SHEET_SELECT, true);
addEventFilter(Events.ON_CELL_HYPERLINK, true);
eventFilterList.setModel(eventFilterModel);
// add default show only
infoList.setModel(infoModel);
addInfo("Spreadsheet initialized");
// hint for special cells
// D1
Ranges.range(ss.getSelectedSheet(), 0, 3).setCellEditText("Edit Me");
// E1
Ranges.range(ss.getSelectedSheet(), 0, 4).setCellEditText("Edit Me");
// D3
Ranges.range(ss.getSelectedSheet(), 2, 3).setCellEditText("Edit Me");
// E3
Ranges.range(ss.getSelectedSheet(), 2, 4).setCellEditText("Edit Me");
}
@Test
public void testZSS502_2007() {
Book book = Util.loadBook(this, "book/502-crossSheetReference.xlsx");
Sheet sheet = book.getSheet("cell-reference");
Range referencingCell = Ranges.range(sheet, "C4");
assertEquals("=row!A1", referencingCell.getCellEditText());
assertEquals("The first row is freezed.", referencingCell.getCellFormatText());
Ranges.range(book.getSheet("row")).deleteSheet();
assertEquals("='#REF'!A1", referencingCell.getCellEditText());
assertEquals(
ErrorConstants.getText(ErrorConstants.ERROR_REF), referencingCell.getCellFormatText());
}
@Listen("onAfterCellChange = #ss")
public void onAfterCellChange(CellAreaEvent event) {
StringBuilder info = new StringBuilder();
info.append("Cell changes on ")
.append(Ranges.getAreaRefString(event.getSheet(), event.getArea()));
info.append(
", first value is \""
+ Ranges.range(event.getSheet(), event.getArea()).getCellFormatText()
+ "\"");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onStartEditing = #ss")
public void onStartEditing(StartEditingEvent event) {
StringBuilder info = new StringBuilder();
String ref = Ranges.getCellRefString(event.getRow(), event.getColumn());
info.append("Start editing ")
.append(ref)
.append(", editing-value is ")
.append("\"" + event.getEditingValue() + "\"")
.append(" client-value is ")
.append("\"" + event.getClientValue() + "\"");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
if (ref.equals("D1")) {
String newValue = "Surprise!!";
// we change the editing value
event.setEditingValue(newValue);
addInfo("Editing value is change to " + newValue);
} else if (ref.equals("E1")) {
// forbid editing
event.cancel();
addInfo("Editing E1 is canceled");
}
}
@Test
public void testZSS502_NonExistingSheet2007() {
Book book = Util.loadBook(this, "book/blank.xlsx");
Sheet sheet = book.getSheetAt(0);
Range cell = Ranges.range(sheet, "A1");
cell.setCellEditText("=nonExisted!B1");
assertEquals("=nonExisted!B1", cell.getCellEditText());
assertEquals(ErrorConstants.getText(ErrorConstants.ERROR_REF), cell.getCellFormatText());
}
@Listen("onHeaderDoubleClick = #ss")
public void onHeaderDoubleClick(HeaderMouseEvent event) {
StringBuilder info = new StringBuilder();
info.append("Double-click on ").append(event.getType()).append(" ");
switch (event.getType()) {
case COLUMN:
info.append(Ranges.getColumnRefString(event.getIndex()));
break;
case ROW:
info.append(Ranges.getRowRefString(event.getIndex()));
break;
}
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellSelectionUpdate = #ss")
public void onCellSelectionUpdate(CellSelectionUpdateEvent event) {
StringBuilder info = new StringBuilder();
info.append("Selection update from[")
.append(
Ranges.getAreaRefString(
event.getOrigRow(),
event.getOrigColumn(),
event.getOrigLastRow(),
event.getOrigLastColumn()))
.append("] to [")
.append(Ranges.getAreaRefString(event.getSheet(), event.getArea()))
.append("]");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellRightClick = #ss")
public void onCellRightClick(CellMouseEvent event) {
StringBuilder info = new StringBuilder();
info.append("Right-click on cell ")
.append(Ranges.getCellRefString(event.getRow(), event.getColumn()));
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellFocus = #ss")
public void onCellFocus(CellEvent event) {
StringBuilder info = new StringBuilder();
info.append("Focus on[")
.append(Ranges.getCellRefString(event.getRow(), event.getColumn()))
.append("]");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellSelection = #ss")
public void onCellSelection(CellSelectionEvent event) {
StringBuilder info = new StringBuilder();
info.append("Select on[")
.append(Ranges.getAreaRefString(event.getSheet(), event.getArea()))
.append("]");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Test
public void testZSS510() {
Book book = Util.loadBook(this, "book/blank.xlsx");
Sheet sheet = book.getSheetAt(0);
Range r = Ranges.range(sheet, "A1");
r.setCellEditText("Hello");
CellOperationUtil.applyDataFormat(r, "");
r.getCellFormatText(); // get text shouldn't cause IndexOutBoundaryException
assertEquals(
"General", r.getCellStyle().getDataFormat()); // should get General instead of empty string
}
@Listen("onCellValidator = #ss")
public void onCellValidator(CellMouseEvent event) {
StringBuilder info = new StringBuilder();
info.append("Validation button clicked ")
.append(" on cell ")
.append(Ranges.getCellRefString(event.getRow(), event.getColumn()));
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onEditboxEditing = #ss")
public void onEditboxEditing(EditboxEditingEvent event) {
StringBuilder info = new StringBuilder();
String ref = Ranges.getCellRefString(event.getRow(), event.getColumn());
info.append("Editing ")
.append(ref)
.append(", value is ")
.append("\"" + event.getEditingValue() + "\"");
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellFilter = #ss")
public void onCellFilter(CellFilterEvent event) {
StringBuilder info = new StringBuilder();
info.append("Filter button clicked")
.append(", filter area: ")
.append(Ranges.getAreaRefString(event.getSheet(), event.getFilterArea()))
.append(", on field: ")
.append(event.getField());
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onCellHyperlink = #ss")
public void onCellHyperlink(CellHyperlinkEvent event) {
StringBuilder info = new StringBuilder();
info.append("Hyperlink ")
.append(event.getType())
.append(" on : ")
.append(Ranges.getCellRefString(event.getRow(), event.getColumn()))
.append(", address : ")
.append(event.getAddress());
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onAuxAction = #ss")
public void onAuxAction(AuxActionEvent event) {
StringBuilder info = new StringBuilder();
info.append("AuxAction ").append(event.getAction());
if (!"deleteSheet".equals(event.getAction())) {
// deleted sheet is unable to access at this moment
info.append(" on : ").append(Ranges.getAreaRefString(event.getSheet(), event.getSelection()));
}
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
@Listen("onWidgetUpdate = #ss")
public void onWidgetUpdate(WidgetUpdateEvent event) {
StringBuilder info = new StringBuilder();
SheetAnchor anchor = event.getSheetAnchor();
info.append("Widget ")
.append(event.getWidgetData())
.append(" ")
.append(event.getAction())
.append(" to ")
.append(
Ranges.getAreaRefString(
anchor.getRow(), anchor.getColumn(), anchor.getLastRow(), anchor.getLastColumn()));
if (isShowEventInfo(event.getName())) {
addInfo(info.toString());
}
}
/* (non-Javadoc)
* @see org.zkoss.zss.ui.sys.ua.impl.AbstractHandler#processAction(org.zkoss.zss.ui.UserActionContext)
*/
@Override
protected boolean processAction(UserActionContext ctx) {
Sheet sheet = ctx.getSheet();
AreaRef selection = ctx.getSelection();
CellSelectionType type = ctx.getSelectionType();
Range range = Ranges.range(sheet, selection);
if (range.isProtected()) {
showProtectMessage();
return true;
}
// zss-623, extends to row,column area
switch (type) {
case ROW:
range = range.toRowRange();
break;
case COLUMN:
range = range.toColumnRange();
break;
case ALL:
// we don't allow to set whole sheet style, use column range instead
range = range.toColumnRange();
}
selection =
new AreaRef(range.getRow(), range.getColumn(), range.getLastRow(), range.getLastColumn());
UndoableActionManager uam = ctx.getSpreadsheet().getUndoableActionManager();
uam.doAction(
new CellStyleAction(
Labels.getLabel("zss.undo.cellStyle"),
sheet,
selection.getRow(),
selection.getColumn(),
selection.getLastRow(),
selection.getLastColumn(),
CellOperationUtil.getVerticalAligmentApplier(_type)));
return true;
}
@Test
public void testZSS511_REPLACEB() throws UnsupportedEncodingException {
Book book = Util.loadBook(this, "book/511-REPLACEB-LEFTB.xlsx");
Sheet sheet = book.getSheet("REPLACEB");
assertEquals(
"A\u6E2C\u660E\u6708\u5149\u662F\u5B57\u4E32",
Ranges.range(sheet, "A1").getCellFormatText());
assertEquals(
"A\u6E2C\u660E\u6708\u5149 \u5B57\u4E32", Ranges.range(sheet, "A2").getCellFormatText());
assertEquals(
"A\u6E2C\u660E\u6708\u5149\u5B57\u4E32", Ranges.range(sheet, "A3").getCellFormatText());
assertEquals("A\u6E2C\u660E\u6708\u5149 \u4E32", Ranges.range(sheet, "A4").getCellFormatText());
assertEquals("A\u6E2C\u660E\u6708\u5149\u4E32", Ranges.range(sheet, "A5").getCellFormatText());
assertEquals("A\u6E2C\u660E\u6708\u5149 ", Ranges.range(sheet, "A6").getCellFormatText());
assertEquals("A\u6E2C\u660E\u6708\u5149", Ranges.range(sheet, "A7").getCellFormatText());
assertEquals("A\u6E2C\u660E\u6708\u5149", Ranges.range(sheet, "A8").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE", Ranges.range(sheet, "C1").getCellFormatText());
assertEquals(
"\u5E8A \u4F60\u597D\u55CE\u660E\u6708\u5149",
Ranges.range(sheet, "C2").getCellFormatText());
assertEquals(
"\u5E8A \u4F60\u597D\u55CE \u6708\u5149", Ranges.range(sheet, "C3").getCellFormatText());
assertEquals(
"\u5E8A \u4F60\u597D\u55CE\u6708\u5149", Ranges.range(sheet, "C4").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE \u5149", Ranges.range(sheet, "C5").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE\u5149", Ranges.range(sheet, "C6").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE ", Ranges.range(sheet, "C7").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE", Ranges.range(sheet, "C8").getCellFormatText());
assertEquals("#VALUE!", Ranges.range(sheet, "E1").getCellFormatText());
assertEquals("#VALUE!", Ranges.range(sheet, "E2").getCellFormatText());
assertEquals("\u4F60\u597D\u55CE", Ranges.range(sheet, "E3").getCellFormatText());
assertEquals(" ", Ranges.range(sheet, "E4").getCellFormatText());
assertEquals("\u6E2C", Ranges.range(sheet, "E5").getCellFormatText());
assertEquals("\u6E2C\u8A66", Ranges.range(sheet, "E6").getCellFormatText());
assertEquals(
"\u4F60\u597D\u55CE\u5E8A\u524D\u660E\u6708\u5149",
Ranges.range(sheet, "G1").getCellFormatText());
assertEquals(" \u4F60\u597D\u55CE", Ranges.range(sheet, "G2").getCellFormatText());
assertEquals(
"\u5E8A\u4F60\u597D\u55CE\u524D\u660E\u6708\u5149",
Ranges.range(sheet, "G3").getCellFormatText());
assertEquals("\u5E8A \u4F60\u597D\u55CE", Ranges.range(sheet, "G4").getCellFormatText());
assertEquals(
"\u5E8A\u524D\u4F60\u597D\u55CE\u660E\u6708\u5149",
Ranges.range(sheet, "G5").getCellFormatText());
assertEquals("\u5E8A\u524D \u4F60\u597D\u55CE", Ranges.range(sheet, "G6").getCellFormatText());
assertEquals(
"\u5E8A\u524D\u660E\u4F60\u597D\u55CE\u6708\u5149",
Ranges.range(sheet, "G7").getCellFormatText());
assertEquals(
"\u5E8A\u524D\u660E \u4F60\u597D\u55CE", Ranges.range(sheet, "G8").getCellFormatText());
assertEquals(
"\u5E8A\u524D\u660E\u6708\u4F60\u597D\u55CE\u5149",
Ranges.range(sheet, "G9").getCellFormatText());
assertEquals("A \u660E\u6708\u5149", Ranges.range(sheet, "I1").getCellFormatText());
assertEquals(
"A \u660E\u6708\u5149\u662F\u5B57\u4E32", Ranges.range(sheet, "I2").getCellFormatText());
assertEquals(
"A \u660E\u6708\u5149 \u5B57\u4E32", Ranges.range(sheet, "I3").getCellFormatText());
assertEquals("A \u660E\u6708\u5149\u5B57\u4E32", Ranges.range(sheet, "I4").getCellFormatText());
assertEquals("A \u660E\u6708\u5149 \u4E32", Ranges.range(sheet, "I5").getCellFormatText());
assertEquals("A \u660E\u6708\u5149\u4E32", Ranges.range(sheet, "I6").getCellFormatText());
assertEquals("HelWhatlo World", Ranges.range(sheet, "K1").getCellFormatText());
assertEquals("HelWhato World", Ranges.range(sheet, "K2").getCellFormatText());
assertEquals("HelWhat World", Ranges.range(sheet, "K3").getCellFormatText());
assertEquals("HelWhatWorld", Ranges.range(sheet, "K4").getCellFormatText());
assertEquals("HelWhatorld", Ranges.range(sheet, "K5").getCellFormatText());
assertEquals("HelWhatrld", Ranges.range(sheet, "K6").getCellFormatText());
assertEquals("HelWhatld", Ranges.range(sheet, "K7").getCellFormatText());
assertEquals("HelWhatd", Ranges.range(sheet, "K8").getCellFormatText());
}
@Test
public void testZSS511_LEFTB() {
Book book = Util.loadBook(this, "book/511-REPLACEB-LEFTB.xlsx");
Sheet sheet = book.getSheet("LEFTB");
assertEquals("#VALUE!", Ranges.range(sheet, "A1").getCellFormatText());
assertEquals("", Ranges.range(sheet, "A2").getCellFormatText());
assertEquals(" ", Ranges.range(sheet, "A3").getCellFormatText());
assertEquals("\u5E8A", Ranges.range(sheet, "A4").getCellFormatText());
assertEquals("\u5E8A ", Ranges.range(sheet, "A5").getCellFormatText());
assertEquals("\u5E8A\u524D", Ranges.range(sheet, "A6").getCellFormatText());
assertEquals("\u5E8A\u524D ", Ranges.range(sheet, "A7").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E", Ranges.range(sheet, "A8").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E ", Ranges.range(sheet, "A9").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E\u6708", Ranges.range(sheet, "A10").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E\u6708 ", Ranges.range(sheet, "A11").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E\u6708\u5149", Ranges.range(sheet, "A12").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E\u6708\u5149", Ranges.range(sheet, "A13").getCellFormatText());
assertEquals("\u5E8A\u524D\u660E\u6708\u5149", Ranges.range(sheet, "A14").getCellFormatText());
assertEquals("#VALUE!", Ranges.range(sheet, "C1").getCellFormatText());
assertEquals("", Ranges.range(sheet, "C2").getCellFormatText());
assertEquals("A", Ranges.range(sheet, "C3").getCellFormatText());
assertEquals("A ", Ranges.range(sheet, "C4").getCellFormatText());
assertEquals("A\u5E8A", Ranges.range(sheet, "C5").getCellFormatText());
assertEquals("A\u5E8A ", Ranges.range(sheet, "C6").getCellFormatText());
assertEquals("A\u5E8A\u524D", Ranges.range(sheet, "C7").getCellFormatText());
assertEquals("A\u5E8A\u524D ", Ranges.range(sheet, "C8").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E", Ranges.range(sheet, "C9").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E ", Ranges.range(sheet, "C10").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E\u6708", Ranges.range(sheet, "C11").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E\u6708 ", Ranges.range(sheet, "C12").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E\u6708\u5149", Ranges.range(sheet, "C13").getCellFormatText());
assertEquals("A\u5E8A\u524D\u660E\u6708\u5149", Ranges.range(sheet, "C14").getCellFormatText());
assertEquals("", Ranges.range(sheet, "E1").getCellFormatText());
}
protected void applyAction() {
Range src = Ranges.range(_sheet, _row, _column, _lastRow, _lastColumn);
Range dest = Ranges.range(_destSheet, _destRow, _destColumn, _destLastRow, _destLastColumn);
_pastedRange = CellOperationUtil.cut(src, dest);
}
