devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

POI读取Excel中图片_poi读取excel图片

作者：笔触狂放9 | 2024-03-20 11:36:31

赞

踩

poi读取excel图片
  1. @RequestMapping("importEsComPartsAndPhotoExcel")
  2. 	public ActionResult getDataFromExcel(@RequestParam(value = "filename") MultipartFile file) throws IOException {
  3. 		
  4. 		String filename = file.getOriginalFilename();
  5. 		FileInputStream fis = (FileInputStream) file.getInputStream();
  6.         //判断是否为excel类型文件
  7.         if (!filename.endsWith(".xls") && !filename.endsWith(".xlsx")) {
  8.             System.out.println("文件不是excel类型");
  9.         }
  10.         Workbook wookbook = null;
  11.         Sheet sheet = null;
  12.         try {
  13.             //2007版本的excel，用.xlsx结尾
  14.             wookbook = new XSSFWorkbook(fis);//得到工作簿
  15.         } catch (IOException e) {
  16.             e.printStackTrace();
  17.         }
  18.         Map<String, PictureData> maplist = null;
  19.         sheet = (Sheet) wookbook.getSheetAt(0);
  20.         // 判断用07还是03的方法获取图片
  21.         if (filename.endsWith(".xls")) {
  22.             maplist = getPictures1((HSSFSheet) sheet);
  23.         } else if (filename.endsWith(".xlsx")) {
  24.             maplist = getPictures2((XSSFSheet) sheet);
  25.         }
  26.         Map<String,String> map = null;
  27.         try {
  28.             map = printImg(maplist);
  29.         } catch (Exception e) {
  30.             Log.error(e.getMessage(),e);
  31.         } finally {
  32.             //释放map
  33.             if (maplist != null) {
  34.                 maplist = null;
  35.             }
  36.         }
  37.         //得到一个工作表
  38.         //获得表头
  39. //        Row rowHead = sheet.getRow(0);
  40.  
  41.         //获得数据的总行数
  42.         int totalRowNum = sheet.getLastRowNum();
  43.         //要获得属性
  44.         String code = "";
  45.         
  46.         try{
  47.             //获得所有数据
  48.             for (int i = 0; i <= totalRowNum; i++) {
  49.                 //获得第i行对象
  50.                 Row row = sheet.getRow(i);
  51.  
  52.                 Cell cell = row.getCell(0);
  53.                 if (cell == null) {
  54.                     continue;
  55.                 }
  56.                 cell.setCellType(CellType.STRING);
  57.                 code = cell.getStringCellValue().toString();
  58.                 if (StringUtils.isEmpty(code)) {
  59.                     break;
  60.                 }
  61.                 EsComParts ecd = escompartsService.getPartsByCode(code);
  62.                 if(ecd != null) {
  63.                 	String partsPhoto = StringUtils.changeToString(ecd.getFparts_photo());
  64.                 	if(StringUtils.equals(partsPhoto, "")) {
  65.                 		String photoPath = StringUtils.changeToString(map.get(i+""));
  66.                 		if(!StringUtils.equals(photoPath, "")) {
  67.                 			File filePhoto = new File(photoPath); 
  68.                     		MultipartFile Photo = getMultipartFile(filePhoto);
  69.  
  70.             				MultipartFile[] Photos = {Photo};
  71.             				if(Photos != null) {
  72.             					ecd.setFparts_photo(PhotoForOSSUtils.changePhotoToString(Photos));
  73.                 			    escompartsService.update(ecd);
  74.             				}
  75.                 		}
  76.                 	}
  77.                 }
  78.             }
  79.         }catch (Exception e){
  80.             System.out.println("数据不符合规范");
  81.         }
  82.         //使用完成关闭
  83.         wookbook.close();
  84.         if (fis != null) {
  85.             fis.close();
  86.         }
  87. 		return ActionResult.Succeed("导入成功!");
  88.     }
  1. public static void inputStreamToFile(InputStream ins, File file) {
  2. 		try {
  3. 			OutputStream os = new FileOutputStream(file);
  4. 			int bytesRead = 0;
  5. 			byte[] buffer = new byte[8192];
  6. 			while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
  7. 				os.write(buffer, 0, bytesRead);
  8. 			}
  9. 			os.close();
  10. 			ins.close();
  11. 		} catch (Exception e) {
  12. 			e.printStackTrace();
  13. 		}
  14. 	}
  15. 	@SuppressWarnings("unused")
  16. 	@RequestMapping("importEsComPartsAndPhotoExcel")
  17. 	public ActionResult getDataFromExcel(@RequestParam(value = "filename") MultipartFile file) throws IOException {
  18. 		
  19. 		String filename = file.getOriginalFilename();
  20. 		FileInputStream fis = (FileInputStream) file.getInputStream();
  21.         //判断是否为excel类型文件
  22.         if (!filename.endsWith(".xls") && !filename.endsWith(".xlsx")) {
  23.             System.out.println("文件不是excel类型");
  24.         }
  25.         Workbook wookbook = null;
  26.         Sheet sheet = null;
  27.         try {
  28.             //2007版本的excel，用.xlsx结尾
  29.             wookbook = new XSSFWorkbook(fis);//得到工作簿
  30.         } catch (IOException e) {
  31.             e.printStackTrace();
  32.         }
  33.         Map<String, PictureData> maplist = null;
  34.         sheet = (Sheet) wookbook.getSheetAt(0);
  35.         // 判断用07还是03的方法获取图片
  36.         if (filename.endsWith(".xls")) {
  37.             maplist = getPictures1((HSSFSheet) sheet);
  38.         } else if (filename.endsWith(".xlsx")) {
  39.             maplist = getPictures2((XSSFSheet) sheet);
  40.         }
  41.         Map<String,String> map = null;
  42.         try {
  43.             map = printImg(maplist);
  44.         } catch (Exception e) {
  45.             Log.error(e.getMessage(),e);
  46.         } finally {
  47.             //释放map
  48.             if (maplist != null) {
  49.                 maplist = null;
  50.             }
  51.         }
  52.         //得到一个工作表
  53.         //获得表头
  54. //        Row rowHead = sheet.getRow(0);
  55.  
  56.         //获得数据的总行数
  57.         int totalRowNum = sheet.getLastRowNum();
  58.         //要获得属性
  59.         String code = "";
  60.         
  61.         try{
  62.             //获得所有数据
  63.             for (int i = 0; i <= totalRowNum; i++) {
  64.                 //获得第i行对象
  65.                 Row row = sheet.getRow(i);
  66.  
  67.                 Cell cell = row.getCell(0);
  68.                 if (cell == null) {
  69.                     continue;
  70.                 }
  71.                 cell.setCellType(CellType.STRING);
  72.                 code = cell.getStringCellValue().toString();
  73.                 if (StringUtils.isEmpty(code)) {
  74.                     break;
  75.                 }
  76.                 EsComParts ecd = escompartsService.getPartsByCode(code);
  77.                 if(ecd != null) {
  78.                 	String partsPhoto = StringUtils.changeToString(ecd.getFparts_photo());
  79.                 	if(StringUtils.equals(partsPhoto, "")) {
  80.                 		String photoPath = StringUtils.changeToString(map.get(i+""));
  81.                 		if(!StringUtils.equals(photoPath, "")) {
  82.                 			File filePhoto = new File(photoPath); 
  83.                     		MultipartFile Photo = getMultipartFile(filePhoto);
  84.  
  85.             				MultipartFile[] Photos = {Photo};
  86.             				if(Photos != null) {
  87.             					ecd.setFparts_photo(PhotoForOSSUtils.changePhotoToString(Photos));
  88.                 			    escompartsService.update(ecd);
  89.             				}
  90.                 		}
  91.                 	}
  92.                 }
  93.             }
  94.         }catch (Exception e){
  95.             System.out.println("数据不符合规范");
  96.         }
  97.         //使用完成关闭
  98.         wookbook.close();
  99.         if (fis != null) {
  100.             fis.close();
  101.         }
  102. 		return ActionResult.Succeed("导入成功!");
  103.     }
  104.  
  105.     /**
  106.      * 获取图片和位置 (xls)
  107.      *
  108.      * @param sheet
  109.      * @return
  110.      * @throws IOException
  111.      */
  112.     public Map<String, PictureData> getPictures1(HSSFSheet sheet) throws IOException {
  113.         Map<String, PictureData> map = new HashMap<String, PictureData>();
  114.         List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
  115.         for (HSSFShape shape : list) {
  116.             if (shape instanceof HSSFPicture) {
  117.                 HSSFPicture picture = (HSSFPicture) shape;
  118.                 HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
  119.                 PictureData pdata = picture.getPictureData();
  120.                 String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
  121.                 String proName = sheet.getRow(cAnchor.getRow1()).getCell(0).getStringCellValue();
  122.                 if(cAnchor.getCol1()==1){
  123.                     key = proName+="01";
  124.                 }else if(cAnchor.getCol1()==11){
  125.                     key = proName+="02";
  126.                 }
  127.                 map.put(key, pdata);
  128.             }
  129.         }
  130.         return map;
  131.     }
  132.  
  133.     /**
  134.      * 获取图片和位置 (xlsx)
  135.      *
  136.      * @param sheet
  137.      * @return
  138.      * @throws IOException
  139.      */
  140.     public Map<String, PictureData> getPictures2(XSSFSheet sheet) throws IOException {
  141.         Map<String, PictureData> map = new HashMap<String, PictureData>();
  142.         List<POIXMLDocumentPart> list = sheet.getRelations();
  143.         for (POIXMLDocumentPart part : list) {
  144.             if (part instanceof XSSFDrawing) {
  145.                 XSSFDrawing drawing = (XSSFDrawing) part;
  146.                 List<XSSFShape> shapes = drawing.getShapes();
  147.                 for (XSSFShape shape : shapes) {
  148.                     XSSFPicture picture = (XSSFPicture) shape;
  149.                     XSSFClientAnchor anchor = picture.getPreferredSize();
  150.                     CTMarker marker = anchor.getFrom();
  151.                     int rowNum = marker.getRow();
  152.                     int colNum = marker.getCol();
  153.                     String key = marker.getRow() + "";
  154. //                    String key = marker.getRow() + "-" + marker.getCol();
  155.                     System.out.println(colNum);
  156.                     if(colNum==1){
  157.                         if(picture.getPictureData().getData().length<120*1024){
  158.                             map.put(key, picture.getPictureData());
  159.                         }else{
  160.                             // 主图大小不符合要求
  161.                             System.out.println(String.format("{}行{}列主图大小不符合要求",rowNum,colNum));
  162.                         }
  163.                         map.put(key, picture.getPictureData());
  164.                     }
  165.                 }
  166.             }
  167.         }
  168.         return map;
  169.     }
  170.  
  171.  
  172.     /**
  173.      * 删除文件
  174.      *
  175.      * @param fileName 文件名
  176.      * @return 删除成功返回true,失败返回false
  177.      */
  178.     public static boolean deleteFile(String fileName) {
  179.         File file = new File(fileName);
  180.         if (file.isFile() && file.exists()) {
  181.             file.delete();
  182.             return true;
  183.         } else {
  184.             return false;
  185.         }
  186.     }
  187.  
  188.     /**
  189.      * 图片写出
  190.      *
  191.      * @param sheetList
  192.      * @throws Exception
  193.      */
  194.     public static Map<String,String> printImg(Map<String, PictureData> sheetList) throws Exception {
  195.         Map<String,String> map = new HashMap<>();
  196.         Object key[] = sheetList.keySet().toArray();
  197.         String filePath = "";
  198.         File delPath = new File("/work/poi");
  199. //        File delPath = new File("D://poi");
  200.         boolean flag = true;
  201.         // 删除文件夹下的所有文件和文件夹
  202.         File[] fileStr = delPath.listFiles();
  203.         for (int i = 0; i < fileStr.length; i++) {  // 循环删除所有的子文件
  204.             // 删除子文件
  205.             if (fileStr[i].isFile()) {
  206.                 flag = deleteFile(fileStr[i].getAbsolutePath());
  207.                 if (!flag) {
  208.                     break;
  209.                 }
  210.             } 
  211.         }
  212.  
  213.         delPath.delete();
  214.         File files = new File("/work/poi");
  215. //        File files=new File("D://poi");
  216.         files.mkdirs();
  217.         for (int i = 0; i < sheetList.size(); i++) {
  218.             // 获取图片流
  219.             PictureData pic = sheetList.get(key[i]);
  220.             // 获取图片索引
  221.             String picName = key[i].toString();
  222.             // 获取图片格式
  223.             String ext = pic.suggestFileExtension();
  224.             byte[] data = pic.getData();
  225.             
  226.             filePath = "/work/poi/" + picName + "." + ext;
  227. //            filePath = "D://poi/" + picName + "." + ext;
  228.             map.put(picName, filePath);
  229.             
  230.             FileOutputStream out = new FileOutputStream(filePath);
  231.             out.write(data);
  232.             out.close();
  233.         }
  234.         return map;
  235.     }
  236.     public static MultipartFile getMultipartFile(File file) {
  237.         FileItem item = new DiskFileItemFactory().createItem("file"
  238.             , MediaType.MULTIPART_FORM_DATA_VALUE
  239.             , true
  240.             , file.getName());
  241.         try (InputStream input = new FileInputStream(file);
  242.             OutputStream os = item.getOutputStream()) {
  243.             // 流转移
  244.             IOUtils.copy(input, os);
  245.         } catch (Exception e) {
  246.             throw new IllegalArgumentException("Invalid file: " + e, e);
  247.         }
  248.  
  249.         return new CommonsMultipartFile(item);
  250.     }

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/273058
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号