How to modify the data in excel built into ppt

Question

enter image description here There is a built-in excel in my ppt template. Now I want to use poi to modify the data in excel. I have tried many methods but it still doesn't work. Please help me.

 public static void replaceExcel(XMLSlideShow ppt, Map<String, Object> excelDataMap) throws IOException {

        List<XSLFSlide> slides = ppt.getSlides();
        for (XSLFSlide slide : slides) {
            List<XSLFShape> shapes = slide.getShapes();
            for (int i = 0; i < shapes.size(); i++) {
                XSLFShape shape = slide.getShapes().get(i);
                if (shape instanceof XSLFObjectShape) {
                    PackagePart packagePart = slide.getPackagePart();
                    System.out.println(packagePart.getContentType());
                    System.out.println(shape.getShapeName());
                    XSLFObjectShape excelShape = (XSLFObjectShape) shape;
                    String shapeName = excelShape.getShapeName();
                    InputStream inputStream = excelShape.getObjectData().getInputStream();;
                    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);




                }
            }

        }
    }

Exception

java.lang.ClassCastException: class org.apache.poi.ooxml.POIXMLDocumentPart cannot be cast to class org.apache.poi.xslf.usermodel.XSLFObjectData (org.apache.poi.ooxml.POIXMLDocumentPart and org.apache.poi.xslf.usermodel.XSLFObjectData are in unnamed module of loader 'app')

    at org.apache.poi.xslf.usermodel.XSLFObjectShape.getObjectData(XSLFObjectShape.java:95)
    at utils.ppt.PPTUtils.replaceExcel(PPTUtils.java:152)
    at TestDemo.testPPT(TestDemo.java:475)

The effect I hope is to write the data into excel in ppt

Answer 1

I would call this a bug in XSLFObjectShape.getObjectData

@Override
public XSLFObjectData getObjectData() {
    String oleRel = getCTOleObject().getId();
    return getSheet().getRelationPartById(oleRel).getDocumentPart();
}

For me this smells bad.

Here getSheet().getRelationPartById(oleRel).getDocumentPart() returns org.apache.poi.ooxml.POIXMLDocumentPart. So why does this even compile? XSLFObjectData extends org.apache.poi.ooxml.POIXMLDocumentPart. So getSheet().getRelationPartById(oleRel).getDocumentPart() may be a XSLFObjectData, thus implicit downcasting is possible. But it don't needs to be a such. Thus casting may fail too, as it does for you.

Better would be:

@Override
public XSLFObjectData getObjectData() {
    String oleRel = getCTOleObject().getId();
    org.apache.poi.ooxml.POIXMLDocumentPart documentPart = getSheet().getRelationPartById(oleRel).getDocumentPart();
    XSLFObjectData objectData = new XSLFObjectData(documentPart.getPackagePart());
    return objectData;
}

Complete example which should work:

import java.io.InputStream;
import java.io.FileInputStream;

import org.apache.poi.xslf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PPTXGetXSLFObjectShapes {
    
 static XSLFObjectData getObjectData(XSLFObjectShape objectShape) {
  String oleRel = objectShape.getCTOleObject().getId();
  org.apache.poi.ooxml.POIXMLDocumentPart documentPart = objectShape.getSheet().getRelationPartById(oleRel).getDocumentPart();
  XSLFObjectData objectData = new XSLFObjectData(documentPart.getPackagePart());
  return objectData;
 }
              
 public static void main(String[] args) throws Exception {
     
  XMLSlideShow slideShow = new XMLSlideShow(new FileInputStream("./PPTXPresentation.pptx"));
  
  for (XSLFSlide slide : slideShow.getSlides()) {
   //System.out.println(slide);        
   for (XSLFShape shape : slide.getShapes()) {
    //System.out.println(shape); 
    
    if (shape instanceof XSLFObjectShape) {
     XSLFObjectShape objectShape = (XSLFObjectShape)shape;
     System.out.println(objectShape); 
     //XSLFObjectData objectData = objectShape.getObjectData(); //fails when XSLFObjectData is embedded file
     XSLFObjectData objectData = getObjectData(objectShape);
     System.out.println(objectData);
     if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(objectData.getPackagePart().getContentType())) {     
      InputStream inputStream = objectData.getInputStream();
      XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
      System.out.println("Workbook created: " + workbook);
     }   
    }
    
   }       
  }
 }
}