java按表格顺序读取excel单元格的图片

   

<!-- POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.12.0</version>
            <scope>compile</scope>
            <optional>true</optional>
        </dependency>

wps的excel :

wps只能用workbook.getAllPictures();但是这种图片是没有顺序的 

List<PictureData> pictureDatas = (List<PictureData>) workbook.getAllPictures();

List lst = workbook.getAllPictures();

office的excel:

dasdasd()方法支持office的excel

主要是获取 byte[] data对象字节流然后就是图片文件的内容

//XSSFPictureData就可以直接获取到getData()的byte[]
 byte[] data = stringXSSFPictureDataMap.get(s).getData();
//将文件字节存储到本地
 FileOutputStream out = new FileOutputStream("d:/tiger/file");
//输出到本地目录,或者其他
 out.write(data);

package com.zz.bgf.utils;

import java.io.*;
import java.util.*;

import cn.hutool.poi.excel.WorkbookUtil;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

/**
 * @since 2013-04-22

 * @author Gerrard

 * 获取excel中 图片，并得到图片位置，支持03 07 多sheet

 */
public class GetImgFromExcel {

    public static void main(String[] args) throws Exception {
        dasdasd();
//        extracted();
        //存储头像，并返回地址

        // 创建文件
//        File file = new File("C:\\Users\\Admin\\Desktop\\新建文件夹\\人物基本信息模板.xlsx");
//        InputStream input = new FileInputStream(file);
//        String fileExt =  file.getName().substring(file.getName().lastIndexOf(".") + 1);
//        Workbook wb = null;
//        Sheet sheet = null;
//        if (fileExt.equals("xls")) {
//            wb = (HSSFWorkbook) WorkbookFactory.create(input);
//        } else {
//            wb = new XSSFWorkbook(input);
//        }
        Sheet sheetAt = workbook.getSheetAt(0);
//        Sheet sheetAt2 = wb.getSheet("Sheet1");
//        getPictures2((XSSFSheet)sheetAt2);
    }

