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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s