Apache POI appending data to xlsx file when task r

问题:

I have a template.xls file that I'm adding data to from some database queries. I add the data and generate a new file named yyyyMMddHHmmss.xls. This works great. The file size is getting large so I'm trying to do the same with an xlsx file. When I generate the file the first time it works great. If I run the process again (even if I restart my java app) it's somehow retaining the last file in memory and appending the data to that file. In both cases it's pulling the source file from template.xls(x) which is an unmodified file.

The code between the two is identical except I'm passing in xlsx instead of xls in the latter case.

ClassLoader classLoader = getClass().getClassLoader();
File file = new File(Objects.requireNonNull(classLoader.getResource("template.xlsx")).getFile());
Workbook workbook = WorkbookFactory.create(file);
// write data
Date date = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
String currentDate = formatter.format(date);
FileOutputStream fileOutputStream = new FileOutputStream(currentDate + ".xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();

I'm using Java 8u201 and org.apache.poi:poi:4.1.0 (also tried 4.0.1)

回答1:

As told in Apache POI - FileInputStream works, File object fails (NullPointerException) already, creating a XSSFWorkbook from a File has the disadvantage, that all changes which was made in that workbook always will be stored into that file while XSSFWorkbook.write. This is true even if write writes to another file. But writing explicitly to the same file is not even possible because the File stays open after the workbook was created and so writing into that same file leads to exceptions.

So creating a XSSFWorkbook from a File using

Workbook workbook = WorkbookFactory.create(file);

is not a good idea when file is a *.xlsx file. Instead the Workbook needs to be created using a FileInputstream:

Workbook workbook = WorkbookFactory.create(new FileInputStream(file));

Although the linked SO Q/A is from 2017, the same problem always nor occurs today using apache poi 4.1.0.