jxls导出xls 报表

来源：互联网发布：php授权验证系统v2.1 编辑：程序博客网时间：2024/06/06 19:50

引入jar:

<dependency><groupId>net.sf.jxls</groupId><artifactId>jxls-core</artifactId><version>1.0.2</version></dependency><dependency><groupId>net.sf.jxls</groupId><artifactId>jxls-reader</artifactId><version>1.0.2</version></dependency>

1.前台jsp：

<li><a id="v_exportExcel" class="icon" href="sysAssessResultRegister/downloadAssessResult" target="dwzExport"><span>下载</span></a></li>

2.后台action：

@RequestMapping("/downloadReport")public ModelAndView downloadReport(HttpServletResponse response,SysAssessResultRegister model,HttpServletRequest request) {String employeeId = String.valueOf(SessionHelper.getEmployeeId(request));model.setAssessPersonId(employeeId);List<SysAssessResultRegister> list= sysAssessResultRegisterService.getSysAssessResultRegisterListByObj(model);if(list!=null&&list.size()>0){for(SysAssessResultRegister p:list){p.setAssessPerson((String) map.get(p.getAssessPersonId()));if("DEPT".equals(p.getAssessmentObject())){p.setAssessmentObjectStr("部门");sysId=p.getDepartmentId();}else if("PERSON".equals(p.getAssessmentObject())){p.setAssessmentObjectStr("个人");p.getEmployeeName();sysId=p.getEmployeeId();}}}map=null;try{Map excelMap = reportService.exportToReportList(list,model);if(excelMap!=null){Workbook wb = (Workbook)excelMap.get("Workbook");HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);int rowCount=0;int colCount=2;for(int i=0;i<assessobjList.size();i++){String assessObj=(String) assessobjList.get(i);rowCount=(Integer)assessObjmap.get(assessObj)+colCount;sheet.addMergedRegion(new Region(colCount,(short) 1,rowCount-1,(short)1));sheet.addMergedRegion(new Region(colCount,(short) 3,rowCount-1,(short)3));sheet.addMergedRegion(new Region(colCount,(short) 4,rowCount-1,(short)4));sheet.addMergedRegion(new Region(colCount,(short)7,rowCount-1,(short)7));colCount=rowCount;}String dateStr = model.getAssessMonth();String fileName = SessionUtil.getUsername(request)+"_"+dateStr+"_check.xls";response.reset();response.setHeader("Content-Disposition","attachment; filename=\""+fileName+"\"");response.setContentType("application/vnd.ms-excel; charset=UTF-8");wb.write(response.getOutputStream());}else{return ajaxDoneError("文件导出失败!错误信息: 没有数据!");}}catch(Exception e){return ajaxDoneError("文件导出失败!错误信息:"+e.getMessage());}return null;}

3.service层包装：

@Overridepublic Map exportToReportList(List<SysAssessResultRegister> list,SysAssessResultRegister model){String templateFileName = LocationInitUtil.getExeclLocation()+"AssessResultRegisterList_Template.xls";InputStream in = null;Workbook wb = null;if(list!=null&&list.size()>0){for(int i = 0; i<list.size(); i++){SysAssessResultRegister v = (SysAssessResultRegister)list.get(i);}}Map excelMap = new HashMap();Map beans = new HashMap();beans.put("resultList",list);Configuration config = new Configuration();XLSTransformer transformer = new XLSTransformer(config);try{in = new FileInputStream(new File(templateFileName));wb = transformer.transformXLS(in,beans);excelMap.put("Workbook",wb);}catch(ParsePropertyException e){e.printStackTrace();}catch(Exception e){e.printStackTrace();excelMap = null;}return excelMap;}

xls模板文档如图：

0 0