Integrate ZK Spreadsheet with Spring

ZK Spreadsheet can resolve the name expressions in cells to bind the data from the Spring-managed beans automatically.

Purpose

Data binding spreadsheet cells to Spring-managed beans.

Template Excel File with Proper Name Expressions

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 Spring-managed Beans

For variables in cells, if valid Excel Defined Names found in template file, ZK Spreadsheet will use them. If not, ZK Spreadsheet follows ZK's EL expression variable resolving mechanism. It first tries to find any matching zscript variables defined in ZUML page. Then check ids 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 original behavior. Once variables are resolved, the associated getter are called and value returned in the cell.

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.

ZUML

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="org.zkoss.zssessentials.bean.spring.DataBeanImpl"/>
</beans>

Spring-managed Bean

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

DataBean.java

package org.zkoss.zssessentials.bean.spring;
public interface DataBean {
	public double getLiquidAssets();

	public void setLiquidAssets(double liquidAssets);

	//...other getters and setters
}

DataBeanImpl.java

package org.zkoss.zssessentials.bean.spring;
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.