EasyExcel导出图片(包含多张)
主要pom
<!--图片压缩缩略图-->
<dependency>
<groupId>net.coobird</groupId>
<artifactId>thumbnailator</artifactId>
<version>0.4.8</version>
</dependency>
<!--EasyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
<scope>compile</scope>
</dependency>
easyecxel多图片导出到单元格转换工具类
package com.excel.coverter; /** * @author:pao * @date: 2024-06-18 10:25 **/ import com.alibaba.excel.converters.Converter; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.GlobalConfiguration; import com.alibaba.excel.metadata.property.ExcelContentProperty; import com.alibaba.excel.util.IoUtils; import lombok.extern.slf4j.Slf4j; import net.coobird.thumbnailator.Thumbnails; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.net.URL; import java.util.ArrayList; import java.util.List; import java.util.concurrent.CompletableFuture; import java.util.concurrent.CountDownLatch; import java.util.concurrent.TimeUnit; /** * @Description: easyecxel多图片导出到单元格转换工具类 * @Version 1.0 */ @Slf4j public class ListUrlConverter implements Converter<List<URL>> { @Override public Class supportJavaTypeKey() { return List.class; } @Override public CellDataTypeEnum supportExcelTypeKey() { /** *这里记得枚举类型为IMAGE */ return CellDataTypeEnum.IMAGE; } @Override public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { return null; } @Override public CellData convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception { // 这里进行对数据实体类URL集合处理 List<CellData> data = new ArrayList<>(); // for 循环一次读取 for (URL url : value) { try (InputStream inputStream = url.openStream()) { log.info("读取图片：{}", url); byte[] bytes = IoUtils.toByteArray(inputStream); while (hasMinimumSize(bytes, 30 * 1024)) { bytes = compressPictureForScale(bytes, 30); } // byte[] compressImg = compressPictureForScale(bytes, 30); data.add(new CellData(bytes)); } catch (Exception e) { log.error("导出excel图片读取异常"); e.printStackTrace(); } } // 这种方式并不能返回一个List,所以只好通过CellData cellData = new CellData(data);将这个list对象塞到返回值CellData对象的data属性中； CellData cellData = new CellData(data); cellData.setType(CellDataTypeEnum.IMAGE); return cellData; }
// 图片压缩，图片大于最小值，一直压缩 private boolean hasMinimumSize(byte[] bytes, int minimumSize) { return bytes != null && bytes.length >= minimumSize; } /** * 根据指定大小压缩图片 * * @param imageBytes 源图片字节数组 * @param desFileSize 指定图片大小，单位kb * @return 压缩质量后的图片字节数组 */ public byte[] compressPictureForScale(byte[] imageBytes, long desFileSize) { if (imageBytes == null || imageBytes.length <= 0 || imageBytes.length < desFileSize * 1024) { return imageBytes; } long srcSize = imageBytes.length; double accuracy = getAccuracy(srcSize / 1024); try { while (imageBytes.length > desFileSize * 1024) { ByteArrayInputStream inputStream = new ByteArrayInputStream(imageBytes); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(imageBytes.length); Thumbnails.of(inputStream).scale(accuracy).outputQuality(accuracy).toOutputStream(outputStream); imageBytes = outputStream.toByteArray(); } log.info("【图片压缩】 图片原大小={}kb | 压缩后大小={}kb", srcSize / 1024, imageBytes.length / 1024); } catch (Exception e) { log.error("【图片压缩】msg=图片压缩失败!", e); } return imageBytes; } /** * 自动调节精度(经验数值) * * @param size 源图片大小 * @return 图片压缩质量比 */ private double getAccuracy(long size) { double accuracy; if (size < 900) { accuracy = 0.85; } else if (size < 2047) { accuracy = 0.6; } else if (size < 3275) { accuracy = 0.44; } else { accuracy = 0.4; } return accuracy; } }
图片信息修改拦截器
package com.excel; import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.CellData; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.handler.CellWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteTableHolder; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.util.Units; import java.util.ArrayList; import java.util.List; /** * @Description: 图片信息修改拦截器 * @Version 1.0 */ public class CustomImageModifyHandler implements CellWriteHandler { private List<String> repeats = new ArrayList<>(); // 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置） private Integer maxDataSize = 6; @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 在 数据转换成功后 不是头就把类型设置成空 if (isHead) { return; } //将要插入图片的单元格的type设置为空,下面再填充图片 if(cellData.getImageValue()!=null||cellData.getData() instanceof ArrayList){ cellData.setType(CellDataTypeEnum.EMPTY); } } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { // 在 单元格写入完毕后 ，自己填充图片 if (isHead || CollectionUtils.isEmpty(cellDataList)) { return; } Boolean listFlag = false; ArrayList data = null; Sheet sheet = cell.getSheet(); // 此处为ListUrlConverterUtil的返回值 if (cellDataList.get(0).getData() instanceof ArrayList){ data = (ArrayList) cellDataList.get(0).getData(); if (CollectionUtils.isEmpty(data)) { return; } if (data.get(0) instanceof CellData){ CellData cellData = (CellData) data.get(0); if (cellData.getImageValue() == null){ return; }else { listFlag = true; } } } if (!listFlag && cellDataList.get(0).getImageValue() == null){ return; } String key = cell.getRowIndex() + "_" + cell.getColumnIndex(); if (repeats.contains(key)){ return; } repeats.add(key); if (data.size() > maxDataSize) { maxDataSize = data.size(); } // 默认要导出的图片大小为175*175px,175px的行高大约是900,175px列宽大概是25*256 sheet.getRow(cell.getRowIndex()).setHeight((short)900); sheet.setColumnWidth(cell.getColumnIndex(),listFlag?240*8*maxDataSize:240*8); if (listFlag){ for (int i = 0; i < data.size(); i++) { CellData cellData= (CellData) data.get(i); if(cellData.getImageValue()==null){ continue; } this.insertImage(sheet,cell,cellData.getImageValue(),i); } }else { // cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个 this.insertImage(sheet,cell,cellDataList.get(0).getImageValue(),0); } } private void insertImage(Sheet sheet,Cell cell,byte[] pictureData,int i){ int picWidth = Units.pixelToEMU(60); int index = sheet.getWorkbook().addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG); Drawing drawing = sheet.getDrawingPatriarch(); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); } CreationHelper helper = sheet.getWorkbook().getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); // 设置图片坐标 anchor.setDx1(picWidth*i); anchor.setDx2(picWidth+picWidth*i); anchor.setDy1(0); anchor.setDy2(0); //设置图片位置 anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex()); anchor.setRow1(cell.getRowIndex()); anchor.setRow2(cell.getRowIndex() + 1); // 设置图片可以随着单元格移动 anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE); drawing.createPicture(anchor, index); } }
使用；
ListUrlConverter用在属性转换器上，这里需要把图片路径转换成url
List<URL> dangerImgUrls = new ArrayList<>(); dangerImgUrls.add(new URL(imgEntity.getThumbnailUrl()));
部分代码：
CustomImageModifyHandler使用
ByteArrayOutputStream byteArrayOutputStream = null; try { byteArrayOutputStream = new ByteArrayOutputStream(); EasyExcel.write(byteArrayOutputStream) .head(DangerExportVO.class) .sheet(moduleName) .registerWriteHandler(new CustomImageModifyHandler()) .doWrite(dangerExportVOS); return byteArrayOutputStream.toByteArray(); } catch (Exception e) { // 添加异常处理，例如记录日志 log.error("导出日志输出"); e.printStackTrace(); // 示例: 实际应用中应使用更合适的日志记录方式 throw e; // 或者根据具体情况处理异常 } finally { if (byteArrayOutputStream != null) { try { byteArrayOutputStream.close(); // 确保关闭流 } catch (IOException e) { // 处理关闭时可能出现的异常 e.printStackTrace(); // 示例: 实际应用中应使用更合适的日志记录方式 } } }
演示图
