java 读取excel图片导入(亲测有效)

古城微笑少年丶 2024-03-27 14:16 62阅读 0赞

##### 从excel文件中获取图片(兼容新老版本) #####

/**
         * 获取excel表中的图片
         *
         * @return
         * @throws IOException
         * @throws InvalidFormatException
         * @throws EncryptedDocumentException
         * @Param fis 文件输入流
         * @Param sheetNum Excel表中的sheet编号
         */
        public static Map<String, PictureData> getPictureFromExcel(File file, int sheetNum) throws EncryptedDocumentException, IOException {
        
    
            //获取图片PictureData集合
            String fileName = file.getName();
            Workbook workbook = null;
            if (StringUtil.isEmpty(fileName)) {
        
                return null;
            }
            FileInputStream fileInputStream = new FileInputStream(file);
            if (fileName.endsWith("xls")) {
        
                //2003
                workbook = new HSSFWorkbook(fileInputStream);
                HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(sheetNum - 1);
                Map<String, PictureData> pictures = getPictures(sheet);
                return pictures;
            } else if (fileName.endsWith("xlsx")) {
        
                //2007
                workbook = new XSSFWorkbook(fileInputStream);
                XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(sheetNum - 1);
                Map<String, PictureData> pictures = getPictures(sheet);
                return pictures;
            }
            return new HashMap();
        }

##### 从sheet页中获取图片及图片位置 #####

/**
         * 获取图片和位置 (xls版)
         * @param sheet
         * @return
         * @throws IOException
         */
        public static Map<String, PictureData> getPictures (HSSFSheet sheet) throws IOException {
        
            Map<String, PictureData> map = new HashMap<String, PictureData>();
            List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
            for (HSSFShape shape : list) {
        
                if (shape instanceof HSSFPicture) {
        
                    HSSFPicture picture = (HSSFPicture) shape;
                    HSSFClientAnchor cAnchor = picture.getClientAnchor();
                    PictureData pdata = picture.getPictureData();
                    String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
                    map.put(key, pdata);
                }
            }
            return map;
        }
    
        /**
         * 获取图片和位置 (xlsx版)
         * @param sheet
         * @return
         * @throws IOException
         */
        public static Map<String, PictureData> getPictures (XSSFSheet sheet) throws IOException {
        
            Map<String, PictureData> map = new HashMap<String, PictureData>();
            List<POIXMLDocumentPart> list = sheet.getRelations();
            for (POIXMLDocumentPart part : list) {
        
                if (part instanceof XSSFDrawing) {
        
                    XSSFDrawing drawing = (XSSFDrawing) part;
                    List<XSSFShape> shapes = drawing.getShapes();
                    for (XSSFShape shape : shapes) {
        
                        XSSFPicture picture = (XSSFPicture) shape;
                        XSSFClientAnchor anchor = picture.getPreferredSize();
                        CTMarker marker = anchor.getFrom();
                        String key = marker.getRow() + "-" + marker.getCol();
                        map.put(key, picture.getPictureData());
                    }
                }
            }
            return map;
        }

##### main方法-获取excel文件中的图片下载到本地 #####

public static void main(String [] args) throws IOException {
        
            File file = new File("C:\\Users\\A\\Desktop\\test.xlsx");
            //获取图片数据
            Map<String, PictureData> map = getPictureFromExcel(file, 1);
            //根据图片行列位置获取图片
            PictureData picData = (PictureData)map.get("3"+"-"+14);
            //后缀名
            String extension = picData.suggestFileExtension();
            byte[] data = picData.getData();
            FileOutputStream out = new FileOutputStream("C:\\Users\\A\\Desktop\\" + "picture." + extension);
            out.write(data);
            out.close();
        }

**遇到问题**：图片导入解析报错异常java.lang.ClassCastException: org.apache.poi.xssf.usermodel.  
XSSFSimpleShape cannot be cast to org.apache.poi.xssf.usermodel.XSSFPicture问题  
**解决方法**：经过排查，为xslx版本shap对象强转为图片对象报错，添加逻辑首先检验if (shape instanceof XSSFPicture)类型然后再进行强转，问题解决