读取excel中的图片数据

公共实现类参考

package com.ztocwst.clp.common.core;

import com.ztocwst.stone.common.utils.UuidUtil;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 读取excel中图片
 * @author zhang
 * @version 1.0
 * @date 2023/2/28
 */
public class ExcelUtils {
    /**
     * 获取图片和位置 (xls)
     * 同一个单元格 有多个图片的情况
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, List<HSSFPictureData>> getPictures(HSSFSheet sheet) throws IOException {
        Map<String, List<HSSFPictureData>> map = new HashMap(16);
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = picture.getPreferredSize();
                HSSFPictureData pdata = picture.getPictureData();
                //行序号 列序号(从0开始计数)
                String key = cAnchor.getRow1() + "-" + cAnchor.getCol1();
                List<HSSFPictureData> pictureDatas;
                if(map.containsKey(key)) {
                    pictureDatas = map.get(key);
                }
                else {
                    pictureDatas = new ArrayList<>();
                }
                pictureDatas.add(pdata);
                map.put(key, pictureDatas);
            }
        }
        return map;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, List<XSSFPictureData>> getPicturesX(XSSFSheet sheet) throws IOException {
        Map<String, List<XSSFPictureData>> map = new HashMap(16);
        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();
                    //行序号 列序号(从0开始计数)
                    String key = marker.getRow() + "-" + marker.getCol();
                    List<XSSFPictureData> pictureDatas;
                    if (map.containsKey(key)) {
                        pictureDatas = map.get(key);
                    } else {
                        pictureDatas = new ArrayList<>();
                    }
                    pictureDatas.add(picture.getPictureData());
                    map.put(key, pictureDatas);
                }
            }
        }
        return map;
    }

    /**
     * 保存图片到文件参考 xls
     * @param map
     * @return
     * @throws IOException
     */
    public static Map<String, String> saveToFile(Map<String, List<HSSFPictureData>> map) throws IOException {
        Map<String, String> map1=new HashMap<>(map.size());
        for(String key: map.keySet()) {
            System.out.println(key);
            List<HSSFPictureData> picDatas = map.get(key);

            for (HSSFPictureData picData:picDatas
                 ) {
                //获取图片的后缀名
                String ext = picData.suggestFileExtension();
                byte[] data = picData.getData();
                String fileName = UuidUtil.getUUID32();
                //文件夹路径：temporarypath 设置临时存储文件的路径
                String temporarypath = "D:\\work\\";
                String fullname=temporarypath + fileName + "."+ext;
                FileOutputStream out = new FileOutputStream(fullname);
                out.write(data);
                out.close();
                map1.put(key,fullname);
            }


        }
        return map1;
    }

    /**
     *  保存图片到文件参考 xlsx
     * @param map
     * @return
     * @throws IOException
     */
    public static Map<String, String> saveToFileX(Map<String, List<XSSFPictureData>> map) throws IOException {
        Map<String, String> map1=new HashMap<>(map.size());
        for(String key: map.keySet()) {
            List<XSSFPictureData> picDatas = map.get(key);
            //获取图片的后缀名
            for (XSSFPictureData picData:picDatas
                 ) {
                String ext = picData.suggestFileExtension();
                byte[] data = picData.getData();
                String fileName = UuidUtil.getUUID32();
                //文件夹路径：temporarypath 设置临时存储文件的路径
                String temporarypath = "D:\\work\\";
                String fullname=temporarypath + fileName + "."+ext;
                FileOutputStream out = new FileOutputStream(fullname);
                out.write(data);
                out.close();
                map1.put(key,fullname);
            }

        }
        return map1;
    }

}

　　测试类参考

package com.ztocwst.clp.common.core;

import junit.framework.TestCase;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

/**
 * 测试excel文件操作
 * @author zhang
 * @version 1.0
 * @date 2023/2/28
 */
public class ExcelUtilsTest extends TestCase {

    public void testGetPictures() {
    }

    public void testTestxlsGetPictures() throws IOException {
        InputStream f = new FileInputStream("D:\\work\\fba.xls");
        Workbook wbs;
        wbs = new HSSFWorkbook(f);
        if (wbs.getNumberOfSheets() == 0) {
            System.out.println("excel文件不存在工作表");
        }
        //获取第一个sheet
        Sheet sheet =  wbs.getSheetAt(0);
        Map<String, HSSFPictureData> pictureDataMap= ExcelUtils.getPictures((HSSFSheet)sheet);
        System.out.println(pictureDataMap.size());
        Map<String, String> map=  ExcelUtils.saveToFile(pictureDataMap);
        for (Map.Entry<String,String> entry: map.entrySet()){
            System.out.println(entry.getKey()+":"+entry.getValue());
        }

    }
    public void testTestGetPictures() throws IOException {
        //工作簿对象 读取xlsx格式的文件
        InputStream f = new FileInputStream("D:\\work\\fba.xlsx");
        Workbook wbs;
        wbs = new XSSFWorkbook(f);
        if (wbs.getNumberOfSheets() == 0) {
            System.out.println("excel文件不存在工作表");
        }
        //获取第一个sheet
        Sheet sheet =  wbs.getSheetAt(0);
        //读取 xlsx
        Map<String, XSSFPictureData> xssfPictureDataMap= ExcelUtils.getPicturesX((XSSFSheet)sheet);
        System.out.println(xssfPictureDataMap.size());
        Map<String, String> map=  ExcelUtils.saveToFileX(xssfPictureDataMap);
        for (Map.Entry<String,String> entry: map.entrySet()){
            System.out.println(entry.getKey()+":"+entry.getValue());
        }
    }
}