How to copy a range from one workbook to another?

8 Dec 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