因为之前有项目需要做大量报表,编写报表,花费大量时间,为了减少时间,所以研究下自定义报表实现方式。在很多报表工具,它们实现自定义报表的方式:基本都是数据集和数据显示格式这部分提取出来,实现自定义。本文也主要是数据集和数据显示格式两部分的自定义,选用技术:利用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
@Entity2@Table(name="student")3
publicclassStudentimplementsSerializable{4privatestaticfinallongserialVersionUID=1L;5 @Id6 @Basic(optional=false)7 @GeneratedValue(strategy=GenerationType.IDENTITY)8 @Column(name="id", nullable=false)9privateInteger id;10 @Column(name="name")11privateString user;12 @Column(name="psw")13privateString psw;14
publicInteger getId(){15returnid;16 }17
publicvoidsetId(Integer id){18this.id=id;19 }2021
publicString getUser(){22returnuser;23 }24
publicvoidsetUser(String user){25this.user=user;26 }27
publicString getPsw(){28returnpsw;29 }30
publicvoidsetPsw(String psw){31this.psw=psw;32 }33}34
课程类
1
@Entity2@Table(name="course")3
publicclassCourse{4 @Id5 @Basic(optional=false)6 @GeneratedValue(strategy=GenerationType.IDENTITY)7 @Column(name="id", nullable=false)8privateInteger id;9 @Column(name="name")10privateString name;11
publicInteger getId(){12returnid;13 }14
publicvoidsetId(Integer id){15this.id=id;16 }17
publicString getName(){18returnname;19 }20
publicvoidsetName(String name){21this.name=name;22 }23}
成绩类
1
@Entity2@Table(name="score")3
publicclassScore{4privatestaticfinallongserialVersionUID=1L;5 @Id6 @Basic(optional=false)7 @GeneratedValue(strategy=GenerationType.IDENTITY)8 @Column(name="id", nullable=false)9privateInteger id;10 @Column(name="studentId")11privateInteger studentId;12 @Column(name="courseId")13privateInteger courseId;14 @Column(name="result")15privateDouble result;16
publicInteger getId(){17returnid;18 }19
publicvoidsetId(Integer id){20this.id=id;21 }22
publicInteger getStudentId(){23returnstudentId;24 }25
publicvoidsetStudentId(Integer studentId){26this.studentId=studentId;27 }28
publicInteger getCourseId(){29returncourseId;30 }31
publicvoidsetCourseId(Integer courseId){32this.courseId=courseId;33 }34
publicDouble getResult(){35returnresult;36 }37
publicvoidsetResult(Double result){38this.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文件,并返回给客户。
1
report.jsp2 pageEncoding="UTF-8"%>45html PUBLIC"-//W3C//DTD HTML 4.01 Transitional//EN""http://www.w3.org/TR/html4/loose.dtd">67
8 9 Insert title here10 11 17 18 19 report eeeee20 21 22 23
Controller实现
1
@Controller2@RequestMapping("/report.do")3
publicclassReportController{4protectedfinaltransientLog log=LogFactory5 .getLog(ReportController.class);6 @Autowired7privateReportService reportService;89 @RequestMapping10
publicString load(ModelMap modelMap){11return"report";12 }1314 @RequestMapping(params="method=export")15publicvoidexport(HttpServletRequest request,16
HttpServletResponse response, ModelMap modelMap)throwsException{17 Map beans=newHashMap();18 String jsFile=request.getParameter("js");19 String path=request.getSession().getServletContext().getRealPath("")20+"/WEB-INF";21 ScriptEngineManager factory=newScriptEngineManager();22 ScriptEngine engine=factory.getEngineByName("JavaScript");23//加载js脚本24InputStreamReader in=newInputStreamReader(newFileInputStream(path25+"/config/"+jsFile+".js"));26 engine.eval(in);27 Invocable inv=(Invocable) engine;28 String excel=engine.get("excel").toString();29//获取js文件中配置的sql,取得数据集,并将数据集传给jxl30Object reObj=inv.invokeFunction("init");31 NativeArray myArray=(NativeArray) reObj;32 Object[] array=newObject[(int) myArray.getLength()];33
for(Object o : myArray.getIds()){34intindex=(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=newReportManagerImpl(conn, beans);46 beans.put("rm", rm);47 InputStream is=newBufferedInputStream(newFileInputStream(48 templateFileName));49//jxl根据数据集,生成excel报表50XLSTransformer transformer=newXLSTransformer();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
@Service2
publicclassReportService{3protectedfinaltransientLog log=LogFactory4 .getLog(ReportService.class);5 @Autowired6privateEntityDao entityDao;78 @Transactional9
publicList getList(String hql){10 StringBuffer sff=newStringBuffer();11 sff.append(hql);12returnentityDao.createQuery(sff.toString());13 }1415
publicConnection getConnection(){16returnentityDao.getConnection();17 }18}
studentScore.js配置数据集的获取
1
function HqlObject(name, method)2
{3this.name=name;4this.method=method;5this.state=0;6}7var excel="/temple/studentscore.xls";8
function init(){9 var hql1=newHqlObject("students","getStudents");10 var hql2=newHqlObject("courses","getCourses");11 var myArray=newArray(2);12 myArray[0]=hql1;13 myArray[1]=hql2;14returnmyArray;15}1617
function getStudents(parame){18 var sql="select a from Student a where a.id = 1";19returnsql;20}2122
function getCourses(parame){23 var sql="select a from Course a";24returnsql;25}
jxl的excel模板
表达式内容:
学生 | | ${c.name} |
${st.user} | | ${sc.result} |
下面看测试运行效果
如果有兴趣的,欢迎交流学习。
源码