使用Apache POI将文件嵌入Excel中的XSSF工作表

Question

我已经找到了如何使用ApachePOI将文件嵌入Excel的问题的奇妙答案，但是不幸的是，他的答案仅涉及HSSF电子表格（XLS格式），我们目前正在使用新的XSSF格式（XLSX），以及解决方案为HSSF电子表格提出的建议将不起作用。我尝试移植它，但是棺材中的最后一个钉子来自XSSF世界中没有等效的HSSFObjectData。

到目前为止，这是我所做的-我找到了一种将文件附加到Excel文件的方法。这段代码可以做到：

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;
}

我还能够创建要用作Excel文件锚点的图像。但是，我无法做的就是将图像“链接”到嵌入的内容，就像奇异鸟在他的回复中所做的那样。

我的最终目标是拥有一个包含嵌入对象的XLSX
Excel文件，这样用户可以双击我在单元格中打开的锚点，然后能够编辑该文件，就像您要执行的操作一样使用Excel客户端嵌入文件。

有谁有一个可行的例子来做到这一点？

Answer 1

与HSSF / Package Manager相比，这更为直接。:)

因此，像往常一样，我首先通过Excel
2016创建必要的文件，然后检查xml中的内容。Office喜欢在其中放置许多AlternateContent标记 -在以下解决方案中，我删除了这些包装，并直接在原始“
Choice”元素中提供了这些元素，至少在Excel2016中它可以工作…-请注意，其中的嵌入Excel2016的原始文件无法在Libre Office
5 / Excel-Viewer中打开，因此您的用户需要常规安装。

由于我已经在POI的完整开发人员代码库中实现了它，因此您可能需要使用完整模式。

