Filter Event
Available in ZK Spreadsheet EE only
Purpose
ZK Spreadsheet can filter data to find a subset of data in a range. Filtered data display rows that meet criteria, and hide trivial data.
Filter Event
The FilterMouseEvent event is fired when user click cell filter button.
Criteria List
AutoFilter
The AutoFilter represents auto filter for specified worksheet. Use AutoFilter.getRangeAddress() to get auto filter range.
FilterColumn
The FilterColumn represents filtered column. Use FilterColumn.getCriteria1() to get current criteria. If the criteria set is empty, means select all.
Blank Criteria
Use BookHelper.isBlankCell(Cell) to test whether cell is blank or not. The blank criteria string is "="
Filter Criteria
Use Range.autoFilter(Integer, Object, Integer, Object, Boolean) to filter list in the range.
final String[] criteria = new String[selcount];
//process criteria
...
range.autoFilter(fieldOffset, criteria, AutoFilter.FILTEROP_VALUES, null, null);
ZUML
<zk>
<window title="ZSS Filter Events" border="normal" width="100%"
height="100%" apply="org.zkoss.zssessentials.events.FilterEventComposer">
<hlayout>
<listbox id="criteriaListbox" checkmark="true" multiple="true" width="200px" height="800px"></listbox>
<spreadsheet id="spreadsheet" width="800px" height="800px" maxrows="200"
maxcolumns="20" src="/WEB-INF/excel/events/filter.xlsx">
</spreadsheet>
</hlayout>
</window>
</zk>
Composer
onFilter
Use onFilter evnet to retrieve current criteria list
public void onFilter$spreadsheet(FilterMouseEvent event) {
final Worksheet worksheet = event.getSheet();
final AutoFilter autoFilter = worksheet.getAutoFilter();
if (autoFilter == null)
return;
int columnIndex = event.getColumn();
fieldOffset = event.getField();
final CellRangeAddress cellRangeAddr = autoFilter.getRangeAddress();
final FilterColumn filterColumn = autoFilter.getFilterColumn(fieldOffset - 1);
final Set criteria1 = filterColumn == null ? null : filterColumn.getCriteria1();
final boolean nofilter = criteria1 == null || criteria1.isEmpty();
boolean hasBlank = false;
boolean selectedBlank = false;
Set<RowInfo> all = new TreeSet<RowInfo>(new MyComparator());
Set<RowInfo> selected = new HashSet<RowInfo>();
for (int r = cellRangeAddr.getFirstRow() + 1; r <= cellRangeAddr.getLastRow(); r++) {
if (nofilter && isHiddenRow(worksheet, r)) {
continue;
}
final Cell c = Utils.getCell(worksheet, r, columnIndex);
final boolean blankcell = BookHelper.isBlankCell(c);
if (!blankcell) {
String displaytxt = BookHelper.getCellText(c);
Object val = BookHelper.getEvalCellValue(c);
if (val instanceof RichTextString) {
val = ((RichTextString)val).getString();
} else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(c)) {
val = c.getDateCellValue();
}
RowInfo rowInfo = new RowInfo(val, displaytxt);
all.add(rowInfo);
if (criteria1 == null || criteria1.isEmpty() || criteria1.contains(displaytxt)) { //selected
selected.add(rowInfo);
}
} else {
hasBlank = true;
if (!selectedBlank && (nofilter || criteria1.contains("="))) { //selected
selectedBlank = true;
}
}
}
if (hasBlank) {
all.add(BLANK_ROW_INFO);
}
if (selectedBlank) {
selected.add(BLANK_ROW_INFO);
}
ListModelList model = new ListModelList(all);
criteriaListbox.setModel(model);
for(RowInfo rowInfo : selected) {
model.addSelection(rowInfo);
}
criteriaListbox.setItemRenderer(new ListitemRenderer() {
public void render(Listitem item, Object data) throws Exception {
RowInfo info = (RowInfo)data;
item.setLabel("" + info.display);
}
});
...
onSelect Criteria
Use onSelect event to filter a list in the range
public void onSelect$criteriaListbox() {
ListModelList model = (ListModelList) criteriaListbox.getModel();
if (model != null) {
final int itemcount = model.size();
final Set selected = model.getSelection();
final int selcount = selected.size();
Range range = Ranges.range(spreadsheet.getSelectedSheet());
if (selcount < itemcount) { //partial selection
final String[] criteria = new String[selcount];
int j = 0;
TreeSet sel = new TreeSet(new MyComparator());
sel.addAll(selected);
for (final Iterator it = sel.iterator(); it.hasNext(); ) {
RowInfo info = (RowInfo) it.next();
criteria[j++] = BLANK_ROW_INFO.equals(info) ? "=" : info.display;
}
range.autoFilter(fieldOffset, criteria, AutoFilter.FILTEROP_VALUES, null, null);
} else { //select all!
range.autoFilter(fieldOffset, null, AutoFilter.FILTEROP_VALUES, null, null);
}
}
}
Version History
Version | Date | Content |
---|---|---|
2.1.0 | May, 2011 | AutoFilter |
All source code listed in this book is at Github.