easyExcel后台生成多个sheet的excel并上传到OSS，将文档地址返回给前端

整理下做笔记

1、Controller层

    @ApiOperation("导出")
    @RequestMapping(value = "/exportAnswerAll", method = RequestMethod.POST)
    @ResponseBody

    public Result exportAnswerAll(QuizUser quizUser) throws IOException {
        return Result.success(quizUserService.exportOSS(quizUser));
    }

2、Service层

2.1当只生成一个sheet时

    public String exportOSS(QuizUser quizUser) {
        List<QuizAnswerLevel> list = this.exportAnswer(quizUser);

        ByteArrayOutputStream out = new ByteArrayOutputStream();

        EasyExcel.write(out, QuizAnswerLevel.class).sheet("模板").doWrite(list);
        String url = resourceService.upload(new ByteArrayInputStream(out.toByteArray()), ExcelTypeEnum.XLSX.getValue(), "easyexcel");
        return url;

    }

 2.2、当生成多个sheet时

    public String exportOSS(QuizUser quizUser) {
        Map<String, Object> map = this.exportAnswer(quizUser);

        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter excelWriter = null;

        try {
            // 这里 指定文件
            excelWriter = EasyExcel.write(out).build();

            WriteSheet allSheet = EasyExcel.writerSheet(0, "总体概览").head(QuizAnswerLevel.class).build();
            excelWriter.write((List) map.get("all"), allSheet);

            WriteSheet citySheet = EasyExcel.writerSheet(1, "市人数占比").head(QuizAnswerPlace.class).build();
            excelWriter.write((List) map.get("city"), citySheet);

            WriteSheet areaSheet = EasyExcel.writerSheet(2, "区人数占比").head(QuizAnswerPlace.class).build();
            excelWriter.write((List) map.get("area"), areaSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }


        String b = resourceService.upload(new ByteArrayInputStream(out.toByteArray()), ExcelTypeEnum.XLSX.getValue(), "easyexcel");
        return b;

    }

 3、OSS上传

    /**
     * 上传
     */
    public String upload(InputStream is, String extension, String sourceType) {

        // 根据扩展名生成一个文件路径
        String fileName = createFilePath(sourceType, extension);

        //上传到oss
        return ossFileService.uploadFile(is, fileName);
    }

    // 根据扩展名生成一个文件路径
    public String createFilePath(String sourceType, String extension) {
        String id = String.valueOf(CurrentTimeMillisId.next());

        // 保证 extension 以 . 开头
        extension = (extension.startsWith(".") ? extension : ("." + extension));
        return "yu/" + sourceType + "/" + FORMAT.format(new Date()) + id + extension;
    }

 OSSFileService.java中的引用代码

    public String uploadFile(InputStream input, String uploadPath) {
        this.ossFileClient.uploadFile(input, uploadPath);
        return ossUrlPath + uploadPath;
    }

 OSSFileService.java完整代码

import lombok.Setter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;
import sun.misc.BASE64Decoder;

import javax.annotation.PostConstruct;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

/**
 * OSS 文件上传下载服务
 */
@Component
@Setter
@ConditionalOnProperty(name = "aliyun.oss.bucket")
public class OSSFileService {

    @Value("${aliyun.oss.bucket}")
    private String ossBucket;

    @Value("${aliyun.oss.url.path}")
    protected String ossUrlPath;

    @Value("${aliyun.oss.endpoint}")
    protected String endpoint;

    @Autowired
    private STSService stsService;

    private OSSFileClient ossFileClient;

    private static final Logger LOG = LoggerFactory.getLogger(OSSFileService.class);

    @PostConstruct
    private void init() {

        OSSTempCridentialKeeper2 cridentialKeeper = new MyOSSTempCridentialKeeper(this.ossBucket);
        cridentialKeeper.setEndpoint(endpoint);

        this.ossFileClient = new OSSFileClient(cridentialKeeper);
    }

    public void uploadFile(String filePath, String uploadPath) {
        this.ossFileClient.uploadFile(new File(filePath), uploadPath);
    }

    public void uploadFile(File file, String uploadPath) {
        this.ossFileClient.uploadFile(file, uploadPath);
    }

    public String uploadFile(InputStream input, String uploadPath) {
        this.ossFileClient.uploadFile(input, uploadPath);
        return ossUrlPath + uploadPath;
    }

    /**
     * 通过base64上传文件
     * 将字符串转换为byte数组，这里的content是那一串base64密文 /9j/4AAQ.................，不包含（data:img/jpg;base64,）注意标点符号
     *
     * @param content    内容
     * @param uploadPath 上传地址（包含文件名）
     */
    @Async
    public void uploadBase64File(String content, String uploadPath) {
        try {
            //替换掉前缀
            content = content.replaceAll("data:(.*);base64,", "");
            byte[] bytes = new BASE64Decoder().decodeBuffer(content);
            this.ossFileClient.uploadFile(bytes, uploadPath);
        } catch (IOException e) {
            throw new AppException("base64 decode 失败 -> " + e.getMessage());
        }
    }

    /**
     * 上传多个文件（串行）
     *
     * @param inputStreams 包含每个文件的 InputStream 对象和上传到的路径（含文件名）
     */
    @Async
    public void uploadManyFiles(Map<InputStream, String> inputStreams) {
        ossFileClient.uploadManyFiles(inputStreams);
    }

    /**
     * 一次性上传多个文件
     *
     * @param files ?
     */
    public void uploadFiles(Map<File, String> files) {
        this.ossFileClient.uploadFiles(files);
    }


    public void downloadFile(String remotePath, String localPath) {
        this.ossFileClient.downloadFile(remotePath, localPath);
    }

    //////////////////////////////////////////////////////////////

    public class MyOSSTempCridentialKeeper extends OSSTempCridentialKeeper2 {

        MyOSSTempCridentialKeeper(String bucketName) {
            super(bucketName);
        }

        @Override
        public OSSTempCridential getOSSOssTempCridential() {
            try {
                AliyunTempCridential cridential = stsService.getTempCridential(bucketName, endpoint);
                OSSTempCridential ossCridential = new OSSTempCridential();
                ossCridential.setAccessKeyId(cridential.getAccessKeyId());
                ossCridential.setAccessKeySecret(cridential.getAccessKeySecret());
                ossCridential.setBucketName(cridential.getBucketName());
                ossCridential.setEndpoint(cridential.getEndpoint());
                ossCridential.setExpiration(cridential.getExpiration());
                ossCridential.setSecurityToken(cridential.getSecurityToken());
                return ossCridential;
            } catch (Exception e) {
                LOG.error("初始化OSS失败", e);
                throw new AppException(e);
            }
        }
    }
}

4、若发送到web可按如下操作

        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("统计结果", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ExcelTypeEnum.XLSX.getValue());
            excelWriter = EasyExcel.write(response.getOutputStream()).build();

            WriteSheet documentSheet = EasyExcel.writerSheet(0, "文档统计").head(DocumentIndex.class).build();
            excelWriter.write(pageViewDocumentList, documentSheet);

            WriteSheet serviceSheet = EasyExcel.writerSheet(1, "服务统计").head(ServiceIndex.class).build();
            excelWriter.write(pageViewServiceList, serviceSheet);


        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> maps = new HashMap<>(16);
            maps.put("status", "failure");
            maps.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(maps));
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

纯手敲原创不易，如果觉得对你有帮助，可以打赏支持一下，哈哈，感谢~