Background: The format of the exported file is different under different conditions. Each of these files contains an image.

1, entity,

import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import lombok.Builder; import lombok.Data; import java.net.URL; /** * @date 2021-03-10 23:32:49 * @author @contentrowheight (200) @columnWidth (50) public class OtherGoodsInfoData {@excelProperty (index = 0, value = "goodsLink ") private String goodsLink; @excelProperty (index = 1, value = "tortPicture ") private URL tortPicture; }Copy the code
import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import lombok.Builder; import lombok.Data; import java.net.URL; /** * @date 2021-03-10 23:32:49 * @author @contentrowheight (200) @columnWidth (50) public class OtherCopyrightGoodsInfoData {@ ExcelProperty (index = 0, value = "product link") private String goodsLink; @excelProperty (index = 1, value = "tortPicture ") private URL tortPicture; @excelProperty (index = 2, value = "") private URL comparePicture; @excelProperty (index = 3, value = "compareUrl ") private String compareUrl; }Copy the code
  • The direct URL is used to export images.

Problems encountered

1. The exported image does not move as the cell moves, just like a sticking plaster.

2. Once the movement is set up, there will be two images in a cell.

3. Images move with each other, but do not adapt to size.

The solution

Problem 1: EasyExcel requires Handler configuration. In afterCellDataConverted method, cellData.setType(CellDataTypeEnum.EMPTY); Property, which means EasyExcel will ignore the cell when writing. So that the image does not fill twice. However, because this needs to be general, the attribute of the URL field in the exported entity class and the index, which is the cell of the image, are ignored when EasyExcel writes.

import cn.afterturn.easypoi.util.PoiPublicUtil; 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.poi.ss.usermodel.*; import java.util.List; import static org.apache.poi.ss.usermodel.ClientAnchor.AnchorType.*; /** * easyExcel ** @author linyuan * @date 2021-03-15 10:45:49 */ public class ImageModifyHandler implements CellWriteHandler { private final List<Integer> ignoreIndexes; public ImageModifyHandler(List<Integer> ignoreIndexes) { this.ignoreIndexes = ignoreIndexes; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { if (! ignoreIndexes.isEmpty()) { if (! ignoreIndexes.contains(head.getColumnIndex()) || aBoolean) { return; } cellData.setType(CellDataTypeEnum.EMPTY); } } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> List, Cell Cell, Head Head, Integer Integer, Boolean aBoolean) { If (head.getColumnIndex()! = 1 || aBoolean || list.isEmpty() || list.get(0).getImageValue() == null) { return; } Sheet sheet = cell.getSheet(); // cellDataList is a list because it is possible to write multiple entries to a cell in the fill case, but there must be only one int index = in the normal write case sheet.getWorkbook().addPicture(list.get(0).getImageValue(), getImageType(list.get(0).getImageValue())); Drawing drawing = sheet.getDrawingPatriarch(); if (drawing == null) { drawing = sheet.createDrawingPatriarch(); } CreationHelper helper = sheet.getWorkbook().getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); // Set image coordinates: anchor.setdx1 (1); anchor.setDx2(1); anchor.setDy1(0); anchor.setDy2(0); // Set image location: anchor.setcol1 (cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(cell.getRow().getRowNum()); anchor.setRow2(cell.getRow().getRowNum() + 1); // Set image to move with cell: anchor. SetAnchorType (MOVE_AND_RESIZE); drawing.createPicture(anchor, index); } public static int getImageType(byte[] value) {String type = PoiPublicUtil.getFileExtendName(value); if ("JPG".equalsIgnoreCase(type)) { return Workbook.PICTURE_TYPE_JPEG; } else if ("PNG".equalsIgnoreCase(type)) { return Workbook.PICTURE_TYPE_PNG; } return Workbook.PICTURE_TYPE_JPEG; }}Copy the code
  • And when I write it at the end, I’ll take this handler
EasyExcel.write(excelPath + fileName,clazz ) .registerWriteHandler(new ImageModifyHandler(getIgnoreIndexes(clazz))) .sheet() .doWrite(tempList); * @param clazz clazz * @return List<Integer></> */ private List<Integer> getIgnoreIndexes(Class clazz) { if (clazz == null) { return Collections.emptyList(); } List<Integer> indexes = new ArrayList<>(); Field[] fields = clazz.getDeclaredFields(); Arrays.asList(fields).forEach(f -> { if (f.getGenericType().equals(URL.class)) { ExcelProperty excelProperty= f.getAnnotation(ExcelProperty.class); if (excelProperty ! = null && excelProperty.index() >= 0) { indexes.add(excelProperty.index()); }}}); return indexes; }Copy the code

Problem 2: This was solved in problem 1. Set properties.

Problem 3: Setting the properties of an Anchor. If the picture is completely inside the cell and does not touch the cell around it, the picture can move with the cell.

ClientAnchor anchor = helper.createClientAnchor(); // Set image coordinates: anchor.setdx1 (1); anchor.setDx2(1); anchor.setDy1(0); anchor.setDy2(0); // Set image location: anchor.setcol1 (cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(cell.getRow().getRowNum()); anchor.setRow2(cell.getRow().getRowNum() + 1); // Set image to move with cell: anchor. SetAnchorType (MOVE_AND_RESIZE);Copy the code