java - 如何在java中从Excel工作表(XSSF)中删除图像

Question

我现在已经尝试从 XSSFSheet 中删除图像太久了。我找不到关于此的任何信息，但我认为这是可能的..

有什么方法可以从我的 XSSFSheet 中删除图像吗？甚至官方(？)apache poi 网站也没有提及除 creating and reading images 之外的任何内容。

我现在离放弃不远了，只是将除所述图像之外的所有内容复制到新工作表中。这显然不应该如何完成。如果我这样做的话，我想我一周都睡不好觉了。

我最后一次不成功的尝试是使用我的代码来移动图像(我在 this post 中共享了该代码)，但我没有设置有效的行号，而是设置了 null ，但这是不可能的，因为 setRow() 的参数是 int (基本类型)。

然后我尝试为 anchor 行设置负值。虽然从技术上讲这会删除图像，但下次打开 Excel 文件时必须对其进行修复。图像未显示。
我相信我也必须从 XSSFDrawing 中删除关系才能完全删除图像(我认为这是在找到 this custom implementation of XSSFDrawing 之后)，但我不知道那里发生了什么......

如果您能在这里提供任何帮助，我将不胜感激!

Answer 1

对于XSSF来说，这并不像听起来那么简单。有 HSSFPatriarch.removeShape 但 XSSFDrawing 中没有可比的内容。

我们必须删除图片本身包含的关系。我们必须从绘图中删除形状的 anchor 。

示例:遍历工作表中的所有图片并删除形状名称为“Image 2”的图片:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.hssf.usermodel.*;

import org.apache.xmlbeans.XmlCursor;

import java.io.*;

class ExcelDeleteImage {

 public static void deleteCTAnchor(XSSFPicture xssfPicture) {
  XSSFDrawing drawing = xssfPicture.getDrawing();
  XmlCursor cursor = xssfPicture.getCTPicture().newCursor();
  cursor.toParent();
  if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor) {
   for (int i = 0; i < drawing.getCTDrawing().getTwoCellAnchorList().size(); i++) {
    if (cursor.getObject().equals(drawing.getCTDrawing().getTwoCellAnchorArray(i))) {
     drawing.getCTDrawing().removeTwoCellAnchor(i);
     System.out.println("TwoCellAnchor for picture " + xssfPicture + " was deleted.");
    }
   }
  } else if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTOneCellAnchor) {
   for (int i = 0; i < drawing.getCTDrawing().getOneCellAnchorList().size(); i++) {
    if (cursor.getObject().equals(drawing.getCTDrawing().getOneCellAnchorArray(i))) {
     drawing.getCTDrawing().removeOneCellAnchor(i);
     System.out.println("OneCellAnchor for picture " + xssfPicture + " was deleted.");
    }
   }
  } else if (cursor.getObject() instanceof org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTAbsoluteAnchor) {
   for (int i = 0; i < drawing.getCTDrawing().getAbsoluteAnchorList().size(); i++) {
    if (cursor.getObject().equals(drawing.getCTDrawing().getAbsoluteAnchorArray(i))) {
     drawing.getCTDrawing().removeAbsoluteAnchor(i);
     System.out.println("AbsoluteAnchor for picture " + xssfPicture + " was deleted.");
    }
   }
  }
 }

 public static void deleteEmbeddedXSSFPicture(XSSFPicture xssfPicture) {
  if (xssfPicture.getCTPicture().getBlipFill() != null) {
   if (xssfPicture.getCTPicture().getBlipFill().getBlip() != null) {
    if (xssfPicture.getCTPicture().getBlipFill().getBlip().getEmbed() != null) {
     String rId = xssfPicture.getCTPicture().getBlipFill().getBlip().getEmbed();
     XSSFDrawing drawing = xssfPicture.getDrawing();
     drawing.getPackagePart().removeRelationship(rId);
     drawing.getPackagePart().getPackage().deletePartRecursive(drawing.getRelationById(rId).getPackagePart().getPartName());
     System.out.println("Picture " + xssfPicture + " was deleted.");
    }
   }
  }
 }

 public static void deleteHSSFShape(HSSFShape shape) {
  HSSFPatriarch drawing = shape.getPatriarch();
  drawing.removeShape(shape);    
  System.out.println("Shape " + shape + " was deleted.");
 }

 public static void main(String[] args) throws Exception {

  String filename = "ExcelWithImages.xlsx";
  //String filename = "ExcelWithImages.xls";

  InputStream inp = new FileInputStream(filename);

  Workbook workbook = WorkbookFactory.create(inp);
  Sheet sheet = workbook.getSheetAt(0);

  Drawing drawing = sheet.getDrawingPatriarch();

  XSSFPicture xssfPictureToDelete = null;
  if (drawing instanceof XSSFDrawing) {
   for (XSSFShape shape : ((XSSFDrawing)drawing).getShapes()) {
    if (shape instanceof XSSFPicture) {
     XSSFPicture xssfPicture = (XSSFPicture)shape;
     String shapename = xssfPicture.getShapeName();
     int row = xssfPicture.getClientAnchor().getRow1();
     int col = xssfPicture.getClientAnchor().getCol1();
     System.out.println("Picture " + "" + " with Shapename: " + shapename + " is located row: " + row + ", col: " + col);

     if ("Image 2".equals(shapename)) xssfPictureToDelete = xssfPicture;

    }
   }
  }
  if  (xssfPictureToDelete != null) deleteEmbeddedXSSFPicture(xssfPictureToDelete);
  if  (xssfPictureToDelete != null) deleteCTAnchor(xssfPictureToDelete);

  HSSFPicture hssfPictureToDelete = null;
  if (drawing instanceof HSSFPatriarch) {
   for (HSSFShape shape : ((HSSFPatriarch)drawing).getChildren()) {
    if (shape instanceof HSSFPicture) {
     HSSFPicture hssfPicture = (HSSFPicture)shape;
     int picIndex = hssfPicture.getPictureIndex();
     String shapename = hssfPicture.getShapeName().trim();
     int row = hssfPicture.getClientAnchor().getRow1();
     int col = hssfPicture.getClientAnchor().getCol1();
     System.out.println("Picture " + picIndex + " with Shapename: " + shapename + " is located row: " + row + ", col: " + col);

     if ("Image 2".equals(shapename)) hssfPictureToDelete = hssfPicture;

    }
   }
  }
  if (hssfPictureToDelete != null) deleteHSSFShape(hssfPictureToDelete);

  FileOutputStream out = new FileOutputStream(filename);
  workbook.write(out);
  out.close();
  workbook.close();
 }
}

此代码使用 apache poi 4.0.1 进行测试，它需要 FAQ N10025 中提到的所有架构 ooxml-schemas-1.4.jar 的完整 jar