在使用 EasyExcel 导出 Excel 时，如果某个单元格是图片内容，且存在多张图片，此时就需要单元格根据图片数量动态设置宽度。
经过自己的研究和实验，导出效果如下：
具体代码如下：
- EasyExcel 版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
- 定义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;
}
}
- 定义图片属性
@ExcelProperty(value = "现场图片(模拟机器人视角)", converter = ExcelImageUrlConverterUtils.class)
private List<String> images;
- 定义单元格图片写入拦截器
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);
}
}
- 写入时引用
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
