easyExcel数据库数据导出excel并保存到OSS

引入jar包

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>1.1.2-beta5</version>
</dependency>

1，定义表头字段

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

import java.util.Date;


@Data
public class ExcelVo extends BaseRowModel {

    @ExcelProperty(value = {"访客编号"},index = 0)
    private String number;

    @ExcelProperty(value = {"信息id"},index = 1)
    private Long infoId;

    @ExcelProperty(value = {"访客标签"},index = 2)
    private String tag;

    @ExcelProperty(value = {"总对话数"},index = 3)
    private int x;

    @ExcelProperty(value = {"客服对话数"},index = 4)
    private int y;

    @ExcelProperty(value = {"访客对话数"},index = 5)
    private int z;

    @ExcelProperty(value = {"对话时间"},index = 6)
    private Date contactTime;

    @ExcelProperty(value = {"访问标签"},index = 7)
    private String visitedSource;

    @ExcelProperty(value = {"访问来源处理后"},index = 8)
    private String visitedSourceDealed;

    @ExcelProperty(value = {"来源风格"},index = 9)
    private String sourceStyle;

    @ExcelProperty(value = {"咨询页面"},index = 10)
    private String visitedPage;

    @ExcelProperty(value = {"咨询页面处理后"},index = 11)
    private String visitedPageDealed;

    @ExcelProperty(value = {"一级行业"},index = 12)
    private String category;

    @ExcelProperty(value = {"信息地区"},index = 13)
    private String area;

    @ExcelProperty(value = {"访问地区处理后"},index = 14)
    private String visitedAreaDealed;

2，excel工具实例工厂,这样我们可以直接使用@Autowired注解把这个实例对象拿出来了

@Component
public class ExcelFactory<T extends BaseRowModel> {

    public ExportExcelUtil<T> createExportExcel() {
        return new ExportExcelUtil<>();
    }
}

3，数据导出

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.guoxin.admin.modules.your.utils.OssUtil;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * @Author: 
 * @Date: 
 * excel导出工具类,这里是直接通过response的方式返回我们创建好的excel对象。
 *  这样的话是直接通过网页的方式直接下载返回文件的。
 */
public class ExportExcelUtil<T extends BaseRowModel> {

    public ExportExcelUtil() {}

    /**
     * 导出excel表格
     */
    public void exportExcel(HttpServletResponse response, List<T> data, Class<T> clazz, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        // 注意，这里filename*=utf-8''是为了避免导出的文件名乱码！！！
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            //ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            StyleExcelHandler handler = new StyleExcelHandler();
            //ExcelWriter writer = new ExcelWriter(null,out, ExcelTypeEnum.XLSX,true);
            ExcelWriter writer = new ExcelWriter(null,out, ExcelTypeEnum.XLSX,true,handler);
            Sheet sheet1 = new Sheet(1, 0, clazz);
            sheet1.setSheetName("sheet1");
            writer.write(data, sheet1);
            writer.finish();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }

    public void createExcel(ByteArrayOutputStream out,List<T> data, Class<T> clazz) throws IOException{
        try {
            //StyleExcelHandler handler = new StyleExcelHandler();
            ExcelWriter writer = new ExcelWriter(null,out, ExcelTypeEnum.XLSX,true);
            Sheet sheet1 = new Sheet(1, 0, clazz);
            sheet1.setSheetName("在线咨询数据分析表");
            writer.write(data, sheet1);
            writer.finish();

        }
        finally {
            if (out != null) {
                out.close();
            }
        }
    }
}

4，上传到oss

import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.ObjectMetadata;
import com.aliyun.oss.model.PutObjectResult;
import com.aliyuncs.utils.StringUtils;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Random;


@Data
@Component
public class OssUtil {
    protected static final Logger log = LoggerFactory.getLogger(OssUtil.class);

    private String endpoint = "";
    private String accessKeyId = "";
    private String accessKeySecret = "";
    private String bucketName = "";

    //文件存储目录
    private String filedir = "mall/";
    private String endDir = "/";

    /**
     *
     * 上传图片
     * @param file
     * @return
     */
    public String uploadImg2Oss(MultipartFile file,Long id) {
        if (file.getSize() > 1024 * 1024 *20) {
            return "图片太大";//RestResultGenerator.createErrorResult(ResponseEnum.PHOTO_TOO_MAX);
        }
        String originalFilename = file.getOriginalFilename();
        String substring = originalFilename.substring(originalFilename.lastIndexOf(".")).toLowerCase();
        Random random = new Random();
        String name = random.nextInt(10000) + System.currentTimeMillis() + substring;
        try {
            InputStream inputStream = file.getInputStream();
            this.uploadFile2OSS(inputStream, name,id);
            return name;//RestResultGenerator.createSuccessResult(name);
        } catch (Exception e) {
            return "上传失败";//RestResultGenerator.createErrorResult(ResponseEnum.PHOTO_UPLOAD);
        }
    }

