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:
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>
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
.
Hi
I have the necessity to modify in the web interface and in a permanent way the file excel located on the server. Can I do it with this software. It seems to me that all the modifications that I do on the web client GUI are not saved in the excel file located in the server.
Thanks
Benny
2008-11-25
You could export Book model to a excel file by the ExcelExporter
To get spring beans in ZK spreadsheet,we have to do is to modify configuration files and then, layout the Spreadsheet at proper place. In the near future, we will introduce more useful tools of ZK Spreadsheet. But still i expect your response to make it better.Thanks!!.
laoyi
2009-09-16
where to get the source of zss zk-zss-20090402?
yugs
2009-09-29
HI
how can i get the source code i down load it , but it was in *war. please send the source code to my email thank you very very much!!
yugs
2009-09-29
HI
My email is yugs234@gmail.com
BGM
2010-02-01
The demo applicaiton raise "org.zkoss.zk.ui.metainfo.DefinitionNotFoundException: Component definition not found: spreadsheet in [LanguageDefinition: xul/html], [SYS file:..." exception after I upgrade to ZK5.0. I wonder if anyone had simiar issue and found any solution. Much approciated. LS
