How to Duplicate a Row in Excel in C#, VB.NET

Sometimes, we need to copy the data with formatting from one cell range (a row or a column) to another. It is an extremely easy work in MS Excel, because we can select the source range and then use Copy and Paste function to insert the same data in destination cells.

In fact, Spire.XLS has provided two methods Worksheet.Copy(CellRange sourceRange, CellRange destRange, bool copyStyle) and Worksheet.Copy(CellRange sourceRange, Worksheet worksheet, int destRow, int destColumn, bool copyStyle) that allow programmers to copy rows and columns within or between workbooks. This article will present how to duplicate a row within a workbook using Spire.XLS.

Test File:

Code Snippet:

Step 1: Create a new instance of Workbook class and load the sample file.

Workbook book = new Workbook();
book.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);

Step 2: Get the first worksheet.

Worksheet sheet = book.Worksheets[0];

Step 3: Call Worksheet.Copy(CellRange sourceRange, CellRange destRange, bool copyStyle) method to copy data from source range (A1:G1) to destination range (A4:G4) and maintain the formatting.

sheet.Copy(sheet.Range["A1:G1"], sheet.Range["A4:G4"],true);

Step 4: Save and launch the file.

book.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");

Output:

How to Duplicate a Row in Excel in C#, VB.NET

Full Code:

[C#]

using Spire.Xls;
namespace DuplicateRowExcel
{
    class Program
    {

        static void Main(string[] args)
        {
            Workbook book = new Workbook();
            book.LoadFromFile("sample.xlsx", ExcelVersion.Version2010);
            Worksheet sheet = book.Worksheets[0];

            sheet.Copy(sheet.Range["A1:G1"], sheet.Range["A4:G4"], true);

            book.SaveToFile("result.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

[VB.NET]

Imports Spire.Xls
Namespace DuplicateRowExcel
	Class Program

		Private Shared Sub Main(args As String())
			Dim book As New Workbook()
			book.LoadFromFile("sample.xlsx", ExcelVersion.Version2010)
			Dim sheet As Worksheet = book.Worksheets(0)

			sheet.Copy(sheet.Range("A1:G1"), sheet.Range("A4:G4"), True)

			book.SaveToFile("result.xlsx", ExcelVersion.Version2010)
			System.Diagnostics.Process.Start("result.xlsx")
		End Sub
	End Class
End Namespace