Error reading Excel.XLSX using Apache POI

Question

I am using Apache POI 3.8 libraries to read the XLSX file in a web application. The following code works fine with a Java console application:

InputStream inputFS = new FileInputStream("test.xlsx"); Workbook workbook = new XSSFWorkbook(inputFS); // below exception is thrown on this line Sheet sheet = workbook.getSheetAt(0); 

but gives a "read error" when used in a web application. The corresponding stack trace break will be inserted below:

 java.io.IOException: Read error at java.io.FileInputStream.readBytes(Native Method) ~[na:1.6.0_31] at java.io.FileInputStream.read(Unknown Source) ~[na:1.6.0_31] at java.io.FilterInputStream.read(Unknown Source) ~[na:1.6.0_31] at java.io.PushbackInputStream.read(Unknown Source) ~[na:1.6.0_31] at java.util.zip.ZipInputStream.readFully(Unknown Source) ~[na:1.6.0_31] at java.util.zip.ZipInputStream.readLOC(Unknown Source) ~[na:1.6.0_31] at java.util.zip.ZipInputStream.getNextEntry(Unknown Source) ~[na:1.6.0_31] at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.<init>(ZipInputStreamZipEntrySource.java:51) ~[poi-ooxml-3.8-20120326.jar:3.8] at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:83) ~[poi-ooxml-3.8-20120326.jar:3.8] at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:228) ~[poi-ooxml-3.8-20120326.jar:3.8] at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39) ~[poi-ooxml-3.8-20120326.jar:3.8] at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:187) ~[poi-ooxml-3.8-20120326.jar:3.8] at com.corp.ReportManager.parseExcelReport(ReportManager.java:575) [ReportManager.class:na] 

The following JAR classes are included in the class path (in the same order):

 poi-3.8-20120326.jar poi-ooxml-3.8-20120326.jar poi-ooxml-schemas-3.8-20120326.jar xbean.jar dom4j-1.6.1.jar 

It seems that memory problems are not memory related, since I collected heap usage statistics just before calling the above code. The XLSX file size is 1.15 MB.

 ##### Heap utilization statistics [MB] ##### Used Memory:13 MB Free Memory:9 MB Total Memory:23 MB Max Memory:247 MB 

Answer 1

A method using the above code has a single parameter - FileInputStream. The first line in the code snippet is a very important part of the code, but it is part of the calling method. Since this method was not aware of the Excel format or even the file extension in order to get a reasonable guess, I decided that I would first try to read FileInputStream using the HSSF API, as shown below:

 Sheet sheet = null; try { POIFSFileSystem poifs = new POIFSFileSystem(inputFS); Workbook workbook = new HSSFWorkbook(poifs); sheet = workbook.getSheetAt(0); } catch (Exception e) { } if (sheet == null) { try { Workbook workbook = new XSSFWorkbook(inputFS); sheet = workbook.getSheetAt(0); } catch (Exception e) { } } 

The problem with the above code is that the state of the inputFS object during the second attempt to open it through the XSSF API is unknown. And that gave a read error . I replaced the above code, which works fine, and the problem seems to be resolved:

 Sheet sheet = null; try { Workbook workbook = WorkbookFactory.create(inputFS); sheet = workbook.getSheetAt(0); } catch (Exception e) { } 

I tested this with both XLS (older, binary) and XLSX (newer, XML-based) formats, and it works. Thanks for the help and help!

Answer 2

An exception indicates that something is happening with your InputStream. However, if you have a file, then transfer it directly to the POI !. Using an InputStream requires buffering everything in memory that eats space. Since you do not need to do this buffering, do not do this! By avoiding this buffering, you should still fix your problem.

If you use the latest POI nightly builds, then it is very simple. Your code will look like this:

 File file = new File("test.xlsx"); OPCPackage opcPackage = OPCPackage.open(file); XSSFWorkbook workbook = new XSSFWorkbook(opcPackage); 

Otherwise, it is very similar:

 File file = new File("test.xlsx"); OPCPackage opcPackage = OPCPackage.open(file.getAbsolutePath()); XSSFWorkbook workbook = new XSSFWorkbook(opcPackage); 

If you are not sure if your file is HSSFWorkbook or XSSFWorkbook, you can use WorkbookFactory to open the corresponding file for you:

 File file = new File("test.xlsx"); Workbook workbook = WorkbookFactory.create(file); 

Answer 3

it looks like you need to use the XSSF API

Answer 4

 I have same error, I have just updated the pom dependencies with same version. It worked. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.0</version> </dependency> 

Answer 5

use this jar

 <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.3.0</version> </dependency>