Using Spreadsheet JSP Tag

From Documentation


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


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();
			}
		};
	}