how to solve JXL error : jxl.read.biff.BiffExcepti

问题:

i am trying to get cell data from my .csv file but it gets error : jxl.read.biff.BiffException: Unable to recognize OLE stream

I don't understand how to solve this,please give me some solution this code is for jxl api & is that api support to .csv?

Code for reference:

public void read() throws IOException, BiffException  {

    File inputWorkbook = new File(inputFile);

    try
    {
        w = Workbook.getWorkbook(inputWorkbook.getAbsoluteFile());
        // Get the first sheet
        Sheet sheet = w.getSheet(0);
        // Loop over first 10 column and lines

        for (row = 1; row < sheet.getRows(); row++) 
        {
            ReadExcelLotSizeEntity readExcelLotSizeEntity =new ReadExcelLotSizeEntity();

                cell = sheet.getCell(1,row);
                type= cell.getType();
                if (cell.getType() == CellType.LABEL)
                {

                    symbol=cell.getContents();
                    System.out.println(":::::::::::::::::"+symbol);
                    readExcelLotSizeEntity.setSymbol(symbol);
                }   

                int col=2;
                cell = sheet.getCell(col,row);
                while(!cell.getContents().equals("")||cell.getContents()!=null)
                {
                    System.out.println("||||||||||||||||"+cell.getContents());
                    cell=sheet.getCell(col,row);
                    col++;
                }
                lotSize= new Double(cell.getContents());
                readExcelLotSizeEntity.setLotSize(lotSize);
                readExcelLotSizeEntity.setCreateUserId(1L);
                readExcelLotSizeEntity.setCreateDtTm(new Date());
                readExcelLotSizeHome.persist(readExcelLotSizeEntity);
            }

    } catch (BiffException e) {
        e.printStackTrace();
    }

}

回答1:

I was also facing this problem earlier. I googled and read this post and many other posts that were asking for solution to this BiffException. I don't have the exact solution but as I fixed my problem you can do it too, perhaps.

I was trying to read data from the Excel file saved in MS Office 2010 and I was getting this error. I saved the file as an Excel 2003-7 and then read it without any problem. It may the case that this problem occurs in Office 10 but not in Office 2003-7.

I hope this will work in your case.

回答2:

Saving File as "Excel 97-2003 Workbook" type solved my issue.

回答3:

JXL library doesnot support .csv and .xslx formats, which is the format used by Excel-2010. hence, use Excel 97-2003 which is .xls foramatted and is supported by JXL library. or else if you want to use excel-2010, use APACHE POI(XSSFWorkbooks) instead of JXL. For using .csv format, google for CSVReader libraries.

回答4:

Save the Excel file type as Excel 97-2003 Worksheet and extension type as xls

回答5:

Actually you are using different version of csv file .Please save it in the exact version.

For ex: we should save the excel sheet in word as 9

回答6:

save the file as Excel 97-2003 and also change the file format from xlsx to xlx , in the code(in the file name)

回答7:

I was trying to read data from the Excel file saved in MS Office 2010 and I was getting this error. I saved the file as an Excel 2003-7 and then read it without any problem. It may the case that this problem occurs in Office 10 but not in Office 2003-7

回答8:

JXL is a simple (and hence limited) API. If it says

Unable to recognize OLE stream

it is what it is. It doesn't quite understand your Excel XLS file. Have confidence that the error is legitimate. This API only supports *.xls files; it doesn't support, for example, *.csv or *.xlsx files. Obviously, having the file renamed to *.xls alone is not sufficient. It must be in Excel 97-2003 format too.

• Copy all the cells from your *.csv or *.xlsx file.
• Open MS Excel and paste the copied cells.
• Save the file as MS Excel 97-2003 (*.xls) file.

This error will surely not appear again.

On the other hand, if you want to process other formats (xlsx, csv) directly, look for other tools like Apache POI.