EasyExcel导出图片到excel

 需求：

1.查询依赖存储库

https://mvnrepository.com

2.导入依赖（找一个适合的版本）

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

3.创建工具类

package com.common.util;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.ObjectUtils;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
@Slf4j
public class ImagUrlUtil implements Converter<String> {
    public static int urlConnectTimeout = 2000;
    public static int urlReadTimeout = 6000;

    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public WriteCellData<?> convertToExcelData(String url, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws IOException {
        InputStream inputStream = null;
        try {
            URL value = new URL(url);
            if (ObjectUtils.isEmpty(value)){
                return new WriteCellData<>("图片链接为空");
            }
            URLConnection urlConnection = value.openConnection();
            urlConnection.setConnectTimeout(urlConnectTimeout);
            urlConnection.setReadTimeout(urlReadTimeout);
            inputStream = urlConnection.getInputStream();
            byte[] bytes = IoUtils.toByteArray(inputStream);
            return new WriteCellData<>(bytes);
        }catch (Exception e){
            log.info("图片获取异常",e);
            return new WriteCellData<>("图片获取异常");
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
}

4.创建实体类，在实体类图片字段上加注解

@ExcelProperty(value = "豁口图片",converter = ImagUrlUtil .class)

5.编写代码

       1. controller

@GetMapping("/check/export")
    @ApiOperation(value = "盲盒列表导出", httpMethod = "GET")
    public void checkExport(KzBlindBoxDTO dto, HttpServletResponse response) throws IOException {
        String fileName = "盲盒审核列表.xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
        List<KzBlindBoxExportVO> export = kzBlindBoxService.checkExport(dto);
        for (KzBlindBoxExportVO kzBlindBoxExportVO : export) {
            switch (kzBlindBoxExportVO.getStatus()){
                case "1":
                    kzBlindBoxExportVO.setStatus("待审核");
                    break;
                case "2":
                    kzBlindBoxExportVO.setStatus("上链中");
                    break;
                case "3":
                    kzBlindBoxExportVO.setStatus("审核通过");
                    break;
                case "4":
                    kzBlindBoxExportVO.setStatus("审核失败");
                    break;
                case "5":
                    kzBlindBoxExportVO.setStatus("冻结");
                    break;
            }
        }
        EasyExcel.write(response.getOutputStream(), KzBlindBoxExportVO.class).sheet("盲盒审核列表")
                .doWrite(export);
    }

        2.service

List<KzBlindBoxExportVO> checkExport(KzBlindBoxDTO dto);

       3. serviceimpl

@Override
    public List<KzBlindBoxExportVO> checkExport(KzBlindBoxDTO dto) {
        return kzBlindBoxMapper.checkExport(dto);
    }

        4.mapper

List<KzBlindBoxExportVO> checkExport(@Param("dto") KzBlindBoxDTO dto);

        5.mapper

<select id="checkExport" resultType="com.cardAdmin.system.vo.KzBlindBoxExportVO">
        SELECT
        kb.id,
        kb.`name`,
        kb.price,
        ku.certificate_name,
        ku.certificate_number,
        kb.created_time,
        kb.`status`,
        kp.`name` platformName,
        kb.auditor_by
        FROM
        kz_blind_box kb
        LEFT JOIN kz_user ku ON kb.user_id = ku.id
        LEFT JOIN kz_platform kp ON kp.id=kb.platform_id
        <where>
        <if test="dto.status!=null and dto.status!=''">
            AND kb.`status`=#{dto.status}
        </if>
        <if test="dto.submitTimeStart!=null and dto.submitTimeEnd!=null">
            AND kb.submit_time BETWEEN #{dto.submitTimeStart} AND #{dto.submitTimeEnd}
        </if>
        <if test="dto.certificateName!=null and dto.certificateName !=''">
            AND ku.certificate_name like concat('%',#{dto.certificateName},'%')
        </if>
        <if test="dto.id!=null and dto.id !=''">
            AND kb.id like concat('%',#{dto.id},'%')
        </if>
        <if test="dto.auditorBy!=null and dto.auditorBy !=''">
            AND kb.auditor_by like concat('%',#{dto.auditorBy},'%')
        </if>
        <if test="dto.platformId!=null and dto.platformId!=''">
            AND kb.platform_id=#{dto.platformId}
        </if>
        </where>
        ORDER BY kb.created_time DESC
    </select>

6.扩展导出

1.controller

@GetMapping("/copyright/Certification/export")
    @ApiOperation(value = "版权存证审核初审导出", httpMethod = "GET")
    public void copyrightCertification(KzWorksProveExportDTO dto, HttpServletResponse response) throws IOException {
        String fileName = "版权存证审核初审导出.xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
        List<KzWorksProveExportVO> export = kzWorksProveService.copyrightCertification(dto);
        EasyExcel.write(response.getOutputStream(), KzWorksProveExportVO.class).sheet("版权存证审核")
                .doWrite(export);
    }

2.service

List<KzWorksProveExportVO> copyrightCertification(KzWorksProveExportDTO dto);

3. serviceimpl

@Override
    public List<KzWorksProveExportVO> copyrightCertification(KzWorksProveExportDTO dto) {
        List<KzWorksProveExportVO> vos= KzWorksProveMapper.copyrightCertification(dto);
        return vos;
    }

4.mapper

List<KzWorksProveExportVO> copyrightCertification(@Param("dto") KzWorksProveExportDTO dto);

5.mapper

<select id="copyrightCertification" resultType="com.cardAdmin.system.vo.KzWorksProveExportVO">
        SELECT
        kwp.id,
        kwp.`name`,
        u.certificate_name as user_usn,
        kwp.submit_time,
        kwp.pass_time,
        CASE
        WHEN kwp.pass_time IS NULL OR kwp.submit_time IS NULL THEN NULL
        ELSE CONCAT(
        FLOOR(TIMESTAMPDIFF(MINUTE, kwp.submit_time, kwp.pass_time) / (60 * 24)), '天',
        LPAD(FLOOR((TIMESTAMPDIFF(MINUTE, kwp.submit_time, kwp.pass_time) % (60 * 24)) / 60), 2, '0'), '小时',
        LPAD(TIMESTAMPDIFF(MINUTE, kwp.submit_time, kwp.pass_time) % 60, 2, '0'), '分'
        )
        END AS duration,
        CASE
        WHEN kwp.status =2 THEN '待初审 '
        WHEN kwp.status =3 THEN '初审失败'
        WHEN kwp.status =4 THEN '待复审'
        WHEN kwp.status =6 THEN '审核成功'
        WHEN kwp.status =7 THEN '终审失败'
        WHEN kwp.status =8 THEN '初审中'
        END as status,
        kwp.reasons_rejection
        from  kz_works_prove kwp
        LEFT JOIN kz_user u ON u.id = kwp.user_id
        where kwp.deleted_time IS NULL and kwp.status !=1
        <if test="dto.name!=null and dto.name!=''">
            AND name like concat(concat('%',trim(#{dto.name})),'%')
        </if>
        <if test="dto.submitTimeStart!=null and dto.submitTimeEnd !=null">
            AND submit_time BETWEEN #{dto.submitTimeStart} AND CONCAT(DATE_FORMAT(#{dto.submitTimeEnd}, '%Y-%m-%d'), ' 23:59:59')
        </if>
        <if test="dto.status!=null and dto.status!=''">
            AND status = #{dto.status}
        </if>
        ORDER BY kwp.created_time DESC
    </select>