当用户尝试打开嵌入的对象时，预览图片将被替换。如果POI的WMF软件包可以用于动态生成预览图像，那将是很好的选择，但是到目前为止，我只将它们实现为只读:(

如果无法打开嵌入的元素，请给我下一行您的用户Office安装，然后尝试相应地降级。

package org.apache.poi.xssf;

import java.awt.geom.Rectangle2D;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Set;

import javax.xml.namespace.QName;

import org.apache.poi.POIXMLDocument;
import org.apache.poi.hpsf.ClassID;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.poifs.filesystem.Ole10Native;
import org.apache.poi.poifs.filesystem.Ole10NativeException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xslf.usermodel.XMLSlideShow;
import org.apache.poi.xslf.usermodel.XSLFTextBox;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.XmlCursor;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlObject;
import org.junit.Test;
import org.openxmlformats.schemas.drawingml.x2006.main.CTOfficeArtExtension;
import org.openxmlformats.schemas.drawingml.x2006.main.CTOfficeArtExtensionList;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOleObject;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOleObjects;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

public class TestEmbed {
    static final String drawNS = "http://schemas.microsoft.com/office/drawing/2010/main";
    static final String relationshipsNS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";

    // write some embedded objects to sheet
    @Test
    public void write() throws IOException, InvalidFormatException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sh = wb.createSheet();

        int imgPptId = addImageToWorkbook(wb, "ppt-icon.jpg", Workbook.PICTURE_TYPE_JPEG);
        int imgPckId = addImageToWorkbook(wb, "PackageIcon.png", Workbook.PICTURE_TYPE_PNG);

        String imgPckRelId = addImageToSheet(sh, imgPckId, Workbook.PICTURE_TYPE_PNG);
        String imgPptRelId = addImageToSheet(sh, imgPptId, Workbook.PICTURE_TYPE_JPEG);

        // embed two different HTML pages via package manager
        XSSFClientAnchor imgAnchor1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 3);
        String oleRelId1 = addHtml(sh, 1);
        int shapeId1 = addImageToShape(sh, imgAnchor1, imgPckId);
        addObjectToShape(sh, imgAnchor1, shapeId1, oleRelId1, imgPckRelId, "Objekt-Manager-Shellobjekt");

        XSSFClientAnchor imgAnchor2 = new XSSFClientAnchor(0, 0, 0, 0, 5, 1, 7, 3);
        String oleRelId2 = addHtml(sh, 2);
        int shapeId2 = addImageToShape(sh, imgAnchor2, imgPckId);
        addObjectToShape(sh, imgAnchor2, shapeId2, oleRelId2, imgPckRelId, "Objekt-Manager-Shellobjekt");

        // embed a slideshow (no package manager needed)
        XSSFClientAnchor imgAnchor3 = new XSSFClientAnchor(0, 0, 0, 0, 1, 5, 7, 10);
        String oleRelId3 = addSlideShow(sh, 1);
        int shapeId3 = addImageToShape(sh, imgAnchor3, imgPptId);
        addObjectToShape(sh, imgAnchor3, shapeId3, oleRelId3, imgPptRelId, "Presentation");


        FileOutputStream fos = new FileOutputStream("bla.xlsx");
        wb.write(fos);
        fos.close();

        wb.close();
    }

    // read Ole10Native objects from workbook
    @Test
    public void read() throws IOException, XmlException, Ole10NativeException {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("bla.xlsx"));
        XSSFSheet sheet = wb.getSheetAt(0);
        CTWorksheet cws = sheet.getCTWorksheet();
        if (!cws.isSetOleObjects()) {
            System.out.println("sheet has no ole objects");
        } else {
            Set<Integer> processedShapes = new HashSet<Integer>();
            for (XmlObject xOleObj : cws.getOleObjects().selectPath("declare namespace p='"+XSSFRelation.NS_SPREADSHEETML+"' .//p:oleObject")) {
                XmlCursor cur = xOleObj.newCursor();
                String shapeId = cur.getAttributeText(new QName("shapeId"));
                String relId = cur.getAttributeText(new QName(relationshipsNS, "id"));
                cur.dispose();

                if (processedShapes.contains(Integer.valueOf(shapeId))) {
                    continue;
                }
                processedShapes.add(Integer.valueOf(shapeId));

                PackagePart pp = sheet.getRelationById(relId).getPackagePart();
                if ("application/vnd.openxmlformats-officedocument.oleObject".equals(pp.getContentType())) {
                    InputStream is = pp.getInputStream();
                    POIFSFileSystem poifs = new POIFSFileSystem(is);
                    is.close();
                    Ole10Native ole10 = Ole10Native.createFromEmbeddedOleObject(poifs);
                    poifs.close();
                    System.out.println("Filename: "+ole10.getFileName()+" - content length: "+ole10.getDataSize());
                }
            }

        }
        wb.close();
    }



    // add a dummy html to the embeddings folder
    private static String addHtml(XSSFSheet sh, int oleId) throws IOException, InvalidFormatException {
        String html10 = "<html><body><marquee>This is the end. Html-id: "+oleId+"</marquee></body></html>";
        Ole10Native ole10 = new Ole10Native("html"+oleId+".html", "html"+oleId+".html", "html"+oleId+".html", html10.getBytes("ISO-8859-1"));

        ByteArrayOutputStream bos = new ByteArrayOutputStream(500);
        ole10.writeOut(bos);

        POIFSFileSystem poifs = new POIFSFileSystem();
        poifs.getRoot().createDocument(Ole10Native.OLE10_NATIVE, new ByteArrayInputStream(bos.toByteArray()));

        poifs.getRoot().setStorageClsid(ClassID.OLE10_PACKAGE);


        final PackagePartName pnOLE = PackagingURIHelper.createPartName( "/xl/embeddings/oleObject"+oleId+".bin" );
        final PackagePart partOLE = sh.getWorkbook().getPackage().createPart( pnOLE, "application/vnd.openxmlformats-officedocument.oleObject" );
        PackageRelationship prOLE = sh.getPackagePart().addRelationship( pnOLE, TargetMode.INTERNAL, POIXMLDocument.OLE_OBJECT_REL_TYPE );
        OutputStream os = partOLE.getOutputStream();
        poifs.writeFilesystem(os);
        os.close();
        poifs.close();

        return prOLE.getId();
    }


    // add a dummy slideshow to the embeddings folder
    private static String addSlideShow(XSSFSheet sh, int pptId) throws IOException, InvalidFormatException {
        XMLSlideShow ppt = new XMLSlideShow();
        XSLFTextBox tb = ppt.createSlide().createTextBox();
        tb.setText("this is the end - PPT-ID: "+pptId);
        tb.setAnchor(new Rectangle2D.Double(100,100,100,100));

        final PackagePartName pnPPT = PackagingURIHelper.createPartName( "/xl/embeddings/sample"+pptId+".pptx" );
        final PackagePart partPPT = sh.getWorkbook().getPackage().createPart( pnPPT, "application/vnd.openxmlformats-officedocument.presentationml.presentation" );
        PackageRelationship prPPT = sh.getPackagePart().addRelationship( pnPPT, TargetMode.INTERNAL, POIXMLDocument.PACK_OBJECT_REL_TYPE );
        OutputStream os = partPPT.getOutputStream();
        ppt.write(os);
        os.close();
        ppt.close();

        return prPPT.getId();
    }



    private static int addImageToWorkbook(XSSFWorkbook wb, String fileName, int pictureType) throws IOException {
        FileInputStream fis = new FileInputStream(fileName);
        int imgId = wb.addPicture(fis, pictureType);
        fis.close();
        return imgId;
    }

    private static String addImageToSheet(XSSFSheet sh, int imgId, int pictureType) throws InvalidFormatException {
        final PackagePartName pnIMG  = PackagingURIHelper.createPartName( "/xl/media/image"+(imgId+1)+(pictureType == Workbook.PICTURE_TYPE_PNG ? ".png" : ".jpeg") );
        PackageRelationship prIMG = sh.getPackagePart().addRelationship( pnIMG, TargetMode.INTERNAL, PackageRelationshipTypes.IMAGE_PART );
        return prIMG.getId();
    }


    private static int addImageToShape(XSSFSheet sh, XSSFClientAnchor imgAnchor, int imgId) {
        XSSFDrawing pat = sh.createDrawingPatriarch();
        XSSFPicture pic = pat.createPicture(imgAnchor, imgId);

        CTPicture cPic = pic.getCTPicture();
        int shapeId = (int)cPic.getNvPicPr().getCNvPr().getId();
        cPic.getNvPicPr().getCNvPr().setHidden(true);
        CTOfficeArtExtensionList extLst = cPic.getNvPicPr().getCNvPicPr().addNewExtLst();
        // https://msdn.microsoft.com/en-us/library/dd911027(v=office.12).aspx
        CTOfficeArtExtension ext = extLst.addNewExt();
        ext.setUri("{63B3BB69-23CF-44E3-9099-C40C66FF867C}");
        XmlCursor cur = ext.newCursor();
        cur.toEndToken();
        cur.beginElement(new QName(drawNS, "compatExt", "a14"));
        cur.insertAttributeWithValue("spid", "_x0000_s"+shapeId);


        return shapeId;
    }



    private static void addObjectToShape(XSSFSheet sh, XSSFClientAnchor imgAnchor, int shapeId, String objRelId, String imgRelId, String progId) {
        CTWorksheet cwb = sh.getCTWorksheet();
        CTOleObjects oo = cwb.isSetOleObjects() ? cwb.getOleObjects() : cwb.addNewOleObjects();

        CTOleObject ole1 = oo.addNewOleObject();
        ole1.setProgId(progId);
        ole1.setShapeId(shapeId);
        ole1.setId(objRelId);


        XmlCursor cur1 = ole1.newCursor();
        cur1.toEndToken();
        cur1.beginElement("objectPr", XSSFRelation.NS_SPREADSHEETML);
        cur1.insertAttributeWithValue("id", relationshipsNS, imgRelId);
        cur1.insertAttributeWithValue("defaultSize", "0");
        cur1.beginElement("anchor", XSSFRelation.NS_SPREADSHEETML);
        cur1.insertAttributeWithValue("moveWithCells", "1");

        CTTwoCellAnchor anchor = CTTwoCellAnchor.Factory.newInstance();
        anchor.setFrom(imgAnchor.getFrom());
        anchor.setTo(imgAnchor.getTo());

        XmlCursor cur2 = anchor.newCursor();
        cur2.copyXmlContents(cur1);
        cur2.dispose();

        cur1.toParent();
        cur1.toFirstChild();
        cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "from"));
        cur1.toNextSibling();
        cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "to"));

        cur1.dispose();
    }
}