ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Implement ZK Spreadsheet Event Listeners/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.

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();
        for (int r = cellRangeAddr.getFirstRow() + 1; r <= cellRangeAddr.getLastRow(); r++) {
             //search range to get criteria
             ....
        }
...

Filter Criteria

Use Range.autoFilter(Integer, Object, Integer, Object, Boolean)

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>

Version History

Version	Date	Content
2.1.0	May, 2011	AutoFilter

All source code listed in this book is at Github.