Filter Data in a Range

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

ZK Spreadsheet can filter the data to find a subset of data in a range.

Purpose

The filtered data is displayed in rows that meet the provided criteria, and hide those trivial ones.

AutoFilter

Use Range.autoFilter() to toggle AutoFilter for sheet in a range. After applying AutoFilter, users can specify criteria by calling Range.autoFilter(integer, object, integer, object, boolean) to filter data in a range.

AutoFilter

AutoFilter with Criteria

Reapply filter

If the content within AutoFilter range has been added, modified or deleted, reapply the current filter can force the sheet to update the result per the current filter criteria. Use Range.applyFilter() to reapply the current filter.

Clear filter

Use Range.showAllData() to clear the current AutoFilter.

ZUML

<zk>
<div height="100%" width="100%" apply="org.zkoss.zssessentials.config.AutoFilterComposer">
	Criteria: hide "222"
	<div>
		<button id="autoFilter" label="Toggle AutoFilter" mold="trendy"></button>
		<button id="reapplyAutoFilter" label="Reapply AutoFilter" mold="trendy"></button>
		<button id="clearAutoFilter" label="Clear AutoFilter" mold="trendy"></button>
	</div>
	<spreadsheet id="spreadsheet" src="/WEB-INF/excel/config/autoFilter.xlsx">
				maxrows="200" 
				maxcolumns="40"
				width="100%"
				height="450px"></spreadsheet>
</div>
</zk>

Composer

Toggle AutoFilter

Spreadsheet spreadsheet;
int rowIndex;
int columnIndex;
public void onClick$autoFilter() {
	AutoFilter autoFilter = Ranges.range(spreadsheet.getSelectedSheet(), rowIndex, columnIndex).autoFilter();
	alert(autoFilter == null ? "Clear AutoFilter" : "Applied AutoFilter");
}

Toggle AutoFilter off

Reapply AutoFilter

public void onClick$reapplyAutoFilter() {
	Ranges.range(spreadsheet.getSelectedSheet()).applyFilter();
}

Edit Cell A4 222 and reapply AutoFilter

Clear AutoFilter

public void onClick$clearAutoFilter() {
	Ranges.range(spreadsheet.getSelectedSheet()).showAllData();
}

View the complete source of ZUML autoFill.zul

View the complete source of composer AutoFilterComposer.java

Version History

Version	Date	Content
2.1.0	May, 2011	AutoFilter

All source code listed in this book is at Github.