java poi读取excel报错Your InputStream was neither an OLE2 stream, nor an OOXML stream

错误描述

java poi读取excel报错Your InputStream was neither an OLE2 stream, nor an OOXML stream

楼主是读取的另存为的excel

定位报错代码

String basePath = "E:\\test\\数据data.xls";
InputStream inp  = new FileInputStream(basePath);
Workbook wb = WorkbookFactory.create(is);

源码定位分析

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");
        }
    }

分析：poi根据path全路径自动解析识别excel格式出错，排除其他文本后缀及格式问题（见错误描述），那就是自动识别xls或者xlsx的问题了

问题解决

InputStream inp = null;
Workbook wb = null;
try {
    inp = new FileInputStream(basePath);
    wb = WorkbookFactory.create(inp);
} catch (IOException e) {
    System.out.println("try catch");
    inp = new FileInputStream(basePath);
    if (filePath.contains(".xlsx")) {
        try {
            wb = new XSSFWorkbook(OPCPackage.open(inp));
        } catch (Exception e1) {
            inp = new FileInputStream(basePath);
            System.out.println("try catch again1");
            wb = new HSSFWorkbook(inp);
        }
    } else {
        try {
            wb = new HSSFWorkbook(inp);
        } catch (Exception e1) {
            inp = new FileInputStream(basePath);
            System.out.println("try catch again1");
            wb = new XSSFWorkbook(OPCPackage.open(inp));
        }
    }

} finally {
    if (null != inp) {
        try {
            inp.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

思路：当自动识别出现错误后，在catch里手动指定后缀读取，问题得以解决（InputStream 默认只能读取一次，可以转换成ByteArrayOutputStream或者利用反射实现多次读取【详见下面附录部分】，楼主嫌弃麻烦直接重新new了）

附录

参考自 https://blog.csdn.net/qq_37220419/article/details/104645398

场景描述：我们需要对同一个InputStream对象使用多次。比如，客户端从服务器获取数据 ，利用HttpURLConnection的getInputStream()方法获得Stream对象，需要将InputStream流读取获取相关参数（第一次读取），又想把InputStream流中的数据写进文件缓存到本地（第二次读取）。

问题描述：第一次读取InputStream对象后，第二次再读取时可能已经到Stream的结尾了（EOFException）或者Stream已经close掉了，所有获取到的数据为null或者说没有获取到数据。

解决方法（一）：
因为InputStream对象本身不能复制（InputStream没有实现Cloneable接口），所以先把InputStream转化成ByteArrayOutputStream，后面要使用InputStream对象时，再从ByteArrayOutputStream转化回来就好了，如下代码：
//获取客户端InputStream对象
InputStream input= requset.getInputStream();
//将InputStream对象转换成ByteArrayOutputStream
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();  
byte[] buffer = new byte[1024];  
int len;  
while ((len = input.read(buffer)) > -1 ) {  
    byteArrayOutputStream.write(buffer, 0, len);  
}  
byteArrayOutputStream.flush();                
//将byteArrayOutputStream可转换成多个InputStream对象，达到多次读取InputStream效果
InputStream inputStreamA = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
InputStream inputStreamB = new ByteArrayInputStream(byteArrayOutputStream.toByteArray()); 
//将InputStream转换成字符串
BufferedReader br = new BufferedReader(new InputStreamReader(inputStreamB,"UTF-8"));
String line = null;
StringBuilder sb = new StringBuilder();
while ((line = br.readLine()) != null) {
    sb.append(line);
}
解决方法二(获取inputStream 的open0方法，反射调用)
@Test
    public void testFileinp() throws IOException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        FileInputStream inputStream=new FileInputStream("E:/test/te.txt");
        FileOutputStream outputStream=new FileOutputStream("E:/test/te2.txt");
        FileOutputStream outputStreams=new FileOutputStream("E:/test/te3.txt");
        int len;
        byte [] by=new byte[8192];
        while ((len=inputStream.read(by))!=-1){
            outputStream.write(by,0,len);
        }
        if(inputStream.read()==-1){
            Class in=inputStream.getClass();
            Method openo= in.getDeclaredMethod("open0", String.class);
            openo.setAccessible(true);
            openo.invoke(inputStream,"E:/test/te.txt");
        }
        while ((len=inputStream.read(by))!=-1){
            outputStreams.write(by,0,len);
        }
        outputStream.close();
    }

附上具体操作demo实例 ：excel(xls或xlsx)便捷copy及poi操作demo.rar https://download.csdn.net/download/qq_25073261/85111496