数据导出为excel，并压缩成zip文件夹-526互联

/**
 * 生成企业数据 excel .xlsx格式 并压缩 zip格式
 * @param response
 */
@Override
public void exportEnterprise(HttpServletResponse response) {
    // 1.得到企业数据
    List<Dept> deptList = deptMapper.selectList(new LambdaQueryWrapper<Dept>().last("limit 5000"));

    // 2.生成动态excel
    List<String> fileNameList = new ArrayList<>();
    long begin=System.currentTimeMillis();
    generateExcel(deptList,fileNameList);
    long end=System.currentTimeMillis();
    System.out.println((end-begin));
    // 3.生成压缩包
    // 4.返回前端
    response.setContentType("application/zip");
    response.setHeader("Content-Disposition", "attachment; filename=\"enterprise.zip\"");
    compressFileZip(response, fileNameList);

    // 5.删除文件
    deleteFile(fileNameList);

}

1.得到企业数据

// sql查询
List<Dept> deptList = deptMapper.selectList(new LambdaQueryWrapper<Dept>());

2.生成动态excel

/**
 * 动态生成excel
 * @param deptList
 * @param fileNameList
 */
public void generateExcel(List<Dept> deptList,List<String> fileNameList){
    // 表头
    List<String> keyList = new ArrayList<>();
    Class<Dept> deptClass = Dept.class;
    Field[] declaredFields = deptClass.getDeclaredFields();
    for (Field declaredField : declaredFields) {
        boolean annotation = declaredField.isAnnotationPresent(ApiModelProperty.class);
        if (annotation) {
            String value = declaredField.getAnnotation(ApiModelProperty.class).value();
            keyList.add(value);
        }
    }
    // 异步 开启多线程
    CountDownLatch countDownLatch = new CountDownLatch(deptList.size());
    ExecutorService executorService = Executors.newFixedThreadPool(50);

    for (Dept dept : deptList) {
        Runnable runnable = () -> {
            try{
                Workbook workbook = getCellList(keyList,dept);
                String fileName = "file/" + dept.getName() + ".xlsx";
                fileNameList.add(fileName);
                FileOutputStream fileOutputStream = new FileOutputStream(fileName);
                workbook.write(fileOutputStream);

                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                countDownLatch.countDown();
            }
        };
        executorService.execute(runnable);
    }
    try {
        countDownLatch.await();
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    executorService.shutdown();
}

/**
 * 生成不同实体的工作簿
 * 目前是Dept
 * @param keyList
 * @param dept
 * @return
 */
public Workbook getCellList(List<String> keyList,Dept dept){
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet1 = workbook.createSheet("Sheet1");
    // 写入表头
    Row headerRow = sheet1.createRow(0);
    for (int i = 0; i < keyList.size(); i++) {
        Cell cell = headerRow.createCell(i);
        cell.setCellValue(keyList.get(i));
    }

    Row row = sheet1.createRow(1);
    List<Cell> cellList = new ArrayList<>();
    for (int j = 0; j < keyList.size(); j++) {
        Cell cell = row.createCell(j);
        cellList.add(cell);
    }
    DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    cellList.get(0).setCellValue(dept.getId().toString());
    cellList.get(1).setCellValue(dept.getName());
    cellList.get(2).setCellValue(dept.getAddress());
    cellList.get(3).setCellValue(dept.getSocialCode());
    cellList.get(4).setCellValue(dept.getAuthCode());
    cellList.get(5).setCellValue(dept.getLongitude());
    cellList.get(6).setCellValue(dept.getLatitude());
    cellList.get(7).setCellValue(dept.getAreaIds());
    cellList.get(8).setCellValue(dept.getAreaName());
    cellList.get(9).setCellValue(dept.getContacts());
    cellList.get(10).setCellValue(dept.getContactNumber());
    cellList.get(11).setCellValue(dept.getCateId().toString());
    cellList.get(12).setCellValue(dept.getCateName());
    cellList.get(13).setCellValue(dept.getContractCapacity());
    cellList.get(14).setCellValue(dept.getTradePath());
    cellList.get(15).setCellValue(dept.getTradePathName());
    if (Objects.isNull(dept.getAddTime()))
        cellList.get(16).setCellValue("");
    else
        cellList.get(16).setCellValue(dept.getAddTime().format(dtf));
    if (Objects.isNull(dept.getUpdateTime()))
        cellList.get(17).setCellValue("");
    else
        cellList.get(17).setCellValue(dept.getUpdateTime().format(dtf));
    cellList.get(18).setCellValue(dept.getCpyType().toString());
    cellList.get(19).setCellValue("");
    cellList.get(20).setCellValue(dept.getCateIdItem());
    cellList.get(21).setCellValue(dept.getFactoryPartition());
    return workbook;
}

3.生成压缩包　

4.流返回前端

/**
 * 把文件压缩成Zip文件 并返回前端
 * @param response
 * @param fileNameList
 */
public void compressFileZip(HttpServletResponse response, List<String> fileNameList) {
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    ZipOutputStream zos = null;
    try {
        zos = new ZipOutputStream(bos);
        for (String fileName : fileNameList) {
            File file = new File(fileName);
            // 创建一个输入流，读取文件数据
            FileInputStream fis = new FileInputStream(file);
            // 创建一个zip条目，指定文件名
            ZipEntry entry = new ZipEntry(file.getName());
            // 将条目添加到zip文件中
            zos.putNextEntry(entry);
            // 将文件数据写入zip文件
            byte[] buffer = new byte[1024];
            int length;
            while ((length = fis.read(buffer)) > 0) {
                zos.write(buffer, 0, length);
            }
            // 关闭输入流
            fis.close();
            // 完成当前文件的压缩
            zos.closeEntry();
        }
        // 关闭输出流
        zos.close();
        System.out.println("压缩完毕");
    } catch (Exception e) {
        e.printStackTrace();
    }

    try (ServletOutputStream out = response.getOutputStream()) {
        out.write(bos.toByteArray());
        out.flush();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

5.删除文件

/**
 * 删除文件
 * @param fileNameList
 */
public void deleteFile(List<String> fileNameList){
    for (String fileName : fileNameList) {
        File file = new File(fileName);
        if (file.exists()){
            file.delete();
        }
    }
}