Tag Archives: poi

[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

Java will convert Excel to list set or JSON, export excel file to local, excel import and export, easyexcel tool class

Introducing Maven coordinates

        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>        
        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>

According to the file path, the excel file is parsed into a list

    /**
     * Parsing Excel files into Lists
     *
     * @param pathName file path (e.g. D:\test\xzh.xlsx)
     * @param head Table header
     * @param <T> generic
     * @return
     */
    public static <T> List<T> upload(String pathName, Class<T> head) {
        List<T> list = new ArrayList<>();
        AnalysisEventListener<T> analysisEventListener = new AnalysisEventListener<T>() {
            // This will be called for every data parsing
            @Override
            public void invoke(T data, AnalysisContext context) {
                log.info("Parsing a piece of data:{}", JSON.toJSONString(data));
                list.add(data);
                // TODO Here you can also manipulate the data
            }

            // All the data parsing is done and will be called
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("All data parsing is complete!") ;
                // TODO Here you can also manipulate the data
            }
        };
        EasyExcel.read(pathName, head, analysisEventListener).sheet().doRead();
        return list;
    }

According to the file stream, the excel file is parsed into a list

    /**
     * Parsing Excel files into Lists
     *
     * @param file File
     * @param head table header
     * @param <T> generic
     * @return
     */
    public static <T> List<T> upload(MultipartFile file, Class<T> head) {
        List<T> list = new ArrayList<>();
        AnalysisEventListener<T> analysisEventListener = new AnalysisEventListener<T>() {
            // This will be called every time the data is parsed
            @Override
            public void invoke(T data, AnalysisContext context) {
                log.info("Parsed a piece of data: {}", JSON.toJSONString(data));
                list.add(data);
                // TODO You can also manipulate the data here
            }

            // All the data parsing is done and it will call
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("All data parsing is complete!") ;
                // TODO Here you can also manipulate the data
            }
        };
        InputStream inputStream;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            log.error("upload-InputStream-Exception：", e);
            return null;
        }
        EasyExcel.read(inputStream, head, analysisEventListener).sheet().doRead();
        return list;
    }

Download excel file to file path

    /**
     * Excel file download
     *
     * @param pathName File path
     * @param sheetName Worksheet name
     * @param data data
     * @param head Table header
     * @param <T> generic
     */
    public static <T> void download(String pathName, String sheetName, List<T> data, Class<T> head) {
        EasyExcel.write(pathName, head).sheet(sheetName).doWrite(data);
    }

Excel file download to file path response body response

    /**
     * Excel file download
     *
     * @param response response body
     * @param excelName File name
     * @param sheetName Worksheet name
     * @param data data
     * @param head table header
     * @param <T> generic
     */
    public static <T> void download(HttpServletResponse response, String excelName, String sheetName, List<T> data, Class<T> head) {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        try {
            String fileName = URLEncoder.encode(excelName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), head).sheet(sheetName).doWrite(data);
        } catch (IOException e) {
            log.error("download-Exception：", e);
        }
    }

GitHub: https://github.com/xxiangzh/xzh-excel/tree/easyexcel