EasyExcel实现导出图片到excel对应的列

EasyExcel实现导出图片到excel

pom依赖：

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

实体类：

package com.aicut.monitor.vo;
 
import com.aicut.monitor.utils.UrlImageConverter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.springframework.format.annotation.DateTimeFormat;
 
import java.util.Date;
 
/**
 * 豁口图片视图类
 */
@ExcelIgnoreUnannotated
@ContentRowHeight(120)
@Data
public class CutterImageVO extends BaseVO{
 
    private static final long serialVersionUID = 1L;
 
    /**
     * 主键
     */
    @ExcelIgnore
    @Schema(description="主键")
    private Long id;
 
    /**
     * 工厂编码
     */
    @Schema(description="工厂编码")
    @ExcelProperty(value = "工厂编码")
    private String factoryCode;
 
    /**
     * 产线编码
     */
    @Schema(description="产线编码")
    @ExcelProperty(value = "产线编码")
    private String productionLineCode;
 
    /**
     * 设备编号
     */
    @Schema(description="设备编号")
    @ExcelProperty(value = "设备编号")
    private String deviceNumber;
 
    /**
     * 设备名称
     */
    @Schema(description="设备名称")
    @ExcelProperty(value = "设备名称")
    private String deviceName;
 
    /**
     * 分切刀编号
     */
    @Schema(description="分切刀编号")
    @ExcelProperty(value = "分切刀编号")
    private String cutterCode;
 
    /**
     * 是否磨损
     */
    @Schema(description="是否磨损")
    @ExcelProperty(value = "是否磨损")
    private Integer wearOrNot;
 
    /**
     * 检测时间
     */
    @Schema(description="检测时间")
    @ExcelProperty(value = "检测时间")
    @JsonFormat(timezone="GMT+8", pattern="yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @ColumnWidth(20)
    private Date detectionTime;
 
    /**
     * 图片路径
     */
    @Schema(description="图片路径")
    @ExcelProperty(value = "豁口图片",converter = UrlImageConverter.class)
    @ColumnWidth(20)
    private String imageUrl;
 
    /**
     * 建议操作
     */
    @Schema(description="建议操作")
    @ExcelProperty(value = "建议操作")
    private String remark;
}

导出excel部分代码：

        String fileName = "豁口图片数据.xlsx";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        try {
            EasyExcel.write(response.getOutputStream(), CutterImageVO.class)
                    .sheet("豁口图片数据")
                    .doWrite(cutterImageVOList);
        }catch (Exception e){
            log.error(e.getMessage());
        }

String类型图片转换器：

package com.aicut.monitor.utils;
 
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;
 
/**
 * @Description 图片处理
 * @Author 
 * @Date 
 **/
@Slf4j
public class UrlImageConverter 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();
            }
        }
    }
}

导出效果：