poi读取excel中的内容（带图片）_poi读取excel中的图片

项目中有一需求：需要读取excel中的信息，带图片，需要将每一行的图片，做对应，整理方法如下：

主要逻辑：
1，获取excel对象，
2，读取excel中所有的图片流，将图片所处的位置和图片流存在map中getPictures（）方法
4，根据图片所处位置，获取图片流，上传图片，并将图片的所处位置和上传路径存在map中printImg()方法
5，读取excel中的每一行文字，当读取到图片列时，根据key获取第四步的map中的value，将路径以及文字信息存入list中；readData（）方法

1. controller层

	@PostMapping("/uploadFile")
	public List<Map<String, String>> uploadMonitorItem(MultipartFile upfile, String providerId) throws Exception {

		InputStream in = null;
		List<Map<String, String>> listob = null;
		in = upfile.getInputStream();
		listob = ExcelUtil.readExcelByInputStream(in, providerId);

		return listob;
	}
1
2
3
4
5
6
7
8
9
10

2.service层

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.qskj.framework.config.ERPConfig;

public class ExcelUtil {
	/**
	 * 读取 Excel文件内容
	 *
	 * @param inputstream 文件输入流
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String, String>> readExcelByInputStream(InputStream inputstream, String providerId)
			throws Exception {
		// 结果集
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();

		XSSFWorkbook wb = new XSSFWorkbook(inputstream);
		String filePath = ERPConfig.getProfile() + "/" + "pic/" + providerId + "/";//图片保存路径
		final XSSFSheet sheet = wb.getSheetAt(0);// 得到Excel工作表对象

		Map<String, PictureData> map = ExcelImgUtil.getPictures(sheet);//获取图片和位置

		Map<String, String> pathMap = ExcelImgUtil.printImg(map, filePath);//写入图片，并返回图片路径，key：图片坐标，value：图片路径
		list = ExcelImgUtil.readData(sheet, pathMap,providerId);
		return list;
	}
}
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

3，工具类

package com.qskj.project.util;

import java.io.File;
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 org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import com.qskj.common.utils.security.Md5Utils;
import com.qskj.framework.config.ERPConfig;

public class ExcelImgUtil {
	private static int counter = 0;

	/**
	 * 获取图片和位置 (xlsx)
	 * 
	 * @param sheet
	 * @return
	 * @throws IOException
	 */
	public static Map<String, PictureData> getPictures(XSSFSheet sheet) throws IOException {
		Map<String, PictureData> map = new HashMap<String, PictureData>();
		List<POIXMLDocumentPart> list = sheet.getRelations();
		for (POIXMLDocumentPart part : list) {
			if (part instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) part;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					XSSFPicture picture = (XSSFPicture) shape;
					XSSFClientAnchor anchor = picture.getPreferredSize();
					CTMarker marker = anchor.getFrom();
					String key = marker.getRow() + "-" + marker.getCol();
					byte[] data = picture.getPictureData().getData();
					map.put(key, picture.getPictureData());
				}
			}
		}
		return map;
	}

	public static Map<String, String> printImg(Map<String, PictureData> sheetList, String path) throws IOException {
		Map<String, String> pathMap = new HashMap<String, String>();
		Object[] key = sheetList.keySet().toArray();
		File f = new File(path);
		if (!f.exists()) {
			f.mkdirs(); // 创建目录
		}
		for (int i = 0; i < sheetList.size(); i++) {
			// 获取图片流
			PictureData pic = sheetList.get(key[i]);
			// 获取图片索引
			String picName = key[i].toString();
			// 获取图片格式
			String ext = pic.suggestFileExtension();
			String fileName = encodingFilename(picName);
			byte[] data = pic.getData();

			// 图片保存路径
			String imgPath = path + fileName + "." + ext;
			FileOutputStream out = new FileOutputStream(imgPath);

			imgPath = imgPath.substring(ERPConfig.getProfile().length(), imgPath.length());// 截取图片路径
			pathMap.put(picName, imgPath);
			out.write(data);
			out.close();
		}
		return pathMap;
	}

	private static final String encodingFilename(String fileName) {
		fileName = fileName.replace("_", " ");
		fileName = Md5Utils.hash(fileName + System.nanoTime() + counter++);
		return fileName;
	}

	/**
	 * 读取excel文字
	 * 
	 * Excel 07版本以上
	 * 
	 * @param sheet
	 */
	public static List<Map<String, String>> readData(XSSFSheet sheet, Map<String, String> map,String providerId) {
	
		List<Map<String, String>> newList = new ArrayList<Map<String, String>>();// 单行数据
		try {

			int rowNum = sheet.getLastRowNum() + 1;
			for (int i = 1; i < rowNum; i++) {// 从第三行开始读取数据,第一行是备注，第二行是标头

				Row row = sheet.getRow(i);// 得到Excel工作表的行
				if (row != null) {
					int col = row.getPhysicalNumberOfCells();
					// 单行数据
					
					Map<String, String> mapRes = new HashMap<String, String>();// 每格数据
					for (int j = 0; j < col; j++) {
						Cell cell = row.getCell(j);
						if (cell == null) {
							// arrayString.add("");
						} else if (cell.getCellType() == 0) {// 当时数字时的处理

							mapRes.put(getMapKey(j), new Double(cell.getNumericCellValue()).toString());
						} else {// 如果EXCEL表格中的数据类型为字符串型
							mapRes.put(getMapKey(j), cell.getStringCellValue().trim());

						}

					}

					if (i != 1) {// 不是标头列时，添加图片路径

						String path = map.get(i + "-9");
						mapRes.put(getMapKey(9), path);

					}
					mapRes.put("providerId", providerId);
					newList.add(mapRes);
				
				}

			}

		} catch (Exception e) {
		}
		return newList;
	}

	public static String getMapKey(int num) {
		String res = "";
		switch (num) {
		case 0:// 分类
			res = "secondDictCode";
			break;
		case 1:// 产品名称
			res = "productName";
			break;
		case 2:// 规格型号
			res = "specification";
			break;
		case 3:// 计量单位
			res = "unit";
			break;
		case 4:// 风格
			res = "style";
			break;
		case 5:// 颜色
			res = "color";
			break;
		case 6:// 采购单价
			res = "purchasePrice";
			break;
		case 7:// 材质
			res = "material";
			break;
		case 8:// 备注
			res = "remark";
			break;

		case 9:// 产品图片
			res = "picture";
			break;

		default:
			break;
		}
		return res;
	}
}

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
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184

4. postman请求测试：/poi/uploadFile?providerId=5
5. 最终效果

[
    {
        "unit": "计量单位",
        "color": "颜色",
        "material": "材质",
        "providerId": "5",
        "specification": "规格型号",
        "style": "风格",
        "remark": "备注",
        "purchasePrice": "采购单价",
        "secondDictCode": "分类",
        "productName": "产品名称",
        "picture": "产品图片"
    },
    {
        "unit": "平方米",
        "color": "白色,绿色,红色",
        "material": "木质1",
        "providerId": "5",
        "specification": "600*900",
        "style": "美式,北欧,中式",
        "remark": "大萨达十大1",
        "purchasePrice": "66.8",
        "secondDictCode": "家具",
        "productName": "床",
        "picture": "/pic/5/b3e42b2d741cc16bff4fefaa6bb61d87.jpeg"
    },
    {
        "providerId": "5",
        "picture": null
    }
]
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

github代码demo，及使用说明

csdn资源文件，代码demo及使用说明