How to copy a range from one workbook to another?

25 May 20234 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;
  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;
  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

See Also