    /**
     * 获取图片和位置 (xlsx)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, PictureData> getPictures2(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;
    }

    private static void extracted() throws IOException {
        //存储头像，并返回地址
        Workbook workbook = WorkbookUtil.createBook(new File("C:\\Users\\Admin\\Desktop\\aa\\dddddddddd.xlsx"));
        List<PictureData> pictureDatas = (List<PictureData>) workbook.getAllPictures();

        List lst = workbook.getAllPictures();
        int a=0;
        for (Iterator it = lst.iterator(); it.hasNext(); ) {
            PictureData pict = (PictureData)it.next();

            String ext = pict.suggestFileExtension();
            byte[] data = pict.getData();
            if (ext.equalsIgnoreCase("png")) {
                FileOutputStream out = new FileOutputStream(++a+"pict.jpg");
                out.write(data);
                out.close();
            }
        }
    }

    private static void dasdasd() throws Exception {
        int a=0;
//存储头像，并返回地址
        Workbook workbook = WorkbookUtil.createBook(new File("C:\\Users\\Admin\\Desktop\\aa\\人员信息.xlsx"));
        List<? extends PictureData> allPictures = workbook.getAllPictures();
        XSSFDrawing dp = (XSSFDrawing) workbook.getSheetAt(0).createDrawingPatriarch();
        List<XSSFShape> pics = dp.getShapes();
        for (XSSFShape pic : pics) {
            XSSFPicture inpPic = (XSSFPicture)pic;

            XSSFClientAnchor clientAnchor = inpPic.getClientAnchor();
            inpPic.getShapeName(); // узнаю название картинки
            PictureData pict = inpPic.getPictureData();
            FileOutputStream out = new FileOutputStream(++a+"pict.png");
            byte[] data = pict.getData();
            out.write(data);
            out.close();
            System.out.println("col1: " + clientAnchor.getCol1() + ", col2: " + clientAnchor.getCol2() + ", row1: " + clientAnchor.getRow1() + ", row2: " + clientAnchor.getRow2());
            System.out.println("x1: " + clientAnchor.getDx1() + ", x2: " + clientAnchor.getDx2() +  ", y1: " + clientAnchor.getDy1() +  ", y2: " + clientAnchor.getDy2());
        }

    }

    /**
     * @param args
     * @throws IOException
     * @throws InvalidFormatException
     */
    public static void main2(String[] args) throws InvalidFormatException, IOException {

        // 创建文件
        File file = new File("D:\\tiger\\资料\\数据导入模板\\人物基本信息模板.xlsx");

        // 创建流
        InputStream input = new FileInputStream(file);

        // 获取文件后缀名
        String fileExt =  file.getName().substring(file.getName().lastIndexOf(".") + 1);

        // 创建Workbook
        Workbook wb = null;

        // 创建sheet
        Sheet sheet = null;

        //根据后缀判断excel 2003 or 2007+
        if (fileExt.equals("xls")) {
            wb = (HSSFWorkbook) WorkbookFactory.create(input);
        } else {
            wb = new XSSFWorkbook(input);
        }

        //获取excel sheet总数
        int sheetNumbers = wb.getNumberOfSheets();

        // sheet list
        List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();

        // 循环sheet
        for (int i = 0; i < sheetNumbers; i++) {

            sheet = wb.getSheetAt(i);
            // map等待存储excel图片
            Map<String, PictureData> sheetIndexPicMap;

            // 判断用07还是03的方法获取图片
            if (fileExt.equals("xls")) {
                sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb);
            } else {
                sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb);
            }
            // 将当前sheet图片map存入list
            sheetList.add(sheetIndexPicMap);
        }

        printImg(sheetList);

    }

    /**
     * 获取Excel2003图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     * @throws IOException
     */
    public static Map<String, PictureData> getSheetPictrues03(int sheetNum,
                                                              HSSFSheet sheet, HSSFWorkbook workbook) {

        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    /**
     * 获取Excel2007图片
     * @param sheetNum 当前sheet编号
     * @param sheet 当前sheet对象
     * @param workbook 工作簿对象
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
     */
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum,
                                                              XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();

        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + ctMarker.getRow() + "_" + ctMarker.getCol();
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }

        return sheetIndexPicMap;
    }

    public static void printImg(List<Map<String, PictureData>> sheetList) throws IOException {

        for (Map<String, PictureData> map : sheetList) {
            Object key[] = map.keySet().toArray();
            for (int i = 0; i < map.size(); i++) {
                // 获取图片流
                PictureData pic = map.get(key[i]);
                // 获取图片索引
                String picName = key[i].toString();
                // 获取图片格式
                String ext = pic.suggestFileExtension();

                byte[] data = pic.getData();

                FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "." + ext);
                out.write(data);
                out.close();
            }
        }

    }

}

package com.zz.bgf.utils;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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;

public class ExcelUtils2 {
    // 默认单元格格式化日期字符串
    public static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    // 默认单元格内容为数字时格式
    private static DecimalFormat df = new DecimalFormat("0");
    // 格式化数字
    private static DecimalFormat nf = new DecimalFormat("0.00");

    /**
     * 读取Excel中sheet1的内容
     * @param file
     * @return ArrayList<ArrayList<Object>>
     */
    public static ArrayList<ArrayList<Object>> readExcel(File file) {
        if (file == null) {
            return null;
        }
        if (file.getName().endsWith("xlsx")) {
            // 处理ecxel2007
            return readExcel2007(file);
        } else if (file.getName().endsWith("xls")) {
            // 处理ecxel2003
            return readExcel2003(file);
        } else {
            return null;
        }
    }
    public static Map<String, XSSFPictureData> readExcelFile(File file) throws IOException {
        if (file == null) {
            return null;
        }
        if (file.getName().endsWith("xlsx")) {
            // 处理ecxel2007
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> oneRow = null;
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = workbook.getSheetAt(0);
            Map<String, XSSFPictureData> pictures2 = getPictures2(sheet);
            return pictures2;
        }
        return null;
    }

