How to copy and paste evaluated values from one range to another using XlsIO?

1 min read

WPF XlsIO doesn’t have any direct function to paste evaluated values from one range to another. But, it can be achieved by exporting a range to a datatable with ExcelExportDataTableOption.ComputedFormulaValues and importing the dataTable to a destination range.

The code snippet to the achieve this is given below.

 
            IWorkbook workbook = application.Workbooks.Open(@"../../Data/Test.xlsx", ExcelOpenType.Automatic);
 
            IWorksheet worksheet = workbook.Worksheets[0];
 
            IRange src1 = worksheet.Range["A3"];
            IRange src2 = worksheet.Range["A4"];
            IRange src3 = worksheet.Range["A5"];
            IRange src4 = worksheet.Range["A6"];
            IRange src5 = worksheet.Range["A7"];
            
            IRange dest1 = worksheet.Range["B3"];
            IRange dest2 = worksheet.Range["B4"];
            IRange dest3 = worksheet.Range["B5"];
            IRange dest4 = worksheet.Range["B6"];
            IRange dest5 = worksheet.Range["B7"];
 
            DataTable dt = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues);
 
            worksheet.ImportDataTable(dt, false,dest1.Row,dest1.Column);

Dim workbook As IWorkbook = application.Workbooks.Open("../../Data/Test.xlsx", ExcelOpenType.Automatic)
 
            Dim worksheet As IWorksheet = workbook.Worksheets(0)
 
            Dim src1 As IRange = worksheet.Range("A3")
            Dim src2 As IRange = worksheet.Range("A4")
            Dim src3 As IRange = worksheet.Range("A5")
            Dim src4 As IRange = worksheet.Range("A6")
            Dim src5 As IRange = worksheet.Range("A7")
 
            Dim dest1 As IRange = worksheet.Range("B3")
            Dim dest2 As IRange = worksheet.Range("B4")
            Dim dest3 As IRange = worksheet.Range("B5")
            Dim dest4 As IRange = worksheet.Range("B6")
            Dim dest5 As IRange = worksheet.Range("B7")
 
            Dim dt As DataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues)
 
            worksheet.ImportDataTable(dt, False, dest1.Row, dest1.Column)

The sample illustrating this behavior can be downloaded here.

Conclusion

I hope you enjoyed learning about how to copy and paste evaluated values from one range to another using XlsIO.

You can refer to our WPF XlsIO feature tour page to know about its other groundbreaking feature representations. You can also explore documentation to understand how to create and manipulate data.

For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.

If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!

Did you find this information helpful?

Yes

Comments (0)

How to copy and paste evaluated values from one range to another using XlsIO?

Access denied