Copy a Worksheet
In some scenarios you may need to copy a specific worksheet and apply modifications to it. Starting with Q1 2016, the Worksheet class exposes API that allows you to copy a sheet to the same or another Workbook.
The CopyFrom(Worksheet source) method of a worksheet copies the passed source sheet into the one the method is called for. This clones 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);If the sheet that you are copying is in a document where a DocumentTheme has been applied, the theme is not copied. Themes are information preserved in the
Workbookand you may need to transfer them separately.
You can copy a worksheet both into a newly created worksheet and an existing one. If you copy the content into an existing worksheet, all previously available content in the target is removed and replaced with the copied content. The only exception is the Name of the sheet, which is not transferred.
Example 2 demonstrates a 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;
}