ZK Spreadsheet Essentials/Working with ZK Spreadsheet/Bind Java Bean to ZK Spreadsheet

ZK Spreadsheet can resolve the name expressions in cells to bind the data from the back end Java beans automatically.

Purpose

Bind back end Java beans' value directly into spreadsheet cells.

Template File with Proper Name Expressions

We can construct an Excel template file with proper name expressions in cells.

You will see #NAME? in cells because Excel does not know such names. However, they will be interpreted when import into ZK Spreadsheet component.

How ZK Spreadsheet Resolve my Java Beans

ZK Spreadsheet follows ZK's EL expression variable resolving mechanism. It will first try to find the zscript variables. Then check id of ZK fellow components. Then search in ZK components' attribute map. Finally ask variable resolvers defined in the zul page to retrieve the bean of the named variable. If still none is found, it will return #NAME? as Excel's behavior. Once variable is resolved, the associated getter is called and value returned in the cell.

ZUML Example

In ZUML page, we declare a zscript variable "dataBean" so ZK Spreadsheet shall find it in no time.

	<zk>
		<zscript>
		<![CDATA[
			import demo.javabean.DataBean;
			DataBean dataBean = new DataBean();
			// initialize dataBean attributes ...
			dataBean.setLiquidAssets(146504221);
			...
		]]>
		</zscript>
		<spreadsheet id="bsheet" 
			src="/bsheet.xls"
			maxrows="200" 
			maxcolumns="40" 
			vflex="1" 
			width="100%">
		</spreadsheet>
	</zk>

Java Bean

This is the Java Bean in our example. Mainly getters and setters for various attributes for a balance sheet.

package demo.javabean;
public class DataBean {
	private double liquidAssets;
	private double fundInvestment;
	private double fixedAssets;
	private double intangibleAsset; 
	private double otherAssets;
	private double currentLiabilities;
	private double longTermLiabilities;
	private double otherLiabilities;
	private double capitalStock;
	private double capitalSurplus;
	private double retainedEarnings;
	private double otherEquity;
	private double treasuryStock;
	
	public double getLiquidAssets() {
		return liquidAssets;
	}

	public void setLiquidAssets(double liquidAssets) {
		this.liquidAssets = liquidAssets;
	}

	//...other getters and setters
}

When Value of Java Bean Changes

Will ZK Spreadsheet update the cells if the value of the Java bean changes? Well, if you notify it.

	bsheet.getBook().notifyChange(new String[] {"dataBean"});

When you notify the ZK Spreadsheet work book model that beans with the specified names have changed, it will collect which cells are affected (i.e. those dependent cells), and publish data change event accordingly. The ZK Spreadsheet UI or any other components that have subscribed to the work book's event queue and are interested in the event will then retrieve the new cells' data again and update themselves.

Integrate ZK Spreadsheet with Spring

As we have mentioned in #How ZK Spreadsheet Resolve my Java Beans, ZK Spreadsheet follows ZK's EL expression variable resolving mechanism. When trying to resolve a variable, it will finally ask variable resolvers declared in the ZUML page to retrieve the bean of the named variable. So simply declare the variable-resolver with org.zkoss.zkplus.spring.DelegatingVariableResolver on top of your ZUML page, you are now data-binding your ZK Spreadsheet with your Spring-Managed beans directly. Following we demonstrate the example that use the same bsheet.xls template file as in #Template File with Proper Name Expressions but data-binding to Spring-Managed bean.

ZUML Example

In ZUML page, we declare the variable resolver for retrieving Spring-Managed beans so ZK Spreadsheet can find them and show the evaluated value in the spreadsheet's cells.

<?variable-resolver class="org.zkoss.zkplus.spring.DelegatingVariableResolver"?>
<spreadsheet id="bsheet"
    src="/bsheet.xls"
    maxrows="200"
    maxcolumns="40"
    vflex="1"
    width="100%">
</spreadsheet>

Spring Configuration File

Define bean definitions in applicationContext.xml file, and put it into your WEB-INF directory.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">
 
<beans>
  <bean id="dataBean" class="demo.DataBeanImpl"/>
</beans>

Spring Bean Class

Define DataBean interface and its implementation. Mainly getters and setters for various attributes for a balance sheet.

DataBean.java

package demo.springbean;
public interface DataBean {
	public double getLiquidAssets();

	public void setLiquidAssets(double liquidAssets);

	//...other getters and setters
}

DataBeanImpl.java

package demo.springbean;
public class DataBeanImpl implements DataBean {
	private double liquidAssets;
	private double fundInvestment;
	private double fixedAssets;
	private double intangibleAsset; 
	private double otherAssets;
	private double currentLiabilities;
	private double longTermLiabilities;
	private double otherLiabilities;
	private double capitalStock;
	private double capitalSurplus;
	private double retainedEarnings;
	private double otherEquity;
	private double treasuryStock;
	
	public double getLiquidAssets() {
		return liquidAssets;
	}

	public void setLiquidAssets(double liquidAssets) {
		this.liquidAssets = liquidAssets;
	}

	//...other getters and setters implementation
}

Version History

Version	Date	Content

All source code listed in this book is at Github.