Copy a Worksheet
There are scenarios in which you may need to copy a specific Worksheet and apply a modification to it. For those cases, in Q1 2016 we introduced API in the Worksheet class allowing you to copy a sheet to the same or another Workbook.
The CopyFrom(Worksheet source) method of a Worksheet will copy the passed source sheet into the one the method is called for. This will clone all content and formatting from the source.
Example 1 illustrates how to copy a specific worksheet from a source Workbook into a new sheet in the desired target workbook.
Example 1: Copy worksheet
Worksheet clonedSheet = target.Worksheets.Add();
clonedSheet.CopyFrom(source.Sheets[0] as Worksheet);
Dim clonedSheet As Worksheet = target.Worksheets.Add()
clonedSheet.CopyFrom(TryCast(source.Sheets(0), Worksheet))
If the sheet that you're copying is in a document where a DocumentTheme has been applied, the theme will not be copied. Themes are information preserved in the Workbook and you may need to transfer them additionally.
Copying a worksheet can be done both in a newly created worksheet and an existing one. If you are copying the content into an existing worksheet, all previously available content in the target will be removed and replaced with the copied one. The sole exception of this is the Name of the sheet which will not be transferred.
Example 2 demonstrates a slightly more complex scenario in which a sheet is copied into an existing workbook. If the workbook contains a worksheet with the same name, the sheet to clone is copied into it. Otherwise, a new worksheet is created and its Name is copied from the source document.
Example 2: Copy to existing workbook
bool containsSameName = false;
foreach (var worksheet in targetWorkbook.Worksheets)
{
if (worksheet.Name == sheetToClone.Name)
{
containsSameName = true;
break;
}
}
if (containsSameName)
{
targetWorkbook.Worksheets[sheetToClone.Name].CopyFrom(sheetToClone);
}
else
{
Worksheet clonedSheet = targetWorkbook.Worksheets.Add();
clonedSheet.CopyFrom(sheetToClone);
clonedSheet.Name = sheetToClone.Name;
}
Dim containsSameName As Boolean = False
For Each worksheet As var In targetWorkbook.Worksheets
If worksheet.Name = sheetToClone.Name Then
containsSameName = True
Exit For
End If
Next
If containsSameName Then
targetWorkbook.Worksheets(sheetToClone.Name).CopyFrom(sheetToClone)
Else
Dim clonedSheet As Worksheet = targetWorkbook.Worksheets.Add()
clonedSheet.CopyFrom(sheetToClone)
clonedSheet.Name = sheetToClone.Name
End If