Using Spreadsheet JSP Tag"
From Documentation
Line 188: | Line 188: | ||
//set other cells... | //set other cells... | ||
} | } | ||
+ | </source> | ||
+ | |||
+ | |||
+ | == Check Cells == | ||
+ | |||
+ | |||
+ | <source lang='java'> | ||
+ | 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 | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | ... | ||
+ | } | ||
</source> | </source> | ||
= Process AJAX Response = | = Process AJAX Response = |
Revision as of 08:11, 30 July 2013
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...
}
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
}
}
}
}
...
}