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.
Blank Criteria
Use BookHelper.isBlankCell(Cell) to test whether cell is blank or not. The blank criteria string is =

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

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.