Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
645 views
in Technique[技术] by (71.8m points)

java - Read Excel and get the row number and file extension for embedded objects

I am using Apache POI with Java 1.8 in my application. In my application, I try to read Excel and get the embedded objects.

I need to know how to get the row number and file extensions for each embedded OLE object.

Workbook workbook = WorkbookFactory.create(new File(path));

XSSFWorkbook fWorkbook = (XSSFWorkbook) workbook;

List<PackagePart> embeddedDocs = fWorkbook.getAllEmbedds();

To get embeddedDocs.getContentType Which returns the application/vnd.openxmlformats-officedocument.oleObject.

But is there anyway where we can get the file extensions (i.e pdf,ppt,mp3) as which is returned by the MimeType. And which way to get row number of embedded objects. Any ideas / Coding logic to resolve this will be very useful.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The following is working for - I guess - the usual suspects. I've tested it with .xls/x on the POI trunk, which will be POI 4.1.0, but should work with POI 4.0.1 too.

Known issues are:

  • an object wasn't embedded based on a file, then you don't get a filename. This probably also applies to most .xls files.

  • a .xlsx only contains a vmlDrawing*.xml, then the DrawingPatriach can't be extracted and no shapes can be determined

  • a shape in a .xlsx wasn't anchored via twoCellAnchor, then you don't get a ClientAnchor

Code:

import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hpsf.ClassIDPredefined;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.ss.usermodel.ChildAnchor;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.ObjectData;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.junit.Test;

public class TestEmbed {
    @Test
    public void extract() throws IOException {
//        String xlsName = "test-data/spreadsheet/WithEmbeddedObjects.xls";
        String xlsName = "embed.xlsx";
        try (FileInputStream fis = new FileInputStream(xlsName);
             Workbook xls = WorkbookFactory.create(fis)) {
            for (Sheet s : xls) {
                Drawing<?> dp = s.getDrawingPatriarch();
                if (dp != null) {
                    for (Shape sh : dp) {
                        if (sh instanceof ObjectData) {
                            ObjectData od = (ObjectData)sh;
                            String filename = od.getFileName();
                            String ext = null;

                            if (filename != null && !filename.isEmpty()) {
                                int i = filename.lastIndexOf('.');
                                ext = (i > 0) ? filename.substring(i) : ".bin";
                            } else {
                                String ct = null;

                                try {
                                    DirectoryEntry de = od.getDirectory();

                                    if (de != null) {
                                        ClassIDPredefined ctcls = ClassIDPredefined.lookup(de.getStorageClsid());
                                        if (ctcls != null) {
                                            ext = ctcls.getFileExtension();
                                        }
                                    }
                                } catch (Exception ignore) {
                                }
                            }

                            if (ext == null) {
                                ext = ".bin";
                            }

                            ChildAnchor chAnc = sh.getAnchor();
                            if (chAnc instanceof ClientAnchor) {
                                ClientAnchor anc = (ClientAnchor) chAnc;
                                System.out.println("Rows: " + anc.getRow1() + " to " + anc.getRow2() + " - filename: "+filename+" - ext: "+ext);
                            }
                        }
                    }
                }
            }
        }
    }
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...