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