How to Copy a Worksheet within a Workbook

  • [アーティクル]

As promised in my previous post about reusable methods for manipulating WordprocessingML, I mentioned I would write a post on how to copy a worksheet within a workbook. Note that this blog post talks about copying a worksheet within the same package. Perhaps sometime in the future I will write a post on how to export/import worksheets across different workbooks.

Solution

To copy a worksheet within a workbook we need to take the following actions:

  1. Open up the Spreadsheet document via the Open XML SDK
  2. Access the main workbook part, which will give us access to a bunch of related parts, like the different worksheets
  3. Access the worksheet we want to copy
  4. Clone the found worksheet plus all related parts and add the clone plus all related parts back to the workbook
  5. Perform cleanup work to ensure that tables, views, etc. work
  6. Add the newly created worksheet reference to the sheets list in the main workbook part
  7. Save changes made to the workbook

My post will talk about using version 2 of the SDK.

For the sake of this post, let's say I am starting with a pretty complex workbook, which contains data, conditional formatting, a shape, an image, a table, a SmartArt, and a chart. The workbook contains three worksheets and looks like the following:

Screenshot of sample Excel workbook

If you just want to jump straight into the code, feel free to download this solution here.

AddPart<T>() vs. AddNewPart<T>()

Before we get into the details of the steps listed above, I wanted to take this opportunity to discuss the difference between two methods that the SDK provides for adding parts to an Open XML package. The AddNewPart method does the following:

  1. It creates an empty part of type T and adds it to the package
  2. Once the part is created it then adds a reference from the referencing part to the new part

The next step after adding a new part via this method is usually calling FeedData() to stream in data into the part.

The AddPart method does the following:

  1. If the added part is not already in the package, it will add that part plus all of its related parts to the package. So if you are adding part A and part A references part B, which in turns references part C, then calling this method will add part A, part B, and part C. In addition it will ensure that each of these added parts will maintain their relationships. You can think of this functionality as something similar to a deep clone import
  2. If the added part is already in the package, it will add a reference from the referencing part to the part already in the package. For example, let's say you have part A and part B in the package, but part A does not reference part B. Calling this method will then add a reference from part A to part B if the Open XML format supports such a reference

As you can see AddPart is a lot more powerful than simply calling AddNewPart. This fact will be useful when I show you how to clone a part within a package.

The Code

As described in the solution section above, the first three steps require us to open the workbook and get access to the worksheet we want to copy. Below are the code snippets necessary to accomplish those tasks:

static void CopySheet(string filename, string sheetName, string clonedSheetName) { //Open workbook using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = mySpreadsheet.WorkbookPart; //Get the source sheet to be copied WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName); ... } }

Below is the snippet necessary to get a worksheet part based on the sheet name:

static WorksheetPart GetWorkSheetPart(WorkbookPart workbookPart, string sheetName) { //Get the relationship id of the sheetname string relId = workbookPart.Workbook.Descendants<Sheet>() .Where(s => s.Name.Value.Equals(sheetName)) .First() .Id; return (WorksheetPart)workbookPart.GetPartById(relId); }

Now that we have access to the worksheet part we want to copy, we need to perform our clone task. Well, here is where I am going to take advantage of our AddPart functionality. Perhaps in a future build of our SDK we will actually have a clone method for parts. As mentioned above, AddPart is great at adding a part plus all referenced parts. Unfortunately, this functionality only works when adding a part that does not already exist in a package. Well, to work around this issue we can simply call AddPart to a temporary workbook and then call AddPart again back into the main workbook. The following code accomplishes this task:

static void CopySheet(string filename, string sheetName, string clonedSheetName) { ... //Take advantage of AddPart for deep cloning SpreadsheetDocument tempSheet = SpreadsheetDocument.Create(new MemoryStream(), mySpreadsheet.DocumentType); WorkbookPart tempWorkbookPart = tempSheet.AddWorkbookPart(); WorksheetPart tempWorksheetPart = tempWorkbookPart.AddPart<WorksheetPart>(sourceSheetPart); //Add cloned sheet and all associated parts to workbook WorksheetPart clonedSheet = workbookPart.AddPart<WorksheetPart>(tempWorksheetPart); ... }

At this point in time, we have successfully cloned the worksheet and added it plus all related parts into the workbook. We are almost done...

The next thing we need to do is perform a couple of cleanup tasks. For example, SpreadsheetML requires that every table has a unique name and id. In addition, there really should be only one worksheet that is set as the main view. The following code shows you how to clean these issues up:

static void CopySheet(string filename, string sheetName, string clonedSheetName) { ... //Table definition parts are somewhat special and need unique ids...so let's make an id based on count int numTableDefParts = sourceSheetPart.GetPartsCountOfType<TableDefinitionPart>(); tableId = numTableDefParts; //Clean up table definition parts (tables need unique ids) if (numTableDefParts != 0) FixupTableParts(clonedSheet, numTableDefParts); //There should only be one sheet that has focus CleanView(clonedSheet); ... }

Clean the view means just remove any view reference in the cloned worksheet.

static void CleanView(WorksheetPart worksheetPart) { //There can only be one sheet that has focus SheetViews views = worksheetPart.Worksheet.GetFirstChild<SheetViews>(); if (views != null) { views.Remove(); worksheetPart.Worksheet.Save(); } }

Fix up the table parts simply means make sure each table has a unique id and name.

static void FixupTableParts(WorksheetPart worksheetPart, int numTableDefParts) { //Every table needs a unique id and name foreach (TableDefinitionPart tableDefPart in worksheetPart.TableDefinitionParts) { tableId++; tableDefPart.Table.Id = (uint)tableId; tableDefPart.Table.DisplayName = "CopiedTable" + tableId; tableDefPart.Table.Name = "CopiedTable" + tableId; tableDefPart.Table.Save(); } }

Alright, last step is to add a reference to the added worksheet in the main workbook part with the following code:

static void CopySheet(string filename, string sheetName, string clonedSheetName) { ... //Add new sheet to main workbook part Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); Sheet copiedSheet = new Sheet(); copiedSheet.Name = clonedSheetName; copiedSheet.Id = workbookPart.GetIdOfPart(clonedSheet); copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1; sheets.Append(copiedSheet); //Save Changes workbookPart.Workbook.Save(); ... }

End Result

Putting everything together and running my code, we end up with a workbook that has four sheets, where the last sheet in the workbook, called CopiedData, is an exact replica of the first sheet.

Here is a screenshot of the final workbook:

Screenshot of Excel workbook after running my code

Zeyad Rajabi

Comments

  • Anonymous
    February 24, 2009
    In addition to posting my own content, I will from time to time post links to the great new Open XML

  • Anonymous
    February 26, 2009
    Thanks. Great explanation. AddPart and addNewPart dissection was very helpful for Excel but, I'm encountering problems to clone a powerpoint slide. I'm based on:        Using ppt As Packaging.PresentationDocument = Packaging.PresentationDocument.Open(output, True)            Dim pptPart As Packaging.PresentationPart = ppt.PresentationPart            Dim sourceSlideId As DocumentFormat.OpenXml.Presentation.SlideId = pptPart.Presentation.SlideIdList.ChildElements(0)            Dim sourceSlide As Packaging.SlidePart = pptPart.GetPartById(sourceSlideId.RelationshipId)            Dim tempPPT As Packaging.PresentationDocument = Packaging.PresentationDocument.Create(New MemoryStream(), ppt.DocumentType)            Dim tempPptPart As Packaging.PresentationPart = tempPPT.AddPresentationPart()            Dim tempSlidePart As Packaging.SlidePart = tempPptPart.AddPart(Of Packaging.SlidePart)(sourceSlide)            Dim newSlidePart As Packaging.SlidePart = pptPart.AddPart(tempSlidePart)            Dim slides As Presentation.SlideIdList = ppt.PresentationPart.Presentation.GetFirstChild(Of Presentation.SlideIdList)()            Dim newid As New Presentation.SlideId '= pptPart.Presentation.SlideIdList.InsertAfter(New Presentation.SlideId(), pptPart.Presentation.SlideIdList.ElementAt(0)) 'sldTemppartID) ' prevSlideId)            newid.RelationshipId = pptPart.GetIdOfPart(newSlidePart)            pptPart.Presentation.Save()        End Using Thanks in advance

  • Anonymous
    February 26, 2009
    Tomas, Stay tuned for my next post, which should be up today once my ftp server is up, that will show you how to merge and clone slides. If you are looking to clone a slide within the same presentation then check out the following post: http://blogs.msdn.com/brian_jones/archive/2008/11/18/creating-a-presentation-report-based-on-data.aspx Thanks, Zeyad Rajabi

  • Anonymous
    March 03, 2009
    hey Brian, a very nice blog. I am creating an addin for excel using c# .net. I am done with my extensibility project. now I want to generate an MSI for my addin. Could you kindly guide me how to go about achieving this? Regards, nayan

  • Anonymous
    March 05, 2009
    I just want to let you guys know we are working on some server issues here, which is why some of the