主要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(); // 示例: 实际应用中应使用更合适的日志记录方式
}
}
}
演示图
标签：return,包含,excel,EasyExcel,导出,cell,CellData,import,com From： https://www.cnblogs.com/yanjj/p/18357623