Using Spreadsheet JSP Tag"

From Documentation
Line 112: Line 112:
  
  
= Handle AJAX Request and Response =  
+
= Handle AJAX Request and Response =
 +
 
 +
 
 +
<source lang='java'>
 +
public class ForLeaveServlet extends HttpServlet{
 +
 
 +
@Override
 +
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
 +
throws ServletException, IOException {
 +
//set encoding
 +
req.setCharacterEncoding("UTF-8");
 +
resp.setCharacterEncoding("UTF-8");
 +
//json content type
 +
resp.setContentType("application/json");
 +
 +
//parameter from ajax request, you have to pass it in AJAX request
 +
//necessary parameter to get ZK server side desktop
 +
final String desktopId = req.getParameter("desktopId");
 +
//necessary parameter to get ZK server side spreadsheet
 +
final String zssUuid = req.getParameter("zssUuid");
 +
 +
final String action = req.getParameter("action");
 +
 +
// prepare a json result object, it can contain your ajax result and
 +
// also the necessary zk component update result
 +
final JSONObject result = new JSONObject();
 +
// set back for client to check action result, it depends on your logic.
 +
result.put("action", action);
 +
 +
// use utility class to wrap zk in servlet request and
 +
// get access and response result
 +
JsonUpdateBridge bridge = new JsonUpdateBridge(getServletContext(), req, resp,
 +
desktopId) {
 +
@Override
 +
protected void process(Desktop desktop) {
 +
Spreadsheet ss = (Spreadsheet)desktop.getComponentByUuidIfAny(zssUuid);
 +
Book book = ss.getBook();
 +
Sheet sheet = book.getSheetAt(0);
 +
 +
if("reset".equals(action)){
 +
handleReset(sheet,result);
 +
}else if("check".equals(action)){
 +
handleCheck(sheet,result);
 +
}
 +
}
 +
};
 +
 +
/*
 +
* Generate ZK update result in given JSON object. An AJAX response
 +
* handler at client side, zssjsp, will 'eval' this result to update ZK
 +
* components.
 +
*/
 +
bridge.process(result);
 +
 
 +
Writer w = resp.getWriter();
 +
w.append(result.toJSONString());
 +
}
 +
...
 +
}
 +
</source>
 +
 
 +
== Reset Cells==
 +
 
 +
<source lang='java'>
 +
public class ForLeaveServlet extends HttpServlet{
 +
...
 +
//reset cells to default value
 +
private void handleReset(Sheet sheet, JSONObject result) {
 +
final String dateFormat =  "yyyy/MM/dd";
 +
//you can use a cell reference to get a range
 +
Range from = Ranges.range(sheet,"E5");//Ranges.range(sheet,"From");
 +
//or you can use a name to get a range (the named range has to be set in Excel);
 +
from.setCellEditText(DateUtil.tomorrow(0,dateFormat));
 +
 +
//set other cells...
 +
}
 +
</source>
  
 
= Process AJAX Response =
 
= Process AJAX Response =

Revision as of 08:01, 30 July 2013


Using Spreadsheet JSP Tag





Available in ZK Spreadsheet EE only


Overview

In this section, we will demonstrate how to make DOM elements interact with Spreadsheet in a JSP by JQuery.

  • scenario
Essentials-jsp-app.png

Interaction between JSP and Spreadsheet

Essentials-jsp-interaction.png


Using Spreadsheet Tag

<%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@taglib prefix="zssjsp" uri="http://www.zkoss.org/jsp/zss"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
		<title>Application for Leave</title>
		<zssjsp:head/>
	</head>
<body>
	<button id="resetBtn">Reset</button>
	<button id="checkBtn">OK</button>
	<div>
		<zssjsp:spreadsheet id="myzss" 
			bookProvider="org.zkoss.zss.jspdemo.DemoBookProvider"
			width="800px" height="600px" 
			maxrows="100" maxcolumns="20"
			showToolbar="true" showFormulabar="true" showContextMenu="true" showSheetbar="true"/>
	</div>
	...
</body>

Book Provider

public class DemoBookProvider implements BookProvider{

