How to copy/paste the cell values that contain only formula?
8 Mar 20226 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);
}