Wednesday, February 13, 2013

ZK Pivottable: Update Pivottable by Javascript


Assume you already known which row/col should be updated and the old value/new value of that cell, you can update pivottable by javascript and update only one cell instead of update the pivotmodel then recalculate/rerender, this way you can save lots of network bandwidth.

This article describe how to update pivottable by javascript.


Display Data in ZK Pivottable

The Program


There is a pivottable on this page, a textbox let you input the update script, the update script will be executed while button clicked.

The format of update script is update (rowIdx, colIdx, val, dir), where rowIdx denotes the index of row, starts from 0, colIdx denotes the index of column, starts from 0, you can locate a specific cell by rowIdx and colIdx, val denotes the value to update, dir denotes whether the new value is grater/lower than the old value, based on dir the new value will be colored by green/red as needed.

    <!-- Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0 -->
        function update (rowIdx, colIdx, val, dir) {
            var dataRow = jq('.z-pivottable-cell-field')[0].parentNode,
                currentRow = 0;
            style = '';
            // find data tr by rowIdx
            while (currentRow < rowIdx) {
                dataRow = dataRow.nextSibling;
            // find data td by colIdx
            cell = jq(dataRow).find('td')[colIdx];
            // create style by dir ('up' or 'down')
            if (dir) style = (dir == 'up'? 'style="color: green;"' : 'style="color: red;"');
            // update data cell
            cell.firstChild.innerHTML = '<span '+style+'>' + val + '</span>';
    <!-- window, apply a SelectorComposer -->
    <window id="win"
            <textbox id="tbx" width="700px" />
            <button label="updateBtn">
                <attribute name="onClick"><![CDATA[
            <!-- pivottable, get model from window's composer -->
            <pivottable id="pivottable" model="${win$composer.pivotModel}" />

Simply provide a pivotmodel.

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.impl.StandardCalculator;
import org.zkoss.pivot.impl.TabularPivotModel;


 * Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0
public class TestComposer extends SelectorComposer {
     * generated serial version UID
    private static final long serialVersionUID = -2897873399288955635L;

    private TabularPivotModel _pivotModel;

    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);
            _pivotModel.setFieldType("RowThree", 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);

            PivotField field = _pivotModel.getField("RowOne");
            _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 should match
     * the order of column name's
     * @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("RowThree - " + (r.nextInt(2) + 1));
            data.add("ColumnOne - " + (r.nextInt(2) + 1));
            data.add("ColumnTwo - " + (r.nextInt(2) + 1));
        return result;

     * prepare columns name for pivottable's model
     * @return
    public List<String> getColumns() {
        return Arrays.asList(new String[]{
                "RowOne", "RowTwo", "RowThree",
                "ColumnOne", "ColumnTwo",

The Result

View demo on line


The dom tree of pivottable from firebug


Full project at github

Demo flash

No comments:

Post a Comment