使用Apache POI HSSF嵌入（EXCEL）到HSLF（PPT） [英] Embedding HSSF(excel) into HSLF(ppt) using apache poi

问题描述

我要嵌入Excel工作表到使用Apache POI presentation（PPT）。我们应该怎么做？如果任何人知道，请帮助我。

I want to embed the excel sheet into presentation(PPT) using apache poi. how can we do this? If anyones knows, please help me.

推荐答案

这花了我一段时间才能找出这些部分是怎样属于一起...

This took me a while to figure out how the parts belong together ...

的嵌入可以通过两种方式来完成：

The embedding can be done in two ways:

• by更新已嵌入的工作表
  
  
  
  • 优点：只需拨打 ObjectData.get /使用setData（）和你完成
  
  
  • 缺点：？如果你想有一个以上的OLE对象嵌入什么
  
  

  像往常一样，当我揣摩，如何实现某种功能的POI，我比较有自由报Office文件的结果，在这种情况下，一些地方不得不创建/修改：

  As usual when I try to figure out, how to implement certain POI features, I'm comparing the results with Libre Office files, in this case a few parts had to be created/modified:

  
  
  • 在PowerPoint对象...
    
    
    
    • 的emebedded对象的二进制数据存储为一个根级别的记录。大部分的根记录<一href=\"http://poi.apache.org/apidocs/org/apache/poi/hslf/record/PositionDependentRecord.html\">position依赖，所以你需要重计算其所有的偏移，当一个新的记录，例如幻灯片，将创建
    
    
    • 二进制数据记录是由嵌入在<一个记录引用href=\"http://poi.apache.org/apidocs/org/apache/poi/hslf/record/Document.html\"><$c$c>Document记录
    
    
    • ...并混淆它多一点，本文参考引用一旦被实际形状对象的更多
    
    
    • in the Powerpoint object ...
      • the binary data of the emebedded object is stored as a root level record. Most of the root records are position dependent, so you need to recalc all their offsets when a new record, e.g. a slide, is created
      • the binary data record is referenced by embedding records inside the Document record
      • ... and to obfuscate it a bit more, this document reference is referenced once more by the actual shape object
      
      
      • 要创建href=\"http://msdn.microsoft.com/en-us/library/dd942265.aspx\">奥莱流作品需要一个
        
      • 和根节点有嵌入文档类型的类ID
      
      
      • 除此之外，还有在嵌入工作簿对象neccessary和数据本身并没有改变，是一个自包含的Excel文件
      
      
      • an Ole Stream entry needs to be created
      • and the root node has to have the class-id of the embedded document type
      • apart from that, there are no changes on the embedded workbook object neccessary and the data itself, is a self-contained excel file

      此外，我用了两个实际的信息类：<一href=\"http://poi.apache.org/apidocs/org/apache/poi/hssf/dev/BiffViewer.html\"><$c$c>BiffViewer和<一个href=\"http://poi.apache.org/apidocs/org/apache/poi/poifs/dev/POIFSLister.html\"><$c$c>POIFSLister.

      Furthermore I've used the two practical info classes: BiffViewer and POIFSLister.

      由于这只是一个概念证明，它是由远未完成。
      有关嵌入式元素的重新presentation进一步的修改，你需要咨询规范

      As this is just a proof of concept, it is by far from complete. For further modifications on the representation of the embedded elements, you'll need to consult the spec.

      还有嵌入式对象创建一个preVIEW图像的未解问题。您可能要使用中性的形象，这是无论如何更换，只要用户激活（双击）的OLE对象......另一种方法是使用的jodconverter ，但比POI的做法是有点懵了......

      There is still an unsolved issue of creating a preview image for the embedded object. You might want to use a neutral image, which is replaced anyway, as soon as user activates (double-clicks) the ole object ... An alternative would be to use jodconverter, but than the POI approach would be a bit senseless ...

      （带POI3.9测试/自由办公室4.0 / MS Excel查看/ MS Office 2003中）

      (tested with POI3.9 / Libre Office 4.0 / MS Excel Viewer / MS Office 2003)

      import java.awt.geom.Rectangle2D;
      import java.io.*;
      import java.lang.reflect.Field;
      
      import org.apache.poi.POIDocument;
      import org.apache.poi.ddf.*;
      import org.apache.poi.hpsf.ClassID;
      import org.apache.poi.hslf.HSLFSlideShow;
      import org.apache.poi.hslf.exceptions.HSLFException;
      import org.apache.poi.hslf.model.*;
      import org.apache.poi.hslf.model.Picture;
      import org.apache.poi.hslf.model.Slide;
      import org.apache.poi.hslf.record.*;
      import org.apache.poi.hslf.usermodel.*;
      import org.apache.poi.hssf.usermodel.*;
      import org.apache.poi.hwpf.HWPFDocument;
      import org.apache.poi.hwpf.usermodel.*;
      import org.apache.poi.poifs.filesystem.*;
      import org.apache.poi.util.*;
      
      public class PoiOleXlsInPpt {
          static final OleType EXCEL97      = new OleType("{00020820-0000-0000-C000-000000000046}");
          static final OleType EXCEL95      = new OleType("{00020810-0000-0000-C000-000000000046}");
          static final OleType WORD97       = new OleType("{00020906-0000-0000-C000-000000000046}");
          static final OleType WORD95       = new OleType("{00020900-0000-0000-C000-000000000046}");
          static final OleType POWERPOINT97 = new OleType("{64818D10-4F9B-11CF-86EA-00AA00B929E8}");
          static final OleType POWERPOINT95 = new OleType("{EA7BAE70-FB3B-11CD-A903-00AA00510EA3}");
      
          static class OleType {
              final String classId;
              OleType(String classId) {
                  this.classId = classId;
              }
              ClassID getClassID() {
                  ClassID cls = new ClassID();
                  byte clsBytes[] = cls.getBytes();
                  String clsStr = classId.replaceAll("[{}-]", "");
                  for (int i=0; i<clsStr.length(); i+=2) {
                      clsBytes[i/2] = (byte)Integer.parseInt(clsStr.substring(i, i+2), 16);
                  }
                  return cls;
              }
          }
      
          public static void main(String[] args) throws Exception {
              HSLFSlideShow _hslfSlideShow = HSLFSlideShow.create(); 
              SlideShow ppt = new SlideShow(_hslfSlideShow);
      
              OLEShape oleShape1 = createOLEShape(getSampleWorkbook1(), ppt, _hslfSlideShow, EXCEL97);
              oleShape1.setAnchor(new Rectangle2D.Double(100,100,100,100));
              OLEShape oleShape2 = createOLEShape(getSampleWorkbook2(), ppt, _hslfSlideShow, EXCEL97);
              oleShape2.setAnchor(new Rectangle2D.Double(300,300,100,100));
              OLEShape oleShape3 = createOLEShape(getSampleDocument(), ppt, _hslfSlideShow, WORD97);
              oleShape3.setAnchor(new Rectangle2D.Double(300,100,100,100));
      
              // create and link visuals to the ole data
              Slide slide = ppt.createSlide();
              slide.addShape(oleShape1);
              slide.addShape(oleShape2);
              slide.addShape(oleShape3);
      
              FileOutputStream fos = new FileOutputStream("ole_xls_in_ppt_out2.ppt");
              ppt.write(fos);
              fos.close();
          }
      
          static OLEShape createOLEShape(
                POIDocument sample
              , SlideShow ppt
              , HSLFSlideShow _hslfSlideShow
              , OleType oleType
          ) throws IOException {
              // generate a preview image
              int prevIdx = generatePreview(ppt, sample);
      
              // add the data to the SlideShow
              ExEmbed eeEmbed = addOleDataToDocumentRecord(ppt);
              ExOleObjStg exOleObjStg = addOleDataToRootRecords(_hslfSlideShow, sample, oleType);
              eeEmbed.getExOleObjAtom().setObjStgDataRef(exOleObjStg.getPersistId());
      
              OLEShape oleShape = new OLEShape(prevIdx);
              linkOleDataToShape(oleShape, eeEmbed);
      
              return oleShape;
          }
      
          static POIDocument getSampleWorkbook1() {
              HSSFWorkbook wb = new HSSFWorkbook();
              HSSFSheet sheet = wb.createSheet();
              sheet.createRow(1).createCell(1).setCellValue("First Workbook");
              return wb;
          }
      
          static POIDocument getSampleWorkbook2() {
              HSSFWorkbook wb = new HSSFWorkbook();
              HSSFSheet sheet = wb.createSheet();
              sheet.createRow(1).createCell(1).setCellValue("Second Workbook");
              return wb;
          }
      
          // the sample document has apparently a problem,
          // i.e. word inside ms powerpoint crashed, and libre office doesn't display the text
          // it was just a test, if embedding elements != Excel works
          // in case HWPF is interesting to you, you probably know anyway, where the error below is ...
          static POIDocument getSampleDocument() throws IOException {
              FileInputStream fis = new FileInputStream("src/test/resources/empty.doc");
              HWPFDocument doc = new HWPFDocument(fis);
              fis.close();
              Range range = doc.getRange();
              CharacterRun run1 = range.insertAfter("Sample text");    
              run1.setFontSize(11);       
              return doc;
          }
      
          /**
           * Generates a modified version of the sample element, which
           * contains embedding informations
           */
          static byte[] wrapOleData(POIDocument oleData, OleType oleType) {
              try {
                  ByteArrayOutputStream bos = new ByteArrayOutputStream();
                  oleData.write(bos);
      
                  ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
                  bos.reset();
                  POIFSFileSystem poifs = new POIFSFileSystem(bis);
      
                  final String OLESTREAM_NAME = "\u0001Ole";
                  DirectoryNode root = poifs.getRoot();
                  if (!root.hasEntry(OLESTREAM_NAME)) {
                      // the following data was taken from an example libre office document
                      // beside this "\u0001Ole" record there were several other records, e.g. CompObj,
                      // OlePresXXX, but it seems, that they aren't neccessary
                      byte oleBytes[] = { 1, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
                      poifs.createDocument(new ByteArrayInputStream(oleBytes), OLESTREAM_NAME);
                  }
      
                  // need to set storage clsid, otherwise embedded object is not recognized
                  root.setStorageClsid(oleType.getClassID());
      
                  poifs.writeFilesystem(bos);
                  return bos.toByteArray();
              } catch (IOException e) {
                  throw new RuntimeException("wth?!", e);
              }
          }
      
      
          /**
           * to be defined, how to create a preview image
           * for a start, I've taken just a dummy image, which will be
           * replaced, when the user activates the ole object
           *
           * not really an alternativ:
           * http://stackoverflow.com/questions/16704624/how-to-print-a-workbook-file-made-using-apache-poi-and-java
           * 
           * @return image index of the preview image 
           */
          static int generatePreview(SlideShow ppt, POIDocument oleData) {
              try {
                  FileInputStream fis = new FileInputStream("src/test/resources/dilbert-2011-09-28-powerpoint.jpg");
                  byte previewImg[] = IOUtils.toByteArray(fis);
                  fis.close();
                  return ppt.addPicture(previewImg, Picture.JPEG);
              } catch (IOException e) {
                  throw new RuntimeException("not really?", e);
              }
          }
      
          static ExEmbed addOleDataToDocumentRecord(SlideShow ppt) {
              // taken from SlideShow.addControl()
              Document _documentRecord = ppt.getDocumentRecord();
              ExObjList lst = _documentRecord.getExObjList();
              if (lst == null) {
                  lst = new ExObjList();
                  _documentRecord.addChildAfter(lst, _documentRecord.getDocumentAtom());
                  try {
                      Field f = Document.class.getDeclaredField("exObjList");
                      f.setAccessible(true);
                      f.set(_documentRecord, lst);
                  } catch (Exception e) {
                      throw new RuntimeException("not here", e);
                  }
              }
              ExObjListAtom objAtom = lst.getExObjListAtom();
              // increment the object ID seed
              int objectId = (int) objAtom.getObjectIDSeed() + 1;
              objAtom.setObjectIDSeed(objectId);
      
              ExEmbed exEmbed = new ExEmbed();
              // remove unneccessary infos, so we don't need to specify the type
              // of the ole object multiple times
              Record children[] = exEmbed.getChildRecords();
              exEmbed.removeChild(children[2]);
              exEmbed.removeChild(children[3]);
              exEmbed.removeChild(children[4]);
      
              ExEmbedAtom eeEmbed = exEmbed.getExEmbedAtom();
              try {
                  Field f = ExEmbedAtom.class.getDeclaredField("_data");
                  f.setAccessible(true);
                  f.set(eeEmbed, new byte[]{0,0,0,0,1/*CantLockServerB*/,0,0,0});
                  // oops, there seems to be an error in the default constructor ...
                  // should be 8 and not 7 bytes
                  setRecordLength(eeEmbed, 8);
              } catch (Exception e) {
                  throw new RuntimeException("trust me ;)", e);
              }
      
              ExOleObjAtom eeAtom = exEmbed.getExOleObjAtom();
              eeAtom.setObjID(objectId);
              eeAtom.setDrawAspect(ExOleObjAtom.DRAW_ASPECT_VISIBLE);
              eeAtom.setType(ExOleObjAtom.TYPE_EMBEDDED);
              // eeAtom.setSubType(ExOleObjAtom.SUBTYPE_EXCEL);
              // should be ignored?!?, see MS-PPT ExOleObjAtom, but Libre Office sets it ...
              eeAtom.setOptions(1226240);
      
              lst.addChildAfter(exEmbed, objAtom);
      
              return exEmbed;
          }
      
          static ExOleObjStg addOleDataToRootRecords(
                HSLFSlideShow _hslfSlideShow
              , POIDocument oleData
              , OleType oleType
          ) throws IOException {
              ExOleObjStg exOleObjStg = new ExOleObjStg();
              int slideRecordPos = _hslfSlideShow.appendRootLevelRecord(exOleObjStg);
              exOleObjStg.setPersistId(slideRecordPos);
              exOleObjStg.setData(wrapOleData(oleData, oleType));
      
              // taken from SlideShow.createSlide
              Record _records[] = _hslfSlideShow.getRecords();
      
              // Add the new OLE record into the PersistPtr stuff
              int offset = 0;
              int slideOffset = 0;
              PersistPtrHolder ptr = null;
              UserEditAtom usr = null;
              for (int i = 0; i < _records.length; i++) {
                  Record record = _records[i];
                  ByteArrayOutputStream out = new ByteArrayOutputStream();
                  try {
                      record.writeOut(out);
                  } catch (IOException e) {
                      throw new HSLFException(e);
                  }
      
                  // Grab interesting records as they come past
                  if (_records[i].getRecordType() == RecordTypes.PersistPtrIncrementalBlock.typeID) {
                      ptr = (PersistPtrHolder) _records[i];
                  }
                  if (_records[i].getRecordType() == RecordTypes.UserEditAtom.typeID) {
                      usr = (UserEditAtom) _records[i];
                  }
      
                  if (i == slideRecordPos) {
                      slideOffset = offset;
                  }
                  offset += out.size();
              }
      
              // the ole objects needs to know its position within
              // the root records, because it will be later accessed
              // via its index from the shape
              int psrId = usr.getMaxPersistWritten() + 1;
              exOleObjStg.setPersistId(psrId);
      
              // Last view is now of the slide
              usr.setLastViewType((short) UserEditAtom.LAST_VIEW_SLIDE_VIEW);
              usr.setMaxPersistWritten(psrId); // increment the number of persit objects
      
              // Add the new slide into the last PersistPtr
              // (Also need to tell it where it is)
              exOleObjStg.setLastOnDiskOffset(slideOffset);
              ptr.addSlideLookup(psrId, slideOffset);
      
              return exOleObjStg;
          }
      
          static void linkOleDataToShape(OLEShape oleShape, ExEmbed exEmbed) {
              oleShape.setEscherProperty(EscherProperties.BLIP__PICTUREID, exEmbed.getExOleObjAtom().getObjID());
      
              EscherSpRecord spRecord = oleShape.getSpContainer().getChildById(EscherSpRecord.RECORD_ID);
              spRecord.setFlags(spRecord.getFlags()|EscherSpRecord.FLAG_OLESHAPE);
      
              // ExObjRefAtom is not set in OLEShape 
              UnknownEscherRecord uer = new UnknownEscherRecord();
              byte uerData[] = new byte[12];
              LittleEndian.putShort( uerData, 0, (short)0 ); // options = 0
              LittleEndian.putShort( uerData, 2, (short)RecordTypes.ExObjRefAtom.typeID); // recordId
              LittleEndian.putInt( uerData, 4, 4 ); // remaining bytes
              LittleEndian.putInt( uerData, 8, exEmbed.getExOleObjAtom().getObjID() ); // the data
              uer.fillFields(uerData, 0, null);
      
              EscherContainerRecord uerCont = new EscherContainerRecord();
              uerCont.setRecordId((short)RecordTypes.EscherClientData);
              uerCont.setVersion((short)0x000F); // yes, we are still a container ...
              uerCont.addChildRecord(uer);
      
              oleShape.getSpContainer().addChildRecord(uerCont);
          }
      
          static void setRecordLength(Record record, int len) throws NoSuchFieldException, IllegalAccessException {
              Field f = record.getClass().getDeclaredField("_header");
              f.setAccessible(true);
              byte _header[] = (byte[])f.get(record);
              LittleEndian.putInt(_header, 4, len);
              f.set(record, _header);
          }
      }
      

      这篇关于使用Apache POI HSSF嵌入（EXCEL）到HSLF（PPT）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！