Filter Event

This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.

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 ones.

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. If set blank criteria, in the criteria set, 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, int index) 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);
		}
	}
}

View complete source of ZUML filterevent.zul

View complete source of composer FilterEventComposer.java

Version History

Version	Date	Content
2.1.0	May, 2011	AutoFilter

All source code listed in this book is at Github.