NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0

Question

What I want to do is ask the user if they want to create a new or select an existing Excel workbook. Selecting an existing file is no problem. However I get an error saying "Your file appears not to be a valid OLE2 document" as soon as I create a name for a new Excel file.

public void selectExcelFile() {
    String excelFileName = null;                                // the name/directory/address of the excel file created/selected
    FileInputStream excelFileIn = null;                         // allows us to connect to the Excel file so we can read it
    FileOutputStream excelFileOut = null;                       // allows us to connect to the Excel file so we can write to it
    ExcelFileUtility eUtil = new ExcelFileUtility();            // used open an excel file

    if(columnsQuery != null) {
        try {
            excelFileName = eUtil.getFile(FileExtensions.XLS); // file extension = ".xls"

            if(excelFileName != null) {
                excelFileIn = new FileInputStream(new File(excelFileName));
                workbook = new HSSFWorkbook(excelFileIn);

                exportColsToWorkbook(columnsQuery);

                excelFileOut = new FileOutputStream(excelFileName);
                workbook.write(excelFileOut);

                // close everything
                workbook.close();
                excelFileIn.close();
                excelFileOut.close();
            }
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

And then:

public String getFile(String extension) {
    String result = null;

    if(extension != null) {
        int choice = askIfNewFile();

        if(choice == 0) { // yes, create new file
            result = createFile(extension);
        } 

        else { // no, select existing file
            result = getFileLocation();                 
        }
    }
    else {
        System.out.println("No file extension.");
    }
    return result;

}

public String createFile(String extension) throws IOException {
    String newFileName = "";
    File newFile = null;
    boolean isCreated = false;

    JFrame frame = new JFrame("Creating a New ." + extension + " File");
    String result = null;

    String dir = getFileDirectory();
    System.out.println("DIR: " + dir);

    if(dir != null) {
        while(newFileName.isEmpty() || newFileName == null) {
            // Used WorkbookUtil.createSafeSheetName to validate file name
            // Please replace if there is a better option
            newFileName = WorkbookUtil.createSafeSheetName(JOptionPane.showInputDialog(frame, "Enter new ." + extension + " file name:"));
        }

        newFile = new File(dir + "\\" + newFileName + "." + extension);
        System.out.println(newFile.toString());

        try {
            isCreated = newFile.createNewFile();

            if(isCreated) {
                result = newFile.getAbsolutePath();
            }
            else {
                System.out.println("File already exists.");
            }
        }
        catch(IOException ioe) {
            System.out.println(ioe);
        }
    }
    return result;
}

public String getFileLocation() {
    String result = null;
    JFileChooser pickFile = new JFileChooser();

    if (pickFile.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {
        try {
            result = pickFile.getSelectedFile().getCanonicalPath();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        // check if file exists
    }
    System.out.println("File location: " + result);

    return result;
}

public String getFileDirectory() {
    String result = null;

    JFileChooser pickFile = new JFileChooser();

    pickFile.setDialogTitle("Choose Folder");
    pickFile.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
    pickFile.setAcceptAllFileFilterUsed(false);

    if (pickFile.showOpenDialog(null) == JFileChooser.APPROVE_OPTION) {
        result = pickFile.getSelectedFile().toString();
    }
    else {
        System.out.println("No Selection ");
    }

    return result;
}

Here is the error I get:

org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:162)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:112)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:300)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:400)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:381)
at mhhls.him.dbtoexcel.program.DBtoExcel.selectExcelFile(DBtoExcel.java:159)
at mhhls.him.dbtoexcel.program.DBtoExcel.exportToExcel(DBtoExcel.java:422)
at mhhls.him.dbtoexcel.ui.main.DBtoExcelWindow$7.actionPerformed(DBtoExcelWindow.java:183)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

EDITED: I changed the selectExcelFile() method into this:

