API tools faq
paste
Advertisement
kiwiwings

XSSF - Embed XSLF and Package Manager

kiwiwings
Nov 6th, 2016
608
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 5 11.07 KB | None | 0 0
raw download clone embed print report
  1. package org.apache.poi.xssf;
  2.  
  3. import java.awt.geom.Rectangle2D;
  4. import java.io.ByteArrayInputStream;
  5. import java.io.ByteArrayOutputStream;
  6. import java.io.FileInputStream;
  7. import java.io.FileOutputStream;
  8. import java.io.IOException;
  9. import java.io.InputStream;
  10. import java.io.OutputStream;
  11. import java.util.HashSet;
  12. import java.util.Set;
  13.  
  14. import javax.xml.namespace.QName;
  15.  
  16. import org.apache.poi.POIXMLDocument;
  17. import org.apache.poi.hpsf.ClassID;
  18. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  19. import org.apache.poi.openxml4j.opc.PackagePart;
  20. import org.apache.poi.openxml4j.opc.PackagePartName;
  21. import org.apache.poi.openxml4j.opc.PackageRelationship;
  22. import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;
  23. import org.apache.poi.openxml4j.opc.PackagingURIHelper;
  24. import org.apache.poi.openxml4j.opc.TargetMode;
  25. import org.apache.poi.poifs.filesystem.Ole10Native;
  26. import org.apache.poi.poifs.filesystem.Ole10NativeException;
  27. import org.apache.poi.poifs.filesystem.POIFSFileSystem;
  28. import org.apache.poi.ss.usermodel.Workbook;
  29. import org.apache.poi.xslf.usermodel.XMLSlideShow;
  30. import org.apache.poi.xslf.usermodel.XSLFTextBox;
  31. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  32. import org.apache.poi.xssf.usermodel.XSSFDrawing;
  33. import org.apache.poi.xssf.usermodel.XSSFPicture;
  34. import org.apache.poi.xssf.usermodel.XSSFRelation;
  35. import org.apache.poi.xssf.usermodel.XSSFSheet;
  36. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  37. import org.apache.xmlbeans.XmlCursor;
  38. import org.apache.xmlbeans.XmlException;
  39. import org.apache.xmlbeans.XmlObject;
  40. import org.junit.Test;
  41. import org.openxmlformats.schemas.drawingml.x2006.main.CTOfficeArtExtension;
  42. import org.openxmlformats.schemas.drawingml.x2006.main.CTOfficeArtExtensionList;
  43. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTPicture;
  44. import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;
  45. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOleObject;
  46. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOleObjects;
  47. import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
  48.  
  49. public class TestEmbed {
  50.     static final String drawNS = "http://schemas.microsoft.com/office/drawing/2010/main";
  51.     static final String relationshipsNS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
  52.  
  53.     // write some embedded objects to sheet
  54.     @Test
  55.     public void write() throws IOException, InvalidFormatException {
  56.         XSSFWorkbook wb = new XSSFWorkbook();
  57.         XSSFSheet sh = wb.createSheet();
  58.  
  59.         int imgPptId = addImageToWorkbook(wb, "ppt-icon.jpg", Workbook.PICTURE_TYPE_JPEG);
  60.         int imgPckId = addImageToWorkbook(wb, "PackageIcon.png", Workbook.PICTURE_TYPE_PNG);
  61.  
  62.         String imgPckRelId = addImageToSheet(sh, imgPckId, Workbook.PICTURE_TYPE_PNG);
  63.         String imgPptRelId = addImageToSheet(sh, imgPptId, Workbook.PICTURE_TYPE_JPEG);
  64.  
  65.         // embed two different HTML pages via package manager
  66.         XSSFClientAnchor imgAnchor1 = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 3, 3);
  67.         String oleRelId1 = addHtml(sh, 1);
  68.         int shapeId1 = addImageToShape(sh, imgAnchor1, imgPckId);
  69.         addObjectToShape(sh, imgAnchor1, shapeId1, oleRelId1, imgPckRelId, "Objekt-Manager-Shellobjekt");
  70.  
  71.         XSSFClientAnchor imgAnchor2 = new XSSFClientAnchor(0, 0, 0, 0, 5, 1, 7, 3);
  72.         String oleRelId2 = addHtml(sh, 2);
  73.         int shapeId2 = addImageToShape(sh, imgAnchor2, imgPckId);
  74.         addObjectToShape(sh, imgAnchor2, shapeId2, oleRelId2, imgPckRelId, "Objekt-Manager-Shellobjekt");
  75.  
  76.         // embed a slideshow (no package manager needed)
  77.         XSSFClientAnchor imgAnchor3 = new XSSFClientAnchor(0, 0, 0, 0, 1, 5, 7, 10);
  78.         String oleRelId3 = addSlideShow(sh, 1);
  79.         int shapeId3 = addImageToShape(sh, imgAnchor3, imgPptId);
  80.         addObjectToShape(sh, imgAnchor3, shapeId3, oleRelId3, imgPptRelId, "Presentation");
  81.  
  82.  
  83.         FileOutputStream fos = new FileOutputStream("bla.xlsx");
  84.         wb.write(fos);
  85.         fos.close();
  86.  
  87.         wb.close();
  88.     }
  89.  
  90.     // read Ole10Native objects from workbook
  91.     @Test
  92.     public void read() throws IOException, XmlException, Ole10NativeException {
  93.         XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream("bla.xlsx"));
  94.         XSSFSheet sheet = wb.getSheetAt(0);
  95.         CTWorksheet cws = sheet.getCTWorksheet();
  96.         if (!cws.isSetOleObjects()) {
  97.             System.out.println("sheet has no ole objects");
  98.         } else {
  99.             Set<Integer> processedShapes = new HashSet<Integer>();
  100.             for (XmlObject xOleObj : cws.getOleObjects().selectPath("declare namespace p='"+XSSFRelation.NS_SPREADSHEETML+"' .//p:oleObject")) {
  101.                 XmlCursor cur = xOleObj.newCursor();
  102.                 String shapeId = cur.getAttributeText(new QName("shapeId"));
  103.                 String relId = cur.getAttributeText(new QName(relationshipsNS, "id"));
  104.                 cur.dispose();
  105.  
  106.                 if (processedShapes.contains(Integer.valueOf(shapeId))) {
  107.                     continue;
  108.                 }
  109.                 processedShapes.add(Integer.valueOf(shapeId));
  110.  
  111.                 PackagePart pp = sheet.getRelationById(relId).getPackagePart();
  112.                 if ("application/vnd.openxmlformats-officedocument.oleObject".equals(pp.getContentType())) {
  113.                     InputStream is = pp.getInputStream();
  114.                     POIFSFileSystem poifs = new POIFSFileSystem(is);
  115.                     is.close();
  116.                     Ole10Native ole10 = Ole10Native.createFromEmbeddedOleObject(poifs);
  117.                     poifs.close();
  118.                     System.out.println("Filename: "+ole10.getFileName()+" - content length: "+ole10.getDataSize());
  119.                 }
  120.             }
  121.  
  122.         }
  123.         wb.close();
  124.     }
  125.  
  126.  
  127.  
  128.     // add a dummy html to the embeddings folder
  129.     private static String addHtml(XSSFSheet sh, int oleId) throws IOException, InvalidFormatException {
  130.         String html10 = "<html><body><marquee>This is the end. Html-id: "+oleId+"</marquee></body></html>";
  131.         Ole10Native ole10 = new Ole10Native("html"+oleId+".html", "html"+oleId+".html", "html"+oleId+".html", html10.getBytes("ISO-8859-1"));
  132.  
  133.         ByteArrayOutputStream bos = new ByteArrayOutputStream(500);
  134.         ole10.writeOut(bos);
  135.  
  136.         POIFSFileSystem poifs = new POIFSFileSystem();
  137.         poifs.getRoot().createDocument(Ole10Native.OLE10_NATIVE, new ByteArrayInputStream(bos.toByteArray()));
  138.  
  139.         poifs.getRoot().setStorageClsid(ClassID.OLE10_PACKAGE);
  140.  
  141.  
  142.         final PackagePartName pnOLE = PackagingURIHelper.createPartName( "/xl/embeddings/oleObject"+oleId+".bin" );
  143.         final PackagePart partOLE = sh.getWorkbook().getPackage().createPart( pnOLE, "application/vnd.openxmlformats-officedocument.oleObject" );
  144.         PackageRelationship prOLE = sh.getPackagePart().addRelationship( pnOLE, TargetMode.INTERNAL, POIXMLDocument.OLE_OBJECT_REL_TYPE );
  145.         OutputStream os = partOLE.getOutputStream();
  146.         poifs.writeFilesystem(os);
  147.         os.close();
  148.         poifs.close();
  149.  
  150.         return prOLE.getId();
  151.     }
  152.  
  153.  
  154.     // add a dummy slideshow to the embeddings folder
  155.     private static String addSlideShow(XSSFSheet sh, int pptId) throws IOException, InvalidFormatException {
  156.         XMLSlideShow ppt = new XMLSlideShow();
  157.         XSLFTextBox tb = ppt.createSlide().createTextBox();
  158.         tb.setText("this is the end - PPT-ID: "+pptId);
  159.         tb.setAnchor(new Rectangle2D.Double(100,100,100,100));
  160.  
  161.         final PackagePartName pnPPT = PackagingURIHelper.createPartName( "/xl/embeddings/sample"+pptId+".pptx" );
  162.         final PackagePart partPPT = sh.getWorkbook().getPackage().createPart( pnPPT, "application/vnd.openxmlformats-officedocument.presentationml.presentation" );
  163.         PackageRelationship prPPT = sh.getPackagePart().addRelationship( pnPPT, TargetMode.INTERNAL, POIXMLDocument.PACK_OBJECT_REL_TYPE );
  164.         OutputStream os = partPPT.getOutputStream();
  165.         ppt.write(os);
  166.         os.close();
  167.         ppt.close();
  168.  
  169.         return prPPT.getId();
  170.     }
  171.  
  172.  
  173.  
  174.     private static int addImageToWorkbook(XSSFWorkbook wb, String fileName, int pictureType) throws IOException {
  175.         FileInputStream fis = new FileInputStream(fileName);
  176.         int imgId = wb.addPicture(fis, pictureType);
  177.         fis.close();
  178.         return imgId;
  179.     }
  180.  
  181.     private static String addImageToSheet(XSSFSheet sh, int imgId, int pictureType) throws InvalidFormatException {
  182.         final PackagePartName pnIMG  = PackagingURIHelper.createPartName( "/xl/media/image"+(imgId+1)+(pictureType == Workbook.PICTURE_TYPE_PNG ? ".png" : ".jpeg") );
  183.         PackageRelationship prIMG = sh.getPackagePart().addRelationship( pnIMG, TargetMode.INTERNAL, PackageRelationshipTypes.IMAGE_PART );
  184.         return prIMG.getId();
  185.     }
  186.  
  187.  
  188.     private static int addImageToShape(XSSFSheet sh, XSSFClientAnchor imgAnchor, int imgId) {
  189.         XSSFDrawing pat = sh.createDrawingPatriarch();
  190.         XSSFPicture pic = pat.createPicture(imgAnchor, imgId);
  191.  
  192.         CTPicture cPic = pic.getCTPicture();
  193.         int shapeId = (int)cPic.getNvPicPr().getCNvPr().getId();
  194.         cPic.getNvPicPr().getCNvPr().setHidden(true);
  195.         CTOfficeArtExtensionList extLst = cPic.getNvPicPr().getCNvPicPr().addNewExtLst();
  196.         // https://msdn.microsoft.com/en-us/library/dd911027(v=office.12).aspx
  197.         CTOfficeArtExtension ext = extLst.addNewExt();
  198.         ext.setUri("{63B3BB69-23CF-44E3-9099-C40C66FF867C}");
  199.         XmlCursor cur = ext.newCursor();
  200.         cur.toEndToken();
  201.         cur.beginElement(new QName(drawNS, "compatExt", "a14"));
  202.         cur.insertAttributeWithValue("spid", "_x0000_s"+shapeId);
  203.  
  204.  
  205.         return shapeId;
  206.     }
  207.  
  208.  
  209.  
  210.     private static void addObjectToShape(XSSFSheet sh, XSSFClientAnchor imgAnchor, int shapeId, String objRelId, String imgRelId, String progId) {
  211.         CTWorksheet cwb = sh.getCTWorksheet();
  212.         CTOleObjects oo = cwb.isSetOleObjects() ? cwb.getOleObjects() : cwb.addNewOleObjects();
  213.  
  214.         CTOleObject ole1 = oo.addNewOleObject();
  215.         ole1.setProgId(progId);
  216.         ole1.setShapeId(shapeId);
  217.         ole1.setId(objRelId);
  218.  
  219.  
  220.         XmlCursor cur1 = ole1.newCursor();
  221.         cur1.toEndToken();
  222.         cur1.beginElement("objectPr", XSSFRelation.NS_SPREADSHEETML);
  223.         cur1.insertAttributeWithValue("id", relationshipsNS, imgRelId);
  224.         cur1.insertAttributeWithValue("defaultSize", "0");
  225.         cur1.beginElement("anchor", XSSFRelation.NS_SPREADSHEETML);
  226.         cur1.insertAttributeWithValue("moveWithCells", "1");
  227.  
  228.         CTTwoCellAnchor anchor = CTTwoCellAnchor.Factory.newInstance();
  229.         anchor.setFrom(imgAnchor.getFrom());
  230.         anchor.setTo(imgAnchor.getTo());
  231.  
  232.         XmlCursor cur2 = anchor.newCursor();
  233.         cur2.copyXmlContents(cur1);
  234.         cur2.dispose();
  235.  
  236.         cur1.toParent();
  237.         cur1.toFirstChild();
  238.         cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "from"));
  239.         cur1.toNextSibling();
  240.         cur1.setName(new QName(XSSFRelation.NS_SPREADSHEETML, "to"));
  241.  
  242.         cur1.dispose();
  243.     }
  244. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!