Created attachment 37703 [details] example source file Hi guys, In the following example second invocation of Workbook.close() produces file test2.xlsx with two sheets, but the second has no data. Also observed: the first file has size of 32,952 bytes and the second one has 61,619 bytes. int rowNumber = 0; int cellNumber = 0; String outputFileName = "c:/temp/test1.xlsx"; File outFile = new File(outputFileName); File outFile2 = new File("c:/temp/test2.xlsx"); Workbook workbook = new SXSSFWorkbook(100); try { Sheet sheet = workbook.createSheet("Sheet1"); Row row = null; for (int i = 0; i < 1000; i++) { row = sheet.createRow(rowNumber++); cellNumber = 0; for (int j = 1; j <= 10; j++) { Cell cell = row.createCell(cellNumber++); cell.setCellValue(String.valueOf(j)); } } try (FileOutputStream out = new FileOutputStream(outFile)) { workbook.write(out); } } finally { workbook.close(); ((SXSSFWorkbook) workbook).dispose(); } workbook = new SXSSFWorkbook((XSSFWorkbook) WorkbookFactory.create(outFile), 100); try { Sheet sheet = workbook.createSheet("Sheet2"); Row row = null; for (int i = 0; i < 1000; i++) { row = sheet.createRow(rowNumber++); cellNumber = 0; for (int j = 1; j <= 10; j++) { Cell cell = row.createCell(cellNumber++); cell.setCellValue(String.valueOf(j)); } } try (FileOutputStream out = new FileOutputStream(outFile2)) { workbook.write(out); } } finally { workbook.close(); ((SXSSFWorkbook) workbook).dispose(); }
There was wrong example. Please use this one: int rowNumber = 0; int cellNumber = 0; String outputFileName = "c:/temp/test1.xlsx"; File outFile = new File(outputFileName); //File outFile2 = new File("c:/temp/test2.xlsx"); Workbook workbook = new SXSSFWorkbook(100); try { Sheet sheet = workbook.createSheet("Sheet1"); Row row = null; for (int i = 0; i < 1000; i++) { row = sheet.createRow(rowNumber++); cellNumber = 0; for (int j = 1; j <= 10; j++) { Cell cell = row.createCell(cellNumber++); cell.setCellValue(String.valueOf(j)); } } try (FileOutputStream out = new FileOutputStream(outFile)) { workbook.write(out); } } finally { workbook.close(); ((SXSSFWorkbook) workbook).dispose(); } rowNumber = 0; cellNumber = 0; workbook = new SXSSFWorkbook((XSSFWorkbook) WorkbookFactory.create(outFile), 100); try { Sheet sheet = workbook.createSheet("Sheet2"); Row row = null; for (int i = 0; i < 1000; i++) { row = sheet.createRow(rowNumber++); cellNumber = 0; for (int j = 1; j <= 10; j++) { Cell cell = row.createCell(cellNumber++); cell.setCellValue(String.valueOf(j)); } } Files.delete(Paths.get(outFile.getPath())); try (FileOutputStream out = new FileOutputStream(outFile)) { workbook.write(out); } } finally { workbook.close(); ((SXSSFWorkbook) workbook).dispose(); }
It is not intuitive but the current POI behaviour is that if you create an XSSFWorkbook based on a file, that that file is modified when you make changes to the XSSFWorkbook. If you create an SXSSFWorkbook that is based on an XSSFWorkbook, then the modifications to the SXSSFWorkbook affect the XSSFWorkbook and this in turn affects the file. If you want use an xlsx file as a template in SXSSFWorkbook but not modify the original file, then you need to create the XSSFWorkbook using an InputStream. When an InputStream is used, modifications to XSSFWorkbook do not affect the original data source (eg a file).
*** This bug has been marked as a duplicate of bug 58779 ***