Using Spreadsheet JSP Tag"

From Documentation
Line 1: Line 1:
 
{{ZKSpreadsheetEssentials3PageHeader}}
 
{{ZKSpreadsheetEssentials3PageHeader}}
 +
<!--
 +
Assumption on Target Readers:
 +
    Not familiar with ZK, know JSP and JQuery.
 +
Purpose:
 +
    introduce how to use AJAX to interact with Spreadsheet
 +
Content:
 +
    Because we assume that readers are not familiar with ZK, we don't demonstrate using a composer.
 +
-->
  
  
 +
__TOC__
  
 +
{{ZSS EE}}
  
{{ZSS EE}}
 
  
  
 
= Overview =  
 
= Overview =  
In this section, we will demonstrate how to make DOM elements interact with Spreadsheet in a JSP by JQuery.
+
In this section, we will demonstrate how to make DOM elements interact with Spreadsheet in a JSP by AJAX request. We suppose you know some basics about [http://jquery.com JQuery].  
* scenario
+
 
 +
The example application is a simple page to request for leave. A user fills the required field in cells and click "OK" button to submit his request for leave. The screenshot below shows a request of a user "John":
  
 
[[File:essentials-jsp-app.png | center]]
 
[[File:essentials-jsp-app.png | center]]
 +
  
 
== Interaction between JSP and Spreadsheet ==
 
== Interaction between JSP and Spreadsheet ==

Revision as of 08:25, 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 AJAX request. We suppose you know some basics about JQuery.

The example application is a simple page to request for leave. A user fills the required field in cells and click "OK" button to submit his request for leave. The screenshot below shows a request of a user "John":

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...
	}


Check Cells

public class ForLeaveServlet extends HttpServlet{
...
	//validate cell data of user input and return a JSONObject
	private void handleCheck(Sheet sheet, JSONObject result) {
		Date from = Ranges.rangeByName(sheet,"From").getCellData().getDateValue();
		Date to = Ranges.rangeByName(sheet,"To").getCellData().getDateValue();
		String reason = Ranges.rangeByName(sheet,"Reason").getCellData().getStringValue();
		Double total = Ranges.rangeByName(sheet,"Total").getCellData().getDoubleValue();
		String applicant = Ranges.rangeByName(sheet,"Applicant").getCellData().getStringValue();
		Date requestDate = Ranges.rangeByName(sheet,"RequestDate").getCellData().getDateValue();
		
		if(from == null){
			result.put("message", "FROM is empty");
		}else if(to == null){
			result.put("message", "TO is empty");
		}else if(total==null || total.intValue()<0){
			result.put("message", "TOTAL small than 1");
		}else if(reason == null){
			result.put("message", "REASON is empty");
		}else if(applicant == null){
			result.put("message", "APPLICANT is empty");
		}else if(requestDate == null){
			result.put("message", "REQUEST DATE is empty");
		}else{
			//Option 1:
			//You can handle your business logic here and return a final result for user directly
			
			
			
			//Or option 2: return necessary form data, 
			//so client can process it by submitting that can be handled by Spring MVC or Struts
			result.put("valid", true);
			JSONObject form = new JSONObject();
			result.put("form", form);

			form.put("from", from.getTime());//can't pass as data, use long for time
			form.put("to", to.getTime());//can't pass as data, use long for time
			form.put("reason", reason);
			form.put("total", total.intValue());//we just need int
			form.put("applicant", applicant);
			form.put("requestDate", requestDate.getTime());
			
			//You can also store the book, and load it back later by export it to a file
			Exporter exporter = Exporters.getExporter();
			FileOutputStream fos = null;
			try {
				File temp = File.createTempFile("app4leave_", ".xlsx");
				fos = new FileOutputStream(temp); 
				exporter.export(sheet.getBook(), fos);
				System.out.println("file save at "+temp.getAbsolutePath());
				form.put("archive", temp.getName());
			} catch (IOException e) {
				e.printStackTrace();
			} finally{
				if(fos!=null)
					try {
						fos.close();
					} catch (IOException e) {
						//handle the exception
					}
			}
		}
	}
...
}

Process AJAX Response

leave.jsp

	//the method to handle ajax result from your servlet 
	function handleAjaxResult(result){
		//process the json result that contains zk client update information 
		zssjsp.processJson(result);
		
		//use your way to hanlde you ajax message or error 
		if(result.message){
			alert(result.message);
		};
		
		//use your way handle your ajax action result 
		if(result.action == "check" && result.valid){
			if(result.form){
				//create a form dynamically to submit the form data 
				var field,form = jq("<form action='submitted.jsp' method='post'/>").appendTo('body');
				for(var nm in result.form){
					field = jq("<input type='hidden' name='"+nm+"' />").appendTo(form);
					field.val(result.form[nm]);
				}
				form.submit();
			}
		};
	}