Filter Data in a Range"

From Documentation
m (correct highlight (via JWB))
 
(25 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
 +
 +
 +
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
  
 
__TOC__
 
__TOC__
 +
 
{{ZSS EE}}
 
{{ZSS EE}}
  
−
ZK Spreadsheet can filter data to find a subset of data in a range.
+
ZK Spreadsheet can filter the data to find a subset of data in a range.
  
 
===Purpose===
 
===Purpose===
−
Filtered data display rows that meet criteria, and hide trivial data.
+
The filtered data is displayed in rows that meet the provided criteria, and hide those trivial ones.
  
 
===AutoFilter===
 
===AutoFilter===
−
Use <javadoc directory="zss" method="autoFilter()">org.zkoss.zss.model.Range</javadoc> can toggle AutoFilter for sheet in a range. After applied AutoFilter, user can specify criteria use <javadoc directory="zss" method="autoFilter(java.lang.integer, java.lang.object, java.lang.integer, java.lang.object, java.lang.boolean)">org.zkoss.zss.model.Range</javadoc> to filter data in a range.
+
Use <javadoc directory="zss" method="autoFilter()">org.zkoss.zss.model.Range</javadoc> to toggle AutoFilter for sheet in a range. After applying AutoFilter, users can specify criteria by calling <javadoc directory="zss" method="autoFilter(java.lang.integer, java.lang.object, java.lang.integer, java.lang.object, java.lang.boolean)">org.zkoss.zss.model.Range</javadoc> to filter data in a range.
 +
 
 +
AutoFilter<br/>
 +
[[File:ZKSsEss_Spreadsheet_FilterData_AutoFilter.png]]<br/>
 +
AutoFilter with Criteria<br/>
 +
[[File:ZKSsEss_Spreadsheet_FilterData_AutoFilter_WithCriteria.png]]<br/>
  
 
===Reapply filter===
 
===Reapply filter===
−
If data within AutoFilter range has been added, modified or deleted. Reapply a filter can display different results using same criteria. Use <javadoc directory="zss" method="applyFilter()">org.zkoss.zss.model.Range</javadoc> to reapply current 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 <javadoc directory="zss" method="applyFilter()">org.zkoss.zss.model.Range</javadoc> to reapply the current filter.
  
 
===Clear filter===
 
===Clear filter===
−
Use <javadoc directory="zss" method="showAllData()">org.zkoss.zss.model.Range</javadoc> to clear current filter.
+
Use <javadoc directory="zss" method="showAllData()">org.zkoss.zss.model.Range</javadoc> to clear the current AutoFilter.
  
 
===ZUML===
 
===ZUML===
Line 28: Line 37:
 
<button id="clearAutoFilter" label="Clear AutoFilter" mold="trendy"></button>
 
<button id="clearAutoFilter" label="Clear AutoFilter" mold="trendy"></button>
 
</div>
 
</div>
−
<spreadsheet id="spreadsheet" src="/WEB-INF/excel/config/autoFilter.xlsx"
+
<spreadsheet id="spreadsheet" src="/WEB-INF/excel/config/autoFilter.xlsx">
 
maxrows="200"  
 
maxrows="200"  
 
maxcolumns="40"
 
maxcolumns="40"
Line 36: Line 45:
 
</zk>
 
</zk>
 
</source>
 
</source>
 +
 
===Composer===
 
===Composer===
−
====Reapply AutoFilter====
+
====Toggle AutoFilter====
−
<source lang="java" high="6">
+
<source lang="java" highlight="5">
 +
Spreadsheet spreadsheet;
 
int rowIndex;
 
int rowIndex;
 
int columnIndex;
 
int columnIndex;
−
Spreadsheet spreadsheet;
+
public void onClick$autoFilter() {
 +
AutoFilter autoFilter = Ranges.range(spreadsheet.getSelectedSheet(), rowIndex, columnIndex).autoFilter();
 +
alert(autoFilter == null ? "Clear AutoFilter" : "Applied AutoFilter");
 +
}
 +
</source>
 +
 
 +
Toggle AutoFilter off<br/>
 +
[[File:ZKSsEss_Spreadsheet_FilterData_Clear_AutoFilter.png]]<br/>
  
 +
====Reapply AutoFilter====
 +
<source lang="java" highlight="2">
 
public void onClick$reapplyAutoFilter() {
 
public void onClick$reapplyAutoFilter() {
−
Ranges.range(spreadsheet.getSelectedSheet(), rowIndex, columnIndex).applyFilter();
+
Ranges.range(spreadsheet.getSelectedSheet()).applyFilter();
 
}
 
}
 
</source>
 
</source>
−
The Criteria is hide "222"
+
 
−
[[File:ZKSsEss_Spreadsheet_AutoFilter.png]] </br>
+
Edit Cell A4 '''222''' and reapply AutoFilter <br/>
−
Input "222" in A4, and click Reapply AutoFilter button
+
[[File:ZKSsEss_Spreadsheet_FilterData_Reapply_Edit.png]]<br/>
−
[[File:ZKSsEss_Spreadsheet_AutoFilter_Reapply.png]] </br>
+
[[File:ZKSsEss_Spreadsheet_FilterData_Reapply.png]]<br/>
  
 
====Clear AutoFilter====
 
====Clear AutoFilter====
 +
<source lang="java" highlight="2">
 +
public void onClick$clearAutoFilter() {
 +
Ranges.range(spreadsheet.getSelectedSheet()).showAllData();
 +
}
 +
</source>
 +
 +
[[File:ZKSsEss_Spreadsheet_FilterData_ClearFilter.png]]<br/>
 +
 +
View the complete source of ZUML [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/config/autoFill.zul autoFill.zul]
 +
 +
View the complete source of composer [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/src/org/zkoss/zssessentials/config/AutoFilterComposer.java AutoFilterComposer.java]
  
 
=Version History=
 
=Version History=

Latest revision as of 12:55, 19 January 2022


DocumentationZK Spreadsheet EssentialsWorking with ZK SpreadsheetConfigure and Control ZK SpreadsheetFilter Data in a Range
Filter Data in a Range




Stop.png 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
ZKSsEss Spreadsheet FilterData AutoFilter.png
AutoFilter with Criteria
ZKSsEss Spreadsheet FilterData AutoFilter WithCriteria.png

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
ZKSsEss Spreadsheet FilterData Clear AutoFilter.png

Reapply AutoFilter

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

Edit Cell A4 222 and reapply AutoFilter
ZKSsEss Spreadsheet FilterData Reapply Edit.png
ZKSsEss Spreadsheet FilterData Reapply.png

Clear AutoFilter

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

ZKSsEss Spreadsheet FilterData ClearFilter.png

View the complete source of ZUML autoFill.zul

View the complete source of composer AutoFilterComposer.java

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.


Retrieved from "https://www.zkoss.org/_w/index.php?title=ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Configure_and_Control_ZK_Spreadsheet/Filter_Data_in_a_Range&oldid=53623"