Java代码实现Excel导出多份文件并压缩成zip包保存到OSS服务器返回URL访问下载

1、首先引入maven依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
 </dependency>

2、实际导出工具类方法

public static byte[] exportExcel(Map<String, List<List<String>>> fieldMap, Map<String, List> valueMap, String sheetName) {
        if (Objects.isNull(valueMap) || 0 == valueMap.size()) {
            throw new RuntimeException("导出数据不可以为空");
        }
        if (StringUtils.isBlank(sheetName)) {
            throw new RuntimeException("Excel单元格sheet名称不可以为空");
        }
        if (Objects.isNull(fieldMap)) {
            throw new RuntimeException("Excel导出渲染模版不可以为空");
        }
        //这里直接使用 try 新特性创建流,会自动关闭,不需要手动关闭流
        //如果需要导出到浏览器用户直接下载 这里使用 response 的输出流到浏览器 需要传参 HttpServletResponse response
       //  try (ServletOutputStream outputStream = response.getOutputStream(); ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
       //这里如果需要导出转换为byte数组上传OSS的话 使用ByteArrayOutputStream 字节流
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream)) {
        //如果需要导出到浏览器 需要设置格式跟字符类型 否则会乱码跟格式错误
//            response.setCharacterEncoding("utf-8");
//            response.setContentType("application/zip");
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            for (Map.Entry<String, List> entry : valueMap.entrySet()) {
                String key = entry.getKey();
                List value = entry.getValue();
                List<List<String>> lists = fieldMap.get(key);
                //构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错
                ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();
                //构建一个sheet页
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                //构建excel表头信息
                WriteTable writeTable0 = EasyExcel.writerTable(0).head(lists).needHead(Boolean.TRUE).build();
                //将表头和数据写入表格
                excelWriter.write(value, writeSheet, writeTable0);
                //创建压缩文件 这里设置导出Excel的名称 记得添加后缀 否则导出文件没后缀
                ZipEntry zipEntry = new ZipEntry(key + ModelCustomizeRuleServiceImpl.timeStamp2Date(System.currentTimeMillis(), "yyyyMMddHHmmss") + ".xls");
                zipOutputStream.putNextEntry(zipEntry);
                Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                //将excel对象以流的形式写入压缩流
                workbook.write(zipOutputStream);
            }
            //记得关闭实体 否则导出的zip包会丢失最后一份数据
            zipOutputStream.closeEntry();
            zipOutputStream.flush();
            //需要转换为byte数组则调用该方法 导出浏览器直接void类型即可
            return outputStream.toByteArray();
        } catch (Exception e) {
            log.error("导出Excel压缩出现异常,信息" + e.getMessage());
            //抛出异常结束程序
            throw new RuntimeException("数据导出接口异常:" + e.getMessage());
        }
    }

3、测试Demo案例，直接拿来即可用，测结果

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @author shilei
 * @create 2021-03-15 17:51
 */
@RestController
@Slf4j
public class TestDemo {

    @GetMapping(value = "/TEST2")
    public void unDirectExport(HttpServletResponse response) throws Exception {
        String fileName = "test2.zip";
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/zip");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        List<AAAA> list = get96Time();
        List<AAAA> list2 = get96Time();
        //按某个条件分组
        Map<String, List<AAAA>> map = new HashMap();
        map.put("test1.xls", list);
        map.put("test2.xls", list2);
        exportExcel(response, map);

    }

    private void exportExcel(HttpServletResponse response, Map<String, List<AAAA>> map) throws IOException {
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
        try {
            for (Map.Entry<String, List<AAAA>> entry : map.entrySet()) {
                String k = entry.getKey();
                List<AAAA> value = entry.getValue();
                //构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错
                ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();
                //构建一个sheet页
                WriteSheet writeSheet = EasyExcel.writerSheet("薪资单").build();
                //构建excel表头信息
                WriteTable writeTable0 = EasyExcel.writerTable(0).head(AAAA.class).needHead(Boolean.TRUE).build();
                //将表头和数据写入表格
                excelWriter.write(value, writeSheet, writeTable0);

                //创建压缩文件
                ZipEntry zipEntry = new ZipEntry(k);
                zipOutputStream.putNextEntry(zipEntry);
                Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                //将excel对象以流的形式写入压缩流
                workbook.write(zipOutputStream);
            }
            zipOutputStream.flush();
        } catch (Exception e) {
            log.error("导XXX失败,原因" + e.getMessage());
            log.error(e.getMessage(), e);
            //抛出异常结束程序
            throw new RuntimeException("数据导出接口异常");
        } finally {
            //关闭数据流，注意关闭的顺序
            zipOutputStream.close();
            outputStream.close();
        }
    }
    private List<AAAA> get96Time() {
        List<AAAA> res = new ArrayList<>(96);
        LocalDate localDate = LocalDate.now();
        LocalDateTime now = LocalDateTime.of(localDate.getYear(), localDate.getMonth(), localDate.getDayOfMonth(), 0, 0);
        for (int i = 1; i < 96; i++) {
            AAAA a = new AAAA();
            String format = now.plusMinutes(15 * i).format(DateTimeFormatter.ofPattern("HH:mm"));
            a.setName(format);
            a.setAge(i);
            res.add(a);
        }
        return res;
    }

    @Data
    class AAAA {
        private Integer age;
        private String name;

    }
}

4、最后导出样式展示