EasyExcel 单元格根据图片数量动态设置宽度

在使用 EasyExcel 导出 Excel 时，如果某个单元格是图片内容，且存在多张图片，此时就需要单元格根据图片数量动态设置宽度。
经过自己的研究和实验，导出效果如下：

具体代码如下：

1. EasyExcel 版本

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

2. 定义Excel图片链接转换工具

   import com.alibaba.excel.converters.Converter;
   import com.alibaba.excel.enums.CellDataTypeEnum;
   import com.alibaba.excel.metadata.GlobalConfiguration;
   import com.alibaba.excel.metadata.data.ImageData;
   import com.alibaba.excel.metadata.data.WriteCellData;
   import com.alibaba.excel.metadata.property.ExcelContentProperty;
   import com.alibaba.excel.util.IoUtils;
   import java.net.URL;
   import java.util.ArrayList;
   import java.util.List;
   
   /**
    * Excel图片链接转换工具
    *
    * @author 天航星
    * @date 2024-07-05 15:03
    */
   public class ExcelImageUrlConverterUtils implements Converter<List<String>> {
   
   	@Override
   	public CellDataTypeEnum supportExcelTypeKey() {
   		return CellDataTypeEnum.STRING;
   	}
   
   	@Override
   	public WriteCellData<?> convertToExcelData(List<String> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
   
   		List<ImageData> imageDataList = new ArrayList<>();
   		for (String url : value) {
   			try {
   				URL imageUrl = new URL(url);
   				byte[] bytes = IoUtils.toByteArray(imageUrl.openConnection().getInputStream());
   				ImageData imageData = new ImageData();
   				imageData.setImage(bytes);
   				imageDataList.add(imageData);
   			} catch (Exception e) {
   				e.printStackTrace();
   			}
   		}
   		WriteCellData writeCellData = new WriteCellData();
   		writeCellData.setImageDataList(imageDataList);
   		writeCellData.setType(CellDataTypeEnum.STRING);
   		return writeCellData;
   	}
   }
   

3. 定义图片属性

   @ExcelProperty(value = "现场图片(模拟机器人视角)", converter = ExcelImageUrlConverterUtils.class)
   private List<String> images;
   

4. 定义单元格图片写入拦截器

   import cn.hutool.core.util.ObjUtil;
   import com.alibaba.excel.enums.CellDataTypeEnum;
   import com.alibaba.excel.metadata.Head;
   import com.alibaba.excel.metadata.data.ImageData;
   import com.alibaba.excel.metadata.data.WriteCellData;
   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.poi.hssf.usermodel.HSSFWorkbook;
   import org.apache.poi.ss.usermodel.Cell;
   import org.apache.poi.ss.usermodel.ClientAnchor;
   import org.apache.poi.ss.usermodel.CreationHelper;
   import org.apache.poi.ss.usermodel.Drawing;
   import org.apache.poi.ss.usermodel.Sheet;
   import org.apache.poi.util.Units;
   import java.util.ArrayList;
   import java.util.HashMap;
   import java.util.List;
   import java.util.Map;
   import java.util.concurrent.atomic.AtomicReference;
   
   /**
    * 单元格图片写入拦截器
    * 
    * @author 天航星
    * @date 2024-07-05 15:03
    */
   public class ImageCellWriteHandler implements CellWriteHandler {
   
   	private final Map<String,List<ImageData>> imageDataMap = new HashMap<>();
   
   	/**
   	 * 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置）
   	 */
   	private final AtomicReference<Integer> MAX_IMAGE_SIZE = new AtomicReference<>(0);
   
   	@Override
   	public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
   
   		if (isHead) {
   			return;
   		}
   		// 将单元格图片数据复制出来，清空单元格图片数据
   		if (!ObjUtil.isEmpty(cellData.getImageDataList())) {
   			imageDataMap.put(cell.getRowIndex() + "_" + cell.getColumnIndex(), cellData.getImageDataList());
   			cellData.setType(CellDataTypeEnum.EMPTY);
   			cellData.setImageDataList(new ArrayList<>());
   		}
   	}
   
   	@Override
   	public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
   
   		if (isHead || ObjUtil.isEmpty(cellDataList)) {
   			return;
   		}
   		String key = cell.getRowIndex() + "_" + cell.getColumnIndex();
   		List<ImageData> imageDataList = imageDataMap.get(key);
   		if (ObjUtil.isEmpty(imageDataList)) {
   			return;
   		}
   		if (imageDataList.size() > MAX_IMAGE_SIZE.get()) {
   			MAX_IMAGE_SIZE.set(imageDataList.size());
   		}
   		Sheet sheet = cell.getSheet();
   		// 设置单元格行高
   		sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
   		// 设置单元格列宽（乘多少代表容纳多少张图片）
   		sheet.setColumnWidth(cell.getColumnIndex(), MAX_IMAGE_SIZE.get() > 0 ? 3493 * MAX_IMAGE_SIZE.get() : 3493);
   		// 插入图片
   		for (int i = 0; i < imageDataList.size(); i++) {
   			ImageData imageData = imageDataList.get(i);
   			if (ObjUtil.isEmpty(imageData)) {
   				continue;
   			}
   			byte[] image = imageData.getImage();
   			this.insertImage(sheet, cell, image, i);
   		}
   		imageDataMap.remove(key);
   	}
   
   	private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) {
   
   		// 图片宽度
   		int pictureWidth = Units.pixelToEMU(100);
   		int index = sheet.getWorkbook().addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG);
   		Drawing<?> drawing = sheet.getDrawingPatriarch();
   		if (ObjUtil.isEmpty(drawing)) {
   			drawing = sheet.createDrawingPatriarch();
   		}
   		CreationHelper helper = sheet.getWorkbook().getCreationHelper();
   		ClientAnchor anchor = helper.createClientAnchor();
   		// 设置图片在哪个单元格中
   		anchor.setCol1(cell.getColumnIndex());
   		anchor.setCol2(cell.getColumnIndex());
   		anchor.setRow1(cell.getRowIndex());
   		anchor.setRow2(cell.getRowIndex() + 1);
   		// 设置图片在单元格中的位置
   		anchor.setDx1(pictureWidth * i);
   		anchor.setDx2(pictureWidth + pictureWidth * i);
   		anchor.setDy1(0);
   		anchor.setDy2(0);
   		// 设置图片可以随着单元格移动
   		anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
   		drawing.createPicture(anchor, index);
   	}
   }
   

   核心代码：

   // 设置单元格列宽（乘多少代表容纳多少张图片）
   sheet.setColumnWidth(cell.getColumnIndex(), MAX_IMAGE_SIZE.get() > 0 ? 3493 * MAX_IMAGE_SIZE.get() : 3493);
   

5. 写入时引用

   EasyExcel.write("Excel文件名称.xlsx", DemoData.class).sheet("测试").registerWriteHandler(new ImageCellWriteHandler()).doWrite(new DemoData());
   

---

环境：

• JDK：1.8.0_202
• SpringBoot：2.7.17
• EasyExcel：3.3.2