How to copy a range from one workbook to another?

26 May 20229 minutes to read

You can copy the range from source workbook to the destination workbook through CopyTo method.

The following code snippet illustrates this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;

    IWorkbook sourceWorkbook = application.Workbooks.Open("SourceWorkbook.xlsx", ExcelOpenType.Automatic);

    IWorkbook destinationWorkbook = application.Workbooks.Open("DestinationWorkbook.xlsx", ExcelOpenType.Automatic);

    //The first worksheet object in the worksheets collection in the Source Workbook is accessed.
    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];

    //The first worksheet object in the worksheets collection in the Destination Workbook is accessed.
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets[0];

    //Assigning an object to the range of cells (90 rows) both for source and destination.
    IRange sourceRange = sourceWorksheet.Range[1, 1, 90, 100];
    IRange destinationRange = destinationWorksheet.Range[1, 1, 90, 100];

    //Copying (90 rows) from Source to Destination worksheet.
    sourceRange.CopyTo(destinationRange);

    destinationWorkbook.SaveAs("CopyingRange.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2013

    Dim sourceWorkbook As IWorkbook = application.Workbooks.Open("SourceWorkbook.xlsx", ExcelOpenType.Automatic)

    Dim destinationWorkbook As IWorkbook = application.Workbooks.Open("DestinationWorkbook.xlsx", ExcelOpenType.Automatic)

    'The first worksheet object in the worksheets collection in the Source Workbook is accessed.
    Dim sourceWorksheet As Syncfusion.XlsIO.IWorksheet = sourceWorkbook.Worksheets(0)

    'The first worksheet object in the worksheets collection in the Destination Workbook is accessed.
    Dim destinationWorksheet As Syncfusion.XlsIO.IWorksheet = destinationWorkbook.Worksheets(0)

    'Assigning an object to the range of cells (90 rows) both for source and destination.
    Dim sourceRange As Syncfusion.XlsIO.IRange = sourceWorksheet.Range(1, 1, 90, 100)
    Dim destinationRange As Syncfusion.XlsIO.IRange = destinationWorksheet.Range(1, 1, 90, 100)

    'Copying (90 rows) from Source to Destination worksheet.
    sourceRange.CopyTo(destinationRange)

    destinationWorkbook.SaveAs("CopyingRange.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;

    //Instantiates the File Picker to open the Source Workbook
    FileOpenPicker openPicker1 = new FileOpenPicker();
    openPicker1.SuggestedStartLocation = PickerLocationId.Desktop;
    openPicker1.FileTypeFilter.Add(".xlsx");
    openPicker1.FileTypeFilter.Add(".xls");
    StorageFile file1 = await openPicker1.PickSingleFileAsync();

    IWorkbook sourceWorkbook = await application.Workbooks.OpenAsync(file1, ExcelOpenType.Automatic);

    //Instantiates the File Picker to open the Destination Workbook
    FileOpenPicker openPicker2 = new FileOpenPicker();
    openPicker2.SuggestedStartLocation = PickerLocationId.Desktop;
    openPicker2.FileTypeFilter.Add(".xlsx");
    openPicker2.FileTypeFilter.Add(".xls");
    StorageFile file2 = await openPicker2.PickSingleFileAsync();

    IWorkbook destinationWorkbook = await application.Workbooks.OpenAsync(file2, ExcelOpenType.Automatic);

    //The first worksheet object in the worksheets collection in the Source Workbook is accessed.
    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];

    //The first worksheet object in the worksheets collection in the Destination Workbook is accessed.
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets[0];

    //Assigning an object to the range of cells (90 rows) both for source and destination.
    IRange sourceRange = sourceWorksheet.Range[1, 1, 90, 100];
    IRange destinationRange = destinationWorksheet.Range[1, 1, 90, 100];

    //Copying (90 rows) from Source to Destination worksheet.
    sourceRange.CopyTo(destinationRange);

    //Initializes FileSavePicker
    FileSavePicker savePicker = new FileSavePicker();
    savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
    savePicker.SuggestedFileName = "CopyingRange";
    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 destinationWorkbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;

    FileStream inputStream1 = new FileStream("SourceWorkbook.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook sourceWorkbook = application.Workbooks.Open(inputStream1, ExcelOpenType.Automatic);

    FileStream inputStream2 = new FileStream("DestinationWorkbook.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook destinationWorkbook = application.Workbooks.Open(inputStream2, ExcelOpenType.Automatic);

    //The first worksheet object in the worksheets collection in the Source Workbook is accessed.
    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];

    //The first worksheet object in the worksheets collection in the Destination Workbook is accessed.
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets[0];

    //Assigning an object to the range of cells (90 rows) both for source and destination.
    IRange sourceRange = sourceWorksheet.Range[1, 1, 90, 100];
    IRange destinationRange = destinationWorksheet.Range[1, 1, 90, 100];

    //Copying (90 rows) from Source to Destination worksheet.
    sourceRange.CopyTo(destinationRange);

    FileStream stream = new FileStream("CopyingRange.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
    destinationWorkbook.SaveAs(stream);

    destinationWorkbook.Close();
    excelEngine.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2013;

    Assembly assembly = typeof(App).GetTypeInfo().Assembly;
    Stream inputStream1 = assembly.GetManifestResourceStream("GettingStarted.SourceWorkbook.xlsx");
    IWorkbook sourceWorkbook = application.Workbooks.Open(inputStream1);

    Stream inputStream2 = assembly.GetManifestResourceStream("GettingStarted.DestinationWorkbook.xlsx");
    IWorkbook destinationWorkbook = application.Workbooks.Open(inputStream2);

    //The first worksheet object in the worksheets collection in the Source Workbook is accessed.
    IWorksheet sourceWorksheet = sourceWorkbook.Worksheets[0];

    //The first worksheet object in the worksheets collection in the Destination Workbook is accessed.
    IWorksheet destinationWorksheet = destinationWorkbook.Worksheets[0];

    //Assigning an object to the range of cells (90 rows) both for source and destination.
    IRange sourceRange = sourceWorksheet.Range[1, 1, 90, 100];
    IRange destinationRange = destinationWorksheet.Range[1, 1, 90, 100];

    //Copying (90 rows) from Source to Destination worksheet.
    sourceRange.CopyTo(destinationRange);

    MemoryStream stream = new MemoryStream();
    destinationWorkbook.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("CopyingRange.xlsx", "application/msexcel", stream);
}

See Also