Posted on 2010-12-13 10:46
penngo 阅读(3855)
评论(3) 编辑 收藏 所属分类:
Java
因为之前有项目需要做大量报表，编写报表，花费大量时间，为了减少时间，所以研究下自定义报表实现方式。在很多报表工具，它们实现自定义报表的方式：基本都是数据集和数据显示格式这部分提取出来，实现自定义。本文也主要是数据集和数据显示格式两部分的自定义，选用技术：利用javascript定义数据集，jxl定义报表的格式。
在jdk6开始，java可以执行javascript脚本语言了，而jxl可以先定义好模板，再把数据集的内容填充模板中。本来考虑数据集的定义是做成类似sqlmap的xml配置，但是在测试时，发现灵活性欠缺。记起java可以执行javascript，如果使用javascript来定义数据集，这样就可以利用javascript的语法，数据集的定义更灵活，可配置性更高。
本文的测试代码，是用了上一篇的介绍spring mvc的例子http://www.blogjava.net/pengo/archive/2010/11/28/339229.html开发的，下面开始贴代码，以一个简单的学生成绩报表为例
测试的实体类：
学生类
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类数据
id psw name
1 111 李明
2 111 张明
课程course类数据
id name
1 数学
2 语文
3 英语
成绩score类数据
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 //通过连接参数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实现
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,取得数据集，并将数据集传给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根据数据集，生成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类实现
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配置数据集的获取
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模板
表达式内容：
学生 | <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>
下面看测试运行效果
如果有兴趣的，欢迎交流学习。
源码