当前位置:首页 > 开发 > 编程语言 > 编程 > 正文

Excel报表导出示例

发表于: 2015-05-25   作者:augustcoco   来源:转载   浏览:
摘要: public HttpServletResponse exportExcel(HttpServletResponse response,List<PayFlowListVo> results) { try { String fileName="XXZF_" + ".xls"; response.reset();
public HttpServletResponse exportExcel(HttpServletResponse response,List<PayFlowListVo> results) {
		try {
			String fileName="XXZF_" + ".xls";
			response.reset();
			response.setHeader("Content-disposition", "attachment; filename="+ fileName);
			response.setContentType("application/vnd.ms-excel");
			OutputStream os = response.getOutputStream();
			WritableWorkbook  wbook = Workbook.createWorkbook(os);
			
			// 标题字体及样式--居中
			WritableFont writableFont = new WritableFont(WritableFont.createFont("宋体"), 10, 
					WritableFont.BOLD, false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			WritableCellFormat wcfTitle = new WritableCellFormat(writableFont);
			
			wcfTitle.setAlignment(Alignment.CENTRE);//水平居中
			wcfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);//垂直居中
			wcfTitle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			wcfTitle.setWrap(true);//自动换行
			
			// 内容字体及样式--居中
			WritableFont wfContentCenter = new WritableFont(WritableFont.createFont("宋体"), 10, 
					WritableFont.NO_BOLD,false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			WritableCellFormat wcfContentCenter = new WritableCellFormat(wfContentCenter);
			wcfContentCenter.setAlignment(Alignment.CENTRE);
			wcfContentCenter.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcfContentCenter.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			wcfContentCenter.setWrap(true);
			
			// 格式化数字
			WritableFont wfFont = new WritableFont(WritableFont.createFont("宋体"), 10, 
					WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
			NumberFormat numberFormatTwo = new NumberFormat("0.00");
			WritableCellFormat wcfContentRightTwo = new WritableCellFormat(wfFont, numberFormatTwo);
			wcfContentRightTwo.setAlignment(Alignment.RIGHT);
			wcfContentRightTwo.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcfContentRightTwo.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框
			wcfContentRightTwo.setWrap(true);
			WritableSheet wsheet = wbook.createSheet("线下支付流水", 0); // sheet名称
			
			
			//设置单元格宽度
			wsheet.setColumnView(0, 10);
			wsheet.setColumnView(1, 25);
			wsheet.setColumnView(2, 25);
			wsheet.setColumnView(3, 25);
			wsheet.setColumnView(4, 25);
			wsheet.setColumnView(5, 15);
			wsheet.setColumnView(6, 25);
			wsheet.setColumnView(7, 15);
			wsheet.setColumnView(8, 15);
			wsheet.setColumnView(9, 25);
			wsheet.setColumnView(10, 25);
			wsheet.setColumnView(11, 15);
			wsheet.setColumnView(12, 15);
			wsheet.setColumnView(13, 15);
			wsheet.setColumnView(14, 30);
			wsheet.mergeCells(0, 0, 14, 1);
			wsheet.addCell(new Label(0,0,"线下支付流水对账报表",wcfTitle));
			
			// 设置单元格标题内容
			wsheet.addCell(new Label(0, 2, "编号", wcfTitle));	
			wsheet.addCell(new Label(1, 2, "收款人账号", wcfTitle));
			wsheet.addCell(new Label(2, 2, "收款人名称", wcfTitle));
			wsheet.addCell(new Label(3, 2, "付款人账号", wcfTitle));
			wsheet.addCell(new Label(4, 2, "付款人名称", wcfTitle));
			wsheet.addCell(new Label(5, 2, "金额", wcfTitle));
			wsheet.addCell(new Label(6, 2, "创建时间", wcfTitle));
			wsheet.addCell(new Label(7, 2, "支付时间", wcfTitle));
			wsheet.addCell(new Label(8, 2, "类型", wcfTitle));
			wsheet.addCell(new Label(9, 2, "订单号", wcfTitle));
			wsheet.addCell(new Label(10, 2, "支付流水号", wcfTitle));
			wsheet.addCell(new Label(11, 2, "支付状态", wcfTitle));
			
			wsheet.addCell(new Label(12, 2, "支付渠道", wcfTitle));
			wsheet.addCell(new Label(13, 2, "操作人", wcfTitle));
			wsheet.addCell(new Label(14, 2, "操作时间", wcfTitle));
//			wsheet.addCell(new Label(13, 2, "付款回单", wcfTitle));
			int row = 3;//行
			int cell;//列
			// 生成报表内容
			if(results.size()<0)
				return null;
			
			for(PayFlowListVo pv : results){
				cell = 0;
				wsheet.addCell(new Label(cell++, row, row-2+ "", wcfContentCenter));
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayeeAccount()) ? "" :
					pv.getPayeeAccount() + "", wcfContentCenter));//收款人账号
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayeeName()) ? "" :
					pv.getPayeeName() + "", wcfContentCenter));//收款人名称
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayerAccount()) ? "" :
					pv.getPayerAccount() + "", wcfContentCenter));//付款人账号
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayerName()) ? "" :
					pv.getPayerName() + "", wcfContentCenter));//付款人名称
				//金额
				//wsheet.addCell(new Label(cell++, row, pv.getAmount() == null ? "" : (StringUtils.isBlank(pv.getAmount().toString()) ? "":
				//(pv.getAmount().doubleValue()==0?"0.00":BigDecimalUtil.formateBigDecimal(pv.getAmount()))) + "", wcfContentCenter));
				wsheet.addCell(new Number(cell++, row, pv.getAmount()==null ? new Double(0): pv.getAmount().doubleValue(),wcfContentRightTwo));
				//手续费
				/*wsheet.addCell(new Number(cell++, row, pv.getHandlingFee()==null ? new Double(0): pv.getHandlingFee().doubleValue(),wcfContentRightTwo));
				//应收金额
				wsheet.addCell(new Number(cell++, row, pv.getReceivable()==null ? new Double(0): pv.getReceivable().doubleValue(),wcfContentRightTwo));*/
				//创建时间
				wsheet.addCell(new Label(cell++, row, pv.getCreateTime() == null ? "" : (StringUtils.isBlank(pv.getCreateTime().toString()) ? "" :
					TimeUtil.getYMDHMS(pv.getCreateTime())) + "", wcfContentCenter));//支付时间
				wsheet.addCell(new Label(cell++, row, pv.getPayDate()==null ? "" : (StringUtils.isBlank(pv.getPayDate().toString()) ? "" :
					TimeUtil.getYMD(pv.getPayDate())) + "", wcfContentCenter));//支付时间
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayType()) ? "" : getStatusDesc(PAY_TYPE, pv.getPayType()) + "", wcfContentCenter));
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getOrderId()) ? "" : pv.getOrderId() + "", wcfContentCenter));
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayFlowId()) ? "" : pv.getPayFlowId() + "", wcfContentCenter));
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getPayStatus()) ? "" : getStatusDesc(PAY_STATUS, pv.getPayStatus()) + "", wcfContentCenter));
				//支付渠道(线下支付)
				wsheet.addCell(new Label(cell++, row, "线下支付",wcfContentCenter));
				//操作人
				wsheet.addCell(new Label(cell++, row, StringUtils.isBlank(pv.getConfirmor()) ? "":pv.getConfirmor() + "" ,wcfContentCenter));
				//操作时间
				wsheet.addCell(new Label(cell++, row, pv.getConfirmTime() == null ? "":TimeUtil.getYMDHMS(pv.getConfirmTime()) + "",wcfContentCenter));
				row++;
			}
			wbook.write(); // 写入文件
			wbook.close();
			os.close();
			response.flushBuffer();
		} catch (Exception ex) {
			log.error("exportExcel:", ex);
		}
		return response;
	}

Excel报表导出示例

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号