java - Embed files into XSSF sheets in Excel, using Apache POI

Question

I have found kiwiwings answer to the question of how you can embed files into Excel using Apache POI, but unfortunately his answer only covers HSSF spreadsheets (the XLS format), and we are currently using the new XSSF format (XLSX), and the solution proposed for HSSF spreadsheets will not work. I tried porting it, but the final nail in the coffin comes from the fact that there is no HSSFObjectData equivalent in the XSSF world.

This is what I have done so far - I have found a way to attach the files to the Excel file. This code does it:

private PackagePart packageNotebook(
    final OPCPackage pkg,
    final String notebookTable,
    final String taskId,
    final String notebookName,
    final byte[] contents
) throws InvalidFormatException, IOException
{
    final PackagePartName partName =
        PackagingURIHelper.createPartName( "/notebook/" + notebookTable + "/" + taskId + "/" + notebookName );
    pkg.addRelationship( partName, TargetMode.INTERNAL, PackageRelationshipTypes.CUSTOM_XML );
    final PackagePart part = pkg.createPart( partName, "text/xml" );
    IOUtils.write( contents, part.getOutputStream() );

    return part;
}

I was also able to create the image I wanted to use as the anchor in the Excel file. What I am unable to do, however, is to "link" that image to the embedded content, as kiwiwings was able to do in his reply.

My end goal is to have an XLSX Excel file with embedded objects in it, in such a way that the user can double click in the anchor I open in the cells and then be able to edit the file, just like you would do if you were embedding a file using the Excel client.

Does anyone have a working example on how to do that?

Answer 1

I've applied a patch via #60586, so embedding is now much easier. The following snipplet is taken from the corresponding JUnit test.

Workbook wb1 = new XSSFWorkbook();
Sheet sh = wb1.createSheet();
int picIdx = wb1.addPicture(getSamplePng(), Workbook.PICTURE_TYPE_PNG);
byte samplePPTX[] = getSamplePPT(true);
int oleIdx = wb1.addOlePackage(samplePPTX, "dummy.pptx", "dummy.pptx", "dummy.pptx");

Drawing<?> pat = sh.createDrawingPatriarch();
ClientAnchor anchor = pat.createAnchor(0, 0, 0, 0, 1, 1, 3, 6);
pat.createObjectData(anchor, oleIdx, picIdx);