C#/VB.NET – Copy Cell Ranges from One Workbook to Another

When you’re copying data from a workbook to another, you may not always need to copy the entire worksheet. Sometimes, you may only need to copy a selected cell ranges that contain the crucial information you want to reference in another workbook. In this article, I am going to show you how to copy cell ranges between different workbooks in C# and VB.NET using Spire.XLS for .NET.

INSTALL SPIRE.XLS.DLL

First of all, you need to download the latest version of Spire.XLS for .NET from this link, and manually add the DLL files in your .NET application as references. Or, you can install it directly via NuGet.

Now let’s take a look at the code part.

Using the code

Spire.XLS offers the Worksheet.Copy(CellRange sourceRange, CellRange destRange, bool copyStyle) methodto copy a cell range between worksheets inside the same workbook or from different workbooks. The bool parameter determines where to preserve the original data formatting, such as font size, font color, and cell background color.

To make the copied range look exactly the same as it is in the original worksheet, you’ll need to copy the column widths as well. You’re able to set or get the width of a specific column though CellRange.Columns[i].Width property. More detailed explanations can be found in the following code example.

[C#]

using Spire.Xls;

namespace CopyCellRange
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook sourceBook = new Workbook();

            //Load the source workbook
            sourceBook.LoadFromFile(@"C:\Users\Administrator\Desktop\source.xlsx");

            //Get the source worksheet
            Worksheet sourceSheet = sourceBook.Worksheets[0];

            //Get the source cell range
            CellRange sourceRange = sourceSheet.Range["A1:E6"];

            //Create another Workbook objecy
            Workbook destBook = new Workbook();

            //Load the destination workbook
            destBook.LoadFromFile(@"C:\Users\Administrator\Desktop\destination.xlsx");

            //Get the destination worksheet
            Worksheet destSheet = destBook.Worksheets[0];

            //Get the destination cell range
            CellRange destRange = destSheet.Range["B2:F7"];

            //Copy data from the source range to the destination range
            sourceSheet.Copy(sourceRange, destRange, true);

            //Loop through the columns in the source range
            for (int i = 0; i < sourceRange.Columns.Length; i++)
            {
                //Copy the column widths also from the source range to destination range
                destRange.Columns[i].ColumnWidth = sourceRange.Columns[i].ColumnWidth;
            }

            //Save the destination workbook to an Excel file
            destBook.SaveToFile("CopyRange.xlsx");
        }
    }
}

[VB.NET]

Imports Spire.Xls
 
Namespace CopyCellRange
    Class Program
        Shared  Sub Main(ByVal args() As String)
            'Create a Workbook object
            Dim sourceBook As Workbook =  New Workbook() 
 
            'Load the source workbook
            sourceBook.LoadFromFile("C:\Users\Administrator\Desktop\source.xlsx")
 
            'Get the source worksheet
            Dim sourceSheet As Worksheet =  sourceBook.Worksheets(0) 
 
            'Get the source cell range
            Dim sourceRange As CellRange =  sourceSheet.Range("A1:E6") 
 
            'Create another Workbook objecy
            Dim destBook As Workbook =  New Workbook() 
 
            'Load the destination workbook
            destBook.LoadFromFile("C:\Users\Administrator\Desktop\destination.xlsx")
 
            'Get the destination worksheet
            Dim destSheet As Worksheet =  destBook.Worksheets(0) 
 
            'Get the destination cell range
            Dim destRange As CellRange =  destSheet.Range("B2:F7") 
 
            'Copy data from the source range to the destination range
            sourceSheet.Copy(sourceRange, destRange, True)
 
            'Loop through the columns in the source range
            Dim i As Integer
            For  i = 0 To  sourceRange.Columns.Length- 1  Step  i + 1
                'Copy the column widths also from the source range to destination range
                destRange.Columns(i).ColumnWidth = sourceRange.Columns(i).ColumnWidth
            Next
 
            'Save the destination workbook to an Excel file
            destBook.SaveToFile("CopyRange.xlsx")
        End Sub
    End Class
End Namespace