利用POI获取Excel中图片和图片位置

利用POI获取Excel中图片和图片位置(支持excel2003or2007多sheet)

转自:http://blog.csdn.net/delongcpp/article/details/8833995

第三方JAR包(apache下载POI即可):

  • poi-3.9-20121203.jar
  • dom4j-1.6.1.jar
  • poi-ooxml-3.9-20121203.jar
  • poi-ooxml-schemas-3.9-20121203.jar
  • poi-scratchpad-3.9-20121203.jar
  • stax-api-1.0.1.jar
  • xmlbeans-2.3.0.jar

测试代码:

[java] view plaincopy  
  1. package com;  
  2.   
  3. import java.io.File;  
  4. import java.io.FileInputStream;  
  5. import java.io.FileOutputStream;  
  6. import java.io.IOException;  
  7. import java.io.InputStream;  
  8. import java.util.ArrayList;  
  9. import java.util.HashMap;  
  10. import java.util.List;  
  11. import java.util.Map;  
  12.   
  13. import org.apache.poi.POIXMLDocumentPart;  
  14. import org.apache.poi.hssf.usermodel.HSSFClientAnchor;  
  15. import org.apache.poi.hssf.usermodel.HSSFPicture;  
  16. import org.apache.poi.hssf.usermodel.HSSFPictureData;  
  17. import org.apache.poi.hssf.usermodel.HSSFShape;  
  18. import org.apache.poi.hssf.usermodel.HSSFSheet;  
  19. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  20. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;  
  21. import org.apache.poi.ss.usermodel.PictureData;  
  22. import org.apache.poi.ss.usermodel.Sheet;  
  23. import org.apache.poi.ss.usermodel.Workbook;  
  24. import org.apache.poi.ss.usermodel.WorkbookFactory;  
  25. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;  
  26. import org.apache.poi.xssf.usermodel.XSSFDrawing;  
  27. import org.apache.poi.xssf.usermodel.XSSFPicture;  
  28. import org.apache.poi.xssf.usermodel.XSSFShape;  
  29. import org.apache.poi.xssf.usermodel.XSSFSheet;  
  30. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  31. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;  
  32.   
  33. /** 
  34.  * @since 2013-04-22 
  35.   
  36.  * @author Gerrard 
  37.   
  38.  * 获取excel中 图片,并得到图片位置,支持03 07 多sheet 
  39.   
  40.  */  
  41. public class GetImgFromExcel {  
  42.   
  43.     /** 
  44.      * @param args 
  45.      * @throws IOException  
  46.      * @throws InvalidFormatException  
  47.      */  
  48.     public static void main(String[] args) throws InvalidFormatException, IOException {  
  49.           
  50.         // 创建文件  
  51.         File file = new File("model/test.xls");  
  52.           
  53.         // 创建流  
  54.         InputStream input = new FileInputStream(file);  
  55.           
  56.         // 获取文件后缀名  
  57.         String fileExt =  file.getName().substring(file.getName().lastIndexOf(".") + 1);  
  58.           
  59.         // 创建Workbook  
  60.         Workbook wb = null;  
  61.           
  62.         // 创建sheet  
  63.         Sheet sheet = null;  
  64.   
  65.         //根据后缀判断excel 2003 or 2007+  
  66.         if (fileExt.equals("xls")) {  
  67.             wb = (HSSFWorkbook) WorkbookFactory.create(input);  
  68.         } else {  
  69.             wb = new XSSFWorkbook(input);  
  70.         }  
  71.           
  72.         //获取excel sheet总数  
  73.         int sheetNumbers = wb.getNumberOfSheets();  
  74.           
  75.         // sheet list  
  76.         List<Map<String, PictureData>> sheetList = new ArrayList<Map<String, PictureData>>();  
  77.           
  78.         // 循环sheet  
  79.         for (int i = 0; i < sheetNumbers; i++) {  
  80.               
  81.             sheet = wb.getSheetAt(i);  
  82.             // map等待存储excel图片  
  83.             Map<String, PictureData> sheetIndexPicMap;   
  84.               
  85.             // 判断用07还是03的方法获取图片  
  86.             if (fileExt.equals("xls")) {  
  87.                 sheetIndexPicMap = getSheetPictrues03(i, (HSSFSheet) sheet, (HSSFWorkbook) wb);  
  88.             } else {  
  89.                 sheetIndexPicMap = getSheetPictrues07(i, (XSSFSheet) sheet, (XSSFWorkbook) wb);  
  90.             }  
  91.             // 将当前sheet图片map存入list  
  92.             sheetList.add(sheetIndexPicMap);  
  93.         }  
  94.           
  95.         printImg(sheetList);  
  96.   
  97.     }  
  98.       
  99.     /** 
  100.      * 获取Excel2003图片 
  101.      * @param sheetNum 当前sheet编号 
  102.      * @param sheet 当前sheet对象 
  103.      * @param workbook 工作簿对象 
  104.      * @return Map key:图片单元格索引(0_1_1)String,value:图片流PictureData 
  105.      * @throws IOException 
  106.      */  
  107.     public static Map<String, PictureData> getSheetPictrues03(int sheetNum,  
  108.             HSSFSheet sheet, HSSFWorkbook workbook) {  
  109.   
  110.         Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
  111.         List<HSSFPictureData> pictures = workbook.getAllPictures();  
  112.         if (pictures.size() != 0) {  
  113.             for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
  114.                 HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();  
  115.                 if (shape instanceof HSSFPicture) {  
  116.                     HSSFPicture pic = (HSSFPicture) shape;  
  117.                     int pictureIndex = pic.getPictureIndex() - 1;  
  118.                     HSSFPictureData picData = pictures.get(pictureIndex);  
  119.                     String picIndex = String.valueOf(sheetNum) + "_"  
  120.                             + String.valueOf(anchor.getRow1()) + "_"  
  121.                             + String.valueOf(anchor.getCol1());  
  122.                     sheetIndexPicMap.put(picIndex, picData);  
  123.                 }  
  124.             }  
  125.             return sheetIndexPicMap;  
  126.         } else {  
  127.             return null;  
  128.         }  
  129.     }  
  130.   
  131.     /** 
  132.      * 获取Excel2007图片 
  133.      * @param sheetNum 当前sheet编号 
  134.      * @param sheet 当前sheet对象 
  135.      * @param workbook 工作簿对象 
  136.      * @return Map key:图片单元格索引(0_1_1)String,value:图片流PictureData 
  137.      */  
  138.     public static Map<String, PictureData> getSheetPictrues07(int sheetNum,  
  139.             XSSFSheet sheet, XSSFWorkbook workbook) {  
  140.         Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
  141.   
  142.         for (POIXMLDocumentPart dr : sheet.getRelations()) {  
  143.             if (dr instanceof XSSFDrawing) {  
  144.                 XSSFDrawing drawing = (XSSFDrawing) dr;  
  145.                 List<XSSFShape> shapes = drawing.getShapes();  
  146.                 for (XSSFShape shape : shapes) {  
  147.                     XSSFPicture pic = (XSSFPicture) shape;  
  148.                     XSSFClientAnchor anchor = pic.getPreferredSize();  
  149.                     CTMarker ctMarker = anchor.getFrom();  
  150.                     String picIndex = String.valueOf(sheetNum) + "_"  
  151.                             + ctMarker.getRow() + "_" + ctMarker.getCol();  
  152.                     sheetIndexPicMap.put(picIndex, pic.getPictureData());  
  153.                 }  
  154.             }  
  155.         }  
  156.   
  157.         return sheetIndexPicMap;  
  158.     }  
  159.       
  160.     public static void printImg(List<Map<String, PictureData>> sheetList) throws IOException {  
  161.           
  162.         for (Map<String, PictureData> map : sheetList) {  
  163.             Object key[] = map.keySet().toArray();  
  164.             for (int i = 0; i < map.size(); i++) {  
  165.                 // 获取图片流  
  166.                 PictureData pic = map.get(key[i]);  
  167.                 // 获取图片索引  
  168.                 String picName = key[i].toString();  
  169.                 // 获取图片格式  
  170.                 String ext = pic.suggestFileExtension();  
  171.                   
  172.                 byte[] data = pic.getData();  
  173.                   
  174.                 FileOutputStream out = new FileOutputStream("D:\\pic" + picName + "." + ext);  
  175.                 out.write(data);  
  176.                 out.close();  
  177.             }  
  178.         }  
  179.           
  180.     }  
  181.     
  182. }  
上一篇:flowable实战(一)flowable与spring boot集成


下一篇:Java 使用Apache POI 导出Excel三种形式