Apache POI – FileInputStream works, File object fails (NullPointerException)

Using a File instead of a FileInputStream for opening a Workbook leads to a lower memory footprint because then, in case of XSSF(*.xlsx), the ZipPackage will be opened from the *.xlsx file directly instead reading the whole ZIP content into the memory.

But this also means, that the ZipPackage gets the file opened until the Workbook will be closed. So until the Workbook will be closed, nothing can write to that file the same time. So, since there is not a possibility to write the Workbook content back to the same file from where the Workbook was opened from, using a File instead a FileInputStream for opening a Workbook is fine if you wants only reading from that Workbook then. But it does not work if you wants reading from and writing to the same file. Then FileInputStream and FileOutputStream is needed.

So in your case you tries reading the Workbook newWB from a File and then writing the Workbook into the same file using

fileOut = new FileOutputStream(newWorkbook);
newWB.write(fileOut);

while the file is opened already. This fails.

But:

   fisNew = new FileInputStream(newWorkbook);
   oldWB = WorkbookFactory.create(new File(oldWorkbook));
   newWB = WorkbookFactory.create(fisNew);
...
   fileOut = new FileOutputStream(newWorkbook);
   newWB.write(fileOut);
   fileOut.close();

   oldWB.close();
   newWB.close();

should work.

Btw.: If you are using a File, then you should not using a FileInputStream for the same file. So don’t use fisOld.

Another disadvantage of using a File instead of a FileInputStream for opening a Workbook is that while closing the Workbook and so implicitly closing the underlaying file system (POIFSFileSystem in case of HSSF and ZipPackage in case of XSSF) the file gets an updated last modified date. There are no changings made into the file but the file had been opened and new written into the file system. That’s why the last modified date is updated.

---

Edit Sep 21 2017:
The disadvantage of using a File seems to be greater than thought first. OPCPackage.close also saves all changings into the underlaying OPCPackage. So if you are opening a XSSFWorkbook from a file and then wants writing the changings into another file using write(java.io.OutputStream stream), then the source file will also be changed while closing the OPCPackage. The problem only occurs if write(java.io.OutputStream stream) is used from XSSFWorkbook since then POIXMLDocument.write is called which calls POIXMLDocumentPart.onSave which “Saves changes in the underlying OOXML package.”. So the OPCPackage is updated with all changings before closing.

Short Example:

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;

class ReadAndWriteExcelWorkbook {

 public static void main(String[] args) throws Exception {

  Workbook workbook  = WorkbookFactory.create(new File("file.xlsx"));

  Sheet sheet = workbook.getSheetAt(0);
  Row row = sheet.getRow(0);
  if (row == null) row = sheet.createRow(0);
  Cell cell = row.getCell(0);
  if (cell == null) cell = row.createCell(0);
  cell.setCellValue("changed");

  FileOutputStream out = new FileOutputStream("fileNew.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }
}

After this code both files fileNew.xlsxas well as file.xlsx are changed.