OpenXML: How to copy a worksheet to another workbook?

I need to merge the worksheets of some workbooks into one new workbook. What I tried is this, but I am getting "Cannot insert the OpenXmlElement "newChild" because it is part of a tree.".

using (var document = SpreadsheetDocument.Create(destinationFileName, SpreadsheetDocumentType.Workbook))
{
    // Add a WorkbookPart to the document
    var workbookPart = document.AddWorkbookPart();
    workbookPart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart
    var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());

    foreach (var sourceFileName in sourceFileNames)
    {
        using (var sourceDocument = SpreadsheetDocument.Open(sourceFileName, false))
        {
            var sheet = (Sheet)sourceDocument.WorkbookPart.Workbook.Sheets.FirstChild;
            workbookPart.Workbook.AppendChild(new Worksheet(sheet));
        }
    }
}

Solution

The error message means that you are trying to add an OpenXmlElement (a Sheet in this case) that already has a parent OpenXmlCompositeElement (a Sheets object in this case) as a new child to another OpenXmlCompositeElement (a Worksheet in this case).

In the following line item, you are grabbing a reference to a Sheet object that already has a parent Sheets object.

var sheet = (Sheet)sourceDocument.WorkbookPart.Workbook.Sheets.FirstChild;

With new Worksheet(sheet), you are trying to add that same sheet to another parent, i.e., the Worksheet instance that you are creating. That does not work.

Assuming for a moment that it makes sense to add a Sheet to a Worksheet, your second line would have to be rewritten as follows:

workbookPart.Workbook.AppendChild(new Worksheet(sheet.CloneNode(true)));

In the above line of code, sheet is replaced with sheet.CloneNode(true), which makes a deep copy of your original Sheet object that does not have a parent. Thus, the clone can be added as a new child to another parent.

While the above solution is an answer to your immediate question (because it avoids the error message), your code does not make sense, because it does not create valid Open XML markup. Worksheet instances should not be added to Workbook instances, and Sheet instances should not be added to Worksheet instances. This is not how you would copy multiple worksheets, which is a very complicated task that requires you to create multiple worksheet parts, link those worksheet parts to your workbook part, and consider shared strings, styles, and other things.