EXCEL 图片处理工具类,支持wps中内嵌图片和浮动图片的处理

EXCEL 图片处理工具类,支持wps中内嵌图片和浮动图片的处理,本工具类使用easyExcel 实现excel中图片中获取,返回对应图片的位置.

EXCEL 图片处理工具类,支持wps中内嵌图片和浮动图片的处理

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.CharsetUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.XML;
import com.zeekr.vidpspecialmanagerserver.model.excel.WpsImg;
import com.zeekr.vidpspecialmanagerserver.model.excel.wps.CellImageRels;
import com.zeekr.vidpspecialmanagerserver.model.excel.wps.CellImages;
import lombok.SneakyThrows;
import org.apache.commons.io.IOUtils;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.net.URI;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipInputStream;

/**
 * @author ：duanyi
 * @date ：Created 2023/9/21
 * @description：
 */

public class WpsImgUtil {

    /**
     * 获取wps中的图片
     * 包括嵌入形式图片和浮动形式图片
     * <p>
     * 嵌入形式图片返回方式:
     * 以map方式返回
     * 键为行列格式 =DISPIMG("ID",1) 字符串
     * <p>
     * 浮动形式图片返回方式:
     * 以map方式返回
     * 键为行列格式 x-y  字符串
     *
     * @param dispStrList
     * @param simpleFile
     * @return
     * @throws IOException
     */
    @SneakyThrows(IOException.class)
    public static Map<String, WpsImg> getWpsImgs(List<String> dispStrList, MultipartFile simpleFile) {
        List<WpsImg> wpsImgList = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(dispStrList)) {
            for (String dispStr : dispStrList) {
                if (Objects.nonNull(dispStr) && dispStr.startsWith("=DISPIMG")) {
                    int start = dispStr.indexOf("\"");
                    int end = dispStr.lastIndexOf("\"");
                    if (start != -1 && end != -1) {
                        String imgId = dispStr.substring(start + 1, end);
                        WpsImg wpsImg = new WpsImg();
                        wpsImg.setType(0);
                        wpsImg.setImgId(imgId);
                        wpsImg.setCellStr(dispStr);
                        wpsImgList.add(wpsImg);
                    }
                }
            }
        }

        ZipInputStream zis = new ZipInputStream(simpleFile.getInputStream());
        try {
            ZipEntry entry;
            while ((entry = zis.getNextEntry()) != null) {
                try {
                    final String fileName = entry.getName();
                    if (Objects.equals(fileName, "xl/cellimages.xml")) {
                        String content = IOUtils.toString(zis, CharsetUtil.UTF_8);
                        JSONObject js = XML.toJSONObject(content);
                        CellImages cellImages = com.alibaba.fastjson2.JSONObject.parseObject(js.toString(), CellImages.class);
                        for (CellImages.CellImagesDTO.CellImageDTO cellImageDTO : cellImages.getCellImages().getCellImage()) {
                            String name = cellImageDTO.getPic().getNvPicPr().getCNvPr().getName();
                            String embed = cellImageDTO.getPic().getBlipFill().getBlip().getEmbed();
                            List<WpsImg> wpsImg = wpsImgList.stream().filter(i -> Objects.equals(i.getImgId(), name)).collect(Collectors.toList());
                            if (CollectionUtil.isNotEmpty(wpsImg)) {
                                wpsImg.forEach(k -> k.setRId(embed));
                            }
                        }
                    }
                } finally {
                    zis.closeEntry();
                }
            }
        } finally {
            zis.close();
        }
        ZipInputStream fzis = new ZipInputStream(simpleFile.getInputStream());
        try {
            ZipEntry entry;
            while ((entry = fzis.getNextEntry()) != null) {
                try {
                    final String fileName = entry.getName();
                    if (Objects.equals(fileName, "xl/_rels/cellimages.xml.rels")) {
                        String content = IOUtils.toString(fzis, CharsetUtil.UTF_8);
                        JSONObject js = XML.toJSONObject(content);
                        CellImageRels cellImageRels = com.alibaba.fastjson2.JSONObject.parseObject(js.toString(), CellImageRels.class);
                        for (CellImageRels.RelationshipsDTO.RelationShipDTO relationshipDTO : cellImageRels.getRelationShips().getRelationship()) {
                            String id = relationshipDTO.getId();
                            String target = "/xl/" + relationshipDTO.getTarget();
                            List<WpsImg> wpsImgs = wpsImgList.stream().filter(i -> Objects.equals(i.getRId(), id)).collect(Collectors.toList());
                            if (CollectionUtil.isNotEmpty(wpsImgs)) {
                                wpsImgs.forEach(k -> k.setImgName(target));
                            }
                        }
                    }
                } finally {
                    fzis.closeEntry();
                }
            }
        } finally {
            fzis.close();
        }

        Workbook workbook = WorkbookFactory.create(simpleFile.getInputStream());
        List<XSSFPictureData> allPictures = (List<XSSFPictureData>) workbook.getAllPictures();
        for (XSSFPictureData pictureData : allPictures) {
            PackagePartName partName = pictureData.getPackagePart().getPartName();
            URI uri = partName.getURI();
            List<WpsImg> wpsImgs = wpsImgList.stream().filter(i -> Objects.equals(i.getImgName(), uri.toString())).collect(Collectors.toList());
            if (CollectionUtil.isNotEmpty(wpsImgs)) {
                wpsImgs.forEach(k -> k.setPictureData(pictureData));
            }
        }
        Map<String, WpsImg> result = new HashMap<>();
        for (WpsImg wpsImg : wpsImgList) {
            result.put(wpsImg.getCellStr(), wpsImg);
        }
        XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);
        Map<String, WpsImg> flotPictures = WpsImgUtil.getFlotPictures(sheet);
        result.putAll(flotPictures);
        return result;
    }

    /**
     * 获取浮动形式的图片
     * 以map方式返回
     * 键为行列格式 x-y
     *
     * @param xssfSheet
     * @return
     */
    public static Map<String, WpsImg> getFlotPictures(XSSFSheet xssfSheet) {
        Map<String, WpsImg> map = new HashMap<>();
        XSSFDrawing drawingPatriarch = xssfSheet.getDrawingPatriarch();
        if (Objects.isNull(drawingPatriarch)) {
            return map;
        }
        List<XSSFShape> list = drawingPatriarch.getShapes();
        for (XSSFShape shape : list) {
            XSSFPicture picture = (XSSFPicture) shape;
            XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor) picture.getAnchor();
            XSSFPictureData pdata = picture.getPictureData();
            // 行号-列号
            String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
            WpsImg wpsImg = new WpsImg();
            String partName = pdata.getPackagePart().getPartName().getName();
            String contentType = pdata.getPackagePart().getContentType();
            int lastSlashIndex = partName.lastIndexOf('/');
            wpsImg.setImgName(partName.substring(lastSlashIndex + 1));
            wpsImg.setPictureData(pdata);
            wpsImg.setType(1);
            map.put(key, wpsImg);
        }
        return map;
    }
}


@NoArgsConstructor
@AllArgsConstructor
@Data
public class WpsImg {
    /**
     * 图片id ,wps 内嵌图片后面的id,=DISPIMG后面
     */
    private String imgId;
    /**
     * cellStr,=DISPIMG 字符串
     */
    private String cellStr;
    /**
     * 引用id
     */
    private String rId;
    /**
     * imgName
     */
    private String imgName;
    /**
     * 图片数据
     */
    private XSSFPictureData pictureData;

    /**
     * 0 嵌入单元格 1 浮动
     */
    private int type;
}



@NoArgsConstructor
@AllArgsConstructor
@Data
public class CellImageRels {

    @JSONField(name = "Relationships")
    private RelationshipsDTO relationShips;

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    public static class RelationshipsDTO {
        @JSONField(name = "Relationship")
        private List<RelationShipDTO> relationship;


        @NoArgsConstructor
        @AllArgsConstructor
        @Data
        public static class RelationShipDTO {
            @JSONField(name = "Id")
            private String id;
            @JSONField(name = "Target")
            private String target;
        }
    }
}


@NoArgsConstructor
@AllArgsConstructor
@Data
public class CellImages {

    @JSONField(name = "etc:cellImages")
    private CellImagesDTO cellImages;

    @NoArgsConstructor
    @AllArgsConstructor
    @Data
    public static class CellImagesDTO {
        @JSONField(name = "etc:cellImage")
        private List<CellImageDTO> cellImage;

        @NoArgsConstructor
        @AllArgsConstructor
        @Data
        public static class CellImageDTO {
            @JSONField(name = "xdr:pic")
            private PicDTO pic;

            @NoArgsConstructor
            @AllArgsConstructor
            @Data
            public static class PicDTO {
                @JSONField(name = "xdr:nvPicPr")
                private NvPicPrDTO nvPicPr;
                @JSONField(name = "xdr:blipFill")
                private BlipFillDTO blipFill;

                @NoArgsConstructor
                @AllArgsConstructor
                @Data
                public static class NvPicPrDTO {
                    @JSONField(name = "xdr:cNvPr")
                    private CNvPrDTO cNvPr;

                    @NoArgsConstructor
                    @AllArgsConstructor
                    @Data
                    public static class CNvPrDTO {
                        private String name;
                    }
                }

                @NoArgsConstructor
                @AllArgsConstructor
                @Data
                public static class BlipFillDTO {
                    @JSONField(name = "a:blip")
                    private BlipDTO blip;

                    @NoArgsConstructor
                    @AllArgsConstructor
                    @Data
                    public static class BlipDTO {
                        @JSONField(name = "r:embed")
                        private String embed;
                    }
                }
            }
        }
    }
}