因为之前有项目需要做大量报表，编写报表，花费大量时间，为了减少时间，所以研究下自定义报表实现方式。在很多报表工具，它们实现自定义报表的方式：基本都是数据集和数据显示格式这部分提取出来，实现自定义。本文也主要是数据集和数据显示格式两部分的自定义，选用技术：利用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 71 1
3 3 73 1
4 1 80 2
5 2 81 2
6 3 88 2
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()%><!--
/script/jquery.min.js">
// --></mce:script>
<mce:script language="javascript"><!--
function exportReport(){
//通过连接参数js=studentScore，指定要生成报表的js配置文件
window.location.href = "<%=request.getContextPath()%>/report.do?method=export&js=studentScore";
}
// --></mce:script>
</head>
<body>
report eeeee<br/>
<c:out value="${name}"/>
<input type="button" οnclick="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配置数据集的获取
function HqlObject(name, method)
{
this.name = name;
this.method = method;
this.state = 0;
}
var excel = "/temple/studentscore.xls";
function init(){
var hql1 = new HqlObject("students", "getStudents");
var hql2 = new HqlObject("courses", "getCourses");
var myArray = new Array(2);
myArray[0] = hql1;
myArray[1] = hql2;
return myArray;
}
function getStudents(parame){
var sql = "select a from Student a where a.id = 1";
return sql;
}
function getCourses(parame){
var sql = "select a from Course a";
return sql;
}
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>
下面看测试效果
如果有兴趣的，欢迎交流学习。
源码