jxl.read.biff.BiffException: Unable to recognize OLE stream
Written by coregps on Wednesday, April 26th, 2006 in Java.
If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!
I have been using JExcelAPI to import data from excel spreadsheets to database. For ease of use, I wrote a JavaBean named DataImport. However, it can not work recently. it always gives me the following exception:
jxl.read.biff.BiffException: Unable to recognize OLE stream at jxl.read.biff.CompoundFile.(CompoundFile.java:116) at jxl.read.biff.File. (File.java:127) at jxl.Workbook.getWorkbook(Workbook.java:268) at jxl.Workbook.getWorkbook(Workbook.java:253)
I first considered the problem was caused by Excel 2003. Because we used Excel 2000 originally, which uses the BIFF8 format, while excel XP and excel 2003 use BIFF8X format which is an extension of The BIFF8 format. However, when I imported an excel file created in excel2000, I got the same exception.
I downloaded the source code of JExcelAPI, and found the code line throwing the exception
// CompoundFile.java
// First verify the OLE identifier
for (int i = 0; i < IDENTIFIER.length; i++)
{
if (data[i] != IDENTIFIER[i])
{
throw new BiffException(BiffException.unrecognizedOLEFile);
}
}
The definition of IDENTIFIER can be found in BaseCompoundFile.java
/**
* The identifier at the beginning of every OLE file
*/
protected static final byte[] IDENTIFIER = new byte[]
{(byte) 0xd0,
(byte) 0xcf,
(byte) 0x11,
(byte) 0xe0,
(byte) 0xa1,
(byte) 0xb1,
(byte) 0x1a,
(byte) 0xe1};
And then, I opened the excel file using UltraEdit in Hex mode
0 1 2 3 4 5 6 7 8 9 a b c d e f 00000000h: D0 CF 11 E0 A1 B1 1A E1 00 00 00 00 00 00 00 00 ; ?.???....... 00000010h: 00 00 00 00 00 00 00 00 3E 00 03 00 FE FF 09 00 ; ........>...?.. 00000020h: 06 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 ; ................ 00000030h: 01 00 00 00 00 00 00 00 00 10 00 00 02 00 00 00 ; ................ 00000040h: 01 00 00 00 FE FF FF FF 00 00 00 00 00 00 00 00 ; ....? ........
As you can see, the first 8 bytes of the file is the same as the definition in BaseCompoundFile.java. So, it is not the problem of the excel file. I compared my code with the working one carefully, and found the root cause of the problem in the end.
The excel file is uploaded using a multipart/form-data form. In the working one, there is only an input type=”file” element in the form element. It is something like this:
<form action="upload.jsp" enctype="MULTIPART/FORM-DATA" method=post>
Select file to import <input type="file" name="excelfilename"> <br>
<input type="submit" value="Import">
</form>
While in the one throwing the exception, there are some other elements besides the file element:
<form action="upload.jsp" enctype="MULTIPART/FORM-DATA" method=post>
Select table to import <br/>
<input type="radio" name="tbl2import" value="1">Product<br/>
<input type="radio" name="tbl2import" value="2">Order<br/>
<input type="radio" name="tbl2import" value="3">Customer<br/>
Select file to import <input type="file" name="excelfilename"> <br>
<input type="submit" value="Import">
</form>
And the following is the code snippet used to parse the input stream from the request.
ServletInputStream is = request.getInputStream(); byte[] junk = new byte[1024]; int bytesRead = 0; // strip off the HTTP information from input stream // the first four lines are request junk bytesRead = is.readLine(junk, 0, junk.length); bytesRead = is.readLine(junk, 0, junk.length); bytesRead = is.readLine(junk, 0, junk.length); bytesRead = is.readLine(junk, 0, junk.length); // create the workbook object from the ServletInputStream Workbook workbook = Workbook.getWorkbook(is); Sheet sheet = workbook.getSheet(0);
When using the former form, the input stream from the request contains only the contents of the uploaded file. While using the latter one, the input stream contains not only the contents of the excel file, but also the contents of the other form elements, this is the reason why JExcelAPI throwing the “Unable to recognize OLE stream” exception.
To solve the problem, there are two solutions.
1) Remove all of the other form elements from the upload form except the file element.
2) Use the commons upload package from Jakarta to handle the multipart/form-data POST request. And change the code used to parse the request to something like this(IMO, this is the better one.):
// Create a factory for disk-based file items
FileItemFactory factory = new DiskFileItemFactory();
// Create a new file upload handler
ServletFileUpload upload = new ServletFileUpload(factory);
// Parse the request
List items = upload.parseRequest(request);
InputStream is = null;
// Process the uploaded items
Iterator iter = items.iterator();
while (iter.hasNext()) {
FileItem item = (FileItem) iter.next();
if (!item.isFormField()) {
is = item.getInputStream();
break;
}
}
// create the workbook object from the ServletInputStream
Workbook workbook = Workbook.getWorkbook(is);
Sheet sheet = workbook.getSheet(0);
The problem above gave me a lession. Most of time I know it works. but don’t understand why it works as it does. I must be serious later.
