C# – Copy Rows and Columns in Excel

When we process the data within Excel sheets, it’s very common that we need to move or copy all data containing formulas, comments, cell formats from one cell rang to another. This article is focusing on how to achieve this task programmatically using Spire.XLS in C#.

In the following section, I built a console application in C# to explain the details. Before we start to code please add the Spire.Xls.dll as reference to your VS project.

A. Copy Rows and Columns

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

Workbook book = new Workbook();

book.LoadFromFile(“sample.xlsx”, ExcelVersion.Version2010);

Step 2: Get the sheet that contains data.

Worksheet sheet = book.Worksheets[0];

Step 3: Copy the data with formatting from one range to another by calling the method Worksheet.Copy(sourceRange, destRange, bool copyStyle).

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

Step 4: Save the file.

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

Output

CopyRow

B. Move the Data from One Range to Another

If you want to move the data from one row or column to another without maintaining the data in the previous row or column, just follow the steps in section A to copy and insert data to destination cells, then use following code to remove the data in previous cells.

sheet.Range[“A1:C1”].Clear(ExcelClearOptions.ClearAll);

sheet.Range[“B4:B7”].Clear(ExcelClearOptions.ClearAll);

Output

CutData

Entire Code

Part A:

Workbook book = new Workbook();

book.LoadFromFile(“test.xlsx”, ExcelVersion.Version2010);

Worksheet sheet = book.Worksheets[0];

 

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

 

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

System.Diagnostics.Process.Start(“result.xlsx”);

Part B:

Workbook book = new Workbook();

book.LoadFromFile(“test.xlsx”, ExcelVersion.Version2010);

Worksheet sheet = book.Worksheets[0];

 

sheet.Copy(sheet.Range[“A1:C1”], sheet.Range[“A9:C9”],true);

sheet.Copy(sheet.Range[“B4:B7”], sheet.Range[“D4:D7”], true);

 

sheet.Range[“A1:C1”].Clear(ExcelClearOptions.ClearAll);

sheet.Range[“B4:B7”].Clear(ExcelClearOptions.ClearAll);

 

book.SaveToFile(“result.xlsx”, ExcelVersion.Version2010);

System.Diagnostics.Process.Start(“result.xlsx”);

Advertisements