Java POI the supplied data appears to be in the Office 2007+ XML

Question

I am getting this error:

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (e.g. XSSF instead of HSSF)

I read throw Google and I found out that I need to use XSSF instead of HSSF because my Excel file is xlsx, but as you see in my maven, I am already using xlsx. Where have I gone wrong please?

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.13-beta1</version>
    </dependency>

The code the makes the exception is:

POIFSFileSystem fs;

            fs = new POIFSFileSystem(new FileInputStream(getFilePath()));

My new code

public void getBColum() {
    try {
        OPCPackage fs;

        fs = new OPCPackage.open(new File(getFilePath()));

        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheet("Master column name - Used Car");
        XSSFRow row;
        CellReference cr = new CellReference("A1");
        row = sheet.getRow(cr.getCol());
        System.out.println(row.getCell(3));
    } catch (FileNotFoundException e) {
        if (logger.isDebugEnabled()) {
            logger.debug("How can this error be possible? we should have already thrown an exception in the construction");
        }
    } catch (IOException e) {
        logger.error(String.format("Exception in reading the file: %s",
                e.getMessage()));
    }
}

I have a compile error in new oPCPackage.open which is:

OPCPackage.open cannot be resolved to a type

Please show the relevant code that threw the exception. – rgettman Aug 05 '15 at 23:15 — rgettman, Aug 05 '15 at 23:15

rgettman · Accepted Answer · 2015-08-06T16:38:57.277

69

According to the Apache POI Quick Guide, the POIFSFileSystem (or similarly, NPOIFSFileSystem) is only used with .xls (Excel versions through 2003) documents.

The equivalent for .xlsx documents (Excel 2007+) is OPCPackage.

OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));

You can create an XSSFWorkbook from the OPCPackage:

XSSFWorkbook wb = new XSSFWorkbook(pkg);

Or you can just create it directly:

XSSFWorkbook wb = new XSSFWorkbook(new File("file.xlsx"));

Generally it's better to create the workbook using a File instead of an InputStream, to save memory.

Also, if you want code that doesn't care whether it's an .xls or an .xlsx:

// or "file.xlsx"
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));

edited Aug 06 '15 at 16:38

answered Aug 05 '15 at 23:27

rgettman

176,041
30
275
357

1

there is wrong in the first line `opcpackage.open` .... `OPCPackage.open cannot be resolved to a type` – Marco Dinatsoli Aug 05 '15 at 23:36
`import org.apache.poi.openxml4j.opc.OPCPackage;` – rgettman Aug 05 '15 at 23:37
1

that is what i am importing, and i am having the error – Marco Dinatsoli Aug 05 '15 at 23:43
It works for me, when I catch the `InvalidFormatException` that `open` throws. – rgettman Aug 05 '15 at 23:47
maybe your forgot something in your code here, because, as you see in the updated question, i am having a compiler error, i am using java 8 – Marco Dinatsoli Aug 05 '15 at 23:49
1

Now that I see your code, remove the "new" -- `OPCPackage.open` is a `static` method. Try `fs = OPCPackage.open(new File(getFilePath()));`. – rgettman Aug 05 '15 at 23:50
import this dependence to your project: implementation 'org.apache.poi:poi-ooxml:3.8-beta4:' or check here if like me, you were missing dependences to use XSSF objects https://stackoverflow.com/a/8202181/7953908 – Gilian Marques May 29 '20 at 08:21

score 4 · Answer 2 · answered Dec 04 '18 at 15:39

4

I was using XSSF with a xlsx file, but got this error when I tried to process a file that was encrypted/protected with a password.

Once I removed the password, everything worked as expected.

answered Dec 04 '18 at 15:39

cs_pupil

2,782
27
39

score 0 · Answer 3 · answered Aug 11 '20 at 08:43

well actually there is no OPCPackage, I am using https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/3.5-beta5 so you have to:

import org.apache.poi.openxml4j.opc.Package;
....
Package fs = Package.open(new ByteArrayInputStream(container.getContent()));
            XSSFWorkbook wb = new XSSFWorkbook(fs);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row;
            XSSFCell cell;

Java POI the supplied data appears to be in the Office 2007+ XML

My new code

3 Answers3

Linked

Related