Filter Event

From Documentation

Stop.png This article is out of date, please refer to for more up to date information.

Available in ZK Spreadsheet EE only


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.

ZKSsEss Spreadsheet FilterEvent onFilter.png

Criteria List

The AutoFilter represents auto filter for specified worksheet. Use AutoFilter.getRangeAddress() to get auto filter range.
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 "=".

ZKSsEss Spreadsheet FilterEvent CriteriaList.png

ZKSsEss Spreadsheet FilterEvent CriteriaList Filtered.png

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


	<window title="ZSS Filter Events" border="normal" width="100%"
		height="100%" apply="">
			<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">



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)
	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)) {
		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); 
			if (criteria1 == null || criteria1.isEmpty() || criteria1.contains(displaytxt)) { //selected
		} else {
			hasBlank = true;
			if (!selectedBlank && (nofilter || criteria1.contains("="))) { //selected
				selectedBlank = true;
	if (hasBlank) {
	if (selectedBlank) {
	ListModelList model = new ListModelList(all);
	for(RowInfo rowInfo : selected) {
	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());
			for (final Iterator it = sel.iterator(); it.hasNext(); ) {
				RowInfo info = (RowInfo);
				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

Version History

Last Update : 2022/01/19

Version Date Content
2.1.0 May, 2011 AutoFilter

All source code listed in this book is at Github.

Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.