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

3 Aug 20216 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);

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

    //Initializes FileSavePicker
    FileSavePicker savePicker = new FileSavePicker();
    savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
    savePicker.SuggestedFileName = "Output";
    savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

    //Creates a storage file from FileSavePicker
    StorageFile storageFile = await savePicker.PickSaveFileAsync();

    //Saves changes to the specified storage file
    await workbook.SaveAsAsync(storageFile);
}
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);

    MemoryStream stream = new MemoryStream();
    workbook.SaveAs(stream);

    stream.Position = 0;

    //Save the stream as a file in the device and invoke it for viewing
    Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Output.xlsx", "application/msexcel", stream);
}

See Also