快捷導(dǎo)航

java讀取excel圖片導(dǎo)入代碼示例(親測有效)

更新時間：2023年10月26日 10:55:37 作者：學(xué)?無止境

在日常工作中,我們經(jīng)常要將一些照片插入到Excel表格中,這篇文章主要給大家介紹了關(guān)于java讀取excel圖片導(dǎo)入的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

從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");
        //獲取圖片數(shù)據(jù)
        Map<String, PictureData> map = getPictureFromExcel(file, 1);
        //根據(jù)圖片行列位置獲取圖片
        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();
    }

遇到問題：圖片導(dǎo)入解析報(bào)錯異常java.lang.ClassCastException: org.apache.poi.xssf.usermodel.
XSSFSimpleShape cannot be cast to org.apache.poi.xssf.usermodel.XSSFPicture問題

解決方法：經(jīng)過排查，為xslx版本shap對象強(qiáng)轉(zhuǎn)為圖片對象報(bào)錯，添加邏輯首先檢驗(yàn)if (shape instanceof XSSFPicture)類型然后再進(jìn)行強(qiáng)轉(zhuǎn)，問題解決