关于导出文件的两种思考方式

╰半橙微兮° 2022-09-12 01:50 139阅读 0赞

有时候我们需要做文件导出的需求，这里以导出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();