public void selectExcelFile() {
    String excelFileName = null;                                // the name/directory/address of the excel file created/selected
    FileInputStream excelFileIn = null;                         // allows us to connect to the Excel file so we can read it
    FileOutputStream excelFileOut = null;                       // allows us to connect to the Excel file so we can write to it
    ExcelFileUtility eUtil = new ExcelFileUtility();            // used open an excel file
    File newFile = null;

    if(columnsQuery != null) {
        try {
            excelFileName = eUtil.getFile(FileExtensions.XLS);

            if(excelFileName != null) {

                newFile = new File(excelFileName);

                if(newFile.exists()) {
                    try {
                        workbook =  WorkbookFactory.create(newFile);
                    } catch (EncryptedDocumentException | InvalidFormatException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                else {
                   if (newFile.getName().endsWith(".xls")) {
                       workbook = new HSSFWorkbook();
                   }
                   else if (newFile.getName().endsWith(".xlsx")) {
                       workbook = new XSSFWorkbook();
                   }
                   else {
                       throw new IllegalArgumentException("Must be .xls or .xlsx");
                   }
                }

                excelFileIn = new FileInputStream(newFile);
                exportColsToWorkbook(columnsQuery);

                excelFileOut = new FileOutputStream(newFile);
                workbook.write(excelFileOut);

                // close everything
                workbook.close();
                excelFileIn.close();
                excelFileOut.close();
            }
        }
        catch (IOException e) {
            e.printStackTrace();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

Writing the workbook on the Excel file worksheet supposedly worked (did not get any errors), but when I opened the Excel file to check, I suddenly get this:

I click ok, and it's just empty. No worksheets or workbook.

So that explains why I get this error the next time I run the program and try to write the workbook on the same Excel file.

Exception in thread "AWT-EventQueue-0" org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:216)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:166)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:278)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:250)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:229)
at mhhls.him.dbtoexcel.program.DBtoExcel.selectExcelFile(DBtoExcel.java:205)
at mhhls.him.dbtoexcel.program.DBtoExcel.exportToExcel(DBtoExcel.java:487)
at mhhls.him.dbtoexcel.ui.main.DBtoExcelWindow$7.actionPerformed(DBtoExcelWindow.java:190)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

I just don't understand why it corrupts the Excel file whenever I try to write the workbook onto a new worksheet.

Author

Answer 1

You seem to have missed some key lines of code. However, assuming you are currently doing something like:

File newFile = new File("output.xlsx");
if (!newFile.exists) { newFile.createNewFile(); }
Workbook wb = WorkbookFactory.create(newFile);

Then this will not work!

You may only use WorkbookFactory to load pre-existing Excel files. You cannot use WorkbookFactory to create a brand new, empty Workbook. Very much related, you also can't do new HSSFWorkbook(emptyStream) or new HSSFWorkbook(emptyFile), if creating a *SSFWorkbook from an InputStream or a File then that must exist and be populated.

Instead, if you want to create a brand new empty workbook, what you need to do is more like:

Workbook wb;
File newFile = new File("output.xlsx");
if (newFile.exists) { 
   // Load existing
   wb = WorkbookFactory.create(newFile);
} else {
   // What kind of file are they trying to ask for?
   // Add additional supported types here
   if (newFile.getName().endsWith(".xls")) {
      wb = new HSSFWorkbook();
   }
   else if (newFile.getName().endsWith(".xlsx")) {
      wb = new XSSFWorkbook();
   }
   else {
      throw new IllegalArgumentException("I don't know how to create that kind of new file");
   }
}

For brand new empty files, you need to decide what kind of file to create, then new the appropriate *SSFWorkbook instance for it with no stream/file passed in

Peking

I'm not using the WorkbookFactory. I have edited it to add the missing code. – Author

Same thing applies - if you give a file or a stream then that must exist and have the content in it – Peking

"Invalid header signature; read 0x0000000000000000" - does this mean that the file does not exist? – Author

Most commonly triggered by an empty, zero byte file – Peking

If you use a new enough version of Apache POI (eg 3.14), you'll now even get a more helpful exception in the case of a zero byte file – Peking

I'm using 3.13 right now. Please check the updates I've made on the post. I'm getting new errors again. – Author

If you're creating a new file, you must add at least 1 sheet before saving it to be a valid Excel file. You also need to check for 0-byte files - a file could exist but be empty, and that isn't valid for reading in – Peking

Answer 2

I my case, I uploaded a csv file instead of xls file which caused this exception.

Halsted

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Afterburner