How to: Copy Worksheets

  • 3 minutes to read

Use the Worksheet.CopyFrom method to copy data from one worksheet to another.

Copy Worksheets Within a Workbook

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl1.Document;

// Add a new worksheet to the workbook.
workbook.Worksheets.Add("Sheet1_Copy");

// Copy all information from "Sheet1" 
// to the newly created worksheet.
workbook.Worksheets["Sheet1_Copy"].CopyFrom(workbook.Worksheets["Sheet1"]);

Copy Worksheets Between Workbooks

Important

The example below uses the Workbook class that is defined in the DevExpress.Docs.v21.1.dll assembly. Add this assembly to your project to use the Workbook API. You need a license to the DevExpress Office File API or DevExpress Universal Subscription to use this assembly in production code. Refer to the DevExpress Subscriptions page for pricing information.

// Add a reference to the DevExpress.Docs.dll assembly.
using DevExpress.Spreadsheet;
// ...

IWorkbook targetWorkbook = spreadsheetControl1.Document;
using (Workbook sourceWorkbook = new Workbook())
{
    // Load a document into the source workbook.
    sourceWorkbook.LoadDocument("Document.xlsx");

    // Copy the first worksheet of the source workbook 
    // to the destination workbook.
    targetWorkbook.Worksheets[0].CopyFrom(sourceWorkbook.Worksheets[0]);
}

Specify Copy Options

Pass a WorksheetCopyOptions instance to the Worksheet.CopyFrom method to specify copy options. The following options are available:

Option Description
PasteOptions Specifies the part of data to paste from the copied worksheet into the target worksheet.
InvalidFormulaReplacementMode Specifies how to replace copied formulas if they contain references to worksheets that do not exist in the destination workbook.
SheetMappings Allows you to specify mappings between worksheet names in the source and destination workbooks. Use this property to replace external cell references in formulas with references to sheets in the destination workbook.
OverwriteProtectionOnLockedWorksheet Specifies whether to apply cell protection options of the source worksheet to cells in the protected destination worksheet.

The following example copies all data from one worksheet to another except for cell comments:

using DevExpress.Spreadsheet;
// ...

IWorkbook workbook = spreadsheetControl1.Document;
workbook.LoadDocument("Document.xlsx");
// Add a new worksheet to the destination workbook.
Worksheet targetWorksheet = workbook.Worksheets.Add("Sheet1_Copy");
// Specify copy options.
var copyOptions = new WorksheetCopyOptions()
{
    // Copy all data except for cell comments. 
    PasteOptions = PasteSpecial.All & ~PasteSpecial.Comments
};
// Copy data from "Sheet1" 
// to the newly created worksheet.
targetWorksheet.CopyFrom(workbook.Worksheets["Sheet1"], copyOptions);
See Also