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

最新推荐文章于 2025-06-02 01:15:25 发布

爱上猫de鱼

最新推荐文章于 2025-06-02 01:15:25 发布

阅读量1k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：项目总结文章标签： java

本文链接：https://blog.youkuaiyun.com/boy_chen93/article/details/120366399

项目总结专栏收录该内容

7 篇文章

订阅专栏

本文介绍两种导出Excel的方法：一是适用于数据量较小的情况，直接通过流的方式在客户端完成；二是针对大规模数据导出，先在服务器生成文件再供客户端下载。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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