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.
To copy a worksheet within a workbook we need to take the following actions:
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:
If you just want to jump straight into the code, feel free to download this solution here.
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:
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:
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.
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:
Below is the snippet necessary to get a worksheet part based on the sheet name:
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:
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:
Clean the view means just remove any view reference in the cloned worksheet.
Fix up the table parts simply means make sure each table has a unique id and name.
Alright, last step is to add a reference to the added worksheet in the main workbook part with the following code:
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: