How to copy a range from one workbook to another?

24 Jan 20238 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