EasyExcel导出类展示:(包括多个url图片地址)
图片类ExcelUrlConverterUtil 采用这个进行图片解析
package com.segi.uhomecp.temprecord.vo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.segi.uhomecp.temprecord.handler.ExcelUrlConverterUtil;
import java.net.URL;
import java.util.List;
@ContentRowHeight(15)
@HeadRowHeight(20)
@Data
public class TaskTempRecordExport {
@ColumnWidth(25)
@ExcelProperty(value = "检查类别")
private String configClassName;
@ColumnWidth(20)
@ExcelProperty(value = "计划名称")
private String planName;
@ColumnWidth(20)
@ExcelProperty(value = "位置")
private String position;
@ColumnWidth(20)
@ExcelProperty(value = "描述")
private String remark;
@ColumnWidth(20)
@ExcelProperty(value = "图片" ,converter = ExcelUrlConverterUtil.class)
private List<URL> writeCellDataFile;
@ColumnWidth(20)
@ExcelProperty(value = "创建人")
private String createName;
@ColumnWidth(20)
@ExcelProperty(value = "创建时间")
private String createDate;
@ColumnWidth(20)
@ExcelProperty(value = "修改人")
private String updateName;
@ColumnWidth(20)
@ExcelProperty(value = "修改时间")
private String updateDate;
@ColumnWidth(20)
@ExcelProperty(value = "类型")
private String recordResultName;
@ExcelIgnore
private String groupId;
@ExcelIgnore
private String organId;
@ExcelIgnore
private String planId;
@ExcelIgnore
private String recordId;
@ExcelIgnore
private String status;
@ExcelIgnore
private String taskId;
@ExcelIgnore
private String tmpRecordId;
@ExcelIgnore
private String updateBy;
@ExcelIgnore
private String subScore;
@ExcelIgnore
private String riskLevel;
@ExcelIgnore
private String standardId;
@ExcelIgnore
private String standardName;
/**
* 图片路径
*/
@ExcelIgnore
private List<String> imagePathList;
}
ExcelUrlConverterUtil 图片工具,在拦截器中会根据这个查询对应的图片类进行解析操作:
package com.segi.uhomecp.temprecord.handler;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class ExcelUrlConverterUtil implements Converter<List<URL>> {
@Override
public Class supportJavaTypeKey() {
return List.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.EMPTY;
}
@Override
public List convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
@Override
public WriteCellData<?> convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
// 这里进行对数据实体类URL集合处理
List<ImageData> data = new ArrayList<>();
ImageData imageData;
// for 循环一次读取
for (URL url : value) {
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.isNotEmpty(data)) {
// 图片返回图片列表
cellData.setImageDataList(data);
cellData.setType(CellDataTypeEnum.EMPTY);
} else {
// 没有图片使用汉字表示
cellData.setStringValue("无图");
cellData.setType(CellDataTypeEnum.STRING);
}
return cellData;
}
}
CustomImageModifyHandler 拦截器,拦截有图片的数据进行压缩,缩略,插入单元格
package com.segi.uhomecp.temprecord.handler;
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.collections.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);
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
drawing.createPicture(anchor, index);
}
}
最后一步,导出数据(包含url图片处理)
/**
* 生成有图片的excel
*
* @param demoDataList 需要导出数据列表(展示的Excel数据)
* @param response 生成文件路径
*/
private void genImageExcel(List<TaskTempRecordExport> demoDataList, HttpServletResponse response) throws IOException {
if (CollectionUtils.isEmpty(demoDataList)) {
return;
}
//图片列最大图片数
AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
demoDataList.forEach(item -> {
//最大图片数大小
if (!CollectionUtils.isEmpty(item.getImagePathList()) && item.getImagePathList().size() > maxImageSize.get()) {
maxImageSize.set(item.getImagePathList().size());
}
});
//设置图片的url,添加到展示的url上
demoDataList.forEach(item -> {
List<URL> urls=new ArrayList<>();
List<String> imagePathList = item.getImagePathList();
if(imagePathList!=null&&!imagePathList.isEmpty()){
for (String value : imagePathList) {
try {
if(!StringUtils.isEmpty(value)){//value:就是图片后续地址
//添加服务器的地址 SOURCE_PATH:http://www.baidu.com
String urlPath = SOURCE_PATH + value;
urls.add(new URL(urlPath));
}
} catch (MalformedURLException e) {
logger.error("查询到的url转换展示url", e);
}
}
item.setWriteCellDataFile(urls);
}
});
logger.debug("需要导出临时记录的数据:"+ JSON.toJSONString(demoDataList));
//导出临时记录的数据(带图片)
EasyExcel.write(response.getOutputStream(), TaskTempRecordExport.class)
.autoCloseStream(true)
.registerWriteHandler(new CustomImageModifyHandler())
.sheet("临时记录")
.doWrite(demoDataList);
}
效果图: