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. Or he can clicks "Reset" button to reset what he inputs to default value. The screenshot below shows a request of a user "John":
Interaction between JSP and Spreadsheet
The sequence diagram displays the overall handling process of a AJAX request when a user clicks a button in leave.jsp.
The leave.jsp is the page that contains spreadsheet. The ForLeaveServlet is a servlet we implement for the application to handle AJAX requests from leave.jsp. JsonUpdateBridge is a utility class that provides access to ZK desktop and starts an execution in foreign AJAX channel. We should access components like spreadsheet in its process() method and it will generate corresponding AU response for us.
Firstly, leave.jsp sends AJAX request with required parameter such as desktop ID, spreadsheet UUID, and application related data to ForLeaveServlet. The servlet extracts necessary parameters to create JsonUpdateBridge object and call its process() handle AJAX request. Then response the JSON object which contains ZK AU response to the client. Then we use a Javascript utility object zssjsp provided by Spreadsheet to process ZK AU response and write some codes to process other data of the JSON response according to our business logic.
Using Spreadsheet Tag
Using Spreadsheet JSP tag is like using other JSP custom tag library. You have to declare a tag library with <%@taglib %> first and write Spreadsheet JSP tag with specified prefix.
leave.jsp
<%@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>
<div>
<zssjsp:spreadsheet id="myzss"
bookProvider="org.zkoss.zss.jspdemo.DemoBookProvider"
width="800px" height="500px"
maxrows="100" maxcolumns="20"
showToolbar="true" showFormulabar="true" showContextMenu="true"/>
</div>
<button id="resetBtn">Reset</button>
<button id="checkBtn">OK</button>
...
</body>
- Line 2, 8: Basic steps to use spreadsheet JSP tag.
- Line 13: A special attribute of spreadsheet JSP tag which should be specified as a book provider class name, and we will explain it later.
Book Provider
The line 13 in previous code, leave.jsp, is a special attribute which contains a book provider class name org.zkoss.zss.jspdemo.DemoBookProvider. The class implements an interface, BookProvider, which is used to load a book model programmatically in JSP or in a servlet. This provider is called when creating a Spreadsheet in ZK context. The returned book model will be set to a Spreadsheet.
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
...
return book;
}
}
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();
}
};
}