Using Spreadsheet JSP Tag
From Documentation
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
Interaction between JSP and Spreadsheet
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...
}