POI 读取Excel中的图片 以feign调用保存

思路

1. 获得所有图片---->
2. 得到sheet DrawingPatriarch的所有shape—>
3. 获得shape的anchor —>
4. 获得picture的pictureIndex(这个很关键)------->
5. 最后假定pictureIndex就是allPictures中图片的位置,从而获得这张picture的data信息.

实现

• POI 提供了对2003版本的Excel的支持 ---- HSSFWorkbook
• POI 提供了对2007版本以及更高版本的支持 ---- XSSFWorkbook

下面使用2007版本以及更高版本进行演示

//String filePath = "E:\\123.xlsx";
	        
//判断是否为excel类型文件
if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx"))
{
    System.out.println("文件不是excel类型");
}
//通过一个绝对地址获取XSSFWorkbook 
FileInputStream fis = new FileInputStream(filePath);	  
XSSFWorkbook workbook = new XSSFWorkbook( fis );    

//通过URL获取XSSFWorkbook 
URL urlC = new URL("http://127.0.0.1.229/group1/wKgBol54HeeAM333ABFVp2wMmY43.xlsx");
URLConnection conn = urlC.openConnection();
XSSFWorkbook workbook1 = new XSSFWorkbook( conn.getInputStream());      

if (workbook != null) {
   int sheetCount = workbook.getNumberOfSheets();
     if (sheetCount > 0) {
         // 文本内容
         StringBuilder content = new StringBuilder();
         for (int i = 0; i < sheetCount ; i++) {
             XSSFSheet sheet = workbook.getSheetAt(i);	 
             // 获取证书图片并保存 map为行和证书图片id的map集合
            Map<Integer, String> map =  certificateService.getSheetPictrues07(sheet,workbook);   
         }  
	 }
}    


public Map<Integer, String> getSheetPictrues07( XSSFSheet sheet, XSSFWorkbook workbook) {
     // Integer 保存行号   PictureData为图片流
     Map<Integer, String> sheetIndexPicMap = new HashMap<Integer, String>();

     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();
                 // 此示例 图片保存在excel中的第七列中
                 if(ctMarker.getCol()==7 ){
                     Integer picIndex = ctMarker.getRow();
                     String  suffix =  pic.getPictureData().suggestFileExtension();
                     byte[] bytes= pic.getPictureData().getData();
                     try {
                         ***// ！！！ 此处的name必须与feign中的@RequestPart(value="headPortrait") 的value相同***
                         MultipartFile file = new MockMultipartFile("headPortrait","图片名称"+"."+suffix,suffix, new ByteArrayInputStream(bytes));
                         R<Object> objectR =  sysFeignUploadFileService.feignUpload(file);
                         if(objectR.getCode()==0 && objectR.getData()!=null  ){
                             Map<String,String> map = mapper.readValue(JSON.toJSONString(objectR.getData()),new TypeReference< Map<String,String>>() {} );
                             String id=  map.get("id");
                             sheetIndexPicMap.put(picIndex,id);
                         }
                     } catch (IOException e) {
                         e.printStackTrace();
                     }
                 }
             }
         }
     }
     return sheetIndexPicMap;
 }

//Feign接口
@FeignClient(contextId="feignUploadFileService", value = SysConstants.SYS_SERVICE,  fallback = SysUploadFileRemoteServiceImpl.class  )
public interface SysFeignUploadFileService {
/**
  * 上传文件
  */
 @PostMapping(value = "/sys/file/feignUpload",consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
 R<Object> feignUpload (@RequestPart(value="headPortrait") MultipartFile file);
}

// 文件上传接口
@PostMapping("/feignUpload")
@ResponseBody
@ApiOperation("上传文件,返回文件的最终浏览地址-PC/APP")
@ApiResponses(value = {
	@ApiResponse(code = 0, message = "上传成功"),
	@ApiResponse(code = 1, message = "该上传文件类型不支持"),
	@ApiResponse(code = 2, message = "上传失败")
})
public R<Object> feignUpload(@RequestPart(value="headPortrait") MultipartFile  file, HttpServletRequest request) throws IOException, ServletException {
	Collection<Part> parts = request.getParts();
	System.out.println(JSONObject.toJSONString(parts, true));
	
	//获取文件名称
	String filename = file.getOriginalFilename();
	String suffix = com.tr.common.utils.StringUtils.getSuffixName(filename);
	
	//uploadTypes() 获取支持上传的文件类型
	Set<String> types = uploadTypes();
	if(!types.contains(suffix.toLowerCase())) {
		return   R.error("该上传文件类型不支持.");
	}
	//获取文件上传下载工具类
	AltFileUtils altFile = AltFileUtils.getFileUtil();
	//获取上传文件的实体类(包含  url、 path、 size 、 name、等能内容)
	SysUploadFileEntity entity = altFile.uploadFile(file);
	if(entity == null) {
		return R.error("上传失败");
	}
	
	// 插入数据库 （保存图片导数据库）
	uploadService.insert(entity);
	
	Map<String, Object> map = new HashMap<>();
	map.put("id", entity.getId());
	map.put("url", entity.getFileUrl());
	//返回图片的id和url
	return R.ok().setData(map);
}