extract .xls embedded in .doc

[Axel Rose <ax...@googlemail.com>]

Hello all,

has anybody time to check this?
I have trouble to extract a tabled embedded into a word document.

I followed to online guides as much as possible in the following manner:

---
...
    String fileName = args[0];
    InputStream inputStream = new FileInputStream(fileName);
    fs = new POIFSFileSystem(inputStream);
    DirectoryEntry root = fs.getRoot();
    recurse(root, 0);
  }

  private static void recurse(DirectoryEntry root, int level) throws
Exception {

      for (Iterator iter = root.getEntries(); iter.hasNext();) {
          Entry entry = (Entry) iter.next();
          for (int i = 0; i < level; i++)
              System.out.print("  ");
          if (entry instanceof DirectoryEntry) {
              System.out.println("found directory entry: " +
entry.getName());
              try {
                  HSSFWorkbook wb = new HSSFWorkbook((DirectoryNode) entry,
fs, false);
                  System.out.println(" found Excel object with " +
wb.getNumberOfSheets() + " sheets");
              }
              catch (Exception e) {
                  e.printStackTrace();
              }
              recurse((DirectoryEntry) entry, ++level);
              level--;
          }
      }
  }

At runtime I have valid "fs" and DirectoryEntry "entry" but I get

found directory entry: ObjectPool
found directory entry: _1147433807
java.lang.IllegalArgumentException: The supplied POIFSFileSystem contained
neither a 'Workbook' entry, nor a 'WORKBOOK' entry. Is it really an excel
file?
  at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:215)

Looking at the source of HSSFWorkbook.java I see that the entry is supposed
to be name /^workbook$/i

Reading the POIFS docu though hints that embedded documents may start with
underscore followed by numbers as is the case with my test case.

What is wrong with my code?


Thanks for any help,
Axel.

Using POI-3.1-beta2

Re: extract .xls embedded in .doc

[Axel Rose <ax...@googlemail.com>]

With great help from Yury I found the following principal solution which
might help others to solve similar questions.

(My original test input converted the embedded Excel table somehow to a Word
table (?))

---
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import org.apache.poi.hssf.record.RecordFormatException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.eventfilesystem.POIFSReader;
import org.apache.poi.poifs.eventfilesystem.POIFSReaderEvent;
import org.apache.poi.poifs.eventfilesystem.POIFSReaderListener;
import org.apache.poi.poifs.filesystem.DirectoryNode;
import org.apache.poi.poifs.filesystem.POIFSDocumentPath;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class POIFSExtract {

    public static void main(String[] args) throws Exception {
        if (args == null || args.length < 1)
            throw new Exception("\n->no input, no output<-");

        final String fileName = args[0];
        POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(fileName));

        POIFSReader reader = new POIFSReader();
        reader.registerListener(new MyPOIFSReaderListener(fs));
        reader.read(new FileInputStream(fileName));
    }

    static class MyPOIFSReaderListener implements POIFSReaderListener {

        private POIFSFileSystem fs;

        public MyPOIFSReaderListener(POIFSFileSystem fs) {
            this.fs = fs;
        }

        public void processPOIFSReaderEvent(POIFSReaderEvent event) {

            String name = event.getName();
            POIFSDocumentPath path = event.getPath();
            System.out.println("got '" + name + "' event for path '" + path
+ "'.");

            if (name.endsWith("Workbook") || name.endsWith("WORKBOOK")) {
                try {
                    DirectoryNode dir = resolveDir(fs,
event.getPath().toString()); // converting it to DirectoryNode
                    System.out.println("  trying DirectoryNode '" +
dir.getName() + "'");
                    HSSFWorkbook wb = new HSSFWorkbook(dir, fs, true); //
Invoke HSSFWorkbook constructor passing this directory to it
                    System.out.println("  !!! success: workbook with " +
wb.getNumberOfSheets() + " sheets.");
                }
                catch (RecordFormatException e) {
                    // MS Graph charts are stored in "Workbook" stream too
                    System.out.println("  skipping embedded MS Graph
object!");
                }
                catch (Exception e) {
                    System.out.println("  " + e.getMessage());
                    throw new RuntimeException(e.getMessage());
                }
            }
        }

        static DirectoryNode resolveDir(POIFSFileSystem filesystem, String
path) throws FileNotFoundException {
            DirectoryNode dir = filesystem.getRoot();

            for (String token : path.split("\\" + File.separator)) {
                if (!token.equals("") && !token.equals(File.separator))
                    dir = (DirectoryNode) dir.getEntry(token);
            }

            return dir;
        }
    }
}
---

