报表的导出方法

jsp:

<button class='search-button btn btn-primary '  type="button" οnclick="toExcel();">
							<i class='fa fa-search' ></i>导出Excel
						</button>

js:

function toExcel() {
	
//	var stateDate = $('#stateDate').val();
	
	var timeStart = $('#startTime').val();
	var timeEnd=$('#endTime').val();
	if(timeStart==""&&timeEnd==""){
		layer.alert('必须选择查询条件', {
			  skin: 'layui-layer-lan' //样式类名
			  ,closeBtn: 0
			});
		return;
	}
	var ii = null;

	$.ajax({
		type : "POST",
		url : ctx + "/pls/reportManage/toRepayDetailReportExcel.in",
		 	        data: {"timeStart":timeStart, "timeEnd":timeEnd},  
		dataType : "json",
		beforeSend : function(xhr) {
			ii = layer.load(1);
		},
		success : function(data) {
			layer.close(ii);
			var a = JSON.stringify(data);
			a = a.replace('"', '');
			a = a.replace('"', '');
			document.getElementById("repayDetailReport").href = a;
			document.getElementById("repayDetailReport").click();
			layer.alert('下载成功', {
				skin : 'layui-layer-lan' //样式类名
				,
				closeBtn : 0
			});
		},
		error : function(data) {
			layer.close(ii);
			layer.alert('下载失败', {
				skin : 'layui-layer-lan' //样式类名
				,
				closeBtn : 0
			});
		}
	});
}

java:

	/**
	 * 还款明细报表导出
	 * @param request
	 * @param response
	 * @param plsReportManageReq
	 * @return
	 */
	@RequestMapping(value= "toRepayDetailReportExcel.in")
	@ResponseBody
	public String repayDetailToExcel(HttpServletRequest request,HttpServletResponse response,PlsReportManageReq plsReportManageReq){
		logger.debug("下载开始");
		String path = reportManage.repayDetailReportExcel(plsReportManageReq);
		logger.debug("下载结束");

		return JSON.toJSONString(path);
	}
