Read embedded Object in excel using java

Question

I want to make one excel sheet which I need to send other for filling it. In the excel sheet , the other person fill his information and can also attach text/doc file with excel sheet.... I need to access that text/doc file .. Please provide me a solution . I am using Apache POI - HSSF api.

Thanks in advance.

package excelExchange;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Vector;

import org.apache.poi.hslf.HSLFSlideShow;
import org.apache.poi.hslf.usermodel.SlideShow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFObjectData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.poifs.filesystem.DirectoryNode;
//import org.apache.poi.h;
import org.apache.poi.poifs.filesystem.*;

public class ReadEmbeddedObject {

    public static void main(String[] args) throws IOException {

        String fileName = "C:\\Mayur\\NewsLetter\\files\\projectInfo.xls";
        //Vector dataHolder = 
        ReadCSV(fileName);
    }

    public static void ReadCSV(String fileName) throws IOException{
        Vector cellVectorHolder = new Vector();
        FileInputStream myInput = new FileInputStream(fileName);

        // myFileSystem=fs
        //myWorkBook=workbook



        POIFSFileSystem fs = new POIFSFileSystem(myInput);
        HSSFWorkbook workbook = new HSSFWorkbook(fs);

          for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) {
            //the OLE2 Class Name of the object
            System.out.println("Objects : "+ obj.getOLE2ClassName()+ "   2 .");
            String oleName = obj.getOLE2ClassName();


            if (oleName.equals("Worksheet")) {
                System.out.println("Worksheet");
                DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false);
                System.out.println(oleName+": " + embeddedWorkbook.getNumberOfSheets());
                System.out.println("Information :--- ");
                System.out.println(" name " + embeddedWorkbook.getSheetName(0));
                //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets());
            } else if (oleName.equals("Document")) {


                System.out.println("Document");
                DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                HWPFDocument embeddedWordDocument = new HWPFDocument(dn,fs);
                System.out.println("Doc : " + embeddedWordDocument.getRange().text());
            }  else if (oleName.equals("Presentation")) {
                System.out.println("Presentation");
                DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                SlideShow embeddedPowerPointDocument = new SlideShow(new HSLFSlideShow(dn, fs));
                //Entry entry = (Entry) entries.next();
                System.out.println(": " + embeddedPowerPointDocument.getSlides().length);
            } else {
                System.out.println("Else part ");
                if(obj.hasDirectoryEntry()){
                    // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is
                    DirectoryNode dn = (DirectoryNode) obj.getDirectory();
                    for (Iterator entries = dn.getEntries(); entries.hasNext();) {
                        Entry entry = (Entry) entries.next();
                        System.out.println(oleName + "." + entry.getName());
                    }
                } else {
                    // There is no DirectoryEntry
                    // Recover the object's data from the HSSFObjectData instance.
                    byte[] objectData = obj.getObjectData();
                }
            }
        }


    }

}

</code>

Jayan · Accepted Answer · 2012-03-19 07:38:41Z

1

POI has APIs to iterate over embedded objects. (HSSFWorkbook .getAllEmbeddedObjects or XSSFWorkbook.getAllEmbedds ). Examples here http://poi.apache.org/spreadsheet/quick-guide.html#Embedded

answered Mar 19, 2012 at 7:38

Jayan

18.6k15 gold badges92 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mayur Sharma Over a year ago

Hello jayan ,Thaanks . I am also using this.. but the problem is : I have included poi-3.7-20101029.jar -- This jar does not have HWPFDocument class. This class is contained in poi-3.0-FINAL.jar . But if I include the later (poi-3.0-FINAL.jar ), this does not have function "getAllEmbeddedObjects" .. I am not able to resolve this problem ..

Gagravarr Over a year ago

The POI Components Page tells you which jars you need depending on the bits of POI you want (hint - for HWPF you also need scratchpad)

Mayur Sharma Over a year ago

Thanks and i'll try this .. and wil come to u soon .

Mayur Sharma Over a year ago

Thanks Gagravarr . I have attached those required API for .doc/.docx .

Mayur Sharma Over a year ago

But on executing it showing an exception -- Exception in thread "main" java.io.FileNotFoundException: no such entry: "WordDocument" at org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:287) at org.apache.poi.hwpf.HWPFDocumentCore.<init>(HWPFDocumentCore.java:139) at org.apache.poi.hwpf.HWPFDocument.<init>(HWPFDocument.java:147) at excelExchange.ReadEmbeddedObject.ReadCSV(ReadEmbeddedObject.java:61) at excelExchange.ReadEmbeddedObject.main(ReadEmbeddedObject.java:27) I have searched it , but not able to figure it out why this is happenig !! Any Idea .

|

Swetha Prem · Accepted Answer · 2017-05-09 15:47:09Z

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;

/**
 * Demonstrates how you can extract embedded data from a .xlsx file
 */
public class GetEmbedded {

    public static void main(String[] args) throws Exception {
        String path = "SomeExcelFile.xlsx"
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(path)));

             for (PackagePart pPart : workbook.getAllEmbedds()) {
                            String contentType = pPart.getContentType();
                            if (contentType.equals("application/vnd.ms-excel")) { //This is to read xls workbook embedded to xlsx file
                                HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());
                                int countOfSheetXls=embeddedWorkbook.getNumberOfSheets();

                 }
                            else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) { //This is to read xlsx workbook embedded to xlsx file
                                 if(pPart.getPartName().getName().equals("/xl/embeddings/Microsoft_Excel_Worksheet12.xlsx")){
                                 //"/xl/embeddings/Microsoft_Excel_Worksheet12.xlsx" - Can read an Excel from a particular sheet 
                                // This is the worksheet from the Parent Excel-sheet-12

                                     XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(pPart.getInputStream());
                                     int countOfSheetXlsx=embeddedWorkbook.getNumberOfSheets();
                                     ArrayList<String> sheetNames= new ArrayList<String>();
                                        for(int i=0;i<countOfSheetXlsx;i++){
                                        String name=workbook.getSheetName(i);
                                        sheetNames.add(name);
                                        }
                                }
                            }
                }
     }
}

Welcome to StackOverflow! Please take a moment to read over the help guide on How do I write a good answer? At the moment you've only provided a large block of code - could you perhaps explain it a bit and how it answers the question so that it will be useful to others?

Collectives™ on Stack Overflow

Read embedded Object in excel using java

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related