How to copy/paste the cell values that contain only formula?

8 Dec 20233 minutes to read

You can copy and paste the values of the cell which contain only formula using CopyTo method by specifying the ExcelCopyRangeOptions as None. The following code snippet illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Assigning formula to a cell
  worksheet.Range["A3"].Formula = "SUM(1+1)";
  IRange sourceRange = worksheet.Range["A3"];
  IRange destinationRange = worksheet.Range["B1"];

  //Copy and paste the values using ExcelCopyRangeOption
  sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.None);

  FileStream stream = new FileStream("Output.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  workbook.Close();
  excelEngine.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Assigning formula to a cell
  worksheet.Range["A3"].Formula="SUM(1+1)";
  IRange sourceRange = worksheet.Range["A3"];
  IRange destinationRange = worksheet.Range["B1"];

  //Copy and paste the values using ExcelCopyRangeOption
  sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.None);

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Assigning formula to a cell
  worksheet.Range("A3").Formula = "SUM(1+1)"
  Dim sourceRange As IRange = worksheet.Range("A3")
  Dim destinationRange As IRange = worksheet.Range("B1")

  'Copy and paste the values using ExcelCopyRangeOption
  sourceRange.CopyTo(destinationRange, ExcelCopyRangeOptions.None)

  workbook.SaveAs("Output.xlsx")
End Using

See Also