	public Book loadBook(ServletContext servletContext, HttpServletRequest request, HttpServletResponse res) {
		
		Book book;
		try {
			URL bookUrl = servletContext.getResource("/WEB-INF/books/application_for_leave.xlsx");
			book = Importers.getImporter().imports(bookUrl, "app4leave");
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

		//initialize the book model
		...
	}
}


Send AJAX Request

<body>
	<button id="resetBtn">Reset</button>
	<button id="checkBtn">OK</button>
	<div>
		<zssjsp:spreadsheet id="myzss" 
			bookProvider="org.zkoss.zss.jspdemo.DemoBookProvider"
			width="800px" height="600px" 
			maxrows="100" maxcolumns="20"
			showToolbar="true" showFormulabar="true" showContextMenu="true" showSheetbar="true"/>
	</div>
	<script type="text/javascript">
	//jq is jquery name in zk, which version is 1.6.4 in sparedsheet 3.0.0 (zk 6.5.3 and later) 
	jq(document).ready(function(){
		//register client event on button by jquery api 
		jq("#checkBtn").click(function(){
			postAjax("check");
		});
		jq("#resetBtn").click(function(){
			postAjax("reset");
		});
	});
	
	function postAjax(action){
		//get the necessary zk ids form zssjsp[component_id] 
		//'myzss' is the sparedhseet id that you gaved in sparedsheet tag 
		var desktopId = zssjsp['myzss'].desktopId; 
		var zssUuid = zssjsp['myzss'].uuid;
		
		
		//use jquery api to post ajax to your servlet (in this demo, it is AjaxBookServlet),
		//provide desktop id and spreadsheet uuid to access zk component data in your servlet 
		jq.ajax({url:"app4l",//the servlet url to handle ajax request 
			data:{desktopId:desktopId,zssUuid:zssUuid,action:action},
			type:'POST',dataType:'json'}).done(handleAjaxResult);
	}
...
  • zssjsp


Handle AJAX Request and Response

public class ForLeaveServlet extends HttpServlet{

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		//set encoding
		req.setCharacterEncoding("UTF-8");
		resp.setCharacterEncoding("UTF-8");
		//json content type
		resp.setContentType("application/json");
		
		//parameter from ajax request, you have to pass it in AJAX request
		//necessary parameter to get ZK server side desktop
		final String desktopId = req.getParameter("desktopId");
		//necessary parameter to get ZK server side spreadsheet
		final String zssUuid = req.getParameter("zssUuid");
		
		final String action = req.getParameter("action");
		
		// prepare a json result object, it can contain your ajax result and
		// also the necessary zk component update result
		final JSONObject result = new JSONObject();
		// set back for client to check action result, it depends on your logic.
		result.put("action", action);
		
		// use utility class to wrap zk in servlet request and
		// get access and response result
		JsonUpdateBridge bridge = new JsonUpdateBridge(getServletContext(), req, resp,
				desktopId) {
			@Override
			protected void process(Desktop desktop) {
				Spreadsheet ss = (Spreadsheet)desktop.getComponentByUuidIfAny(zssUuid);
				Book book = ss.getBook();
				Sheet sheet = book.getSheetAt(0);
				
				if("reset".equals(action)){
					handleReset(sheet,result);
				}else if("check".equals(action)){
					handleCheck(sheet,result);
				}
			}
		};
		
		/*
		 * Generate ZK update result in given JSON object. An AJAX response
		 * handler at client side, zssjsp, will 'eval' this result to update ZK
		 * components.
		 */
		bridge.process(result);

		Writer w = resp.getWriter();
		w.append(result.toJSONString());	
	}
...
}

Reset Cells

public class ForLeaveServlet extends HttpServlet{
...
	//reset cells to default value
	private void handleReset(Sheet sheet, JSONObject result) {
		final String dateFormat =  "yyyy/MM/dd";
		//you can use a cell reference to get a range
		Range from = Ranges.range(sheet,"E5");//Ranges.range(sheet,"From");
		//or you can use a name to get a range (the named range has to be set in Excel);
		from.setCellEditText(DateUtil.tomorrow(0,dateFormat));
		
		//set other cells...
	}

Process AJAX Response