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