Skip to main content
Row

WorksheetCopyOptions.SheetMappings Property

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Spreadsheet.v24.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

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

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.

    Update references within the target worksheet

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

    Replace references within the target worksheet

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.

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);
    }
}
See Also