Bug 57165

Created attachment 32165 [details]
The Excel file

I created an Excel 2010 file (XLS first, then saved to XLSX format).
Later, I re-ordered the tabs.

I have code that copies this file, opens it, removes all sheets except the selected one, then clone this sheet n times before filling them.
Ie. the document is a template with several models.

I found out that in some cases, the clone operation fails.

Note: I have to use a convoluted method to delete the sheets, because of the bug #57163.

My method to isolate a sheet:

  void removeAllSheetsBut(int sheetIndex, Workbook wb)
  {
    int sheetNb = wb.getNumberOfSheets();
    // Move this sheet at the first position
    wb.setSheetOrder(wb.getSheetName(sheetIndex), 0);
    // Must make this sheet active (otherwise, for XLSX, Excel might protest that active sheet no longer exists)
    // I think POI should automatically handle this case when deleting sheets...
    wb.setActiveSheet(0);
    for (int sn = sheetNb - 1; sn > 0; sn--)
    {
      wb.removeSheetAt(sn);
    }
  }

(I will open a new bug about the comment, once I will be able to reproduce it.)

Then I just do:

Workbook wb = readWorkbook(fileName);
removeAllSheetsBut(3, wb);
wb.cloneSheet(0);
wb.setSheetName(1, "New Sheet");
saveWorkbook(wb, fileName + " Updated");

On the cloneSheet line, I get an exception:

Exception in thread "main" org.apache.poi.openxml4j.exceptions.PartAlreadyExistsException: A part with the name '/xl/worksheets/sheet4.xml' already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12]
	at org.apache.poi.openxml4j.opc.OPCPackage.createPart(OPCPackage.java:785)
	at org.apache.poi.openxml4j.opc.OPCPackage.createPart(OPCPackage.java:749)
	at org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:374)
	at org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:358)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:728)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.cloneSheet(XSSFWorkbook.java:503)
	at org.apache.poi.xssf.usermodel.XSSFWorkbook.cloneSheet(XSSFWorkbook.java:102)

From my investigation, I would say the culprit is the line:

for(XSSFSheet sh : sheets) sheetNumber = (int)Math.max(sh.sheet.getSheetId() + 1, sheetNumber);

in XSSFSheet createSheet(String sheetname) (in XSSFWorkbook).

This sheetNumber is used to name the sheet<n>.xml file, but apparently, after moving around the tabs (I suppose) in Excel, they are no longer in synch, hence the conflict.