0

I am writing a code for populating a workbook then writing it out to an excel file. However, when I run the code for the second time, it throws the error saying "sheet already exists".

Here is the basic code -

File file = getFileObjectForExistingXLSXTemplate(existingTemplateLocation);
Workbook workbook = null;
workbook = WorkbookFactory.create(file);
populateWorkbookThenWriteOutToExcel(workbook, someOtherDestinationDifferentFromTemplateLocation);
workbook.close();
file.delete();

Please help me in understanding how to clear the workbook from memory when I run the code again after 1st time.

Initially, the existing template only has "Sheet 1". The code writes "Sheet 2" to it. First time, everything runs great. But second time when I run the same code immediately, it says "Sheet 2 already exists".

Dhruv Chadha
  • 1,161
  • 2
  • 11
  • 33
  • 1
    Well the "sheet already exists" is pretty clear, isn't it? Seems your `populateWorkbookThenWriteOutToExcel` tries creating a new sheet in the workbook which has the same name as a sheet which already exists in that workbook. – Axel Richter Feb 10 '21 at 08:23
  • NO NO, the first time it works. The template has sheet 1 already. Then the code adds Sheet 2 to it. Works fine the first time. When I run it again, it says "Sheet 2 already exists". – Dhruv Chadha Feb 10 '21 at 08:34
  • Do opening the template in `Excel` after the first run. I suspect you will find sheet named "Sheet 2" in the template then? – Axel Richter Feb 10 '21 at 08:37
  • I thought the same, but no, template remains untouched. I write the final workbook out to a different location. – Dhruv Chadha Feb 10 '21 at 08:40
  • Basically, the issue seems to be that workbook is not getting erased from memory after first run, and it starts pointing to the same workbook during second run. – Dhruv Chadha Feb 10 '21 at 08:42
  • 2
    Then I cannot help further because I don't know what `getFileObjectForExistingXLSXTemplate` and `populateWorkbookThenWriteOutToExcel` is doing exactly. But see https://stackoverflow.com/questions/55929312/apache-poi-appending-data-to-xlsx-file-when-task-ran-twice/55937539#55937539 and try using `FileInputStream` instead of `File` to create the `Workbook`. – Axel Richter Feb 10 '21 at 08:43
  • Using FileInputStream(file) actually worked, I'm still not sure how. But thanks a lot. – Dhruv Chadha Feb 10 '21 at 10:16

1 Answers1

0

Answer -> Using WorkbookFactory.create(FileInputStream(file)) fixed the problem, not exactly sure how.

Dhruv Chadha
  • 1,161
  • 2
  • 11
  • 33