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