@Override
	public String repayDetailReportExcel(PlsReportManageReq req) {
		try {
			
			//设置单元格边框
			WritableFont font = new WritableFont(WritableFont.createFont("楷体_GB2312"), 13, WritableFont.BOLD);
			WritableCellFormat cellBorder = new WritableCellFormat(font);
			cellBorder.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
			cellBorder.setAlignment(jxl.format.Alignment.CENTRE);
			cellBorder.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			
			//设置字体格式为Excel支持的格式,加粗 右对齐
			WritableFont font2 = new WritableFont(WritableFont.createFont("楷体_GB2312"), 15, WritableFont.BOLD);
			WritableCellFormat boldLeft = new WritableCellFormat(font2);
			boldLeft.setAlignment(jxl.format.Alignment.RIGHT);
			
			//千位一分
			WritableFont font3 = new WritableFont(WritableFont.createFont("楷体_GB2312"), 13, WritableFont.BOLD);
			jxl.write.NumberFormat bell = new jxl.write.NumberFormat("#,##0.00");
			WritableCellFormat percentage = new WritableCellFormat(font3, bell);
			percentage.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);
			percentage.setAlignment(jxl.format.Alignment.CENTRE);
			percentage.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			
			WritableWorkbook wwb = null;
			SimpleDateFormat sdf = new SimpleDateFormat("YYYY年MM月dd日");
			String fileName = downFileSysPath+"/reportDetail.xls";
			File file = new File(fileName);
			if(!file.getParentFile().exists()){
				//如果目录不存在将创建目录
				file.getParentFile().mkdirs();
			}
				 file.createNewFile();
			wwb = Workbook.createWorkbook(file);
			WritableSheet ws = wwb.createSheet("还款明细报表", 0);
			ws.setColumnView(0, 7);
//			ws.setColumnView(1, 15);
//			ws.setColumnView(2, 15);
//			ws.setColumnView(3, 15);
		
			// 合并单元格
			ws.mergeCells(1, 0, 18, 0);
			Label BT = new Label(1, 0, "还款明细报表",cellBorder);
			ws.addCell(BT);
			ws.mergeCells(1, 1, 18, 1);
			Label BT2 = new Label(1, 1, "统计日期:" + sdf.format(new Date()),boldLeft);
			ws.addCell(BT2);
			Label stateDate  = new Label(1, 2, "统计日期 ",cellBorder);
			Label caseId = new Label(2, 2, "案件编号",cellBorder);
			Label txnSeq  = new Label(3, 2, "交易流水号",cellBorder);
			Label collOrg  = new Label(4, 2, "催收组 ",cellBorder);
			Label collector  = new Label(5, 2, "催收员  ",cellBorder);
			Label custNo  = new Label(6, 2, "客户号  ",cellBorder);
			Label custName  = new Label(7, 2, "客户姓名",cellBorder);
			Label idNo  = new Label(8, 2, "证件号码  ",cellBorder);
			Label pmtAmt  = new Label(9, 2, "还款金额  ",cellBorder);
			Label actualPrin  = new Label(10, 2, "实还本金 ",cellBorder);
			Label actualInt  = new Label(11, 2, "实还利息 ",cellBorder);
			Label actualDedit  = new Label(12, 2, "实还违约金  ",cellBorder);
			Label txnDate  = new Label(13, 2, "交易日期  ",cellBorder);
			Label overDueTerm  = new Label(14, 2, "逾期期数 ",cellBorder); 
			Label overDueDays  = new Label(15, 2, "逾期天数 ",cellBorder);  
			Label functionCode  = new Label(16, 2, "逾期阶段  ",cellBorder);
			Label caseType  = new Label(17, 2, "案件类型  ",cellBorder);
			Label createTime  = new Label(18, 2, "创建时间 ",cellBorder);
			ws.addCell(stateDate);
			ws.addCell(caseId);
			ws.addCell(txnSeq);
			ws.addCell(collOrg);
			ws.addCell(collector);
			ws.addCell(custNo);
			ws.addCell(custName);
			ws.addCell(idNo);
			ws.addCell(pmtAmt);
			ws.addCell(actualPrin);
			ws.addCell(actualInt);
			ws.addCell(actualDedit);
			ws.addCell(txnDate);
			ws.addCell(overDueTerm);
			ws.addCell(overDueDays);
			ws.addCell(functionCode);
			ws.addCell(caseType);
			ws.addCell(createTime);
			Iterable<PlsCollUser>  userList = rPlsCollUser.findAll();
			LinkedHashMap<String, String> collUserMap = new LinkedHashMap<String, String>();
			if(userList != null){
				for(PlsCollUser collUser : userList){
					String key = collUser.getColluserLogname();
					String value = collUser.getColluserName();
					collUserMap.put(key, value);
				}
			}
			LinkedHashMap<String, String> caseTypeMap = new LinkedHashMap<String, String>();
			caseTypeMap.put("OA", "委外");
			caseTypeMap.put("PHONE", "电催");
			caseTypeMap.put("LEGAL", "法务");
			caseTypeMap.put("OUT", "委外");
			caseTypeMap.put("CALLON", "外访");
			Iterable<PlsCollOrg>  orgList = rPlsCollOrg.findAll();
			LinkedHashMap<String, String> collOrgMap2 = new LinkedHashMap<String, String>();
			if(orgList != null){
				for(PlsCollOrg Org : orgList){
					String key = Org.getCollorgCode();
					String value = Org.getCollorgName();
					collOrgMap2.put(key, value);
				}
			}
			List<PlsRepaydetailReport> list = this.queryRepayDetailExcel(req);
			if(list.size()>0){
			for (int i = 0; i < list.size(); i++) {
			if(list.get(i).getStateDate()!=null){
				Label stateDatei  = new Label(1, i + 3, sdf.format(list.get(i).getStateDate()),cellBorder);
				ws.addCell(stateDatei);
			}else{
				Label stateDatei=new Label(1, i + 3, "",cellBorder);
				ws.addCell(stateDatei);
			}
			if(list.get(i).getCaseId()!=null){
				Label  caseIdi  = new Label(2, i + 3, list.get(i).getCaseId(),cellBorder);
				ws.addCell(caseIdi);
			}else{
				Label caseIdi=new Label(2, i + 3, "",cellBorder);
				ws.addCell(caseIdi);
			}
			if(list.get(i).getTxnSeq()!=null){
				Label	txnSeqi  = new Label(3, i + 3, list.get(i).getTxnSeq(),cellBorder);
				ws.addCell(txnSeqi);
			}else{
				Label txnSeqi=new Label(3, i + 3, "",cellBorder);
				ws.addCell(txnSeqi);
			}
				
			if(list.get(i).getCollOrg()!=null){
				Label collOrgi  = new Label(4, i + 3, collOrgMap2.get(list.get(i).getCollOrg()),cellBorder);
				ws.addCell(collOrgi);
			}else{
				Label collOrgi=new Label(4, i + 3, "",cellBorder);
				ws.addCell(collOrgi);	
			}
			if(list.get(i).getCollector()!=null){
				Label collectori  = new Label(5, i + 3, collUserMap.get(list.get(i).getCollector()),cellBorder);
				ws.addCell(collectori);	
			}else{
				Label collectori=new Label(5, i + 3, "",cellBorder);
				ws.addCell(collectori);
			}
			if(list.get(i).getCustNo()!=null){
				Label custNoi  = new Label(6, i + 3, list.get(i).getCustNo(),cellBorder);
				ws.addCell(custNoi);	
			}else{
				Label custNoi=new Label(6, i + 3, "",cellBorder);
				ws.addCell(custNoi);
			}
			if(list.get(i).getCustName()!=null){
				Label custNamei  = new Label(7, i + 3, list.get(i).getCustName(),cellBorder);
				ws.addCell(custNamei);	
			}else{
				Label custNamei=new Label(7, i + 3, "",cellBorder);
				ws.addCell(custNamei);	
			}
				
			if(list.get(i).getIdNo()!=null){
				Label idNoi  = new Label(8, i + 3,list.get(i).getIdNo(),cellBorder);
				ws.addCell(idNoi);
			}else{
				Label idNoi=new Label(8, i + 3, "",cellBorder);
				ws.addCell(idNoi);
			}
			if(list.get(i).getPmtAmt()!=null){
				jxl.write.Number pmtAmtii  = new jxl.write.Number(9, i + 3,list.get(i).getPmtAmt().doubleValue(),cellBorder);
				ws.addCell(pmtAmtii);
			}else{
				Label pmtAmti=new Label(9, i + 3, "",cellBorder);
				ws.addCell(pmtAmti);
			}
				
			if(list.get(i).getActualInt()!=null){
				jxl.write.Number actualPrinii  = new jxl.write.Number(10, i + 3,list.get(i).getActualInt().doubleValue(),cellBorder);
				ws.addCell(actualPrinii);
			}else{
				Label actualPrini=new Label(10, i + 3, "",cellBorder);
				ws.addCell(actualPrini);
			}
			if(list.get(i).getActualInt()!=null){
				jxl.write.Number actualIntiii  = new jxl.write.Number(11, i + 3,list.get(i).getActualInt().doubleValue(),cellBorder);
				ws.addCell(actualIntiii);
			}else{
				Label actualInti=new Label(11, i + 3, "",cellBorder);
				ws.addCell(actualInti);
			}
			if(list.get(i).getActualDedit()!=null){
				jxl.write.Number actualDeditii  = new jxl.write.Number(12, i + 3,list.get(i).getActualDedit().doubleValue(),cellBorder);
				ws.addCell(actualDeditii);
			}else{
				Label actualDediti=new Label(12, i + 3, "",cellBorder);
				ws.addCell(actualDediti);
			}
				
				if(list.get(i).getTxnDate()!=null){
				Label txnDatei  = new Label(13, i + 3, sdf.format(list.get(i).getTxnDate()),cellBorder);
				ws.addCell(txnDatei);
			}else{
				Label txnDatei=new Label(13, i + 3, "",cellBorder);
				ws.addCell(txnDatei);
			}
			if(list.get(i).getOverDueTerm()!=null){
				jxl.write.Number overDueTermi  = new jxl.write.Number(14, i + 3, list.get(i).getOverDueTerm(),cellBorder);
				ws.addCell(overDueTermi);
			}else{
				Label overDueTermi=new Label(14, i + 3, "",cellBorder);
				ws.addCell(overDueTermi);
			}
			if(list.get(i).getOverDueDays()!=null){
				jxl.write.Number overDueDaysi  = new jxl.write.Number(15, i + 3, list.get(i).getOverDueDays(),cellBorder);
				ws.addCell(overDueDaysi);
			}else{
				Label overDueDaysi=new Label(15, i + 3, "",cellBorder);
				ws.addCell(overDueDaysi);
			}
				
			if(list.get(i).getFunctionCode()!=null){
				Label functionCodei  = new Label(16, i + 3, list.get(i).getFunctionCode(),cellBorder);
				ws.addCell(functionCodei);
			}else{
				Label functionCodei=new Label(16, i + 3, "",cellBorder);
				ws.addCell(functionCodei);
			}
				
			if(list.get(i).getCaseType()!=null){
				Label caseTypei  = new Label(17, i + 3,caseTypeMap.get(list.get(i).getCaseType()),cellBorder);
				ws.addCell(caseTypei);
			}else{
				Label caseTypei=new Label(17, i + 3, "",cellBorder);
				ws.addCell(caseTypei);
			}
				
			if(list.get(i).getCreateTime()!=null){
				Label createTimei  = new Label(18, i + 3,sdf.format(list.get(i).getCreateTime()),cellBorder);
				ws.addCell(createTimei);	
			}else{
				Label createTimei=new Label(18, i + 3, "",cellBorder);
				ws.addCell(createTimei);
			}
			}
		}
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		String load =fileVirtualPath+"/reportDetail.xls";
		return load;
	}


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值