一、需求背景：
做一个大屏管理系统，基础信息包括管理的应用名称，大屏的截图，通过一个excel批量导入
excel的单元格里要插入图片，对应一个大屏应用的信息
导入需要读取到大屏截图，至于存哪还没说....
二、技术实现
Hutool这块没有做图片读取的封装，看了网上的方式都是通过poi原生的api实现的
但是Hutool读取很方便，返回的Workbook接口对象可以识别新旧工作簿类型
然后原生的api的话，只能自己去封装想要的逻辑了
几个图片的关键信息，图片字节，图片类型，图片对应的记录坐标
我还纳闷怎么拿不到图片的文件名，仔细找了API才发现是压根没存这个信息
那就算了不强求了
根据上述需求，我需要封装一个存储图片的集合，拿到后提供给业务做具体实现
或者也可以在读取到这个图片的时候，执行业务逻辑
maven 依赖坐标：
<dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.4.1</version> </dependency>
poi 的坐标没找着...
下面就是封装的工具类：
import cn.hutool.poi.excel.ExcelReader;
import lombok.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileOutputStream;
import java.util.*;
import java.util.function.Consumer;
public class ExcelPicUtil {
/**
* @description 读取Excel中的图片 返回sheet下标和对应图片的集合
* @author OnCloud9
* @date 2024/3/20 13:39
* @params ExcelReader excelReader
* @return Map<Integer, List<ExcelPic>>
*/
public static Map<Integer, List<ExcelPic>> getExcelPic(ExcelReader excelReader) {
Workbook workbook = excelReader.getWorkbook();
if (Objects.isNull(workbook)) return null;
boolean isXSSF = workbook instanceof XSSFWorkbook;
boolean isHSSF = workbook instanceof HSSFWorkbook;
if (isXSSF) return getExcelPicByXssfType((XSSFWorkbook) workbook, null);
else if (isHSSF) return getExcelPicByHssfType((HSSFWorkbook) workbook, null);
return null;
}
/**
* @description
* @author OnCloud9
* @date 2024/3/20 14:12
* @params
* @return
*/
public static Map<Integer, List<ExcelPic>> getExcelPic(ExcelReader excelReader, Consumer<ExcelPic> consumer) {
Workbook workbook = excelReader.getWorkbook();
if (Objects.isNull(workbook)) return null;
boolean isXSSF = workbook instanceof XSSFWorkbook;
boolean isHSSF = workbook instanceof HSSFWorkbook;
if (isXSSF) return getExcelPicByXssfType((XSSFWorkbook) workbook, consumer);
else if (isHSSF) return getExcelPicByHssfType((HSSFWorkbook) workbook, consumer);
return null;
}
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public static final class ExcelPic {
private Integer sheetIdx;
private String suffix;
private String mimeType;
private byte[] data;
private Integer rowIdx;
private Integer colIdx;
}
/**
* @description XSSF工作簿读取图片
* @author OnCloud9
* @date 2024/3/20 13:57
* @params
* @return
*/
private static Map<Integer, List<ExcelPic>> getExcelPicByXssfType(XSSFWorkbook xssfWorkbook, Consumer<ExcelPic> excelPicConsumer) {
int numberOfSheets = xssfWorkbook.getNumberOfSheets();
Map<Integer, List<ExcelPic>> picMap = new HashMap<>();
for (int xssfSheetIdx = 0; xssfSheetIdx < numberOfSheets; xssfSheetIdx++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(xssfSheetIdx);
XSSFDrawing drawingPatriarch = xssfSheet.getDrawingPatriarch();
if (Objects.isNull(drawingPatriarch)) {
picMap.put(xssfSheetIdx, Collections.emptyList());
continue;
}
List<XSSFShape> shapes = drawingPatriarch.getShapes();
List<ExcelPic> excelPicList = new ArrayList<>(shapes.size());
for (XSSFShape xssfShape : shapes) {
XSSFPicture xssfPicture = (XSSFPicture) xssfShape;
XSSFClientAnchor clientAnchor = xssfPicture.getClientAnchor();
XSSFPictureData xssfPictureData = xssfPicture.getPictureData();
String fileExtension = xssfPictureData.suggestFileExtension();
byte[] data = xssfPictureData.getData();
String mimeType = xssfPictureData.getMimeType();
short col2 = clientAnchor.getCol2();
int row2 = clientAnchor.getRow2();
ExcelPic build = ExcelPic.builder()
.sheetIdx(xssfSheetIdx)
.suffix(fileExtension)
.mimeType(mimeType)
.data(data)
.rowIdx(row2)
.colIdx((int) col2)
.build();
if (Objects.nonNull(excelPicConsumer)) excelPicConsumer.accept(build);
excelPicList.add(build);
}
picMap.put(xssfSheetIdx, excelPicList);
}
return picMap;
}
/**
* @description HSSF工作簿读取图片
* @author OnCloud9
* @date 2024/3/20 13:58
* @params
* @return
*/
private static Map<Integer, List<ExcelPic>> getExcelPicByHssfType(HSSFWorkbook hssfWorkbook, Consumer<ExcelPic> excelPicConsumer) {
int numberOfSheets = hssfWorkbook.getNumberOfSheets();
Map<Integer, List<ExcelPic>> picMap = new HashMap<>();
for (int hssfSheetIdx = 0; hssfSheetIdx < numberOfSheets; hssfSheetIdx++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(hssfSheetIdx);
HSSFPatriarch drawingPatriarch = hssfSheet.getDrawingPatriarch();
if (Objects.isNull(drawingPatriarch)) {
picMap.put(hssfSheetIdx, Collections.emptyList());
continue;
}
List<HSSFShape> hssfShapeList = drawingPatriarch.getChildren();
List<ExcelPic> excelPicList = new ArrayList<>(hssfShapeList.size());
for (HSSFShape hssfShape : hssfShapeList) {
HSSFPicture hssfPicture = (HSSFPicture) hssfShape;
HSSFClientAnchor clientAnchor = hssfPicture.getClientAnchor();
HSSFPictureData hssfPictureData = hssfPicture.getPictureData();
String fileExtension = hssfPictureData.suggestFileExtension();
byte[] data = hssfPictureData.getData();
String mimeType = hssfPictureData.getMimeType();
short col2 = clientAnchor.getCol2();
int row2 = clientAnchor.getRow2();
ExcelPic build = ExcelPic.builder()
.sheetIdx(hssfSheetIdx)
.suffix(fileExtension)
.mimeType(mimeType)
.data(data)
.rowIdx(row2)
.colIdx((int) col2)
.build();
if (Objects.nonNull(excelPicConsumer)) excelPicConsumer.accept(build);
excelPicList.add(build);
}
picMap.put(hssfSheetIdx, excelPicList);
}
return picMap;
}
/**
* @description 根据文件路径和图片字节输出
* @author OnCloud9
* @date 2024/3/20 13:56
* @params
* @return
*/
@SneakyThrows
public static void writePicByteTo(byte[] picBytes, String outPutPath) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(outPutPath);
fos.write(picBytes);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (Objects.nonNull(fos)) fos.close();
}
}
}
测试代码DEMO：
@Test
public void excelPicRead() {
String file = "C:\\Users\\Administrator\\Desktop\\工作日志\\图片导入测试.xlsx";
ExcelReader excelReader = ExcelUtil.getReader(file);
List<List<Object>> read = excelReader.read();
System.out.println(read);
Map<Integer, List<ExcelPicUtil.ExcelPic>> excelPicMap = ExcelPicUtil.getExcelPic(excelReader, excelPic -> {
// todo ...... 自己实现读取到这个图片时做啥
});
String rootPath = "D:\\ymcd-project\\config\\ouput\\";
excelPicMap.values().forEach(excelPics -> excelPics.forEach(excelPic -> {
ExcelPicUtil.writePicByteTo(excelPic.getData(), rootPath + excelPic.getRowIdx() + "-" + excelPic.getColIdx() + "." + excelPic.getSuffix());
}));
System.out.println(excelPicMap.size());
}
演示文件：
读取后输出到目录：
标签：return,读取,Excel,private,String,workbook,Java,图片 From： https://www.cnblogs.com/mindzone/p/18085147