NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0
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
Wabbage
Updated on September 29, 2020Comments
-
Wabbage over 3 years
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.