有时候我们需要做文件导出的需求,这里以导出excel文件为例,给出两种方式。
1、文件内数据不是很多的时候,比如只有“千”这个数量级的时候,我们可以用“流”的方式从客户端导出,如下代码示例:
/**
* 入账记录导出为excel表
* @param response
* @param qo
* @return
* @throws IOException
*/
@PostMapping("/records/income/export")
public HttpServletResponse exportIncomeRecord(HttpServletResponse response, @RequestBody IncomeExportQO qo) throws IOException {
log.info("入账导出excel入参,开始时间戳={},结束时间戳={}", qo.getStartTime(), qo.getEndTime());
String userId = (String) SecurityUtils.getSubject().getSession().getAttribute("id");
Date startDate = null;
Date endDate = null;
if (!StringUtils.isBlank(qo.getStartTime())) {
startDate = DateUtils.tranTimestampForDate(qo.getStartTime());
}
if (!StringUtils.isBlank(qo.getEndTime())) {
endDate = DateUtils.tranTimestampForDate(qo.getEndTime());
}
IncomeExport ie = new IncomeExport();
ie.setStartDate(startDate);
ie.setEndDate(endDate);
ie.setUserId(userId);
log.info("入账导出excel,开始时间={},结束时间={}", startDate, endDate);
List<IncomeExportVo> voList = recordService.incomeExport(ie);
log.info("提现查询出来的数据条数:" + voList.size());
//以上为业务代码,仅仅是为了获取数据集合voList
ExcelWriter writer = ExcelUtil.getWriter();
//设置excel单元格大小
writer.setColumnWidth(0, 40);
writer.setColumnWidth(1, 35);
writer.setColumnWidth(2, 30);
writer.setColumnWidth(3, 20);
writer.setColumnWidth(4, 10);
writer.setColumnWidth(5, 20);
//writer.setColumnWidth(6, 20);
writer.setColumnWidth(6, 30);
//设置excel单元格标题
writer.merge(6, "入账记录表");
//填充excel单元格数据
if(voList != null && voList.size() > 0){
writer.addHeaderAlias("incomeId", "入账流水号");
writer.addHeaderAlias("foreignBankAccount", "收款银行账号");
writer.addHeaderAlias("platform", "平台");
writer.addHeaderAlias("money", "入账金额");
writer.addHeaderAlias("currency", "币种");
writer.addHeaderAlias("leftMoney", "可用余额");
//writer.addHeaderAlias("status", "状态");
writer.addHeaderAlias("gmtCreate", "创建日期");
writer.write(voList, true);
}else{
writer.writeCellValue(0, 1, "入账流水号");
writer.writeCellValue(1, 1, "收款银行账号");
writer.writeCellValue(2, 1, "平台");
writer.writeCellValue(3, 1, "入账金额");
writer.writeCellValue(4, 1, "币种");
writer.writeCellValue(5, 1, "可用余额");
writer.writeCellValue(6, 1, "创建日期");
}
//设置字体及标题栏高度
Font font = writer.createFont();
font.setFontHeight((short) 250);
writer.getStyleSet().setFont(font, true);
writer.setRowHeight(0, 20);
writer.setRowHeight(1, 20);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename=incomeRecord.xls");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
writer.close();
IoUtil.close(out);
return response;
}
2、当需要的导出以“万”为数量级的时候,用流的方式显得无力了,即客户端下载的会非常非常慢,所以此时换一种方式,现将文件生成,放到服务器上,让客户端从服务器上下载,就会非常快,如下业务代码:
//生成服务器路劲
String newFileName = System.nanoTime() + ".xlsx";
String fileDir = "/" + new SimpleDateFormat("yyyyMMdd").format(new Date());
//uploadFileProperties.getSaveDir()为服务器中某个文件夹路径,我这里是/data/images/news/admin
File dir = new File(uploadFileProperties.getSaveDir() + fileDir);
// File dir = new File("/Users/chenjianwen/myDisk" + fileDir);
if (!dir.exists()) {
dir.mkdirs();
}
String filePath = fileDir + "/" + newFileName;
BigExcelWriter writer= ExcelUtil.getBigWriter(new File(uploadFileProperties.getSaveDir() + fileDir + "/" + newFileName));
// BigExcelWriter writer = ExcelUtil.getBigWriter(new File("/Users/chenjianwen/myDisk" + fileDir + "/" + newFileName));
//设置excel单元格大小
writer.setColumnWidth(0, 30);
writer.setColumnWidth(1, 30);
writer.setColumnWidth(2, 30);
writer.setColumnWidth(3, 30);
writer.setColumnWidth(4, 20);
writer.setColumnWidth(5, 20);
writer.setColumnWidth(6, 30);
writer.setColumnWidth(7, 20);
writer.setColumnWidth(8, 20);
writer.setColumnWidth(9, 30);
writer.setColumnWidth(10, 20);
writer.setColumnWidth(11, 20);
writer.setColumnWidth(12, 10);
//设置excel单元格标题
writer.merge(12, "流水记录");
//填充excel单元格数据
if(incomeRecordAdminVoList != null && incomeRecordAdminVoList.size() > 0){
writer.addHeaderAlias("id", "流水号");
writer.addHeaderAlias("bankAccount", "银行卡号");
writer.addHeaderAlias("fid", "国外银行账号id");
writer.addHeaderAlias("userId", "用户ID");
writer.addHeaderAlias("money", "金额");
writer.addHeaderAlias("ableMoney", "可用金额");
writer.addHeaderAlias("currency", "币种");
writer.addHeaderAlias("payAccount", "付款人信息");
writer.addHeaderAlias("uploadTime", "审核创建时间");
writer.addHeaderAlias("createTime", "创建时间");
writer.addHeaderAlias("remarks", "备注");
writer.addHeaderAlias("auditFailReason", "审核失败原因");
writer.addHeaderAlias("status", "状态(0签名审核通过,用户可提现 1未到账(amazon) 2对账不平(amazon) 3需提交订单 4审核的订单有误 5等待订单审核 6等待发票生成 7发票生成完毕,等待用户签名 8等待后台审核签名 9用户已申请提现 10已拆分 11冻结中 12可用余额已结完 13已汇总)");
writer.write(incomeRecordAdminVoList, true);
}else{
writer.writeCellValue(0, 1, "流水号");
writer.writeCellValue(1, 1, "银行卡号");
writer.writeCellValue(2, 1, "国外银行账号id");
writer.writeCellValue(3, 1, "用户ID");
writer.writeCellValue(4, 1, "金额");
writer.writeCellValue(5, 1, "可用金额");
writer.writeCellValue(6, 1, "币种");
writer.writeCellValue(7, 1, "付款人信息");
writer.writeCellValue(8, 1, "审核创建时间");
writer.writeCellValue(9, 1, "创建时间");
writer.writeCellValue(10, 1, "备注");
writer.writeCellValue(11, 1, "审核失败原因");
writer.writeCellValue(12, 1, "状态");
}
//设置字体
Font font = writer.createFont();
font.setFontHeight((short) 240);
writer.getStyleSet().setFont(font, true);
writer.close();
//uploadFileProperties.getImageServerDomain()是服务器域名
return MessageVo.newBuilder().code(200).data(uploadFileProperties.getImageServerDomain() + "/news/admin" + filePath).build();
// return MessageVo.newBuilder().code(200).data("/Users/chenjianwen/myDisk" + filePath).build();