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报表导出示例
最新推荐文章于 2021-02-12 17:54:24 发布