复杂导出和打印

public void exportAll() throws Exception {Long bossInId = this.getParameter("bossInGrid", Long.class);Long dayReportId = this.getParameter("id", Long.class);Long bossoutId = this.getParameter("bossOutGrid", Long.class);TempDayReportMeg tdr = getDayReportMegWhenUpdate1(dayReportId);DayReport dr = this.dayReportService.getDayReport(dayReportId);List<Object[]> bossOutLines =new ArrayList<Object[]>();if(bossoutId!=null){bossOutLines = bossOutService.loadBossOutLineForReport(bossoutId);}List<Object[]> bossInLine_list=new ArrayList<Object[]>();if(bossInId!=null){bossInLine_list = bossInService.loadBossInLine(bossInId);}List<ShortSpill> ssList = this.dayReportShortSpillService.getShortSpillUnionByDayReportId(dayReportId);List<Object[]> ol = new ArrayList<Object[]>();DecimalFormat df = new DecimalFormat("#.##"); if (!ATool.clNull(ssList)) {for (ShortSpill s : ssList) {Object[] o = new Object[5];// 设置行Idif (ShortSpillType.TYPE_SHORT.equals(s.getShortType().getTypeCode())) {o[0] = ShortSpillType.NAME_SHORT;} else {o[0] = ShortSpillType.NAME_SPILL;}o[1] = (s.getSalesPerson() == null ? null : s.getSalesPerson().getName());o[2] = s.getExplain();o[3] = s.getShortType().getName();o[4] = df.format(s.getAmount());ol.add(o);}}String fileName = "营业日报编制";File file = ExportUtil.createExcelFile(ol, tdr,dr, bossOutLines, bossInLine_list, fileName);String filename=file.getName();ResponseDownloadUtils.download(this.getResponse(), file);}@SuppressWarnings("unchecked")public ActionResponse printAll(){Long bossInId = this.getParameter("bossInGrid", Long.class);Long dayReportId = this.getParameter("id", Long.class);Long bossoutId = this.getParameter("bossOutGrid", Long.class);TempDayReportMeg tdr = getDayReportMegWhenUpdate1(dayReportId);DayReport dr = this.dayReportService.getDayReport(dayReportId);List<Object[]> bossOutLines =new ArrayList<Object[]>();if(bossoutId!=null){bossOutLines = bossOutService.loadBossOutLineForReport(bossoutId);}List<Object[]> bossInLine_list=new ArrayList<Object[]>();if(bossInId!=null){bossInLine_list = bossInService.loadBossInLine(bossInId);}List<ShortSpill> ssList = this.dayReportShortSpillService.getShortSpillUnionByDayReportId(dayReportId);List<Object[]> ol = new ArrayList<Object[]>();DecimalFormat df = new DecimalFormat("#.##"); Double total=0.00d;if (!ATool.clNull(ssList)) {for (ShortSpill s : ssList) {Object[] o = new Object[5];// 设置行Idif (ShortSpillType.TYPE_SHORT.equals(s.getShortType().getTypeCode())) {o[0] = ShortSpillType.NAME_SHORT;} else {o[0] = ShortSpillType.NAME_SPILL;}o[1] = (s.getSalesPerson() == null ? null : s.getSalesPerson().getName());o[2] = s.getExplain();o[3] = s.getShortType().getName();o[4] = df.format(s.getAmount());total+=s.getAmount();ol.add(o);}}        String[] headerNames1=new String[]{"序号","项目名称","笔数","金额","查看明细"};String[] headerNames2=new String[]{"分类","营业员名称","长/短款说明","类型","金额"};ActionResponse ar=new ActionResponse(ResponseType.SUCCESS);Double bossInAmount=tdr.getBossIn()==null?0.00d:tdr.getBossIn().getAmount();Double bossOutAmount=tdr.getBossOut()==null?0.00d:tdr.getBossOut().getAmount();Double bossInOut=bossInAmount+bossOutAmount;String twoName=tdr.getTwoAuditUser()==null?"":tdr.getTwoAuditUser().getName();String threeName=tdr.getThreeAuditUser()==null?"":tdr.getThreeAuditUser().getName();this.getRequest().setAttribute("bossOutLines", bossOutLines);this.getRequest().setAttribute("bossInAmount", bossInAmount);this.getRequest().setAttribute("bossOutAmount", bossOutAmount);this.getRequest().setAttribute("bossInOut", bossInOut);this.getRequest().setAttribute("total", total);this.getRequest().setAttribute("twoName", twoName);this.getRequest().setAttribute("threeName", threeName);this.getRequest().setAttribute("bossInLine_list",bossInLine_list);this.getRequest().setAttribute("ol",ol);this.getRequest().setAttribute("dr",dr);this.getRequest().setAttribute("tdr",tdr);this.getRequest().setAttribute("headerNames1",headerNames1);this.getRequest().setAttribute("headerNames2",headerNames2);ar.setForward("dayReportViewPrintPage");return ar;}

public static File createExcelFile(List<Object[]> ssList,TempDayReportMeg tdr,DayReport dr,List<Object[]> bossOutLines,List<Object[]> bossInLine_list,String fileName) throws Exception{File file=new File(ExcelUtils.getExcelName(fileName));WritableWorkbook wk=Workbook.createWorkbook(file);WritableSheet st=wk.createSheet(fileName,0);for (int i = 0; i < 12; i++) {st.setColumnView(i, 10);// 设置列的宽度为10}WritableCellFormat cwcfF = ExcelUtils.getDefaultTitleFormat();cwcfF.setAlignment(Alignment.LEFT);cwcfF.setBackground(jxl.format.Colour.GRAY_25);WritableCellFormat cwcfF1 = ExcelUtils.getDefaultTitleFormat();cwcfF1.setBackground(Colour.WHITE);cwcfF1.setAlignment(Alignment.LEFT);WritableCellFormat dateFormat1=new WritableCellFormat(new DateFormat("yyyy-MM-dd HH:mm:ss"));dateFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);dateFormat1.setAlignment(Alignment.LEFT);WritableCellFormat dateFormat2=new WritableCellFormat(new DateFormat("yyyy-MM-dd"));dateFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);dateFormat2.setAlignment(Alignment.LEFT);st.addCell(createCell(0, 0, "营业厅",cwcfF1));st.mergeCells(0, 0, 1, 0);st.addCell(createCell(2,0,dr.getHall().getName(),cwcfF1));st.mergeCells(2, 0, 5, 0);st.addCell(createCell(6, 0, "营业日期",cwcfF1));st.mergeCells(6, 0, 7, 0);st.addCell(createCell(8,0,dr.getBusinessDate(),dateFormat2));st.mergeCells(8, 0, 11, 0);st.addCell(createCell(0, 1, "（一）营业资金（系统内）", cwcfF));st.mergeCells(0, 1, 11, 1);String[] headerNames1=new String[]{"序号","项目名称","笔数","金额","查看明细"};for(int i=0;i<headerNames1.length;i++){st.addCell(createCell(i,2,headerNames1[i],cwcfF1));}st.addCell(createCell(headerNames1.length, 2, " ",cwcfF1));st.mergeCells(headerNames1.length, 2, 11, 2);int j=2;if(CollectionUtils.isNotEmpty(bossInLine_list)){for(int i=0;i<bossInLine_list.size();i++){j++;for(int k=0;k<bossInLine_list.get(i).length;k++){st.addCell(createCell(k,j,bossInLine_list.get(i)[k],cwcfF1));}st.addCell(createCell(bossInLine_list.get(i).length, j, " ",cwcfF1));st.mergeCells(bossInLine_list.get(i).length, j, 11, 2);}}st.addCell(createCell(0, ++j, "小计(A)",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(3,j,tdr.getBossIn()==null?0.00:tdr.getBossIn().getAmount(),cwcfF1));st.mergeCells(2, j, 4, j);st.addCell(createCell(5,j," ",cwcfF1));st.mergeCells(5, j, 11, j);st.addCell(createCell(0, ++j, "（二）营业资金（系统外）", cwcfF));st.mergeCells(0, j, 11, j);j++;for(int i=0;i<headerNames1.length;i++){st.addCell(createCell(i,j,headerNames1[i],cwcfF1));}st.addCell(createCell(headerNames1.length, j, " ",cwcfF1));st.mergeCells(headerNames1.length, j, 11, 2);if(CollectionUtils.isNotEmpty(bossOutLines)){for(int i=0;i<bossOutLines.size();i++){j++;for(int k=0;k<bossOutLines.get(i).length;k++){st.addCell(createCell(k,j,bossOutLines.get(i)[k],cwcfF1));}st.addCell(createCell(bossOutLines.get(i).length, j, " ",cwcfF1));st.mergeCells(bossOutLines.get(i).length, j, 11, 2);}}st.addCell(createCell(0, ++j, "小计(B)",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getBossOut()==null?0.00:tdr.getBossOut().getAmount(),cwcfF1));st.mergeCells(2, j, 4, j);st.addCell(createCell(5,j," ",cwcfF1));st.mergeCells(5, j, 11, j);st.addCell(createCell(0, ++j, "营业资金合计(C=A+B)",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getShouldIncomeAmount(),cwcfF1));st.mergeCells(2, j, 4, j);st.addCell(createCell(5,j," ",cwcfF1));st.mergeCells(5, j, 11, j);st.addCell(createCell(0, ++j, "长短款列表", cwcfF));st.mergeCells(0, j, 11, j);String[] headerNames2=new String[]{"分类","营业员名称","长/短款说明","类型","金额"};j++;for(int i=0;i<headerNames2.length;i++){st.addCell(createCell(i,j,headerNames2[i],cwcfF1));}st.addCell(createCell(headerNames2.length, j, " ",cwcfF1));st.mergeCells(headerNames2.length, j, 11, 2);Double total=0.00d;if(CollectionUtils.isNotEmpty(ssList)){for(int i=0;i<ssList.size();i++){j++;for(int k=0;k<ssList.get(i).length;k++){st.addCell(createCell(k,j,ssList.get(i)[k],cwcfF1));if(ssList.get(i)[4]!=null&&!StringUtils.isNullOrBlank(ssList.get(i)[4].toString())){total+=Double.parseDouble(ssList.get(i)[4].toString());}}st.addCell(createCell(ssList.get(i).length, j, " ",cwcfF1));st.mergeCells(ssList.get(i).length, j, 11, 2);}}st.addCell(createCell(0, ++j, "长短款合计",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,total,cwcfF1));st.mergeCells(2, j, 4, j);st.addCell(createCell(5,j," ",cwcfF1));st.mergeCells(5, j, 11, j);st.addCell(createCell(0, ++j, "昨日余款",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getYesterdayMantissa(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "今日应缴款",cwcfF1));st.mergeCells(6, j, 7, j);st.addCell(createCell(8,j,tdr.getShouldPayAmount(),cwcfF1));st.mergeCells(8, j, 11, j);st.addCell(createCell(0, ++j, "今日实缴款",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getTodayFactPayAmount(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "今日余款",cwcfF1));st.mergeCells(6, j,7, j);st.addCell(createCell(8,j,tdr.getTodayMantissa(),cwcfF1));st.mergeCells(8, j, 11, j);st.addCell(createCell(0, ++j, "今日实缴款说明",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getTodayFactPayRemark(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "其他原因备注",cwcfF1));st.mergeCells(6, j,7, j);st.addCell(createCell(8,j,tdr.getRemark(),cwcfF1));st.mergeCells(8, j, 11, j);st.addCell(createCell(0, ++j, "稽核操作日志", cwcfF));st.mergeCells(0, j, 11, j);st.addCell(createCell(0, ++j, "制表人",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getTableUser().getName(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "制表日期",cwcfF1));st.mergeCells(6, j, 7, j);st.addCell(createCell(8,j,tdr.getTableDate(),dateFormat2));st.mergeCells(8, j, 11, j);st.addCell(createCell(0, ++j, "二级稽核",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getTwoAuditUser()==null?"":tdr.getTwoAuditUser().getName(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "二级稽核日期",cwcfF1));st.mergeCells(6, j, 7, j);st.addCell(createCell(8,j,tdr.getTwoAuditDate(),dateFormat2));st.mergeCells(8, j, 11, j);st.addCell(createCell(0, ++j, "三级稽核",cwcfF1));st.mergeCells(0, j, 1, j);st.addCell(createCell(2,j,tdr.getThreeAuditUser()==null?"":tdr.getThreeAuditUser().getName(),cwcfF1));st.mergeCells(2, j, 5, j);st.addCell(createCell(6, j, "三级稽核日期",cwcfF1));st.mergeCells(6, j, 7, j);st.addCell(createCell(8,j,tdr.getThreeAuditDate(),dateFormat2));st.mergeCells(8, j, 11, j);// 设置列高for (int k = 0; k <= j; k++) {st.setRowView(k, 350);}wk.write();wk.close();return file;}private static WritableCell createCell(int colIndex, int rowIndex,Object value, WritableCellFormat format) {return ExcelUtils.createCell(colIndex, rowIndex, value, format);}