WorksheetCopyOptions.SheetMappings Property

Allows you to specify mappings between worksheet names in the source and destination workbooks.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v23.2.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public Dictionary<string, string> SheetMappings { get; set; }

VB.NET

Public Property SheetMappings As Dictionary(Of String, String)

Property Value

Type	Description
Dictionary<String, String>	A dictionary where keys contain sheet names in the source workbook and values define corresponding sheets in the destination workbook.

Remarks

When you copy a worksheet with formulas to another workbook, the Spreadsheet updates formula references in the copied sheet as follows:

• Absolute and relative references to cells within the source worksheet point to the copied worksheet in the destination workbook.

  

• References to other sheets in the source workbook are converted to external references that include the source workbook name in square brackets.

  

You can use the SheetMappings property to replace external references to the source workbook with references to worksheets within the destination workbook. Configure all sheet mappings before you execute the copy operation. Otherwise, the Spreadsheet cannot resolve all formula references correctly.

The example below demonstrates how to copy all worksheets from one workbook to another. Sheets are copied with their original names. The SheetMappings property is used to update formula references so they point to worksheets in the destination workbook.

C#

using DevExpress.Spreadsheet;
using System.Linq;
// ...

using (Workbook sourceWorkbook = new Workbook())
using (Workbook targetWorkbook = new Workbook())
{
    targetWorkbook.LoadDocument(@"Documents\Book1.xlsx");
    sourceWorkbook.LoadDocument(@"Documents\Book2.xlsx");
    var copyOptions = new WorksheetCopyOptions();
    // Specify mappings between worksheets in the source
    // and destination workbooks. Sheets are copied 
    // with their original names.
    copyOptions.SheetMappings = sourceWorkbook.Sheets.ToDictionary(
        sheet => sheet.Name, sheet => sheet.Name);
    // Copy all worksheets from the source workbook
    // to the target workbook.
    CopySheetsToWorkbook(sourceWorkbook, targetWorkbook, copyOptions);
    targetWorkbook.Calculate();
    targetWorkbook.SaveDocument("MergedDocument.xlsx");
}

// ...

private static void CopySheetsToWorkbook(Workbook sourceWorkbook, 
    Workbook targetWorkbook, WorksheetCopyOptions options)
{
    foreach (var sheet in sourceWorkbook.Worksheets)
    {
        string sheetName = sheet.Name;
        if (!targetWorkbook.Worksheets.Contains(sheetName))
            targetWorkbook.Worksheets.Add(sheetName);
        targetWorkbook.Worksheets[sheetName].CopyFrom(sheet, options);
    }
}

VB.NET

Imports DevExpress.Spreadsheet
Imports System.Linq
' ...

Using sourceWorkbook As New Workbook()
Using targetWorkbook As New Workbook()
    targetWorkbook.LoadDocument("Documents\Book1.xlsx")
    sourceWorkbook.LoadDocument("Documents\Book2.xlsx")
    Dim copyOptions As New WorksheetCopyOptions()
    ' Specify mappings between worksheets in the source
    ' and destination workbooks. Sheets are copied 
    ' with their original names.
    copyOptions.SheetMappings = sourceWorkbook.Sheets.ToDictionary(
      Function(sheet) sheet.Name, Function(sheet) sheet.Name)
    ' Copy all worksheets from the source workbook
    ' to the target workbook.
    CopySheetsToWorkbook(sourceWorkbook, targetWorkbook, copyOptions)
    targetWorkbook.Calculate()
    targetWorkbook.SaveDocument("MergedDocument.xlsx")
End Using
End Using

' ...

Private Sub CopySheetsToWorkbook(sourceWorkbook As Workbook, targetWorkbook As Workbook,
                                 options As WorksheetCopyOptions)
    For Each sheet In sourceWorkbook.Worksheets
        Dim sheetName As String = sheet.Name
        If Not targetWorkbook.Worksheets.Contains(sheetName) Then
            targetWorkbook.Worksheets.Add(sheetName)
        End If
        targetWorkbook.Worksheets(sheetName).CopyFrom(sheet, options)
    Next sheet
End Sub