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

关注

原创最新推荐文章于 2026-08-17 15:01:16 发布 1.1w 阅读 AI 写同款· GEO 优化 ›

【POI稀有技巧】获取excel导入的图片并存储最近公司要通过excel进行图片导入导出多行试题数据，发现网上资料较少，并且很多教程也用不了，特此记录分享，让各位道友减少查找资料时间。阅读详情

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

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

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

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

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

postman请求测试：/poi/uploadFile?providerId=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
    }
]

github代码demo，及使用说明

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

收录于