如何使用java读取Excel文件里图片和文字

在很多人脸识别和报表应用里面，批量导入是一个非常常见的需求，这里我分享一个读取使用POI和Easypoi读取Excel文件里面的图片以及文字的方法

导入easypoi的pom包

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

2.书写按顺序读取excel内图片的工具类

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

// 用户读取用户上传Excel表格里面的图片
public class ReadExcelUtils {
    public static ArrayList<byte[]> uploadPerson(String filePath) throws Exception{
        InputStream inp = new FileInputStream(filePath);
        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inp);
        List <HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);

        ArrayList<byte[]> arrayList = new ArrayList<>();

        for (HSSFShape shape: sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int row = anchor.getRow2();
                int col = anchor.getCol2();
                System.out.println("--->" + anchor.getRow2() + ":" + anchor.getCol2());
                //map.put(row+":"+col, row+":"+col);
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                byte[] data = picData.getData();
                arrayList.add(data);
            }
        }
        return arrayList;
    }

    public static void savePic(String i, PictureData pic) throws Exception {
        String ext = pic.suggestFileExtension();
        byte[] data = pic.getData();
        if (ext.equals("jpeg")) {
            FileOutputStream out = new FileOutputStream("D:\\excel\\" + i + ".jpg");
            out.write(data);
            out.close();
            File file = new File("D:\\excel\\" + i + ".jpg");
            FileInputStream in = new FileInputStream(file);
            System.out.println("in===>" + in );
            if (file.isFile()) {
                file.delete();
                System.out.println("=============delete");
            }

        }
    }

}

3.按顺序读取完图片后，再按顺序读取excel内的文字信息

personDto dto类

    @Excel(name = "姓名")
    public String personName;

    @Excel(name = "人员类型")
    public String personType;

    // 此字段用于存放图片的byte流，因此不需要加上@Excel
    byte[] faceImage;

读取Excel内表格文字数据的函数

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @Description: 表格工具类
 */
public class PoiUtils {
    public static void exportExcel(List < ? > list, String title, String sheetName, Class < ? > pojoClass,
                                   String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

    public static void exportExcel(List < ? > list, String title, String sheetName, Class < ? > pojoClass,
                                   String fileName, HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List < Map < String, Object >> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List < ? > list, Class < ? > pojoClass, String fileName,
                                      HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    private static void defaultExport(List < Map < String, Object >> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 导入
     * @param filePath
     * @param titleRows
     * @param headerRows
     * @param pojoClass
     * @param <T>
     * @return
     */
    public static < T > List < T > importExcel(String filePath, Integer titleRows, Integer headerRows, Class <
            T > pojoClass) {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List < T > list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
        return list;
    }

    /**
     * 导入表格
     * @param file
     * @param pojoClass
     * @param <T>
     * @return
     */
    public static < T > List < T > importExcel(MultipartFile file, Class < T > pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        try {
            List < T > list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            return list;
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
    }
}

最后外层只需要这样调用即可组装成一个读取到excel图片byte流和文字的List了

            // 读取Excel表格里面的所有图片的byte[]
            FileUtils.byte2File(file.getBytes(),"D:\\excel\\excelFile.xls");
            ArrayList<byte[]> bytes=ReadExcelUtils.uploadPerson("D:\\excel\\excelFile.xls");

            // 读取Excel表格里面的所有基础数据
            List<PersonDto> result = PoiUtils.importExcel(file,PersonDto.class);
            for (int i=0;i<result.size();i++){
                result.get(i).setFaceImage(bytes.get(i));
            }