[Solved] POI Read excel Error: Your InputStream was neither an OLE2 stream, nor an OOXML stream

When Java uses POI to read Excel files with XLS suffix, an error is reported:

Your InputStream was neither an OLE2 stream, nor an OOXML stream

Error code:

Workbook wb = WorkbookFactory.create(is);

Click the Create method to see the source code of POI:

public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {
        if (!((InputStream)inp).markSupported()) {
            inp = new PushbackInputStream((InputStream)inp, 8);
        }

        if (POIFSFileSystem.hasPOIFSHeader((InputStream)inp)) {
            return new HSSFWorkbook((InputStream)inp);
        } else if (POIXMLDocument.hasOOXMLHeader((InputStream)inp)) {
            return new XSSFWorkbook(OPCPackage.open((InputStream)inp));
        } else {
            throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
        }
    }

The haspoofshade and hasooxmlreader in the two IFS are to read the first 8 bytes of the excel file stream to determine the file information. If it is not an excel type file or a binary type file, the exception “your InputStream was neither an ole2 stream, nor an OOXML stream” will be thrown directly

Analysis:
open the problematic XLS file with sublime text text editor or Notepad + +, which is XML in text form:

Open other normal XLS files with sublime text text editor as follows, which are binary:

Reason for the problem:
the XLS file in question is actually an office openxml file, also known as spreadsheetml format (XML format of Excel). Its suffix should be XML instead of XLS. It is not a standard excel file, so it will report an error when reading with POI.

Solution:
use Excel to open the problematic XLS file, as follows:
select Yes, then save the file as XLS format, and then use POI analysis to avoid reporting errors

Read More: