Create a new .xls file if it doesn't exist and if it does, then modify it [Apache POI]

Question

I am using Apache POI to create and modify excel file(s). I want my program to create a new .xls file with a given name if it doesn't exist in the directory. However, if it exists, I want my program to just open that .xls file and modify it.

My program is able to create a new .xls file but whenever I try to open the file as an HSSFWorkbook, Program spits out an error saying that "Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document"

The code that I am using to create the new file is :

         File excelFile = new File("Test.xls");
         excelFile.createNewFile();
         wb = new HSSFWorkbook(new FileInputStream(excelFile.getName())); // Throws the error right here [wb is an HSSFWorkbook object from the Apache POI Library]

Note that if a .xls file with that name already exists in the directory and I use just a FileOutputStream object, it successfully writes all the required data in the file.

Code that I am using to write in the excel file is :

        FileOutputStream fileout = new FileOutputStream("Test.xls", true);
        wb.write(fileout);
        fileout.close();
        wb.close(); // wb is an HSSFWorkbook object from the Apache POI Library

The error message with the complete stack-trace is :

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.(HeaderBlock.java:162) at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:112) at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.(NPOIFSFileSystem.java:302) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:393) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:374) at prototpye_2.FrameJScraperTool.processWorkbook(FrameJScraperTool.java:224) at prototpye_2.FrameJScraperTool.run(FrameJScraperTool.java:54) at prototpye_2.JFrameTest$Open.actionPerformed(JFrameTest.java:123) 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.AbstractButton.doClick(Unknown Source) at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source) at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source) at java.awt.AWTEventMulticaster.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)

Also, when this error is generated, there is till an .xls file that is created in the directory but it is 0 KB in size and if we try to open the file, it complains about the file being corrupted. The following prompt comes up in excel :

Prompt That comes up in excel when you try to open the generated .xls:

Let me know if there is a better way to achieve what I am trying to do i.e. If an .xls file doesn't exist, create it and then write into it but, if it does exist, just access it and write the data in it.

Any kind of help is appreciated

Thanks

I will try that as well. But isn't the createNewFile() function supposed to check whether the file exists in the directory before it creates it? @assylias — Taran Rai

FuSsA FuSsA · Accepted Answer · 2016-10-05T17:07:01

Create New Excel .xls File:

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//..
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("FuSsA sheet");
//Create a new row in current sheet
Row row = sheet.createRow(0);
//Create a new cell in current row
Cell cell = row.createCell(0);
//Set value to new value
cell.setCellValue("Slim Shady");
    try {
        FileOutputStream out = 
                new FileOutputStream(new File("C:\\new.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Excel written successfully..");

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

Update Existing Excel File (.xls or .xlsx)

try {
            File file = new File"C:\\update.xls");
            Workbook workbook = WorkbookFactory.create(file);
            Sheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Update the value of cell
            cell = sheet.getRow(1).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(2).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(3).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
            workbook.write(outFile);
            outFile.close();

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

for more details check this cquestion : Read / Write different Microsoft Office file formats using Apache POI

Create a new .xls file if it doesn't exist and if it does, then modify it [Apache POI]

1 Answers