Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

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.

[C#] Example 1: Copy worksheet

Worksheet clonedSheet = target.Worksheets.Add();
clonedSheet.CopyFrom(source.Sheets[0] as Worksheet);

[VB.NET] Example 1: Copy 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.

[C#] 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;
}

[VB.NET] Example 2: Copy to existing workbook

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

See Also