JAVA POI的excel中包含图片进行读取保存,单张图片,多张图片_java获取单元格是多个图片,保存数据库中

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

---------------------------------------------效果-----------------------------------------------------------
2.多张图片

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();
		}
	}

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143