Posted on 2010-12-13 10:46
penngo 閱讀(3864)
評論(3) 編輯 收藏 所屬分類:
Java
因為之前有項目需要做大量報表，編寫報表，花費大量時間，為了減少時間，所以研究下自定義報表實現(xiàn)方式。在很多報表工具，它們實現(xiàn)自定義報表的方式：基本都是數(shù)據(jù)集和數(shù)據(jù)顯示格式這部分提取出來，實現(xiàn)自定義。本文也主要是數(shù)據(jù)集和數(shù)據(jù)顯示格式兩部分的自定義，選用技術：利用javascript定義數(shù)據(jù)集，jxl定義報表的格式。
在jdk6開始，java可以執(zhí)行javascript腳本語言了，而jxl可以先定義好模板，再把數(shù)據(jù)集的內(nèi)容填充模板中。本來考慮數(shù)據(jù)集的定義是做成類似sqlmap的xml配置，但是在測試時，發(fā)現(xiàn)靈活性欠缺。記起java可以執(zhí)行javascript，如果使用javascript來定義數(shù)據(jù)集，這樣就可以利用javascript的語法，數(shù)據(jù)集的定義更靈活，可配置性更高。
本文的測試代碼，是用了上一篇的介紹spring mvc的例子http://m.tkk7.com/pengo/archive/2010/11/28/339229.html開發(fā)的，下面開始貼代碼，以一個簡單的學生成績報表為例
測試的實體類：
學生類
1
@Entity
2@Table(name = "student")
3
public class Student implements Serializable 
{
4
private static final long serialVersionUID = 1L;
5 @Id
6 @Basic(optional = false)
7 @GeneratedValue(strategy = GenerationType.IDENTITY)
8 @Column(name = "id", nullable = false)
9 private Integer id;
10 @Column(name = "name")
11 private String user;
12 @Column(name = "psw")
13 private String psw;
14
public Integer getId() {
15 return id;
16
}
17 public void setId(Integer id) {
18 this.id = id;
19 }
20
21 public String getUser() {
22 return user;
23 }
24 public void setUser(String user) {
25 this.user = user;
26 }
27 public String getPsw() {
28 return psw;
29 }
30 public void setPsw(String psw) {
31 this.psw = psw;
32 }
33
}
34
課程類
1@Entity
2@Table(name = "course")
3public class Course {
4 @Id
5 @Basic(optional = false)
6 @GeneratedValue(strategy = GenerationType.IDENTITY)
7 @Column(name = "id", nullable = false)
8 private Integer id;
9 @Column(name = "name")
10 private String name;
11 public Integer getId() {
12 return id;
13 }
14 public void setId(Integer id) {
15 this.id = id;
16 }
17 public String getName() {
18 return name;
19 }
20 public void setName(String name) {
21 this.name = name;
22 }
23}
成績類
1@Entity
2@Table(name = "score")
3public class Score {
4 private static final long serialVersionUID = 1L;
5 @Id
6 @Basic(optional = false)
7 @GeneratedValue(strategy = GenerationType.IDENTITY)
8 @Column(name = "id", nullable = false)
9 private Integer id;
10 @Column(name = "studentId")
11 private Integer studentId;
12 @Column(name = "courseId")
13 private Integer courseId;
14 @Column(name = "result")
15 private Double result;
16 public Integer getId() {
17 return id;
18 }
19 public void setId(Integer id) {
20 this.id = id;
21 }
22 public Integer getStudentId() {
23 return studentId;
24 }
25 public void setStudentId(Integer studentId) {
26 this.studentId = studentId;
27 }
28 public Integer getCourseId() {
29 return courseId;
30 }
31 public void setCourseId(Integer courseId) {
32 this.courseId = courseId;
33 }
34 public Double getResult() {
35 return result;
36 }
37 public void setResult(Double result) {
38 this.result = result;
39 }
40}
學生student類數(shù)據(jù)
id psw name
1 111 李明
2 111 張明
課程course類數(shù)據(jù)
id name
1 數(shù)學
2 語文
3 英語
成績score類數(shù)據(jù)
id courseId result studentId
1 1 70 1
2 2 71 1
3 3 73 1
4 1 80 2
5 2 81 2
6 3 88 2
Viw只做了一個簡單的jsp，頁面只放了一個按鈕，點擊按鈕時，生成一個excel文件，并返回給客戶。
1report.jsp
2<%@ page language="java" contentType="text/html; charset=UTF-8"
3 pageEncoding="UTF-8"%>
4<%@ include file="/include/head.jsp"%>
5<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
6<html>
7<head>
8<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
9<title>Insert title here</title>
10<script language="javascript" src="<%=request.getContextPath()%>/script/jquery.min.js"></script>
11<script language="javascript">
12function exportReport(){
13 //通過連接參數(shù)js=studentScore，指定要生成報表的js配置文件
14 window.location.href = "<%=request.getContextPath()%>/report.do?method=export&js=studentScore";
15}
16</script>
17</head>
18<body>
19report eeeee<br/>
20<c:out value="${name}"/>
21<input type="button" onclick="exportReport()" value="生成報表" />
22</body>
23</html>
Controller實現(xiàn)
1@Controller
2@RequestMapping("/report.do")
3public class ReportController {
4 protected final transient Log log = LogFactory
5 .getLog(ReportController.class);
6 @Autowired
7 private ReportService reportService;
8
9 @RequestMapping
10 public String load(ModelMap modelMap) {
11 return "report";
12 }
13
14 @RequestMapping(params = "method=export")
15 public void export(HttpServletRequest request,
16 HttpServletResponse response, ModelMap modelMap) throws Exception {
17 Map beans = new HashMap();
18 String jsFile = request.getParameter("js");
19 String path = request.getSession().getServletContext().getRealPath("")
20 + "/WEB-INF";
21 ScriptEngineManager factory = new ScriptEngineManager();
22 ScriptEngine engine = factory.getEngineByName("JavaScript");
23 //加載js腳本
24 InputStreamReader in = new InputStreamReader(new FileInputStream(path
25 + "/config/" + jsFile + ".js"));
26 engine.eval(in);
27 Invocable inv = (Invocable) engine;
28 String excel = engine.get("excel").toString();
29 //獲取js文件中配置的sql,取得數(shù)據(jù)集，并將數(shù)據(jù)集傳給jxl
30 Object reObj = inv.invokeFunction("init");
31 NativeArray myArray = (NativeArray) reObj;
32 Object[] array = new Object[(int) myArray.getLength()];
33 for (Object o : myArray.getIds()) {
34 int index = (Integer) o;
35 array[index] = myArray.get(index, null);
36 NativeObject aObj = (NativeObject) array[index];
37 String name = aObj.get("name", null).toString();
38 String method = aObj.get("method", null).toString();
39 String hql = inv.invokeFunction(method).toString();
40 List list = reportService.getList(hql);
41 beans.put(name, list);
42 }
43 Connection conn = reportService.getConnection();
44 String templateFileName = path + excel;
45 ReportManager rm = new ReportManagerImpl(conn, beans);
46 beans.put("rm", rm);
47 InputStream is = new BufferedInputStream(new FileInputStream(
48 templateFileName));
49 //jxl根據(jù)數(shù)據(jù)集，生成excel報表
50 XLSTransformer transformer = new XLSTransformer();
51 Workbook resultWorkbook = transformer.transformXLS(is, beans);
52 response.setHeader("Content-Transfer-Encoding", "base64");
53 response.setContentType("application/octet-stream");
54 String contentDisposition = "attachment;filename=\"score.xls\"";
55 response.setHeader("Content-Disposition", contentDisposition);
56 java.io.OutputStream outputStream = response.getOutputStream();
57 resultWorkbook.write(outputStream);
58 outputStream.flush();
59 outputStream.close();
60 outputStream = null;
61 resultWorkbook = null;
62 }
63}
Service類實現(xiàn)
1@Service
2public class ReportService {
3 protected final transient Log log = LogFactory
4 .getLog(ReportService.class);
5 @Autowired
6 private EntityDao entityDao;
7
8 @Transactional
9 public List getList(String hql){
10 StringBuffer sff = new StringBuffer();
11 sff.append(hql);
12 return entityDao.createQuery(sff.toString());
13 }
14
15 public Connection getConnection(){
16 return entityDao.getConnection();
17 }
18}
studentScore.js配置數(shù)據(jù)集的獲取
1function HqlObject(name, method)
2{
3 this.name = name;
4 this.method = method;
5 this.state = 0;
6}
7var excel = "/temple/studentscore.xls";
8function init(){
9 var hql1 = new HqlObject("students", "getStudents");
10 var hql2 = new HqlObject("courses", "getCourses");
11 var myArray = new Array(2);
12 myArray[0] = hql1;
13 myArray[1] = hql2;
14 return myArray;
15}
16
17function getStudents(parame){
18 var sql = "select a from Student a where a.id = 1";
19 return sql;
20}
21
22function getCourses(parame){
23 var sql = "select a from Course a";
24 return sql;
25}
jxl的excel模板
表達式內(nèi)容：
學生 | <jx:forEach items="${courses}" var="c"> | ${c.name} | </jx:forEach>
<jx:forEach items="${students}" var="st">
${st.user} | <jx:forEach items="${rm.exec('select * from score where studentId=' + st.id + ' order by courseId
asc ')}" var="sc"> | ${sc.result} | </jx:forEach>
</jx:forEach>
下面看測試運行效果
如果有興趣的，歡迎交流學習。
源碼