复杂导出和打印

[/code][code="java"]
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];
				// 设置行Id
				if (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];
				// 设置行Id
				if (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);
	}