如何从java中的Excel工作表(XSSF)中删除图像 [英] How to remove an image from an excel sheet (XSSF) in java

问题描述

我一直在尝试从我的 XSSFSheet 中删除图像太久了.我找不到关于此的任何信息，但我认为它必须是可能的..

I have now been trying for too long to remove an image from my XSSFSheet. I cannot find any information about this, but I would think that it has to be possible..

有什么办法可以从我的 XSSFSheet 中删除图像?除了 创建和阅读图片

Is there any way to remove an image from my XSSFSheet? Even the official (?) apache poi website does not mention anything besides creating and reading images

我现在离放弃不远了，只是将除上述图像之外的所有内容复制到新工作表中.这显然不是应该如何做到这一点.如果那样做，我想我一个星期都睡不好.

I am now not far away from giving up and just copying everything except said image into a new sheet. Which is obviously not how this should be done. I don't think I would be able to sleep well for a week if I did that.

我最后一次失败的尝试是使用我的代码移动图像(我在这篇文章中分享了该代码) 但我会设置 null 而不是设置有效的行号，但这是不可能的，因为 setRow() 的参数是 int(原始类型).

My last unsuccessful attempt was to use my code which moves images (I shared that code in this post) but instead of setting valid row numbers I would set null, but that's not possible since the parameter for setRow() is int (primitive type).

然后我尝试为锚行设置一个负值.虽然这在技术上删除了图像，但下次打开 Excel 文件时必须对其进行修复.图像未显示.
我相信我也必须从 XSSFDrawing 中删除关系才能完全删除图像(我认为这是在找到 这个XSS的自定义实现是什么...

Then I tried setting a negative value for the anchor rows. While this technically removes the images, the excel file has to be repaired when it is opened the next time. The images are not being displayed.
I believe I would have to remove the relation from the XSSFDrawing too to completely remove the image (I think this after finding this custom implementation of XSSFDrawing) but I have no idea what is going on there...

如果能得到任何帮助，我将不胜感激！

I would be grateful for any kind of help here!

推荐答案

对于 XSSF 这并不像听起来那么简单.有 HSSFPatriarch.removeShape 但XSSFDrawing.

For XSSF this is not as simple as it sounds. There is HSSFPatriarch.removeShape but there is not something comparable in XSSFDrawing.

我们必须删除图片本身，包括关系.我们必须从绘图中删除形状的锚点.

We must delete the picture itself inclusive the relations. And we must delete the shape's anchor from the drawing.

如果形状名称为Image 2"，则遍历工作表中的所有图片并删除图片的示例:

Example which goes trough all pictures in a sheet and deletes a picture if it's shape name is "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 进行测试，它需要 ooxml-schemas-1.4.jar 中提到的所有模式的完整 jarhref="https://poi.apache.org/help/faq.html#faq-N10025" rel="nofollow noreferrer">FAQ N10025

This code is tested using apache poi 4.0.1 and it needs the full jar of all of the schemas ooxml-schemas-1.4.jar as mentioned in FAQ N10025

这篇关于如何从java中的Excel工作表(XSSF)中删除图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！