Using ZK Spreadsheet JSP Tag

From Documentation


Using ZK Spreadsheet JSP Tag



Available in ZK Spreadsheet EE only

ZK Spreadsheet JSP tag can be used in JSP applications much the same way as any other custom JSP tag.First specify ZK Spreadsheet JSP taglib declaration as shown below

<%@ taglib prefix="zss" uri="http://www.zkoss.org/jsp/zss"%>

and use ZK Spreadsheet JSP tag as shown below

<zss:spreadsheet id="myzss" src="/WEB-INF/Excel/ZSS-demo_sample.xlsx"/>

ZK spreadsheet JSP tag supports all the properties that are supported by ZK Spreasdsheet ZUL component tag. Below is another example of using some more spreadsheet component tag properties

<zss:spreadsheet id="myzss" src="/WEB-INF/Excel/ZSS-demo_sample.xlsx"
	width="100%" height="500px" maxrows="200" maxcolumns="40"
	apply="org.zkoss.zssjsp.ZSSComposer" />

JSP

Here is the HTML fragment of index.jsp file.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>ZSS JSP Tag Sample</title>
</head>
<body style="height: 100%">
...
<%@ taglib prefix="zss" uri="http://www.zkoss.org/jsp/zss"%>
<table>
	<tr>
		<td>Cell:</td>
		<td><input type="text" id="cellRefTxt"></input></td>
		<td><input type="button" id="showBtn" onclick="showCellTextBtnClick();"
			value="Show"></input></td>
		<td>Value:</td>
		<td><input type="text" id="cellTextTxt"></input></td>
		<td><input type="button" id="setCellTextBtn" onclick="setCellTextBtnClick();"
			value="Set"></input></td>
		<td><input type="button" onclick="export();" value="Export"></input>
		</td>
	</tr>
</table>
<div width="100%" style="height: 100%;"><zss:spreadsheet
	id="myzss" src="/WEB-INF/Excel/ZSS-demo_sample.xlsx" width="100%"
	height="800px" maxrows="200" maxcolumns="40"
	apply="org.zkoss.zssjsp.ZSSComposer" /></div>
</body>
</html>

Example above has two input textbox HTML components to enter cell reference and cell text values, three input button HTML components Show, Set & Export followed by ZK Spreadsheet rendered as a JSP tag.

Retrieving ZK Spreadsheet data

In this example I demostrate two use cases. First use case is to get value of a specific ZK spreadsheet cell as indicated by cell reference input textbox and display it in the cell text input textbox when Show input button is clicked. Here is the piece of code from index.jsp that makes it work.

Javascript

	function showCellValueBtnClick() {
		zAu.send(new zk.Event(zk.Widget.$(jq('$myzss')[0]), 'onShowCellText',
				[ jq('#cellRefTxt').val() ]));
	}

and HTML

        ...
		<td>Cell:</td>
		<td><input type="text" id="cellRefTxt"></input></td>
		<td><input type="button" id="showBtn" onclick="showCellTextBtnClick();"
			value="Show"></input></td>
		<td>Value:</td>
		<td><input type="text" id="cellValueTxt"></input></td>
      ...

As you can see when user clicks on showBtn input button a Javscript function showCellTextBtnClick is invoked. Here I retrieve cellRefTxt value using ZK jq API and send a custom onShowCellText event to the server also passing cellRefTxt value along with it. On the server side I have registered onShowCellText event with the ZK Spreadsheet component using ZK's MVC way as shown below.

	... 
      <div width="100%" style="height: 100%;"><zss:spreadsheet
	id="myzss" src="/WEB-INF/Excel/ZSS-demo_sample.xlsx" width="100%"
	height="800px" maxrows="200" maxcolumns="40"
	apply="org.zkoss.zssjsp.ZSSComposer" /></div>
	...

First we apply a controller to ZK Spreadsheet and declare and define event handler in the controller. Here is the event handler code for the registered onShowCellText event.

	public void onShowCellText$myzss(ForwardEvent evt) {
		Object[] data = (Object[]) evt.getOrigin().getData();
		String cellRef = (String) data[0];
		Range range = Ranges.range(myzss.getSelectedSheet(), cellRef);
		FormatText ft = range.getFormatText();
		String eval = null;
		if (ft != null && ft.isCellFormatResult()) {
			eval = "jq('#cellTextTxt').val(\"" + ft.getCellFormatResult().text + "\");";
		} else {
			final RichTextString rstr = range == null ? null : range
					.getRichEditText();
			eval = "jq('#cellTextTxt').val(\"" + (rstr != null ? rstr.getString() : "") + "\");";
		}
		Clients.evalJavaScript(eval);
	}

