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)

Iorgos

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 – Blankbook

Answer 1

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.

Hippo

Well...that was quite insightful....can you give me an idea about any API that handles both the format – Iorgos

Apache POI handles both file types in Java: poi.apache.org/spreadsheet/index.html – Hippo

Answer 2

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

Pejorative

Answer 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.

Cease

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. – Wakeful

Answer 4

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

Noah

Answer 5

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

Righthander