EasyExcel导出多张图片
通过EasyExcel导出多张图片
1. 引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.verion}</version>
</dependency>
2.创建图片转换器
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.ReadCellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.alibaba.excel.util.IoUtils;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.CollectionUtils;
import java.io.InputStream;
import java.net.URL;
import java.util.List;
@Slf4j
public class ImageConvert implements Converter<List<String>> {
@Override
public Class<?> supportJavaTypeKey() {
return List.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.EMPTY;
}
@Override
public List<String> convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
@Override
public WriteCellData<?> convertToExcelData(List<String> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
// 这里进行对数据实体类URL集合处理
List<ImageData> data = ListUtils.newArrayList();
ImageData imageData;
// for 循环一次读取
for (String imageUrl : value) {
URL url = new URL(imageUrl);
try (InputStream inputStream = url.openStream()) {
byte[] bytes = IoUtils.toByteArray(inputStream);
imageData = new ImageData();
imageData.setImage(bytes);
data.add(imageData);
} catch (Exception e) {
log.error("导出临时记录图片异常：", e);
}
}
WriteCellData<?> cellData = new WriteCellData<>();
if (!CollectionUtils.isEmpty(data)) {
// 图片返回图片列表
cellData.setImageDataList(data);
cellData.setType(CellDataTypeEnum.EMPTY);
} else {
// 没有图片使用汉字表示
cellData.setStringValue("无图");
cellData.setType(CellDataTypeEnum.STRING);
}
return cellData;
}
}
3.创建图片处理器
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.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.atomic.AtomicReference;
public class CustomImageModifyHandler implements CellWriteHandler {
/**
* 已经处理的Cell
*/
private final CopyOnWriteArrayList<String> REPEATS = new CopyOnWriteArrayList<>();
/**
* 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置）
*/
private final AtomicReference<Integer> MAX_IMAGE_SIZE = new AtomicReference<>(0);
/**
* 标记手动添加的图片，用于排除EasyExcel自动添加的图片
*/
private final CopyOnWriteArrayList<Integer> CREATE_PIC_INDEX = new CopyOnWriteArrayList<>();
@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, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 在 数据转换成功后 不是头就把类型设置成空
if (isHead) {
return;
}
//将要插入图片的单元格的type设置为空,下面再填充图片
if (CollectionUtils.isNotEmpty(cellData.getImageDataList())) {
cellData.setType(CellDataTypeEnum.EMPTY);
}
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 在 单元格写入完毕后 ，自己填充图片
if (isHead || CollectionUtils.isEmpty(cellDataList)) {
return;
}
boolean listFlag = false;
Sheet sheet = cell.getSheet();
// 此处为ExcelUrlConverterUtil的返回值
List<ImageData> imageDataList = cellDataList.get(0).getImageDataList();
if (CollectionUtils.isNotEmpty(imageDataList)) {
listFlag = true;
}
if (!listFlag && imageDataList == null) {
return;
}
String key = cell.getRowIndex() + "_" + cell.getColumnIndex();
if (REPEATS.contains(key)) {
return;
}
REPEATS.add(key);
if (imageDataList.size() > MAX_IMAGE_SIZE.get()) {
MAX_IMAGE_SIZE.set(imageDataList.size());
}
// 默认要导出的图片大小为60*60px,60px的行高大约是900,60px列宽大概是248*8
sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
sheet.setColumnWidth(cell.getColumnIndex(), listFlag ? 240 * 8 * MAX_IMAGE_SIZE.get() : 240 * 8);
if (listFlag) {
for (int i = 0; i < imageDataList.size(); i++) {
ImageData imageData = imageDataList.get(i);
if (imageData == null) {
continue;
}
byte[] image = imageData.getImage();
this.insertImage(sheet, cell, image, i);
}
} else {
this.insertImage(sheet, cell, imageDataList.get(0).getImage(), 0);
}
// 清除EasyExcel自动添加的没有格式的图片
XSSFDrawing drawingPatriarch = (XSSFDrawing) sheet.getDrawingPatriarch();
List<XSSFShape> shapes = drawingPatriarch.getShapes();
for (int i = 0; i < shapes.size(); i++) {
XSSFShape shape = shapes.get(i);
if (shape instanceof XSSFPicture && !CREATE_PIC_INDEX.contains(i)) {
CREATE_PIC_INDEX.add(i);
XSSFPicture picture = (XSSFPicture) shape;
picture.resize(0);
}
}
}
/**
* 重新插入一个图片
*
* @param sheet Excel页面
* @param cell 表格元素
* @param pictureData 图片数据
* @param i 图片顺序
*/
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);
CREATE_PIC_INDEX.add(index);
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);
//设置图片位置
int columnIndex = cell.getColumnIndex();
anchor.setCol1(columnIndex);
anchor.setCol2(columnIndex);
int rowIndex = cell.getRowIndex();
anchor.setRow1(rowIndex);
anchor.setRow2(rowIndex + 1);
// ClientAnchor.AnchorType里有多种类型可选，从网上看的之前是不移动，直接悬浮在单元格上了，现在这个是随着单元格移动
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
drawing.createPicture(anchor, index);
}
}
4. 在导出类的属性上添加转换器注解
@Schema(description = "图片")
@ExcelProperty(value = "图片",converter = ImageConvert.class)
private List<String> signImages;
5. 导出时添加处理器
try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),ExportDataVO.class)
.autoCloseStream(false)
.registerWriteHandler(new CustomImageModifyHandler()) // 添加处理器
.build()) {
WriteSheet writeSheet = EasyExcel.writerSheet("测试数据").build();
excelWriter.write(dataList, writeSheet);
}
浙公网安备 33010602011771号