如何从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中删除图像?即使是正式的(?)apache poi网站也没有提及创建并读取图像

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中删除该关系才能完全删除图像(我发现

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 ，但是

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.

示例可以遍历工作表中的所有图片，并删除形状名称为图片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的完整jar，如

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屋！