Java - Apache POI - Read/Write .xlsx file - File getting corrupted and becomes empty

Question

I am using a Java program to read and write an existing .xlsx file(same file) but the file is getting corrupted, and the file size becomes zero bytes which is causing "org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)”.

One more thing is - this is not happening consistently. The program is reading and writing to file properly most of the time but occurring once out of 10-15 runs. It would be helpful if anyone has a solution to this. BTW, am using Apache POI 3.13.

Read File Program:

public String getExcelData(String sheetName, int rowNum, int colNum){
    String retVal = null;
    try {
        FileInputStream fis = new FileInputStream("/Absolute/File/Path/Test-File.xlsx");
        Workbook wb = WorkbookFactory.create(fis);
        Sheet s = wb.getSheet(sheetName);
        Row r = s.getRow(rowNum);
        Cell c = r.getCell(colNum);
        retVal=(c.getStringCellValue());
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return retVal;

Write File Program :

public void writeToExcel(String sheetName,int rowNum,int cellNum,String desc){
    try {
        FileInputStream fis = new FileInputStream("/Absolute/File/Path/Test-File.xlsx");
        Workbook wb = WorkbookFactory.create(fis);
        Sheet s = wb.getSheet(sheetName);
        Row r = s.getRow(rowNum);
        Cell c = r.createCell(cellNum);
        c.setCellValue(desc);
        FileOutputStream fos = new FileOutputStream("/Absolute/File/Path/Test-File.xlsx");
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

Error Trace:

Exception in thread "main" org.apache.poi.EmptyFileException: The supplied file was empty (zero bytes long)
at org.apache.poi.util.IOUtils.peekFirst8Bytes(IOUtils.java:55)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:201)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:168)
at ExcelLibrary.getExcelData(ExcelLibrary.java:139)
at Driver.main(Driver.java:82)

As far as I see your `public String getExcelData(String sheetName, int rowNum, int colNum)` is able to get *one* cells value from the given sheet, row and column. For this small task every time it creates a new Workbook from the stored file. Also the `public void writeToExcel(String sheetName,int rowNum,int cellNum,String desc)` every time creates a new Workbook from the stored file, writes *one* cells value into the given sheet, row and column and saves the whole Workbook. Why not having the Workbook open outside this methods and saving it only once if all cell values are set? — Axel Richter, Dec 17 '15 at 09:57
Visit http://stackoverflow.com/a/12263649/2734784 for finding out how to close the xlsx properly — user2734784, Nov 21 '16 at 07:03

score 4 · Answer 1 · answered Dec 14 '15 at 10:31

4

You are reading and writing to the same file at the same time. Try to first close FileInputStream fis before writing to FileOutputStream fos. Or use temporary file to write new result and then rename it to original one.

BTW. close automatically performs flush, so it don't has to be called separately.

answered Dec 14 '15 at 10:31

TouDick

1,262
12
18

I have closed FileInputStream fis before writing to FileOutputStream fos and excel was fine for almost 10 runs but later again same problem occurred and file became empty. Please let me know if any other solution to this problem, I am using OSX 10.9.5 with Microsoft Excel mac 2011. – chivas_hvn Dec 16 '15 at 10:39

score 3 · Answer 2 · answered Dec 14 '15 at 10:25

3

you need to close the FileInputStream and FileOutputStream with org.apache.commons.io.IOUtils.closeQuietly(fos) and fis.

answered Dec 14 '15 at 10:25

PhstKv

37
4

score 0 · Answer 3 · answered Dec 14 '15 at 10:24

0

When you write to your file, you seem not to be using flush. Furthermore, your close code should be done in a finally block to ensure the stream is closed even if something wrong happens.

answered Dec 14 '15 at 10:24

Valentin Rocher

11,667
45
59

score 0 · Answer 4 · answered Dec 14 '15 at 10:37

0

As a java dev, you need to use finally block when you use try-catch block. in your finally block, you must close FileInputStream and FileOutputStream.may be filehandler opened as you didn't close.

answered Dec 14 '15 at 10:37

Ataur Rahman Munna

3,887
1
23
34

score 0 · Answer 5 · answered Sep 13 '21 at 05:06

I faced this issue initially, solution is simple.

I am writting only code line which u need to add fis.close(); wb.close(); before returning retval

public String getExcelData(String sheetName, int rowNum, int colNum){
String retVal = null;
try {
    FileInputStream fis = new FileInputStream("/Absolute/File/Path/Test-File.xlsx");
    Workbook wb = WorkbookFactory.create(fis);
    Sheet s = wb.getSheet(sheetName);
    Row r = s.getRow(rowNum);
    Cell c = r.getCell(colNum);
    retVal=(c.getStringCellValue());
    } catch (FileNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    } catch (InvalidFormatException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    } catch (IOException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
fis.close();
wb.close();
return retVal;    
}

score 0 · Answer 6 · answered Aug 03 '22 at 13:36

0

Try this one out. I was facing the same issue but resolved it with below.

FileInputStream fis=new FileInputStream(-Location-);
--- do whatever you want to do --
fis.close();
FileOutputStream fos=new FileOutputStream(-Location-);
wb.write(fos);
fos.close();
wb.close();

answered Aug 03 '22 at 13:36

Subhaneil Sengupta

3
1

1

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](/help/how-to-answer). – Community Aug 05 '22 at 22:04

Java - Apache POI - Read/Write .xlsx file - File getting corrupted and becomes empty

6 Answers6