利用javascript和jxl实现自定义报表的输出

xidajiancun

浏览: 454598 次

最近访客更多访客>>

tzk117

sharphero

云飘雾散

makemyownlife

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1060)

社区版块

存档分类

因为之前有项目需要做大量报表，编写报表，花费大量时间，为了减少时间，所以研究下自定义报表实现方式。在很多报表工具，它们实现自定义报表的方式：基本都是数据集和数据显示格式这部分提取出来，实现自定义。本文也主要是数据集和数据显示格式两部分的自定义，选用技术：利用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开发的，下面开始贴代码，以一个简单的学生成绩报表为例

测试的实体类：
学生类

@Entity @Table(name = "student") public class Student implements Serializable { private static final long serialVersionUID = 1L; @Id @Basic(optional = false) @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", nullable = false) private Integer id; @Column(name = "name") private String user; @Column(name = "psw") private String psw; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUser() { return user; } public void setUser(String user) { this.user = user; } public String getPsw() { return psw; } public void setPsw(String psw) { this.psw = psw; } }

课程类

@Entity @Table(name = "course") public class Course { @Id @Basic(optional = false) @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", nullable = false) private Integer id; @Column(name = "name") private String name; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }

成绩类

@Entity @Table(name = "score") public class Score { private static final long serialVersionUID = 1L; @Id @Basic(optional = false) @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", nullable = false) private Integer id; @Column(name = "studentId") private Integer studentId; @Column(name = "courseId") private Integer courseId; @Column(name = "result") private Double result; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public Integer getStudentId() { return studentId; } public void setStudentId(Integer studentId) { this.studentId = studentId; } public Integer getCourseId() { return courseId; } public void setCourseId(Integer courseId) { this.courseId = courseId; } public Double getResult() { return result; } public void setResult(Double result) { this.result = result; } }

学生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 711
3 3 73 1
4 1 80 2
5 2 81 2
6 3 882

Viw只做了一个简单的jsp，页面只放了一个按钮，点击按钮时，生成一个excel文件，并返回给客户。
report.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ include file="/include/head.jsp"%> <!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>Insert title here</title> <mce:script language="javascript" src="<%=request.getContextPath()%></mce:script> <mce:script language="javascript"></mce:script> </head> <body> report eeeee<br/> <c:out value="${name}"/> <input type="button" onclick="exportReport()" value="生成报表" /> </body> </html>

Controller实现

@Controller @RequestMapping("/report.do") public class ReportController { protected final transient Log log = LogFactory .getLog(ReportController.class); @Autowired private ReportService reportService; @RequestMapping public String load(ModelMap modelMap) { return "report"; } @RequestMapping(params = "method=export") public void export(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws Exception { Map beans = new HashMap(); String jsFile = request.getParameter("js"); String path = request.getSession().getServletContext().getRealPath("") + "/WEB-INF"; ScriptEngineManager factory = new ScriptEngineManager(); ScriptEngine engine = factory.getEngineByName("JavaScript"); //加载js脚本 InputStreamReader in = new InputStreamReader(new FileInputStream(path + "/config/" + jsFile + ".js")); engine.eval(in); Invocable inv = (Invocable) engine; String excel = engine.get("excel").toString(); //获取js文件中配置的sql,取得数据集，并将数据集传给jxl Object reObj = inv.invokeFunction("init"); NativeArray myArray = (NativeArray) reObj; Object[] array = new Object[(int) myArray.getLength()]; for (Object o : myArray.getIds()) { int index = (Integer) o; array[index] = myArray.get(index, null); NativeObject aObj = (NativeObject) array[index]; String name = aObj.get("name", null).toString(); String method = aObj.get("method", null).toString(); String hql = inv.invokeFunction(method).toString(); List list = reportService.getList(hql); beans.put(name, list); } Connection conn = reportService.getConnection(); String templateFileName = path + excel; ReportManager rm = new ReportManagerImpl(conn, beans); beans.put("rm", rm); InputStream is = new BufferedInputStream(new FileInputStream( templateFileName)); //jxl根据数据集，生成excel报表 XLSTransformer transformer = new XLSTransformer(); Workbook resultWorkbook = transformer.transformXLS(is, beans); response.setHeader("Content-Transfer-Encoding", "base64"); response.setContentType("application/octet-stream"); String contentDisposition = "attachment;filename=/"score.xls/""; response.setHeader("Content-Disposition", contentDisposition); java.io.OutputStream outputStream = response.getOutputStream(); resultWorkbook.write(outputStream); outputStream.flush(); outputStream.close(); outputStream = null; resultWorkbook = null; } }

Service类实现

@Service public class ReportService { protected final transient Log log = LogFactory .getLog(ReportService.class); @Autowired private EntityDao entityDao; @Transactional public List getList(String hql){ StringBuffer sff = new StringBuffer(); sff.append(hql); return entityDao.createQuery(sff.toString()); } public Connection getConnection(){ return entityDao.getConnection(); } }

studentScore.js配置数据集的获取

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>

下面看测试效果