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

2023-08-11 19:24:10

利用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

测试代码：

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


下一篇：使用selenium操作ant design前端的页面，感觉页面没加载完