poi解析excel中图片（导入试题中图片）

导入模板如下：

读取图片(03，07兼容)：将图片数据（sheet，row，column，data）存到ExcelPicPositionEntity实体类中
ExcelPicPositionEntity类如下：

package com.stone.exam.bean;

import com.stone.exam.domain.QuestionBank;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.apache.poi.ss.usermodel.PictureData;

import java.util.List;

/**
 * @author kiki
 * @date 2021/04/20 11:07
 */
@Data
public class ExcelPicPositionEntity {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "sheet页数")
    private Integer sheetNum;

    @ApiModelProperty(value = "第n行")
    private Integer row;

    @ApiModelProperty(value = "第n列")
    private Integer column;

    @ApiModelProperty(value = "图片")
    private PictureData pictureData;

    @ApiModelProperty(value = "图片url路径")
    private String url;

}

解析代码如下：

package com.stone.exam.utils;

import com.stone.exam.bean.ExcelPicPositionEntity;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ooxml.POIXMLDocumentPart;
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.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.web.multipart.MultipartFile;

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

public class ImportExcelByImg {
	//从03格式excel中获取图片
	private static List<ExcelPicPositionEntity> getSheetPictures03(int sheetNum,
																	 HSSFSheet sheet, HSSFWorkbook workbook) {
		List<ExcelPicPositionEntity> positionEntities = new ArrayList<>();
		List pictures = workbook.getAllPictures();
		if (pictures.size() != 0) {
			for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
				HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
				if (shape instanceof HSSFPicture) {
					ExcelPicPositionEntity positionEntity = new ExcelPicPositionEntity();
					HSSFPicture pic = (HSSFPicture) shape;
					positionEntity.setSheetNum(sheetNum);
					positionEntity.setColumn((int) anchor.getCol1());
					positionEntity.setRow(anchor.getRow1());
					positionEntity.setPictureData(pic.getPictureData());
					positionEntities.add(positionEntity);
				}
			}
		}
		return positionEntities;
	}
	//07格式excel获取图片。
	private static List<ExcelPicPositionEntity> getSheetPictures07(int sheetNum,
																	 XSSFSheet sheet, XSSFWorkbook workbook) {
		List<ExcelPicPositionEntity> positionEntities = new ArrayList<>();
		for (POIXMLDocumentPart dr : sheet.getRelations()) {
			if (dr instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) dr;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					ExcelPicPositionEntity positionEntity = new ExcelPicPositionEntity();
					XSSFPicture pic = (XSSFPicture) shape;
					XSSFClientAnchor anchor = pic.getPreferredSize();
					CTMarker ctMarker = anchor.getFrom();
					positionEntity.setSheetNum(sheetNum);
					positionEntity.setColumn(ctMarker.getCol());
					positionEntity.setRow(ctMarker.getRow());
					positionEntity.setPictureData(pic.getPictureData());
					positionEntities.add(positionEntity);
				}
			}
		}
		return positionEntities;
	}
	//图片及位置获取
	public static List<ExcelPicPositionEntity> getAllData(MultipartFile file) throws Exception {
//		// 创建文件
//		File file = new File(excelPath);
		// 创建流
		InputStream input = new ByteArrayInputStream(file.getBytes());
		// 获取文件后缀名
		String fileExt =  file.getName().substring(file.getName().lastIndexOf(".") + 1);
		// 创建Workbook
		Workbook wb;
		// 创建sheet
		Sheet sheet;
		//根据后缀判断excel 2003 or 2007+
		if (fileExt.equals("xls")) {
			wb = WorkbookFactory.create(input);
		} else {
			wb = new XSSFWorkbook(input);
		}
		//获取excel sheet总数
		int sheetNumbers = wb.getNumberOfSheets();
		List<ExcelPicPositionEntity> positionEntities = new ArrayList<>();
		// 循环sheet
		for (int i = 0; i < sheetNumbers; i++) {
			sheet = wb.getSheetAt(i);
		// 判断用07还是03的方法获取图片
			if (fileExt.equals("xls")) {
				positionEntities.addAll(getSheetPictures03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb));
			} else {
				positionEntities.addAll(getSheetPictures07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb));
			}
		}
		return positionEntities;
	}
}

业务代码如下：

一张张上传到minio后，保存返回的url，之后再和每一题的行数比对，对应：

题目的图片格式为：

[{"key":"answer","url":["http://120.79.152.161:9000/cjyl/questionTitle/2021-06-24/1624499801569.jpg"]},{"key":"option","url":["","",""]}]

要下班了，就只粗糙地记录一下吧~~~

俺要去霍奶茶啦！！！！！！！！！