Introduction
This article how to get the distinct values of a row or column field when you click on it.
Pre-request
ZK Pivottable: Display Data in ZK Pivottable
http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html
ZK Basic MVC Pattern with SelectorComposer
http://ben-bai.blogspot.tw/2012/10/zk-basic-mvc-pattern-with_31.html
The Program
index.zul
A pivottable, a label that will show the info of clicked row/column field, a listbox that will show the distinct values of clicked row/column field
<zk>
<!-- Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0 -->
<!-- window, apply a SelectorComposer -->
<window id="win" xmlns:w="client"
apply="test.TestComposer">
<!-- pivottable, get model from window's composer -->
<pivottable id="pivottable" model="${win$composer.pivotModel}" />
<!-- label, show info of clicked row or column field -->
<div style="margin: 15px;">
<div>
clicked field: <label id="lb" />
</div>
<!-- listbox, show all distinct values of clicked row or column field -->
values<listbox id="lbx" />
</div>
</window>
</zk>
TestComposer.java
Listen to onPivotPopup event (will be fired when clicking on row/column/data cell), update field info and values list as needed.
package test;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.zkoss.pivot.event.PivotUIEvent;
import org.zkoss.pivot.impl.TabularPivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
import org.zkoss.zk.ui.select.SelectorComposer;
import org.zkoss.zk.ui.select.annotation.Listen;
import org.zkoss.zk.ui.select.annotation.Wire;
import org.zkoss.zul.Label;
import org.zkoss.zul.Listbox;
import org.zkoss.zul.Listcell;
import org.zkoss.zul.Listitem;
/**
* Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0
*
* @author benbai123
*/
@SuppressWarnings("rawtypes")
public class TestComposer extends SelectorComposer {
private static final long serialVersionUID = -8249566421884806620L;
@Wire
Label lb; /** label, contains type and name of clicked row or column */
@Wire
Listbox lbx; /** listbox, contains distinct values of clicked row or column field */
/** pivot model with the 'whole' raw data */
private TabularPivotModel _pivotModel;
/** model provider, provide the columns, raw data and pivot model */
private PivotModelProvider _modelProvider = new PivotModelProvider();
/**
* map that contains all field index
* use field name as the keys
*/
private Map<String, Integer> _indexMap = new HashMap<String, Integer>();
/**
* Get pivottable's model
* @return TabularPivotModel the pivottable's model
* @throws Exception
*/
public TabularPivotModel getPivotModel () throws Exception {
if (_pivotModel == null) {
_pivotModel = _modelProvider.getPivotModel();
}
return _pivotModel;
}
/**
* update the value list and label if clicked on row or column field
* @param e
* @throws Exception
*/
@Listen("onPivotPopup = #pivottable")
public void updateValueList (PivotUIEvent e) throws Exception {
TabularPivotField field = getClickedField(e);
if (field != null) {
updateValueList(field);
} else {
lb.setValue("");
lbx.getChildren().clear();
}
}
/**
* get the clicked row or column field
* @param e
*/
private TabularPivotField getClickedField (PivotUIEvent e) {
TabularPivotField field = null;
if (e != null
&& (e.getRowContext() == null
|| e.getColumnContext() == null)) {
// not clicked on data field
if (e.getRowContext() != null) {
// clicked on row field
field = _pivotModel.getRowFields()[e.getRowContext().getNode().getDepth() - 1];
} else {
// clicked on column field
field = _pivotModel.getColumnFields()[e.getColumnContext().getNode().getDepth() - 1];
}
}
return field;
}
/**
* create the value list of clicked row/column field
* @param field pivot field, used to get fieldName
* @throws Exception
*/
@SuppressWarnings({ "unchecked" })
private void updateValueList (TabularPivotField field) throws Exception {
// clear old children
lbx.getChildren().clear();
List<String> columns = _modelProvider.getColumns();
// index of field name in columns
int index = getFieldIndex(columns, field.getFieldName());
// raw data of pivot model
Iterable<List<Object>> rawData = (Iterable<List<Object>>)getPivotModel().getRawData();
// distinct values of the given field
List<Object> distinctValues = getDistinctValues(rawData, columns, index);
// create listitem for each value
for (final Object value : distinctValues) {
Listitem li = new Listitem();
Listcell lc = new Listcell(value.toString());
lc.setParent(li);
li.setParent(lbx);
}
// update field info label
lb.setValue("field type: " + field.getType() + ", field name: " + field.getFieldName());
}
/**
* get all different values of a field
* @param rawData the raw data to get different values from
* @param columns all columns in pivot model
* @param index the index to get value from list
* @return
*/
@SuppressWarnings({ "unchecked" })
private static List getDistinctValues (Iterable<List<Object>> rawData, List<String> columns, int index) {
// set used to hold distinct values
Set s = new HashSet();
// result list
List result = new ArrayList();
if (index == -1) return result;
// add all value to set directly
for (List<Object> data : rawData) {
s.add(data.get(index));
}
// copy to list then sort the list
for (Object o : s) {
result.add(o);
}
Collections.sort(result);
return result;
}
/**
* get the index in raw data of a field
* @param columns columns used in pivot model
* @param fieldName name of the field to search index
* @return int the found index
*/
private int getFieldIndex (List<String> columns, String fieldName) {
// search it from index map at first
if (_indexMap.containsKey(fieldName)) {
return _indexMap.get(fieldName);
} else { // not found
int index = -1;
// search field name in columns
for (int i = 0; i < columns.size(); i++) {
if (columns.get(i).equals(fieldName)) {
index = i;
break;
}
}
// store it to index map
_indexMap.put(fieldName, index);
return index;
}
}
}
PivotModelProvider.java
Simply provide pivot model.
package test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;
import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.impl.TabularPivotModel;
public class PivotModelProvider {
/** pivot model with the 'whole' raw data */
private TabularPivotModel _pivotModel;
/**
* Get pivottable's model
* @return TabularPivotModel the pivottable's model
* @throws Exception
*/
public TabularPivotModel getPivotModel () {
if (_pivotModel == null) {
_pivotModel = new TabularPivotModel(getData(), getColumns());
// assign rows, the order matches to the level of row node field
_pivotModel.setFieldType("Row_Level_001", PivotField.Type.ROW);
_pivotModel.setFieldType("Row_Level_002", PivotField.Type.ROW);
_pivotModel.setFieldType("Row_Level_003", PivotField.Type.ROW);
_pivotModel.setFieldType("Row_Level_004", PivotField.Type.ROW);
// assign columns, the order matches to the level of column node field
_pivotModel.setFieldType("Column_Level_001", PivotField.Type.COLUMN);
_pivotModel.setFieldType("Column_Level_002", PivotField.Type.COLUMN);
// assign datas, the order matches to the order of data field
_pivotModel.setFieldType("Data_Field_001", PivotField.Type.DATA);
_pivotModel.setFieldType("Data_Field_002", PivotField.Type.DATA);
_pivotModel.setFieldType("Data_Field_003", PivotField.Type.DATA);
}
return _pivotModel;
}
/**
* prepare the data for pivottable's model
* The order of object put into data list matches
* the order of column names
* @return
* @throws Exception
*/
public List<List<Object>> getData() {
List<List<Object>> result = new ArrayList<List<Object>>();
Random r = new Random();
for (int i = 0; i < 100; i++) {
List<Object> data = new ArrayList<Object>();
data.add("Row_Level_001 - " + (r.nextInt(5) + 1));
data.add("Row_Level_002 - " + (r.nextInt(5) + 1));
data.add("Row_Level_003 - " + (r.nextInt(5) + 1));
data.add("Row_Level_004 - " + (r.nextInt(5) + 1));
data.add("Column_Level_001 - " + (r.nextInt(5) + 1));
data.add("Column_Level_002 - " + (r.nextInt(5) + 1));
data.add(r.nextInt(10000));
data.add(r.nextDouble() * 10000.0);
data.add(r.nextInt(100));
result.add(data);
}
return result;
}
/**
* prepare columns name for pivottable's model
* @return
*/
public List<String> getColumns() {
return Arrays.asList(new String[]{
"Row_Level_001", "Row_Level_002", "Row_Level_003", "Row_Level_004",
"Column_Level_001", "Column_Level_002",
"Data_Field_001", "Data_Field_002", "Data_Field_003"
});
}
}
The Result
View demo on line
http://screencast.com/t/q9fvH9M23ZJ
Reference
Start point of APIs
http://www.zkoss.org/javadoc/latest/zkpvt/org/zkoss/pivot/impl/TabularPivotModel.html
Download
Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/GetDistinctFieldValue
Demo Flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/addon/pvt_get_distinct_values_of_field.swf