Cheers,
Axel.

Re: extract .xls embedded in .doc

[Yury Batrakov <ba...@gmail.com>]

Hello Axel,

Your file looks strange as it doesn't contain Workbook OLE stream. I
can only guess that it was created by on system with no Excel
installed, so Word was unable to decide whether it's Excel
sheet/workbook or not. AFAIK, POI is unable to fetch such objects
(check http://mail-archives.apache.org/mod_mbox/poi-user/200805.mbox/%3c786c7f0805062348n1d19a648l962d50e0ec10c5c6@mail.gmail.com%3e
for details)

I attached a code that fetch Excel files but it relies on OLE
filesystem to contain Workbook streams. Also there is a docfile with
Excel and MS Graph objects embedded via Insert -> Object, I hope they
might be useful.

Re: extract .xls embedded in .doc

[Axel Rose <ax...@googlemail.com>]

Thanks Yury for your guidance.

I still cannot see how to solve this.
Do I need to read the InputStream and parse myself to recognize a workbook?

Feeding the DocumentInputStream to HSSFWorkBook constructor doesn't work.

---
import java.io.FileInputStream;

import org.apache.poi.poifs.eventfilesystem.POIFSReader;
import org.apache.poi.poifs.eventfilesystem.POIFSReaderEvent;
import org.apache.poi.poifs.eventfilesystem.POIFSReaderListener;
import org.apache.poi.poifs.filesystem.DocumentInputStream;
import org.apache.poi.poifs.filesystem.POIFSDocumentPath;

public class POIFSExtract {

    public static void main(String[] args) throws Exception {
        if (args == null || args.length < 1)
            throw new Exception("\n->no input, no output<-");

        final String fileName = args[0];
        POIFSReader reader = new POIFSReader();
        reader.registerListener(new MyPOIFSReaderListener());
        reader.read(new FileInputStream(fileName));
    }

    static class MyPOIFSReaderListener implements POIFSReaderListener {

        public void processPOIFSReaderEvent(POIFSReaderEvent event) {

            String name = event.getName();
            POIFSDocumentPath path = event.getPath();
            System.out.println("got '" + name + "' event for path '" + path
+ "'.");

            DocumentInputStream stream = event.getStream();
            try {
                byte[] buffer = new byte[stream.available()];
                int read = stream.read(buffer, 0, stream.available());
                System.out.println("  " + read + " bytes read.");
                // TODO: "fetch" directory name , convert to DirectoryNode
and feed to HSSFWorkbook

//                HSSFWorkbook wb = new HSSFWorkbook(stream);
//                System.out.println("  success: workbook with " +
wb.getNumberOfSheets() + " sheets.");
            }
            catch (Exception e) {
                System.out.println("  " + e.getMessage());
            }
        }
    }

}
----

at runtime:

got 'SummaryInformation' event for path '/'.
  436 bytes read.
got 'DocumentSummaryInformation' event for path '/'.
  320 bytes read.
got 'WordDocument' event for path '/'.
  11827 bytes read.
got '1Table' event for path '/'.
  6311 bytes read.
got 'ObjInfo' event for path '/ObjectPool/_1147433807'.
  4 bytes read.
got 'LinkInfo' event for path '/ObjectPool/_1147433807'.
  92 bytes read.
got 'PRNTPICT' event for path '/ObjectPool/_1147433807'.
  2310 bytes read.
got '
Ole' event for path '/ObjectPool/_1147433807'.
  1748 bytes read.
got '
CompObj' event for path '/'.
  88 bytes read.
got 'Data' event for path '/'.
  4096 bytes read.


Many thanks for your time,
Axel.

Re: extract .xls embedded in .doc

[Yury Batrakov <ba...@gmail.com>]

Your code tries to initialize HSSFWorkbook from ObjectPool OLE stream
abut this is wrong.

I think, the best way to do what you want is to:
1. create POIFSReader and register listener for all streams it returns
2. fetch directory name for those streams that end with "Workbook",
convert it to DirectoryNode
3. Invoke HSSFWorkbook constructor passing this directory to it
4. Beware of charts, embedded to word, they have Workbook stream name too

Example, showing how to deal with POIFS events can be found in
http://svn.apache.org/viewvc/poi/tags/REL_3_1_BETA2/src/examples/src/org/apache/poi/hpsf/examples/ReadCustomPropertySets.java?revision=653487&view=markup

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org