A Sip of the ZK Spreadsheet Component

{{Template:Smalltalk_Author|
|author=Henri Chen, Principal Engineer, Potix Corporation
|date=September 21, 2007
|version=
}}



=Introduction=

The ZK team is taking effort on implementing a new ZK spreadsheet "component". This component includes three major parts -- the UI part, the data model, and the evaluation engine. The UI part will be something like the grid component that you can show and in-place editing the contents of a spreadsheet. The evaluation engine is where the spreadsheet formulas are parsed and calculated. The data model part is where the real data is stored.


=The Architecture Of the ZK Spreadsheet=

[[Image:Zsssip.png]]

As you can see on the above figure, whenever an end user make some change to the UI "cell", the UI component will notify the ZK spreadsheet model to save new value to the "cell" of the associated model. The model then notify ZK spreadsheet engine to check the formula dependency. The engine calculates related formulas and notify back the data model all dependent cells and registered "FormulaListener"s (will talk this later). And finally the UI components are notified to update themselves.

Notice that the ZK spreadsheet model is not limited to be used with spreadsheet UI component only. You can associate other ZK component to a FormulaListener, provide a formula, and register it to the spreadsheet model. The ZK spreadsheet model will then notify the registered FromulaListener whenever a change is associated with the given formula. In fact, this mechanism does not even limited to a ZK component. You can simply write any POJO object that associates with the formula and do things whenever the data associated with the formula changes.

On the following, we will use a simple example to demonstrate this architecture


=A Simple Travel Expense Example=

The following is a simple example. Since the spreadsheet UI component is not ready yet, we use a ZK grid to simulate it. And we implement two adapter classes SSListModel and SSRowRenderer to bridge spreadsheet data model to grid's original ListModel and RowRenderer mechanism. So we can manipulate the spreadsheet model data and expect the grid to reflect the data changing. Following is the recorded demonstration.

<gflash width="300" height="300">Ssdemo.swf</gflash>

The demo page index.zul is shown below. The SSListModel java object imports an existing Excel file "travel.xls" to create a ZK spreadsheet model. The Excel is a simple travel expense calculator.

<source lang="xml" >
<?xml version="1.0" encoding="UTF-8"?>
<?taglib uri="http://www.zkoss.org/ss/function" prefix="ss" ?>
<window xmlns="http://www.zkoss.org/2005/zul" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://www.zkoss.org/2005/zul 
  http://www.zkoss.org/2005/zul/zul.xsd ">
  <zscript>
    import org.zkoss.ssdemo.SSListModel;
    import org.zkoss.ss.model.Book;
    
    //import travel.xls Excel file and returns the associated SSListModel.
    SSListModel model = new SSListModel("travel.xls");
    
    //retrieve the ZK Book (The ZK spreadsheet data model) to be used later
    Book book = model.getSheet().getBook();
  </zscript>
  <hbox><label style="font-weight:bold" id="lbpos"/><textbox id="tbxval"/></hbox>
  <grid id="grd" model="${model}" rowRenderer="org.zkoss.ssdemo.SSRowRenderer" width="300px">
    <columns>
      <column label="Expenditures"/>
      <column label="Amount" align="right"/>
    </columns>
  </grid>
  <vbox>
    <hbox>
      Reimbursement (Total * 0.5) : 
      <label id="lb" value="${ss:formula(book, '=Sheet1!B5 * 0.5')}"/>
    </hbox>
  </vbox>
  <zscript>
    //Associate formula to the value attribute of the label component "lb".
    //Whenever the result of the associate formula changes, book will fire
    //an FormulaEvent to the registered "lb" component and change the registered
    //attribute "value" with the new formula result.
    book.addFormulaListener(new SimpleFormulaListener(lb, "value", "=Sheet1!B5 * 0.5"));
  </zscript>
</window>
</source>


At the bottom of the code there is a "Reimbursement" label that you can register a formula to the ZK spreadsheet model and whenever a data change associated to the given formula , the label's value is updated accordingly. In this example, whenever the B5 is changed, the Reimbursement is calculated automatically. Since B5 is the summation of B1 to B4, any change from B1 to B5 will cause this "Reimbursement" value changes.

=Summary=

This is our first draft of the ZK spreadsheet component implementation. There are definitely big improving spaces. We need your feedbacks on this component and we can make it fit your needs.


''Download the demo code (including demo source code) [http://zkoss.org/smalltalks/zsssip/ssdemo.war here]''


<comment>http://docs.zkoss.org/wiki/A_Sip_of_the_ZK_Spreadsheet_Component</comment>



[[Category:ZK Spreadsheet]]
{{Template:Smalltalk_Footer|
|name=Potix Corporation
}}