poi读取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及使用说明