Filter Event"

From Documentation
Line 20: Line 20:
 
The <javadoc directory="zss"  directory="zss">org.zkoss.poi.ss.usermodel.FilterColumn</javadoc> represents filtered column. Use <javadoc directory="zss" method="getCriteria1()">org.zkoss.poi.ss.usermodel.FilterColumn</javadoc> to get current criteria. If the criteria set is empty, means select all.<br/>
 
The <javadoc directory="zss"  directory="zss">org.zkoss.poi.ss.usermodel.FilterColumn</javadoc> represents filtered column. Use <javadoc directory="zss" method="getCriteria1()">org.zkoss.poi.ss.usermodel.FilterColumn</javadoc> to get current criteria. If the criteria set is empty, means select all.<br/>
 
'''Blank Criteria'''<br/>
 
'''Blank Criteria'''<br/>
Use <javadoc directory="zss" method="isBlankCell(org.zkoss.poi.ss.usermodel.Cell)">org.zkoss.zss.model.impl.BookHelper</javadoc> to test whether cell is blank or not. The blank criteria string is '''='''
+
Use <javadoc directory="zss" method="isBlankCell(org.zkoss.poi.ss.usermodel.Cell)">org.zkoss.zss.model.impl.BookHelper</javadoc> to test whether cell is blank or not. The blank criteria string is ''''=''''
  
 
[[File:ZKSsEss_Spreadsheet_FilterEvent_CriteriaList.png]]<br/>
 
[[File:ZKSsEss_Spreadsheet_FilterEvent_CriteriaList.png]]<br/>

Revision as of 03:18, 24 May 2011


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.

ZKSsEss Spreadsheet FilterEvent onFilter.png

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 '='

ZKSsEss Spreadsheet FilterEvent CriteriaList.png

File:ZKSsEss Spreadsheet FilterEvent CriteriaList Blank.png

ZKSsEss Spreadsheet FilterEvent CriteriaList Filtered.png

Filter Criteria

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

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

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

Select criteria

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

Version History

Last Update : 2011/05/24


Version Date Content
2.1.0 May, 2011 AutoFilter
     


All source code listed in this book is at Github.


Last Update : 2011/05/24

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