    /**
     * @param file
     * @return
     */
    private static ArrayList<ArrayList<Object>> readExcel2003(File file) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> oneRow = null;
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
            HSSFSheet sheet = workbook.getSheetAt(0);
            HSSFRow row;
            HSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                oneRow = new ArrayList<Object>();
                if (row == null || checkRowNull2003(row)) {
                    continue;
                }
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            oneRow.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                double doubleVal = cell.getNumericCellValue();
                                int intVal = (int) Math.round(doubleVal);
                                if (Double.parseDouble(intVal + ".0") == doubleVal) {
                                    value = df.format(intVal);
                                } else {
                                    value = nf.format(doubleVal);
                                }
                            } else {
                                value = ((Double)cell.getNumericCellValue()).toString();
                            }
                            break;
                        default:
                            value = cell.toString();
                            break;
                    }
                    oneRow.add(value);
                }
                rowList.add(oneRow);
            }
            return rowList;
        } catch (IOException e) {
            return null;
        }
    }

    /**
     * @param file
     * @return
     */
    private static ArrayList<ArrayList<Object>> readExcel2007(File file) {
        try {
            ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
            ArrayList<Object> oneRow = null;
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet sheet = workbook.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;
            Object value;
            for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                oneRow = new ArrayList<Object>();
                if (row == null || checkRowNull2007(row)) {
                    continue;
                }
                for (int j = row.getFirstCellNum(); j < row.getPhysicalNumberOfCells(); j++) {
                    cell = row.getCell(j);
                    if (cell == null || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        if (j != row.getLastCellNum()) {
                            oneRow.add("");
                        }
                        continue;
                    }
                    switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_STRING:
                            value = cell.getStringCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BOOLEAN:
                            value = cell.getBooleanCellValue();
                            break;
                        case XSSFCell.CELL_TYPE_BLANK:
                            value = "";
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC:
                            if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                                double doubleVal = cell.getNumericCellValue();
                                int intVal = (int) Math.round(doubleVal);
                                if (Double.parseDouble(intVal + ".0") == doubleVal) {
                                    value = df.format(intVal);
                                } else {
                                    value = nf.format(doubleVal);
                                }
                            } else {
                                value = ((Double)cell.getNumericCellValue()).toString();
                            }
                            break;
                        default:
                            value = cell.toString();
                            break;
                    }
                    oneRow.add(value);
                }
                rowList.add(oneRow);
            }
            return rowList;
        } catch (IOException e) {
            return null;
        }
    }

    /**
     * 判断行为空(xls)
     * @param row
     * @return
     */
    private static boolean checkRowNull2003(HSSFRow row) {
        for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
            HSSFCell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 判断行为空(xlsx)
     * @param row
     * @return
     */
    private static boolean checkRowNull2007(XSSFRow row) {
        for (int i = row.getFirstCellNum(); i < row.getPhysicalNumberOfCells(); i++) {
            XSSFCell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != XSSFCell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }
    /**
     * 获取图片和位置 (xls)
     * @param sheet
     * @return
     * @throws IOException
     */
    public static Map<String, HSSFPictureData> getPictures (HSSFSheet sheet) throws IOException {
        Map<String, HSSFPictureData> map = new HashMap<String, HSSFPictureData>();
        List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
        for (HSSFShape shape : list) {
            if (shape instanceof HSSFPicture) {
                HSSFPicture picture = (HSSFPicture) shape;
                HSSFClientAnchor cAnchor = picture.getClientAnchor();
                HSSFPictureData 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, XSSFPictureData> getPictures (XSSFSheet sheet) throws IOException {
        Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
        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;
    }
    public static Map<String, XSSFPictureData> getPictures2(XSSFSheet sheet) throws IOException {
        Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
        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;
    }
}