BiffException while reading an excel sheet

Question

I have a code to read from an excel sheet which is in old format(97-2003). I made some changes to data and ended up a 2007 format excel sheet. When I used this xlsx sheet instead of xls sheet, I am getting this:

jxl.read.biff.BiffException: Unable to recognize OLE stream
    at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
    at jxl.read.biff.File.<init>(File.java:127)
    at jxl.Workbook.getWorkbook(Workbook.java:268)
    at readexcel.ReadExcel.readContent(ReadExcel.java:50)
    at readexcel.ReadExcel.init(ReadExcel.java:25)
    at readexcel.ReadExcel.main(ReadExcel.java:183)

It looks like jxl is still using the old xls reader to try and read your new xlsx file - why not simply "Save As" from within MS Excel to save it in the older 97-2003 format — Mark Baker, Mar 25 '11 at 12:05

jmcnamara · Answer 1 · 2011-04-11T09:10:50.210

17

The xls format (< Excel 2007) is comprised of binary BIFF data in an OLE container. The xlsx format (>= Excel 2007) is comprised of XML files in a zip container.

The Java Excel API only deals with the first format so it throws an exception when it doesn't encounter an OLE container.

You will need to restrict your input to xls files only or find another tool that handles both formats.

edited Apr 11 '11 at 09:10

answered Mar 28 '11 at 10:58

jmcnamara

38,196
6
90
108

Well...that was quite insightful....can you give me an idea about any API that handles both the format – nowfal Mar 31 '11 at 09:34
1

Apache POI handles both file types in Java: http://poi.apache.org/spreadsheet/index.html – jmcnamara Mar 31 '11 at 15:59

score 4 · Answer 2 · edited May 28 '13 at 11:29

4

"save as" your workbook as "Excel 97-2003 workbook" (option comes in Microsoft Excel 2007) it saves your file in OLE container.

edited May 28 '13 at 11:29

Taryn

242,637
56
362
405

answered Jan 10 '13 at 08:02

Shweta R

41
1

score 3 · Answer 3 · answered Aug 02 '13 at 08:52

3

I just have had the same trouble. Project was Maven based and side effect of following directive was to filter XLS file.

<resources>
        <resource>
            <directory>src/main/resources</directory>
            <filtering>true</filtering>
        </resource>
</resources>

Clean workaround solution was to define sub folder "config" and allow filtering on this specific folder, while including but not filtering on other sub-folders.

answered Aug 02 '13 at 08:52

Olivier ROMAND

579
4
15

1

I had the same problem. I'm using maven to start up SoapUI with some Excel Workbooks. And when you filter those Excel files with maven, it changes them internally making them unusable. So the solution is not to filter them. – CHiRo79 Dec 16 '14 at 14:14

score 0 · Answer 4 · answered Jan 18 '17 at 03:29

0

I was facing same issue in Eclipse/Selenium and tried to "Save As" 97-2000 (.xls) and it solved my problem.

answered Jan 18 '17 at 03:29

Hiten

1

score 0 · Answer 5 · answered Apr 13 '22 at 00:53

I have also faced the same problem.

It is because the Excel you are using of extension (.xlsx) and this format is not correctly reading by the software you are using to load.

Hence Use the older versions of excel extension which is (.xls) as those may be compatible with the tool you are working.

Then it will understand and read successfully

BiffException while reading an excel sheet

5 Answers5