Introduction
This article describe how to get value from pivot model as needed (e.g., draw graph by pivot model content or use another component to display the content of pivot model), including basic value, value of calculators and grand total.
Pre-request
Display data in pivottable
http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html
Use calculator
http://ben-bai.blogspot.tw/2012/10/use-custom-calculator-in-zk-pivottable.html
And the official documents in References
The Program
index.zul
A simple zul page contains a pivottable, a button and a textbox, the textbox will show the current values of pivottable while button clicked.
<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">
<vlayout>
<!-- pivottable, get model from window's composer -->
<pivottable id="pivottable" model="${win$composer.pivotModel}" />
<button id="btn" label="update" />
<textbox id="tbx" hflex="1" rows="20" />
</vlayout>
</window>
</zk>TestComposer.java
Provide data and show pivot model value in textbox.
Regarding to show pivot model value, please refer to showCurrentContent method, it climbs rowHeaderTree and columnHeaderTree to get all current leaf node then get there value by TabularPivotModel#getValue.
package test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;
import org.zkoss.pivot.Calculator;
import org.zkoss.pivot.PivotField;
import org.zkoss.pivot.PivotHeaderNode;
import org.zkoss.pivot.PivotHeaderTree;
import org.zkoss.pivot.impl.StandardCalculator;
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.Textbox;
/**
* Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0
*
*/
@SuppressWarnings("rawtypes")
public class TestComposer extends SelectorComposer {
/**
* generated serial version UID
*/
private static final long serialVersionUID = -2897873399288955635L;
@Wire
private Textbox tbx;
private TabularPivotModel _pivotModel;
/**
* Get pivottable's model
* @return TabularPivotModel the pivottable's model
* @throws Exception
*/
public TabularPivotModel getPivotModel () throws Exception {
if (_pivotModel == null) {
_pivotModel = new TabularPivotModel(getData(), getColumns());
// assign rows, the order matches to the level of row node field
_pivotModel.setFieldType("RowOne", PivotField.Type.ROW);
_pivotModel.setFieldType("RowTwo", PivotField.Type.ROW);
// assign columns, the order matches to the level of column node field
_pivotModel.setFieldType("ColumnOne", PivotField.Type.COLUMN);
_pivotModel.setFieldType("ColumnTwo", PivotField.Type.COLUMN);
// assign datas, the order matches to the order of data field
_pivotModel.setFieldType("DataOne", PivotField.Type.DATA);
_pivotModel.setFieldType("DateTwo", PivotField.Type.DATA);
PivotField field = _pivotModel.getField("RowOne");
_pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
field = _pivotModel.getField("ColumnOne");
_pivotModel.setFieldSubtotals(field, new Calculator[] {StandardCalculator.SUM, StandardCalculator.MAX});
}
return _pivotModel;
}
/**
* prepare the data for pivottable's model
* The order of object put into data list matches
* the order of column name's order
* @return
* @throws Exception
*/
public List<List<Object>> getData() throws Exception {
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("RowOne - " + (r.nextInt(2) + 1));
data.add("RowTwo - " + (r.nextInt(2) + 1));
data.add("ColumnOne - " + (r.nextInt(2) + 1));
data.add("ColumnTwo - " + (r.nextInt(2) + 1));
data.add(r.nextInt(100)+1000);
data.add(r.nextInt(100)+1000);
result.add(data);
}
return result;
}
/**
* prepare columns name for pivottable's model
* @return
*/
public List<String> getColumns() {
return Arrays.asList(new String[]{
"RowOne", "RowTwo",
"ColumnOne", "ColumnTwo",
"DataOne", "DateTwo"
});
}
/**
* Display the current content of pivottable
*/
@Listen ("onClick = #btn")
public void showCurrentContent () {
StringBuilder sb = new StringBuilder("");
PivotHeaderTree rowHeaderTree = _pivotModel.getRowHeaderTree();
PivotHeaderTree columnHeaderTree = _pivotModel.getColumnHeaderTree();
PivotHeaderNode rRoot = rowHeaderTree.getRoot();
PivotHeaderNode cRoot = columnHeaderTree.getRoot();
// the length of data fields under a column
int dataFieldsLength = _pivotModel.getDataFields().length;
List<PivotHeaderNode> rows = getNodeList(rowHeaderTree);
List<PivotHeaderNode> columns = getNodeList(columnHeaderTree);
rows.add(rRoot);
for (PivotHeaderNode row : rows) { // for each row
// show original data
displayColumns(rRoot, cRoot, row, columns, dataFieldsLength, -1, sb);
// not first level row node
if (row.getDepth() > 1) {
PivotHeaderNode parentRow = row.getParent();
List children = parentRow.getChildren();
// is last child
if (children.get(children.size()-1).equals(row)) {
for (int calIdx = 0; calIdx < parentRow.getSubtotalCount(false); calIdx++) {
// show row subtotal
displayColumns(rRoot, cRoot, parentRow, columns, dataFieldsLength, calIdx, sb);
}
}
}
}
tbx.setValue(sb.toString());
}
/**
* Get all current "leaf" node of row/column
* @param headerTree
* @return
*/
@SuppressWarnings("unchecked")
public List<PivotHeaderNode> getNodeList (PivotHeaderTree headerTree) {
PivotHeaderNode root = headerTree.getRoot();
List<PivotHeaderNode> nodes = new ArrayList<PivotHeaderNode>();
List<PivotHeaderNode> tmp = new ArrayList<PivotHeaderNode>();
nodes = (List<PivotHeaderNode>)root.getChildren();
boolean foundAllRows = false;
while (!foundAllRows) {
foundAllRows = true;
for (PivotHeaderNode phn : nodes) {
// get only leaf nodes
// you can adjust condition as needed
if (phn.isOpen()) {
List<PivotHeaderNode> children = (List<PivotHeaderNode>)phn.getChildren();
if (children != null && children.size() > 0) {
tmp.addAll(children);
foundAllRows = false;
} else {
tmp.add(phn);
}
} else {
tmp.add(phn);
}
}
nodes = tmp;
tmp = new ArrayList<PivotHeaderNode>();
}
return nodes;
}
private void displayColumns (PivotHeaderNode rRoot, PivotHeaderNode cRoot,
PivotHeaderNode row, List<PivotHeaderNode> columns, int dataFieldsLength, int rowCalIdx, StringBuilder sb) {
for (PivotHeaderNode column : columns) { // for each column
// for each data field
for (int i = 0; i < dataFieldsLength; i++) {
if (i > 0) {
sb.append("\t");
}
// get data value from pivot model by row node, column node and data index
sb.append(_pivotModel.getValue(row, rowCalIdx, column, -1, i) == null?
"####" : _pivotModel.getValue(row, rowCalIdx, column, -1, i));
// last data and
// not first level node
if (i+1 == dataFieldsLength
&& column.getDepth() > 1) {
PivotHeaderNode parentColumn = column.getParent();
List children = parentColumn.getChildren();
// is last child
if (children.get(children.size()-1).equals(column)) {
// for each column calculator
for (int calIdx = 0; calIdx < parentColumn.getSubtotalCount(false); calIdx++) {
sb.append("\t|\t");
// again, for each data field
for (int j = 0; j < dataFieldsLength; j++) {
if (j > 0) {
sb.append("\t");
}
// get data value from pivot model by row node, column node and data index
sb.append(_pivotModel.getValue(row, rowCalIdx, parentColumn, calIdx, j) == null?
"####" : _pivotModel.getValue(row, rowCalIdx, parentColumn, calIdx, j));
}
}
}
}
}
sb.append("\t|\t");
}
for (int i = 0; i < dataFieldsLength; i++) {
if (i > 0) {
sb.append("\t");
}
// grand total for columns
sb.append(_pivotModel.getValue(row, rowCalIdx, cRoot, -1, i) == null?
"####" : _pivotModel.getValue(row, rowCalIdx, cRoot, -1, i));
}
sb.append("\n");
}
}The Result
View demo on line
http://screencast.com/t/zT7gos0whbdj
References
Prepare data for pivottable
http://books.zkoss.org/wiki/ZK_Pivottable_Essentials/Working_With_Pivottable/Prepare_Data
Javadoc of TabularPivotModel
http://www.zkoss.org/javadoc/latest/zkpvt/org/zkoss/pivot/impl/TabularPivotModel.html
Download
Project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/GetValueFromPivotmodel
Demo flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/addon/GetValueFromPivotmodel.swf
No comments:
Post a Comment