Bug 65077 - Workbook.close() clears the second (the last) sheet data in previously saved file
Summary: Workbook.close() clears the second (the last) sheet data in previously saved ...
Status: RESOLVED DUPLICATE of bug 58779
Alias: None
Product: POI
Classification: Unclassified
Component: SXSSF (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-01-13 11:20 UTC by Andrey Grigoriev
Modified: 2021-10-13 14:10 UTC (History)
0 users



Attachments
example source file (1.92 KB, text/plain)
2021-01-13 11:20 UTC, Andrey Grigoriev
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andrey Grigoriev 2021-01-13 11:20:32 UTC
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();
      }
Comment 1 Andrey Grigoriev 2021-01-13 13:35:28 UTC
 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();
      }
Comment 2 PJ Fanning 2021-10-13 09:52:30 UTC
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).
Comment 3 PJ Fanning 2021-10-13 14:10:37 UTC

*** This bug has been marked as a duplicate of bug 58779 ***