Exception when reading excel file using apache poi

Asked

Viewed 1,850 times

0

I am trying to read an Excel (XLS) file, using the Apache Poi API, and am taking the Exception:

java.io.IOException: Invalid header signature; read 0x0020000A000DFEFF, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:140)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:104)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.<init>(POIFSFileSystem.java:128)
    at br.com.sfera.utility.LerXLS.lerArquivoXls(LerXLS.java:32)
    at br.com.sfera.utility.LerXLS.main(LerXLS.java:21)

Below is the java method that reads the xls file.

public void lerArquivoXls(String arquivoXls)
    {
        try {
            InputStream input = new BufferedInputStream(new FileInputStream(arquivoXls));

            POIFSFileSystem fileSystem = new POIFSFileSystem(input);
            HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
            HSSFSheet sheet = workbook.getSheetAt(0);

            Iterator linhas = sheet.rowIterator();
            while(linhas.hasNext()){
                HSSFRow linha = (HSSFRow) linhas.next();
                Iterator celulas = linha.cellIterator();
                while(celulas.hasNext()){
                    HSSFCell celula = (HSSFCell) celulas.next();
                    if(HSSFCell.CELL_TYPE_NUMERIC==celula.getCellType()){
                        //celula numerica
                        System.out.println(celula.getNumericCellValue()+" - ");
                    }
                    else if(HSSFCell.CELL_TYPE_STRING==celula.getCellType()){
                        //celula de string
                        System.out.println(celula.getStringCellValue() + " - ");
                    }else if(HSSFCell.CELL_TYPE_BOOLEAN==celula.getCellType()){
                        //celula booleana
                        System.out.println(celula.getBooleanCellValue() + " - ");
                    }else if(HSSFCell.CELL_TYPE_BLANK==celula.getCellType()){
                        //celula em branco
                        System.out.println(" == VAZIO == ");
                    }else{
                        //celula desconhecida
                        System.out.println(" == FORMATO DESCONHECIDO == ");
                    }
                }
            }



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

Can someone help me, how to avoid this problem, or what is the cause of how to avoid this Exception and be able to read the XLS file smoothly?

java exception xls

  • The file opens in excel ? Why the line Your file appears not to be a valid OLE2 document indicates that you are having problems with the header. Try saving again if appropriate or convert to a newer version of excel xlsx back to xls.

  • @Kyllopardiun yes the document opens in Excel, I did the tests and saving in a version of the same . xls (2003), he managed to read the file normally. The problem is that the file that my program will be is auto generated by another program that perform the export from file. With this I cannot ask the user to open and save the file again.

  • 1

    I know it seems unlikely, but you tried to use the XSSFWorkbook() ?

  • @Kyllopardiun then did another method using Xssfworkbook() to read file *.xlsx, and it works beauty. My system needs to read *.xlsx files, . csv and .xls. And so, as these are generated by another system, the problem I found in . xls is that doesn’t seem to be saved in the pattern. When I opened and saved the file again manually, my program read it and processed normally without any error.

  • Did you find a solution? Poste as an answer to help other people.

1 answer

1


Use the below method to select the file. Here it worked:

JFileChooser chooser = new JFileChooser();
        chooser.showDialog(chooser, "Selecionar");
        File arquivo = chooser.getSelectedFile();
        BufferedInputStream buf = new BufferedInputStream(new FileInputStream(arquivo));

Browser other questions tagged java exception xls

You are not signed in. Login or sign up in order to post.