JAVA POI的excel中包含图片进行读取保存,单张图片,多张图片

た入场券 2023-10-13 12:01 8阅读 0赞

### JAVA POI的excel中包含图片进行读取保存,单张图片,多张图片 ###

\---------------------------------------------效果----------------------------------------------------------

1.单张图片  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw_size_16_color_FFFFFF_t_70_pic_center]

2.多张图片  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw_size_16_color_FFFFFF_t_70_pic_center 1]

import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;
    
    import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
    import org.apache.poi.hssf.usermodel.HSSFPicture;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFShape;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.PictureData;
    
    public class importExcelByImg {
        
    
    	public static void main(String[] args) {
        
    		// 1.一个单元格一个图片
    		importExcelOne();
    		// 2.一个单元格多个图片
    		importExcelTwo();
    
    	}
    
    	/**
    	 * 1.一个单元格一个图片
    	 */
    	public static void importExcelOne() {
        
    		try {
        
    			FileInputStream fileInputStream = new FileInputStream("D://1603169080802.xls");
    
    			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);// 工作簿
    
    			HSSFSheet sheet = hssfWorkbook.getSheetAt(0);// 工作表
    
    			int lastRowNum = sheet.getLastRowNum();// 获取最后一行序号,从零开始
    
    			Map<String, PictureData> picMap = new HashMap<>();// 存储图片信息和坐标
    
    			List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
    
    			if (list != null && list.size() > 0) {
        // 处理获取图片信息和坐标
    				list = list.stream().filter(item -> item instanceof HSSFPicture).collect(Collectors.toList());// 过滤出图片的数据
    				for (HSSFShape hssfShape : list) {
        
    					HSSFPicture hSSFPicture = (HSSFPicture) hssfShape;
    					HSSFClientAnchor hSSFClientAnchor = (HSSFClientAnchor) hSSFPicture.getAnchor();
    					PictureData pictureData = hSSFPicture.getPictureData();
    					picMap.put(hSSFClientAnchor.getRow1() + "," + hSSFClientAnchor.getCol1(), pictureData);
    				}
    			}
    
    			for (int i = 1; i <= lastRowNum; i++) {
        
    				HSSFRow row = sheet.getRow(i);
    				String name = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue();
    				String sex = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
    
    				// 获取图片数据
    				PictureData pictureData = picMap.get(i + "," + 2);
    				String path = "";
    				if (pictureData != null) {
        
    					String suggestFileExtension = pictureData.suggestFileExtension();// 图片格式
    					path = "D://" + i + "-2." + suggestFileExtension;// 存储路径
    					FileOutputStream out = new FileOutputStream(path);// 流写入
    					out.write(pictureData.getData());
    					out.close();
    				}
    
    				System.out.println("名称:" + name + ",    性别:" + sex + ",    图片存储路径:" + path);
    
    			}
    
    		} catch (IOException e) {
        
    			e.printStackTrace();
    		}
    	}
    
    	/**
    	 * 1.一个单元格多图片
    	 */
    	public static void importExcelTwo() {
        
    		try {
        
    			FileInputStream fileInputStream = new FileInputStream("D://1603169081675.xls");
    
    			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);// 工作簿
    
    			HSSFSheet sheet = hssfWorkbook.getSheetAt(0);// 工作表
    
    			int lastRowNum = sheet.getLastRowNum();// 获取最后一行序号,从零开始
    
    			Map<String, List<PictureData>> picMap = new HashMap<>();// 存储图片信息和坐标
    
    			List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
    
    			if (list != null && list.size() > 0) {
        // 处理获取图片信息和坐标
    				list = list.stream().filter(item -> item instanceof HSSFPicture).collect(Collectors.toList());// 过滤出图片的数据
    				for (HSSFShape hssfShape : list) {
        
    					HSSFPicture hSSFPicture = (HSSFPicture) hssfShape;
    					HSSFClientAnchor hSSFClientAnchor = (HSSFClientAnchor) hSSFPicture.getAnchor();
    					PictureData pictureData = hSSFPicture.getPictureData();
    					String point = hSSFClientAnchor.getRow1() + "," + hSSFClientAnchor.getCol1();
    					// 如果存在这个坐标KEY表示相同单元格中的图片,直接集合添加该图片,不存在该坐标key直接创建添加
    					if (picMap.containsKey(point)) {
        
    						picMap.get(point).add(pictureData);
    					} else {
        
    						List<PictureData> arrayList = new ArrayList<PictureData>();
    						arrayList.add(pictureData);
    						picMap.put(point, arrayList);
    					}
    				}
    			}
    
    			for (int i = 1; i <= lastRowNum; i++) {
        
    				HSSFRow row = sheet.getRow(i);
    				String name = row.getCell(0) == null ? "" : row.getCell(0).getStringCellValue();
    				String sex = row.getCell(1) == null ? "" : row.getCell(1).getStringCellValue();
    
    				// 获取图片数据
    				List<PictureData> pictureDataList = picMap.get(i + "," + 2);
    				String paths = "";
    				if (pictureDataList != null)
    					for (PictureData pictureData : pictureDataList) {
        
    						String suggestFileExtension = pictureData.suggestFileExtension();// 图片格式
    						String path = "D://" + i + "-2--" + pictureDataList.indexOf(pictureData) + "."
    								+ suggestFileExtension;// 存储路径
    						paths = paths + path;
    						FileOutputStream out = new FileOutputStream(path);// 流写入
    						out.write(pictureData.getData());
    						out.close();
    					}
    
    				System.out.println("名称:" + name + ",    性别:" + sex + ",    图片存储路径:" + paths);
    
    			}
    
    		} catch (IOException e) {
        
    			e.printStackTrace();
    		}
    	}
    
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20201020161509215.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201020161509531.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk2NDQwMw==,size_16,color_FFFFFF,t_70#pic_center