So here I brief a little bit the process sequence of this use case

  1. End user enters cell reference value in cellRefTxt input textbox.
  2. End user clicks Show input button which invokes showCellTextBtnClick() javascript function
  3. In showCellTextBtnClick() function we retrieve cellRefTxt value using ZK's jq() API and send a custom onShowCellText event using ZK's zAu send() API
  4. ZK framework receives onShowCellText event on the server side and and invokes onShowCellText event handler in ZSSComposer controller class. ZK framework also passes the event data i.e. value of cellRefTxt input textbox as entered by the user
  5. In the onShowCellText event handler I use Ranges.range() API to retrieve ZK Spreadsheet cell text. Here the cell is identified by the cellRefTxt value for eg. B10
  6. Once the cell text is retrieved I use Clients.evalJavaScript(java.lang.String) API to evaluate simple javascript fragment that updates cellTextTxt input textbox with it.

Setting ZK Spreadsheet data and properties

Second use case is to set value entered in cellTextTxt inputTextbox into a specific ZK spreadsheet cell as indicated by cellRefTxt input textbox. Here is the piece of code from index.jsp that makes it work. First javascript function setCellTextBtnClick()

	... 
	function setCellTextBtnClick() {
		zk.log('Setting cell value');
		zAu.send(new zk.Event(zk.Widget.$(jq('$myzss')[0]), 'onSetCellText',
				[ jq('#cellRefTxt').val(), jq('#cellTextTxt').val() ]));
	}
	...

and then HTML fragment of setCellTextBtn

	... 
		<td>Cell:</td>
		<td><input type="text" id="cellRefTxt"></input></td>
		<td><input type="button" id="showBtn" onclick="showCellTextBtnClick();"
			value="Show"></input></td>
		<td>Value:</td>
		<td><input type="text" id="cellTextTxt"></input></td>
		<td><input type="button" id="setCellTextBtn" onclick="setCellTextBtnClick();"
			value="Set"></input></td>
	...

Clicking input button setCellTextBtn invokes setCellTextBtnClick javascript function. In setCellTextBtnClick() I retrieve cellRefTxt and cellTextTxt values using ZK jq() API and send a server side custom onSetCellText event to the server also passing cellRefTxt & cellTextTxt values along with it. On the server side I have registered onSetCellText event with the ZK Spreadsheet component using ZK's MVC way as shown below

	... 
      <div width="100%" style="height: 100%;"><zss:spreadsheet
	id="myzss" src="/WEB-INF/Excel/ZSS-demo_sample.xlsx" width="100%"
	height="800px" maxrows="200" maxcolumns="40"
	apply="org.zkoss.zssjsp.ZSSComposer" /></div>
	...

... and here is the controller code for the registered onSetCellText event.

	... 
		public void onSetCellText$myzss(ForwardEvent evt) {
		Object[] data = (Object[]) evt.getOrigin().getData();
		String cellRef = (String) data[0];
		String cellText = (String) data[1];
		final Range range = Ranges.range(myzss.getSelectedSheet(), cellRef);
		range.setEditText(cellText);
	}
	...

So here I brief a little bit the process sequence of this use case

  1. End user enters cell reference value and cell text value in respective cellRefTxt and cellTextTxt input textboxes.
  2. End user clicks setCellTextBtn input button which invokes setCellTextBtnClick() javascript function
  3. In setCellTextBtnClick() function we retrieve cellRefTxt & cellTextTxt input textboxes values using ZK's jq() API and send a custom onSetCellText event using ZK's zAu.send() API
  4. ZK framework receives onSetCellText event on the server side and and invokes onSetCellText event handler in ZSSComposer controller class. ZK framework also passes the event data i.e. value of cellRefTxt & cellTextTxt input textboxes as entered by the user.
  5. In the onSetCellText event handler I use Ranges.range() API & Range#setEditText(java.lang.String) API to set ZK Spreadsheet cell text. Here the cell is identified by the cellRefTxt value for eg. B10
  6. ZK Spreadsheet will set Cell text and update the client side to reflect this change on the UI.


In similar way ZK Spreadsheet APIs to access/modify its data and visual properties.

View complete source of JSP zssdemo.jsp

View complete source of composer ZSSComposer.java

Version History

Last Update : 2011/02/25


Version Date Content
2.0.1 Jan. 2011 add new feature


All source code listed in this book is at Github.

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.