EasyExcel 读取单元格内图片
1.需求介绍
需要实现Excel数据批量导入，且Excel中某个单元格内存在图片，需要将图片解析上传,并与所在行数据进行绑定，具体数据如：
2.具体实现
2.1 版本介绍
- easyexcel：3.3.2
- SpringBoot：2.7.1
- hutool：5.7.21
2.2 解决思路
参考hutool ExcelPicUtil工具类，通过调用getPicMap方法可以获得Map<String, PictureData>数据，其中key为:行_列,value为:此单元格图片对象
通过学习其源码发现存在一些问题：
-
当同一个单元格内存在多张照片，会出现覆盖情况，即先解析出来的图片会被后解析的覆盖，造成图片丢失的情况
-
采用双循环的方式解析数据，且获取的PictureData并不符合文件上传需求，若直接调用会存在两次双循环，解析速度变慢
2.3 具体实现
- 新增ExcelImageUtil工具类
public class ExcelImageUtil {
/**
* 获取工作簿指定sheet中图片列表
*
* @param workbook 工作簿{@link Workbook}
* @param sheetIndex sheet的索引
* @return 图片映射，键格式：行_列，值：{@link PictureData}
*/
public static Map<Integer, List<Attachment>> getPicMap(Workbook workbook, int sheetIndex) {
Assert.notNull(workbook, "Workbook must be not null !");
if (sheetIndex < 0) {
sheetIndex = 0;
}
// 由于实际业务提供的模板为XLSX,暂时不考虑XLS解析
if (workbook instanceof HSSFWorkbook) {
return null;
} else if (workbook instanceof XSSFWorkbook) {
try {
return getRowPicMapXlsx((XSSFWorkbook) workbook, sheetIndex);
} catch (IOException e) {
e.printStackTrace();
}
} else {
throw new IllegalArgumentException(StrUtil.format("Workbook type [{}] is not supported!", workbook.getClass()));
}
return Collections.emptyMap();
}
// -------------------------------------------------------------------------------------------------------------- Private method start
/**
* 获取XLS工作簿指定sheet中图片列表
*
* @param workbook 工作簿{@link Workbook}
* @param sheetIndex sheet的索引
* @return 图片映射，键格式：行_列，值：{@link PictureData}
*/
//private static Map<Integer, List<Attachment>> getPicMapXls(HSSFWorkbook workbook, int sheetIndex) {
// final Map<String, List<Attachment>> picMap = new HashMap<>();
// final List<HSSFPictureData> pictures = workbook.getAllPictures();
// if (CollectionUtil.isNotEmpty(pictures)) {
// final HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
// HSSFClientAnchor anchor;
// int pictureIndex;
// for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
// if (shape instanceof HSSFPicture) {
// pictureIndex = ((HSSFPicture) shape).getPictureIndex() - 1;
// anchor = (HSSFClientAnchor) shape.getAnchor();
// HSSFPictureData hssfPictureData = pictures.get(pictureIndex);
// Attachment attachment = new Attachment();
// ByteArrayInputStream inputStream = new ByteArrayInputStream(hssfPictureData.getData());
// hssfPictureData.get
// name = name.substring(name.lastIndexOf("/") + 1);
// InputStream inputStreamPart = packagePart.getInputStream();
// attachment.setFileSize(Func.toLong(inputStreamPart.available()));
// BladeFile bladeFile = MinioUtil.getMinioTemplate().putFile(name, inputStream);
// attachment.setObjName(bladeFile.getName());
// attachment.setDisplayName(bladeFile.getOriginalName());
// //获取后缀名
// attachment.setFileType(FileUtil.extName(bladeFile.getOriginalName()));
// picMap.put(StrUtil.format("{}_{}", anchor.getRow1(), anchor.getCol1()), );
// }
// }
// }
// return picMap;
//}
/**
* 获取XLSX工作簿指定sheet中图片列表
*
* @param workbook 工作簿{@link Workbook}
* @param sheetIndex sheet的索引
* @return 图片映射，键格式：行_列，值：{@link PictureData}
*/
private static Map<Integer, List<Attachment>> getRowPicMapXlsx(XSSFWorkbook workbook, int sheetIndex) throws IOException {
final Map<Integer, List<Attachment>> sheetIndexPicMap = new HashMap<>();
final XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
XSSFDrawing drawing;
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
drawing = (XSSFDrawing) dr;
final List<XSSFShape> shapes = drawing.getShapes();
XSSFPicture pic;
CTMarker ctMarker;
for (XSSFShape shape : shapes) {
if (shape instanceof XSSFPicture) {
pic = (XSSFPicture) shape;
ctMarker = pic.getPreferredSize().getFrom();
int row = ctMarker.getRow();
XSSFPictureData data = pic.getPictureData();
Attachment attachment = new Attachment();
PackagePart packagePart = data.getPackagePart();
String name = packagePart.getPartName().getName();
name = name.substring(name.lastIndexOf("/") + 1);
InputStream inputStreamPart = packagePart.getInputStream();
attachment.setFileSize(Func.toLong(inputStreamPart.available()));
try {
BladeFile bladeFile = MinioUtil.getMinioTemplate().putFile(name, inputStreamPart);
attachment.setObjName(bladeFile.getName());
attachment.setDisplayName(bladeFile.getOriginalName());
//获取后缀名
attachment.setFileType(FileUtil.extName(bladeFile.getOriginalName()));
} catch (Exception e) {
log.error("MINIO 上传文件失败，" + e.getMessage());
continue;
}
// 通过map.computeIfAbsent 方法，如果存在key 则返回已有的List，不存在则new ArrayList
// 再通过add()方法，添加当前对象
sheetIndexPicMap.computeIfAbsent(row, k -> new ArrayList<>()).add(attachment);
}
// 其他类似于图表等忽略，see: https://gitee.com/loolly/hutool/issues/I38857
}
}
}
return sheetIndexPicMap;
}
}
-
解析数据
public boolean importInspectData(MultipartFile file) throws IOException, ParseException { InputStream inputStream = file.getInputStream(); InputStream inputStream2 = file.getInputStream(); ExcelReader excelReader = ExcelUtil.getReader(inputStream2); Map<Integer, List<Attachment>> attachMap = ExcelImageUtil.getPicMap(excelReader.getWorkbook(), 0); List<InspectionTemplateExcel> inspectionTemplateExcelList = EasyExcelUtil.read(inputStream, 0, 1, InspectionTemplateExcel.class); if (Func.isEmpty(inspectionTemplateExcelList)) { return true; } int i = 1; for (InspectionTemplateExcel excel : inspectionTemplateExcelList) { excel.setNo(i++); } Map<String, List<InspectionTemplateExcel>> collect = inspectionTemplateExcelList.stream().collect(Collectors.groupingBy(e -> e.getDiscoverDatetime() + "#" + e.getSegmentId())); for (Map.Entry<String, List<InspectionTemplateExcel>> stringListEntry : collect.entrySet()) { String key = stringListEntry.getKey(); String[] split = key.split("#"); Date date = ExcelDateUtil.parseDate(split[0]); List<InspectionTemplateExcel> value = stringListEntry.getValue(); InspectionTemplateExcel templateExcel = inspectionTemplateExcelList.get(0); InspectionTask task = new InspectionTask(); task.setType(Func.toInt(templateExcel.getInspectType())); task.setStartDatetime(date); task.setEndDatetime(date); task.setPersonId(AuthUtil.getUserName()); task.setDeptId(SysCache.getDeptName(Func.firstLong(AuthUtil.getDeptId()))); task.setSource(3); save(task); Long taskId = task.getId(); Set<String> diseaseTypes = new HashSet<>(); for (InspectionTemplateExcel excel : value) { int no = excel.getNo(); InspectionBizRecord record = cn.hutool.core.bean.BeanUtil.copyProperties(excel, InspectionBizRecord.class, "discoverDatetime"); record.setSource(3); record.setType(excel.getDiseaseType()); diseaseTypes.add(excel.getDiseaseType()); record.setInspectionTaskId(taskId); if (attachMap.containsKey(no)) { record.setImages(attachMap.get(no)); } fillLocations(record); recordService.save(record); } } return true; }