ZK Pivottable: Update Pivottable by Javascript

Introduction

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.

Pre-request

Display Data in ZK Pivottable
http://ben-bai.blogspot.tw/2012/07/zk-pivottable-display-data-in-zk.html

The Program

index.zul

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.

<zk>
    <!-- Tested with ZK 6.0.2 EE and ZK Pivottable 2.0.0 -->
    <script><![CDATA[
        function update (rowIdx, colIdx, val, dir) {
            var dataRow = jq('.z-pivottable-cell-field')[0].parentNode,
                style,
                currentRow = 0;
            style = '';
            // find data tr by rowIdx
            while (currentRow < rowIdx) {
                dataRow = dataRow.nextSibling;
                currentRow++;
            }
            // 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>';
        }
    ]]></script>
    <!-- window, apply a SelectorComposer -->
    <window id="win"
        apply="test.TestComposer">
        <vlayout>
            <textbox id="tbx" width="700px" />
            <button label="updateBtn">
                <attribute name="onClick"><![CDATA[
                    Clients.evalJavaScript(tbx.getValue());
                ]]></attribute>
            </button>
            <!-- pivottable, get model from window's composer -->
            <pivottable id="pivottable" model="${win$composer.pivotModel}" />
        </vlayout>
    </window>
</zk>

TestComposer.java

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;

import org.zkoss.zk.ui.select.SelectorComposer;


/**
 * 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;

    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));
            data.add(r.nextInt(10));
            result.add(data);
        }
        return result;
    }

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

The Result

View demo on line
http://screencast.com/t/WzufgfjpLNS

Reference

The dom tree of pivottable from firebug

Download

Full project at github
https://github.com/benbai123/ZK_Practice/tree/master/Components/projects/Addon_Practice/PivottableTest/UpdatePivottableByJavascript

Demo flash
https://github.com/benbai123/ZK_Practice/blob/master/Components/demos/addon/UpdatePivottableByJavascript.swf