Bug 57165 - Failed to clone a sheet from an Excel 2010
Summary: Failed to clone a sheet from an Excel 2010
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.11-dev
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Blocks: 55791
  Show dependency tree
Reported: 2014-10-29 17:17 UTC by Philippe Lhoste
Modified: 2016-03-13 21:35 UTC (History)
0 users

The Excel file (31.59 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-10-29 17:17 UTC, Philippe Lhoste
Show bugs (4.32 KB, text/x-java)
2014-10-31 11:30 UTC, Philippe Lhoste

Note You need to log in before you can comment on or make changes to this bug.
Description Philippe Lhoste 2014-10-29 17:17:27 UTC
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...
    for (int sn = sheetNb - 1; sn > 0; 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.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.
Comment 1 Nick Burch 2014-10-29 17:29:06 UTC
Can you create a small junit unit test which triggers the problem? We can then use that to test a fix, and to ensure it stays fixed going forward?
Comment 2 Philippe Lhoste 2014-10-31 11:30:21 UTC
Created attachment 32174 [details]
Show bugs
Comment 3 Philippe Lhoste 2014-10-31 11:32:46 UTC
Not really a JUnit test, but it is a standalone class that loads the sample Excel file and reproduce the bugs (three bugs are shown, I numbered them with the Bugzilla id).
You might need to adjust the path to the sample.
Comment 4 Dominik Stadler 2014-12-22 09:16:19 UTC
FYI, there is now a test in class org.apache.poi.xssf.usermodel.TestUnfixedBugs which reproduces this, a fix is still pending, though...
Comment 5 Dominik Stadler 2015-03-01 20:02:15 UTC
This exception is now avoided when cloning/creating sheets via r1663153 as we now look at the existing parts and increase the internal sheet index accordingly if necessary.