【Java】Excel 读写图片工具类
一、需求背景：
做一个大屏管理系统，基础信息包括管理的应用名称，大屏的截图，通过一个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());
}
演示文件：
读取后输出到目录：
三、图片写入
代码参考自51CTO
https://blog.51cto.com/u_16213405/9673126
关于图片写入Anchor参数的详细说明见：
https://www.bilibili.com/video/BV1eA41157va?p=25
我想到有图片读取导入，就有图片导出的功能
然后导出的时候和导入一样，一定是基于单元格定位的。
然后图片的呈现是根据单元格大小控制的（不影响图片质量）
这里继续对工具类追加了方法：
/**
* @description 将图片写入到Excel中
* @author OnCloud9
* @date 2024/3/20 16:40
* @params
* @return
*/
@SneakyThrows
public static void writePicToExcel(ExcelWriter excelWriter, List<ExcelPicWrite> excelPicWriteList) {
Workbook workbook = excelWriter.getWorkbook();
CreationHelper creationHelper = workbook.getCreationHelper();
Sheet sheet;
for (ExcelPicWrite picWrite : excelPicWriteList) {
/* 判断是否提供sheet名称，不提供默认写入到第一个，遍历时不一定存在，所以要判断，不存在时创建出来 */
String sheetName = picWrite.getSheetName();
if (StringUtils.isBlank(sheetName)) sheet = workbook.getSheetAt(0);
else {
sheet = workbook.getSheet(sheetName);
if (Objects.isNull(sheet)) sheet = workbook.createSheet(sheetName);
}
/* 获取图片内容，写入工作簿中 */
byte[] picContent = picWrite.getData();
Integer picType = picWrite.getPicType();
int picIdx = workbook.addPicture(picContent, picType);
/* 设置图片存放的位置 */
Integer rowIdx = picWrite.getRowIdx();
Integer colIdx = picWrite.getColIdx();
ClientAnchor clientAnchor = creationHelper.createClientAnchor();
clientAnchor.setRow1(rowIdx);
clientAnchor.setRow2(rowIdx + 1);
clientAnchor.setCol1(colIdx);
clientAnchor.setCol2(colIdx + 1);
/* 图片绘制渲染 */
Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();
Picture picture = drawingPatriarch.createPicture(clientAnchor, picIdx);
}
}
/**
* @description 图片类型翻译
* @author OnCloud9
* @date 2024/3/20 17:46
* @params
* @return
*/
public static int picTypeTranslate(String picType) {
if (StringUtils.isBlank(picType)) return Workbook.PICTURE_TYPE_JPEG;
switch (picType) {
case "png":
case "PNG":
return Workbook.PICTURE_TYPE_PNG;
case "bmp":
case "BMP":
return Workbook.PICTURE_TYPE_DIB;
default:
case "jpeg":
case "JPEG":
case "jpg":
case "JPG":
return Workbook.PICTURE_TYPE_JPEG;
}
}
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public static final class ExcelPicWrite {
private String sheetName; /* 导出创建时依照sheetName名称为标识，如果不写则默认放到第一个sheet页 */
private byte[] data; /* 图片字节数组 这里不关心图片输入的方式 */
private Integer rowIdx; /* 定位的单元格行 */
private Integer colIdx; /* 定位的单元格列 */
private Integer picType; /* 图片类型 见上面翻译方法 */
}
测试代码：
这里图省事我就都用同一个图片源了，导出的Excel上的图片会跟随单元格宽高变化而变化
右键另存为的图片大小也是一样的
@Test
public void excelPicWrite() {
ExcelWriter excelWriter = ExcelUtil.getWriter();
String picPath = "D:\\ymcd-project\\config\\ouput\\3-2.jpeg";
byte[] demoData = FileUtil.readBytes(picPath);
ExcelPicUtil.writePicToExcel(excelWriter, Arrays.asList(
ExcelPicUtil.ExcelPicWrite.builder()
.picType(Workbook.PICTURE_TYPE_JPEG)
.data(demoData)
.rowIdx(1)
.colIdx(3)
.build(),
ExcelPicUtil.ExcelPicWrite.builder()
.picType(Workbook.PICTURE_TYPE_JPEG)
.data(demoData)
.rowIdx(2)
.colIdx(3).build(),
ExcelPicUtil.ExcelPicWrite.builder()
.picType(Workbook.PICTURE_TYPE_JPEG)
.data(demoData)
.rowIdx(3)
.colIdx(3)
.build()
));
excelWriter.flush(new File("D:\\ymcd-project\\config\\ouput\\write-test.xlsx"));
excelWriter.close();
}
