前言
亲身使用,好用
解决:
实体类返回的是逗号隔开的多个图片
图片在Microsoft Office正常显示,在WPS Office显示不了
效果展示
实体类和业务准备
实体类代码
@ApiModelProperty("图片连接")
@ExcelIgnore
private String imgUrls;
@ExcelProperty(value = "图片" ,converter = ExcelUrlConverterUtil.class)
private List<URL> imgUrlList;
业务处理,先查出list,因为后端表里存的是阿里云的图片地址为String,我这里转一下
list.getRows().forEach(item -> {
if (StringUtils.isNotBlank(item.getImgUrls())) {
List<URL> urlList = new ArrayList<>();
Arrays.asList(item.getImgUrls().split(",")).forEach(i -> {
try {
urlList.add(new URL(i));
} catch (MalformedURLException e) {
log.error("临库盘点导出地址不是一个图片" + i);
}
});
item.setImgUrlList(urlList);
}
});
导出代码
try {
// 响应头的信息设置
resetResponse("导出", response);
ServletOutputStream os = response.getOutputStream();
ExcelWriterSheetBuilder builder = ((EasyExcel.write(os, TestDataVO.class)
.autoCloseStream(false)
// 自带的自动处理宽度
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 自定义重新写入图片的Handler
.registerWriteHandler(new CustomImageModifyHandler())
.registerConverter(new ExcelBigNumberConvert()))
.sheet("临库盘点导出"));
builder.doWrite(list.getRows());
} catch (IOException var7) {
throw new RuntimeException("导出Excel异常");
}
2.x版本
pox文件
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
响应头的信息设置
private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
String filename = ExcelUtil.encodingFilename(sheetName);
FileUtils.setAttachmentResponseHeader(response, filename);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
}
首先是标在实体类上的转换
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 java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;
/**
* @author Wang WenLei
* @version 1.0
* @since 2023/6/13 16:10
*/
public class ExcelUrlConverterUtil 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) {
InputStream inputStream = null;
try {
inputStream = url.openStream();
byte[] bytes = IoUtils.toByteArray(inputStream);
data.add(new CellData(bytes));
} catch (Exception e) {
//图片异常展示的图片
data.add(new CellData("无法加载图片"));
continue;
} finally {
if (inputStream != null) {
inputStream.close();
}
}
}
// 这种方式并不能返回一个List,所以只好通过CellData cellData = new CellData(data);将这个list对象塞到返回值CellData对象的data属性中;
CellData cellData = new CellData(data);
cellData.setType(CellDataTypeEnum.IMAGE);
return cellData;
}
}
CustomImageModifyHandler的代码
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
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.*;
import org.apache.poi.util.Units;
import java.util.ArrayList;
import java.util.List;
/**
* @author Wang WenLei
* @version 1.0
* @since 2023/6/13 16:22
*/
public class CustomImageModifyHandler implements CellWriteHandler {
private List<String> repeats = new ArrayList<>();
// 单元格的图片最大张数(每列的单元格图片张数不确定,单元格宽度需按照张数最多的长度来设置)
private Integer maxDataSize = 0;
@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();
// 此处为ExcelUrlConverterUtil的返回值
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();
}
//60px的行高大约是900,60px列宽大概是248*8,根据需要调整
sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
sheet.setColumnWidth(cell.getColumnIndex(), (int) (listFlag ? 21.8 * 256 * maxDataSize : 22.8 * 256));
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(175);
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);
}
}
3.x版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
转换:
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;
/**
* @author Wang WenLei
* @version 1.0
* @since 2023/6/13 16:10
*/
@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;
}
}
填充图片
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;
/**
* @author Wang WenLei
* @version 1.0
* @since 2023/6/13 16:22
*/
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);
}
}
遇到问题
图片在Microsoft Office正常显示,在WPS Office显示不了
这个问题的解决方案:在GitHub中EasyExcel开源项目的Issues里寻到答案,详情可以查看
我的原因:项目使用的3.x版本的easyexcel,默认是4.x的POI但手动升级引入的5.x的POI,版本不兼容导致的问题
我的解决方案:
/**
* 导出数据
* 1.先使用easyExcel导出到临时文件
* 2. 压缩再解压缩解决:手动POI 5.X与easyExcel3.X不兼容问题,导致wps无法展示图片
* 3. 文件转流输出
*
* @param list 数据列表
* @param response 输出流
*/
private void exportDate(List<TestDataVO> list, HttpServletResponse response) {
String path = null;
File file = null;
try {
path = ResourceUtils.getURL("classpath:").getPath() + "file_replace" + System.currentTimeMillis() + ".xlsx";
file = new File(path);
FileUtil.touch(file);
} catch (FileNotFoundException e) {
throw new ServiceException("导出Excel异常,没有路径");
}
try(FileOutputStream tempOut = new FileOutputStream(file)) {
ExcelWriterSheetBuilder builder = ((EasyExcel.write(tempOut, OrderAgentWorkflowVO.class)
.autoCloseStream(true)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new CustomImageModifyHandler())
.registerConverter(new ExcelBigNumberConvert()))
.sheet("临库盘点导出"));
builder.doWrite(list);
} catch (IOException e) {
throw new ServiceException("读取文件失败");
}
// 压缩再解压缩
easyExcelRepairPic(file);
// 循环取出流中的数据
byte[] b = new byte[100];
int len;
try (InputStream inStream = FileUtil.getInputStream(file)) {
resetResponse("临库盘点导出", response);
while ((len = inStream.read(b)) > 0) {
response.getOutputStream().write(b, 0, len);
}
} catch (IOException e) {
throw new ServiceException("读取Excel异常");
} finally {
FileUtil.del(path);
}
}
压缩再解压缩
/**
* 压缩再解压缩,用于解决 easyExcel和poi兼容性的问题,easyExcel目前(3.X)依赖的poi是4.x版本的,
* 如果poi版本变成5.X就会出现这个问题:导出excel,其中图片在Microsoft Office正常显示,在WPS Office显示不了
* 项目中默认引入了5.X的poi导致的
*
* @param file 文件
*/
public void easyExcelRepairPic(File file) {
if (!file.isFile()) {
throw new ServiceException("excel文件不存在");
}
// 创建解压后的临时目录
File tempDir = new File(file.getParent(), IdUtil.fastSimpleUUID());
if(!tempDir.exists()){
// 递归生成文件夹
tempDir.mkdirs();
}
// 解压源文件到临时目录
ZipUtil.unzip(file, tempDir);
// 删除源文件
FileUtil.del(file);
// 压缩临时目录 替换源文件
ZipUtil.zip(file, CharsetUtil.defaultCharset(), false, tempDir);
// 删除临时目录
FileUtil.del(tempDir);
}
完~
有更好的方案请指导
躬身自省,淳朴而谦逊否
前辈见之,如有问题,麻烦留言斧正。