Excel数据包含图片导入，获取excel中图片的数据封装，注意这里只会单独获取图片数据
import cn.afterturn.easypoi.util.PoiCellUtil;
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.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 处理excel图片导入
* @Author sxt
* @Date 2023/2/28 11:02
**/
public class ExcelImageImportUtil {
/**
* 获取03图片和位置 (xls) clerk
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getPicturesXls(HSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
HSSFPictureData pdata = picture.getPictureData();
// 行号-列号
String key = cAnchor.getRow1() + "_" + cAnchor.getCol1();
map.put(key, pdata);
}
}
return map;
}
/**
* 获取Excel2007图片 xlsx
* @param sheet 当前sheet对象
* @return Map key:图片单元格索引（0_1）String，value:图片流PictureData
*/
public static Map<String, PictureData> getPicturesXlsx(XSSFSheet sheet) {
int sheetNum = 7;
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 = ctMarker.getRow() + "_" + ctMarker.getCol();
if(sheetNum==ctMarker.getCol() ){
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
}
return sheetIndexPicMap;
}
/**
* 获取Excel2007图片 xlsx
* @param in
* @param column 读取第几列
* @return Map key:图片单元格索引（0_1）String，value:图片流PictureData
*/
public static Map<String, PictureData> getPicturesXlsx(InputStream in, int column) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(0);
String dw = PoiCellUtil.getCellValue(sheet, 1, 0);
Map<String, PictureData> sheetIndexPicMap = new HashMap<>(sheet.getRelations().size());
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 = ctMarker.getRow() + "_" + ctMarker.getCol();
if(column == ctMarker.getCol() ){
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
}
return sheetIndexPicMap;
}
/**
* 获取Excel2007图片 xlsx
* @param in
* @param column 读取第几列
* @return Map key:图片索引 行（0），value:图片流PictureData
*/
public static Map<Integer, PictureData> getRowPicturesXlsx(InputStream in, int column) throws IOException {
XSSFWorkbook workbook = new XSSFWorkbook(in);
XSSFSheet sheet = workbook.getSheetAt(0);
Map<Integer, PictureData> sheetIndexPicMap = new HashMap<>(sheet.getRelations().size());
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();
if(column == ctMarker.getCol() ){
sheetIndexPicMap.put(ctMarker.getRow(), pic.getPictureData());
}
}
}
}
return sheetIndexPicMap;
}
/** 支持多个图片
* 获取Excel2003图片
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
* @throws IOException
*/
public static Map<String, List<PictureData>> getSheetPictrues03s(HSSFSheet sheet, HSSFWorkbook workbook) {
Map<String, PictureData> map = new HashMap<>();
Map<String, List<PictureData>> maps = new HashMap<String, List<PictureData>>();
List<HSSFPictureData> pictures = workbook.getAllPictures();
List<PictureData> list = new ArrayList<>();
if (pictures.size() != 0) {
String picIndex = "";
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);
picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());
list.add(picData );
}
}
maps.put(picIndex, list);
return maps;
} else {
return null;
}
}
}