Ruoyi 从数据库中导出多个excel打包为zip

1.需求描述

将MySQL多个表转excel, 打包下载为zip.

2. 修改ExcelUtil.java

新增如下方法:将Workbook写入字节流ByteOutputStream

public void byteOutputStreamExcel(ByteOutputStream byteOutputStream, List<T> list, String sheetName, String title) {
        this.init(list, sheetName, title);
        try {
            writeSheet();
            wb.write(byteOutputStream);
        } catch (Exception e) {
            log.error("导出Excel异常{}", e.getMessage());
        } finally {
            IOUtils.closeQuietly(wb);
        }
    }
1
2
3
4
5
6
7
8
9
10
11

3. Service层方法

没什么好说的,就是这么写, 当作模板使用,自己优化.

    public void exportZip(HttpServletResponse response, int[] ids) {
        String fileName = "Mapping_Tables.zip";
        response.setContentType("application/zip");
        response.setHeader("content-disposition", "attachment;filename=" + fileName);
        ZipOutputStream zos = null;
        try {
            zos = new ZipOutputStream(response.getOutputStream());
            for (int tableId : ids) {
                IMappingTableBaseService service = mappingTableFactory.getMappingTableService(tableId);
                MappingTable mappingTable = this.selectMappingTableById(tableId);
                List list = service.list(null);
                ExcelUtil util = service.getExcelUtil();
                ZipEntry entry = new ZipEntry(mappingTable.getDescription() + ".xls");
                zos.putNextEntry(entry);
                ByteOutputStream bos = new ByteOutputStream();
                util.byteOutputStreamExcel(bos, list,"Date List", "");
                bos.writeTo(zos);
                bos.close();
                zos.closeEntry();
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            if(zos != null){
                try{
                    zos.close();
                } catch(Exception e){
                    e.printStackTrace();
                }
            }
        }
        
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

4. Controller方法

根据请求判断是要下载多个文件还是单个文件. 如果是单个文件则无需打包.

    @PostMapping("/export")
    public void export(HttpServletResponse response, @RequestBody int[] ids) {
        if (ids != null) {
            if (ids.length == 1) {
                IMappingTableBaseService service = mappingTableFactory.getMappingTableService(ids[0]);
                MappingTable mappingTable = mappingTableService.selectMappingTableById(ids[0]);
                List list = service.list(null);
                ExcelUtil util = service.getExcelUtil();
                util.exportExcel(response, list, "Date List", mappingTable.getDescription() + ".xls");
            } else {
                mappingTableService.exportZip(response, ids);
            }
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

结束~