    /**
     * 上传图片获取fileUrl
     * @param instream
     * @param fileName
     * @return
     */
    private String uploadFile2OSS(InputStream instream, String fileName, Long id) {
        String ret = "";
        this.setFiledir("mall/");
        try {
            //创建上传Object的Metadata
            ObjectMetadata objectMetadata = new ObjectMetadata();
            objectMetadata.setContentLength(instream.available());
            objectMetadata.setCacheControl("no-cache");
            objectMetadata.setHeader("Pragma", "no-cache");
            objectMetadata.setContentType(getcontentType(fileName.substring(fileName.lastIndexOf("."))));
            objectMetadata.setContentDisposition("inline;filename=" + fileName);
            //上传文件

            OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
            this.setFiledir(this.getFiledir() + id + "/");
            PutObjectResult putResult = ossClient.putObject(bucketName, this.getFiledir() + fileName, instream, objectMetadata);
            ret = putResult.getETag();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (instream != null) {
                    instream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return ret;
    }


    public String uploadExcel2OSS(InputStream instream, String fileName) {
        String ret = "";
        this.setFiledir("osc/");
        //String fileDir = "osc/";
        try {
            //创建上传Object的Metadata
            ObjectMetadata objectMetadata = new ObjectMetadata();
            objectMetadata.setContentLength(instream.available());
            objectMetadata.setCacheControl("no-cache");
            objectMetadata.setHeader("Pragma", "no-cache");
            objectMetadata.setContentType("application/vnd.ms-excel");
            objectMetadata.setContentDisposition("inline;filename=" + URLEncoder.encode(fileName,"UTF-8"));
            //上传文件

            OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
            PutObjectResult putResult = ossClient.putObject(bucketName, this.filedir + fileName, instream, objectMetadata);
            ret = putResult.getETag();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
        } finally {
            try {
                if (instream != null) {
                    instream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //return ret;
        String key = this.filedir + fileName.substring(0,fileName.lastIndexOf("."));
        return getUrl(key);
    }


    public static String getcontentType(String FilenameExtension) {
        if (FilenameExtension.equalsIgnoreCase(".bmp")) {
            return "image/bmp";
        }
        if (FilenameExtension.equalsIgnoreCase(".gif")) {
            return "image/gif";
        }
        if (FilenameExtension.equalsIgnoreCase(".jpeg") ||
                FilenameExtension.equalsIgnoreCase(".jpg") ||
                FilenameExtension.equalsIgnoreCase(".png")) {
            return "image/jpeg";
        }
        if (FilenameExtension.equalsIgnoreCase(".html")) {
            return "text/html";
        }
        if (FilenameExtension.equalsIgnoreCase(".txt")) {
            return "text/plain";
        }
        if (FilenameExtension.equalsIgnoreCase(".vsd")) {
            return "application/vnd.visio";
        }
        if (FilenameExtension.equalsIgnoreCase(".pptx") ||
                FilenameExtension.equalsIgnoreCase(".ppt")) {
            return "application/vnd.ms-powerpoint";
        }
        if (FilenameExtension.equalsIgnoreCase(".docx") ||
                FilenameExtension.equalsIgnoreCase(".doc")) {
            return "application/msword";
        }
        if (FilenameExtension.equalsIgnoreCase(".xml")) {
            return "text/xml";
        }
        return "image/jpeg";
    }

    /**
     * 获取图片路径
     * @param fileUrl
     * @return
     */
    public String getImgUrl(String fileUrl) {
        if (!StringUtils.isEmpty(fileUrl)) {
            String[] split = fileUrl.split("/");
            String url =  this.getUrl(this.getFiledir() + split[split.length - 1]);
            return url;
        }
        return null;
    }

    /**
     * 获得url链接
     *
     * @param key
     * @return
     */
    public String getUrl(String key) {
        // 设置URL过期时间为10年  3600l* 1000*24*365*10
        Date expiration = new Date(new Date().getTime() + 3600l * 1000 * 24 * 365 * 10);
        // 生成URL
        OSSClient ossClient = new OSSClient(endpoint, accessKeyId, accessKeySecret);
        URL url = ossClient.generatePresignedUrl(bucketName, key, expiration);
        if (url != null) {
            return url.toString();
        }
        return null;
    }


    /**
     * 多图片上传
     * @param fileList
     * @return
     */
    public String checkList(List<MultipartFile> fileList, Long id) {
        String  fileUrl = "";
        String  str = "";
        String  photoUrl = "";
        for(int i = 0;i< fileList.size();i++){
            fileUrl = uploadImg2Oss(fileList.get(i),id);
            str = getImgUrl(fileUrl);
            if(i == 0){
                photoUrl = str;
            }else {
                photoUrl += "," + str;
            }
        }
        return photoUrl.trim();
    }

    /**
     * 单个图片上传
     * @param file
     * @return
     */
    public String checkImage(MultipartFile file,Long id){
        String fileUrl = uploadImg2Oss(file,id);
        String str = getImgUrl(fileUrl);
        return str.trim();
    }
}

核心代码：

//从数据库查询数据
List<NewData> results = newDataDao.findAll(spec(dataVo));
ByteArrayOutputStream out = new ByteArrayOutputStream();
String fileName = "数据分析表_测试" + new Random().nextInt(10000) + System.currentTimeMillis()+".xlsx";
//生成excel
excelFactory.createExportExcel().createExcel(out,voList, ExcelVo.class);
//上传到oss
OssUtil ossUtil = new OssUtil();
String url = ossUtil.uploadExcel2OSS(new ByteArrayInputStream(out.toByteArray()),fileName);
//该url可直接返回给前端下载
String returnUrl = url.substring(0,url.lastIndexOf("?"))+".xlsx";