How to Build a Rich Excel Report with ZK Spreadsheet

Ivan Cheng, Engineer, Potix Corporation
April 8, 2008

In this article, we will demonstrate how to build a rich Excel report with ZK Spreadsheet. If you'd like to know more about ZK Spreadsheet, please refer to the following URLs:

Version

Applicable to ZK 3.0.4 and later

Applicable to ZK Spreadsheet 1.0.0 beta and later

Introduction

With ZK Spreadsheet, data from excel can be integrated into ZK framework and vice versa. For example, you can alter formats easily when using Excel sheets, such as border, font, color, size and set variable values. ZK Spreadsheet will display those format settings and variables from server side will show in the right cells. If you would like to update variable values, you only need to update the data source (ex:database). Although the layout (ex: cell position) and content are modified frequently, variable values can be calculated correctly.

Besides, you can also use ZK components to change cell contents as often as you like. Furthermore, you can access any cell of ZK Spreadsheet from the scope of ZK components.

Let's start to build an application for calculating balance sheet.

Live Demo

How to Build a Template Using Excel

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

Define Name Ranges

Naming ranges is a method which Excel provides to assign a name in a range. You can also use naming ranges in formulas. If you want to know more about naming ranges, please check this URL: Naming Ranges

The following example defines several name ranges in BalanceSheet.xls, such as Quarter, LiquidAssets, FundInvestment etc.

Assign Cells to Name Ranges

After naming the ranges, we assign values of cells to those name ranges. With naming ranges, we don't need to know the actual cell positions anymore, but only the name.

Integrate Data with Name Ranges of Template

<window>
  <listbox id="quarter" mold="select" rows="1" onSelect="quarter()">
  	<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>
  //Import BalanceSheet.xls
  <spreadsheet id="balance" url="/BalanceSheet.xls" maxrow="20" maxcolumn="20" height="600px" width="800px" />
  
 <zscript>
 	import value.Database;
 	import value.DataBean;
 	Database database = new Database();
 	Book book = balance.getBook();
 	void quarter() {
 		Listitem listitem = quarter.getSelectedItem();
 		int quarter = Integer.parseInt(listitem.value);
		//Call the method to access data from data source
 		DataBean dataBean = database.quaryData(quarter);

		setCellValue("Quarter", dataBean.getItem());			
		setCellValue("LiquidAssets", dataBean.getLiquidAssets());
		setCellValue("FundInvestment", dataBean.getFundInvestment());
		......
		
		setCellValue("TreasuryStock", dataBean.getTreasuryStock());
 	}
	//This method gets the cell position of defined name range and set value into cells
 	void setCellValue(String rangeName,Object value){
		Range range = book.lookupNameRange(rangeName);
		Sheet sheet = range.getSheet();
		//Use setCellValue(Top, Left, value) to set the value of cell
		sheet.setCellValue(range.getTop(), range.getLeft(), value);
	}
 </zscript>
</window>

Download

ZssBalanceSheet.war

Conclusion

ZK developers should have a general idea about new features of ZK Spreadsheet. From now on, ZK developers are able to access the data from ZK Spreadsheet easily. If you have any questions or suggestions, please feel free to leave comments here or post to ZK forum.