Exception when open xls files

Question

I have a xls file which i can open in excel, but when i try to open it with Apache POI, i get this exception :

java.io.IOException: block[ 3 ] already removed - does your POIFS have circular or duplicate block references?
at org.apache.poi.poifs.storage.BlockListImpl.remove(BlockListImpl.java:89)
at org.apache.poi.poifs.storage.RawDataBlockList.remove(RawDataBlockList.java:34)
at org.apache.poi.poifs.storage.BlockAllocationTableReader.fetchBlocks(BlockAllocationTableReader.java:221)
at org.apache.poi.poifs.storage.BlockListImpl.fetchBlocks(BlockListImpl.java:123)
at org.apache.poi.poifs.storage.RawDataBlockList.fetchBlocks(RawDataBlockList.java:34)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.processProperties(POIFSFileSystem.java:528)
at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:163)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:327)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:308)
at controlers.ExcelProject2.setBook(ExcelProject2.java:327)
at controlers.ExcelProject2.<init>(ExcelProject2.java:149)
at controlers.ExcelProject2Tests.main(ExcelProject2Tests.java:41)

The problem appears at this part of my code :

FileInputStream fs = new FileInputStream(pathFiles);
Workbook book = new HSSFWorkbook(fs);

If I open the file in excel, "Save as" and then open the new file with Java, it works. Even if I open the file and save, without changing anything, it works. I don't know why but the new file is bigger than the old one.

I have tried to use differents solution that i found on google, like using BOMInputStream or NPOI File but it didn't work. I'm currently using poi-3.8-20120326.

I can not share the file because it contains private information but there nothing else than number and String.

I remain at your disposal for more information.

Regards.

EDIT : I don't know if that makes a difference, but i have forgot to say that the file is in Compatibility mode

How was the xls generated? Can you post that code? Can you generate one without the private information and share it? — Burkhard
– Burkhard, Commented May 27, 2015 at 8:19
Well, idk how the xls was generated because he come from a customer. And i can't share without private information because if i change it, the bug disappears :/. I think i can post a screen if i hide some informations but i don't know if it's useful — HRomain
– HRomain, Commented May 27, 2015 at 8:21

Gagravarr · Accepted Answer · 2015-05-27 09:12:36Z

4

You have two problems that I can see. Firstly, that you're using the older POIFS implementation, which is known to have some issues on certain block combinations, and secondly that you're using an InputStream when you have a File which has a higher memory footprint

Taking the code

FileInputStream fs = new FileInputStream(pathFiles);
Workbook book = new HSSFWorkbook(fs);

On POI 3.12 or newer, the easy fix is to replace that with:

Workbook book = WorkbookFactory.create(new File(pathFiles));

That will use a File not a stream, and will use NPOIFS, which is a re-write which fixes issues like the one you have

answered May 27, 2015 at 9:12

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HRomain Over a year ago

is this works for both xls and xlsx ? if it works, i'll try asap and i'll tell you the result.

Gagravarr Over a year ago

Make sure you write out to a different / new file. In-place write isn't currently supported

HRomain Over a year ago

Well, it solved my problem but now i have the following error. Exception in thread "main" java.lang.IllegalArgumentException: Position 1024 past the end of the file , when i do : book.write(fileOut);. I think it's because i don't have InputStream anymore, but what can i do to solve it ?

CBono Over a year ago

This helped me solve a similar problem with the .NET port of POI, NPOI. In my case, instead of calling WorkbookFactory.Create with a Stream or a string (filename), I had to create a new instance of NPOI.POIFS.FileSystem.NPOIFSFileSystem (using the FileInfo overload), and call the factory with it.

Collectives™ on Stack Overflow

Exception when open xls files

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related