Latest

Use JavaBean in Excel with ZK Spreadsheet

Ivan Cheng, Engineer, Potix Corporation
April 18, 2008

In this article, we will demonstrate how ZK Spreadsheet use external variables directly via Variable-Resolver.

If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:

If you'd like to know more about Variable-Resolver, please refer to the following URL:

Using ZK JndiVariableResolver - Retrieve Session Beans and EntityManagerFactory

Version

Applicable to ZK 3.0.4 and later

Applicable to ZK Spreadsheet 1.0.0 and later

Introduction

In How to Build a Rich Excel Report with ZK Spreadsheet, we demonstrate how to use name ranges to integrate data from Excel into ZK framework and vice versa. In this article, we will solve it with a more powerful feature - Variable-Resolver. Instead of saving values with defining name ranges and refreshing values of cells with method setCellValue, you only need to assign bean name in cells of Excel to get the data from the server side! In other words, values in cells will be refreshed dynamically by reloading data of bean!

Let's start to build an application for calculating balance sheet with Variable-Resolver.

Live Demo

How to Build a Template Using Excel

Define the Layout Template in Excel
You can design the format of Excel data:

Input External Variable in Spreadsheet

Input bean and its data names in BalanceSheet.xls, such as dataBean.item, dataBean.liquidAssets, dataBean.fundInvestment etc.

Get Values via Variable-Resolver

sheet.zul

<window>
Quarter:
  <listbox id="quarter" mold="select" rows="1" onSelect="refreshQuarter()">
  	<listitem value="0" label="Select"/>
  	<listitem value="1" label="Quarter 1"/>
  	<listitem value="2" label="Quarter 2"/>
  	<listitem value="3" label="Quarter 3"/>
  	<listitem value="4" label="Quarter 4"/>
  </listbox>
<zscript>
	import model.Database;
	import model.DataBean;
	void refreshQuarter() {
		Listitem listitem = quarter.getSelectedItem();
		int quarter = Integer.parseInt(listitem.value);
		//Resolve variable data bean via ZssVariableResolver
		reloadQuarter(quarter, dataBean);
		//Call the method to refresh values of cells
		balance.book.notifyChange(new String[]{"dataBean"});
	}
	
	void reloadQuarter(int quarter, dataBean){
		//Call the method to reload data
		new Database().reloadQuarter(quarter, dataBean);
	}
	//Declare a bean in zscript, ZK will find it via default Variable-Resolver.
	DataBean dataBean = new DataBean();
	//initial data
	dataBean = new Database().reloadQuarter(0, dataBean);
	
</zscript>
	//Import BalanceSheet.xls
	<spreadsheet id="balance" url="/BalanceSheet.xls" maxrow="40" maxcolumn="20" height="600px" width="1300px"/>
 </window>

Download

ZssVarRes.war

Conclusion

In recent release of ZK Spreadsheet, developers can even bind Java Bean into a spreadsheet application. This technical breakthrough will bring a new age to web spreadsheet applications. From now on, ZK developers are able to access the JavaBean from ZK Spreadsheet directly.If you have any questions or suggestions, please feel free to leave comments here or post to ZK forum .

Comments

2009-04-23

I am trying the code with zk version 3.5 and spring web-flow but the zscript tag inside the zul file is not detecting bean variables or any standard zk elements like self or even the objects in the same zul file like listbox (I want to get the selected value from listbox like above spreadsheet code)
It shows error Inline execution of .... (all code in zscript tag)
when traced it shows error particularly at the variables which are not getting recognized like bean variables or even the listbox in the same zul file is also not getting identified.

2009-08-05

Thanks,

2009-08-05

Thanks,Click here

ilke

2009-12-21

i try to deploy ZssVarRes.war into apache tomcat and start the program but it gives me the error

type Status report

message /ZssVarRes/

description The requested resource (/ZssVarRes/) is not available.

other examples like zssapp.war works correctly but this does not work
pls help