WorksheetCopyOptions.InvalidFormulaReplacementMode Property

Specifies how to replace copied formulas if they contain references to worksheets that do not exist in the destination workbook.

Namespace: DevExpress.Spreadsheet

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

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public FormulaReplacementMode InvalidFormulaReplacementMode { get; set; }

VB.NET

Public Property InvalidFormulaReplacementMode As FormulaReplacementMode

Property Value

Type	Description
FormulaReplacementMode	An enumeration member that specifies how to resolve formulas with references to sheets in the source workbook. Default mode is UseExternalReferences.

Available values:

Name	Description
UseExternalReferences	Converts references to other sheets in the source workbook to external references.
KeepFormulaValuesOnly	Copies only calculated values for formulas with external references.

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.

  

Set the InvalidFormulaReplacementMode property to KeepFormulaValuesOnly to paste only calculated values for formulas with external references.

C#

using DevExpress.Spreadsheet;
// ...

using (Workbook sourceWorkbook = new Workbook())
using (Workbook targetWorkbook = new Workbook())
{
    targetWorkbook.LoadDocument(@"Documents\Book1.xlsx");
    sourceWorkbook.LoadDocument(@"Documents\Book2.xlsx");
    // Add a new worksheet to the destination workbook.
    targetWorkbook.Worksheets.Add("Sheet1_Copy");
    // Specify copy options.
    var copyOptions = new WorksheetCopyOptions()
    {
        InvalidFormulaReplacementMode = FormulaReplacementMode.KeepFormulaValuesOnly
    };
    // Copy data from "Sheet1" in the source workbook
    // to the newly created worksheet in the destination workbook. 
    targetWorkbook.Worksheets["Sheet1_Copy"].CopyFrom(sourceWorkbook.Worksheets["Sheet1"], copyOptions);
    targetWorkbook.SaveDocument("Result.xlsx");
}

VB.NET

Imports DevExpress.Spreadsheet
' ...

Using sourceWorkbook As New Workbook()
Using targetWorkbook As New Workbook()
  targetWorkbook.LoadDocument("Documents\Book1.xlsx")
  sourceWorkbook.LoadDocument("Documents\Book2.xlsx")
  ' Add a new worksheet to the destination workbook.
  targetWorkbook.Worksheets.Add("Sheet1_Copy")
  ' Specify copy options.
  Dim copyOptions As New WorksheetCopyOptions() With {
      .InvalidFormulaReplacementMode = FormulaReplacementMode.KeepFormulaValuesOnly
      }
  ' Copy data from "Sheet1" in the source workbook
  ' to the newly created worksheet in the destination workbook. 
  targetWorkbook.Worksheets("Sheet1_Copy").CopyFrom(sourceWorkbook.Worksheets("Sheet1"), copyOptions)
  targetWorkbook.SaveDocument("Result.xlsx")
End Using
End Using

You can also use the WorksheetCopyOptions.SheetMappings property to replace external references with references to worksheets within the destination workbook. This property allows you to specify mappings between worksheet names in the source and destination workbooks.