java读取excel文件

时间:2021-01-07 11:25:30

使用到的包: poi-ooxml-3.14.jar;  poi-ooxml.jar;

 

1. 在网上查找相关文件,最多的答案就是使用 HSSFWorkbook 进行读取:  

POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));
HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);

 结果:对xlsx文件识别不了;

 

2.专门针对xlsx文件找解决方案,得到用 XSSFWorkbook 进行处理;    

XSSFWorkbook workbook = new XSSFWorkbook(new BufferedInputStream(stream)); 

 结果:可以识别xlsx文件;

 

3.需要对文件进行判定,什么情况用 HSSFWorkbook,什么情况下用XSSFWorkbook 

if(!inp.markSupported())
  inp = new PushbackInputStream(inp, 8);
if(NPOIFSFileSystem.hasPOIFSHeader(IOUtils.peekFirst8Bytes(inp))){
  NPOIFSFileSystem fs = new NPOIFSFileSystem(inp);
}else if(POIXMLDocument.hasOOXMLHeader(inp))
  return new XSSFWorkbook(OPCPackage.open(inp));
else
  throw new InvalidFormatException("你的excel版本目前poi解析不了");

 ok,看似完美解决问题,但其实在WorkbookFactory里已经有了对excel 的相关操作方法

 

4. 借用工具类 WorkbookFactory  

/** 
 * Creates the appropriate HSSFWorkbook / XSSFWorkbook from 
 *  the given InputStream. 
 * Your input stream MUST either support mark/reset, or 
 *  be wrapped as a {@link PushbackInputStream}! 
 */  
 public static Workbook create(InputStream inp) throws IOException, InvalidFormatException {  
    // If clearly doesn't do mark/reset, wrap up  
    if(! inp.markSupported()) {  
        inp = new PushbackInputStream(inp, 8);  
    }  
          
    if(POIFSFileSystem.hasPOIFSHeader(inp)) {  
        return new HSSFWorkbook(inp);  
    }  
    if(POIXMLDocument.hasOOXMLHeader(inp)) {  
        return new XSSFWorkbook(OPCPackage.open(inp));  
    }  
    throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");  
  }  

 

5. 终极版 

Workbook workbook = WorkbookFactory.create(new FileInputStream(file));

 一行代码搞定,^_^;