How to: Copy Worksheets

• Jul 08, 2021
• 3 minutes to read

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

Copy Worksheets Within a Workbook

C#

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"]);

VB.NET

Imports DevExpress.Spreadsheet
' ...

Dim workbook As IWorkbook = 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.v24.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.

C#

// 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]);
}

VB.NET

' Add a reference to the DevExpress.Docs.dll assembly.
Imports DevExpress.Spreadsheet
' ...

Dim targetWorkbook As IWorkbook = spreadsheetControl1.Document
Using sourceWorkbook As 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))
End Using

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:

C#

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);

VB.NET

Imports DevExpress.Spreadsheet
' ...

Dim workbook As IWorkbook = spreadsheetControl1.Document
workbook.LoadDocument("Document.xlsx")
' Add a new worksheet to the destination workbook.
Dim targetWorksheet As Worksheet = workbook.Worksheets.Add("Sheet1_Copy")
' Specify copy options.
Dim copyOptions As New WorksheetCopyOptions() With {
    .PasteOptions = PasteSpecial.All And Not PasteSpecial.Comments}
' Copy data from "Sheet1" 
' to the newly created worksheet.
targetWorksheet.CopyFrom(workbook.Worksheets("Sheet1"), copyOptions)