Springboot导出大数据量excel（三）-导出csv到压缩包

一、简介

        在上篇文章《Springboot导出大数据量excel（二）-分页查询数据List＜Map＞》说道excel的最大支持数据行数1048576(2的20次方)。本文内容主要就是解决这个问题。

二、效果展示

导出200行数据，每个文件存储10万行数据。

 三、源码

controller

    @RequestMapping(value = "export5", method = RequestMethod.GET)
    @ApiOperation(value = "导出大量数据(zip)", notes = "csv")
    @ApiImplicitParams({})
    public void export5(HttpServletResponse response, HttpServletRequest request) throws Exception {
        Date start = new Date();
        Map<String, Object> params = new HashMap<>();
        params.put("pageSize", 10000);
        String fileName = "csv_" + System.currentTimeMillis();
        ExcelUtil.exportCsvZip(params, response, fileName, 100000, (param, page) -> {
            param.put("pageNum", page);
            //查询数据
            List<Map<String, Object>> list = getPageData2(param);
            return list;
        });
        log.info("耗时:{}", (new Date().getTime() - start.getTime()));
    }

ExcelUtil.exportCsvZip

/**
     * 导出csv压缩包-分页查询数据
     *
     * @param queryParams  查询参数
     * @param response     输出
     * @param fileName     文件名
     * @param fileRowCount 文件行数
     * @param server       查询数据接口
     * @throws IOException IO异常
     */
    public static void exportCsvZip(Map<String, Object> queryParams, HttpServletResponse response, String fileName, int fileRowCount, MyExcelExportServer server) throws IOException {
        //使用临时文件
        List<File> fileList = new ArrayList<>();
        //设置response参数
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".zip", "UTF-8"));
        response.setContentType("application/x-zip-compressed");
        File file = null;
        FileWriter writer = null;
        CSVPrinter printer = null;
        try (ZipOutputStream zipOutputStream = new ZipOutputStream(response.getOutputStream());) {
            List<Object> tempList = new ArrayList<>();

            //从list取第一行到最后一行的内容并放到对应的Excel里，若记录里某字段值没有会有问题
            int rowNum = 0;//行数
            int page = 1;
            int var6 = page + 1;
            //文件号
            int fileNum = 1;

            for (List<Map<String, Object>> list = server.selectListForExcelExport(queryParams, page); list != null && list.size() > 0; list = server.selectListForExcelExport(queryParams, var6++)) {
                //分sheet的条件（excel最大支持 1048576 (2的20次方)行数据）
                int pageNum = (int) queryParams.get("pageNum");
                int pageSize = (int) queryParams.get("pageSize");
                int fileNumNew = (pageSize * pageNum - 1) / fileRowCount + 1;//计算文件数
                log.info("第{}个文件,查询页数{}", fileNum, pageNum);
                if (fileNumNew > fileNum) {//创建临时文件
                    fileNum = fileNumNew;
                    file = File.createTempFile(fileNum + "_" + fileName, ".csv");
                    fileList.add(file);
                    if (writer != null) {
                        writer.close();
                    }
                    if (printer != null) {
                        printer.close();
                    }
                    writer = new FileWriter(file);
                    printer = CSVFormat.EXCEL.print(writer);
                }
                if (file == null) {//首次创建临时文件
                    file = File.createTempFile(fileNum + "_" + fileName, ".csv");
                    fileList.add(file);
                    writer = new FileWriter(file);
                    printer = CSVFormat.EXCEL.print(writer);
                }
                if (rowNum == 0) {
                    //设置表头
                    Map<String, Object> map = list.get(0);
                    //创建sheet的第一行标题
                    tempList.addAll(map.keySet());
                    //写入数据
                    printer.printRecord(tempList);
                    tempList.clear();
                    rowNum++;
                }
                for (Map<String, Object> data : list) {
                    for (String key : data.keySet()) {
                        tempList.add(data.get(key) != null ? data.get(key).toString() : "");
                    }
                    //写入数据
                    printer.printRecord(tempList);
                    //log.info(JSONUtil.toJsonStr(tempList));
                    tempList.clear();
                    rowNum++;
                }
                printer.flush();//必须执行，否则会出现丢失末尾数据的问题
            }

            //输出文件
            int i = 1;
            for (File f : fileList) {
                zipOutputStream.putNextEntry(new ZipEntry(String.format("%0" + String.valueOf(fileList.size()).length() + "d", i++) + "_" + fileName + ".csv"));
                byte[] buf = new byte[1024];
                int len;
                try (FileInputStream in = new FileInputStream(f)) {
                    while ((len = in.read(buf)) != -1) {
                        zipOutputStream.write(buf, 0, len);
                    }
                }
            }
            zipOutputStream.flush();
        } finally {
            if (writer != null) {
                writer.close();
            }
            if (printer != null) {
                printer.close();
            }
            for (File f : fileList) {
                boolean delete = f.delete();
                log.info("删除临时文件:{},删除结果:{}", file.getName(), delete);
            }
        }
    }

源码地址

POI大数据量导出