当前位置：首页 > news >正文

EasyExcel实现导出图片到excel

news 2025/7/1 3:58:19

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;/*** 豁口图片视图类* @author zhangzhi*/
@ExcelIgnoreUnannotated
@ContentRowHeight(120)
@Getter
@Setter
@ToString
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 songwp* @Date 2023/3/30 15:04**/
@Slf4j
public class UrlImageConverter implements Converter<String> {public static int urlConnectTimeout = 2000;public static int urlReadTimeout = 6000;@Overridepublic Class<?> supportJavaTypeKey() {return String.class;}@Overridepublic 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();}}}
}

导出效果：