如何从java中的Excel工作表（XSSF）中删除图像

debugcn 发表于 Dev

克舍尔

我现在一直在尝试从我的XSSFSheet. 我找不到有关此的任何信息，但我认为它必须是可能的..

有什么办法可以从我的图像中删除图像XSSFSheet吗？甚至官方 (?) apache poi 网站除了创建和读取图像之外没有提及任何内容

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

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

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

我会很感激这里的任何帮助！

阿克塞尔·里希特

因为XSSF这并不像听起来那么简单。有HSSFPatriarch.removeShape但在XSSFDrawing 中没有可比的东西。

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

如果形状名称为“图像 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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。