Copying Rows and Columns - Aspose.Cells for .NET and Java

Sometimes, you 'll need to copy rows and columns in a worksheet without copying the entire worksheet. Aspose.Cells has this ability to copy rows and columns within or between workbooks. When you copy a row / column, it will copy all the data that the row / column contains, including formulas (with updated reference) and their resulting values, comments, cell formats, hidden cells, images and other drawing objects etc with high degree of accuracy and precision.

Using MS Excel

Copying Rows and Columns

1. Select the row or column that you want to copy.

2. To copy rows or columns, click Copy on the Standard toolbar, or press CTRL+C.

3. Select a row or column below or to the right of where you want to copy your selection.

4. When you are copying rows or columns, click Copied Cells on the Insert menu.

Note If you click Paste on the Standard toolbar or press CTRL+V instead of clicking a command on the Insert menu, you will replace any contents of the destination cells.

Using Aspose.Cells

Copying Rows

Aspose.Cells provides CopyRow method of the Aspose.Cells.Cells class, this method copies all types of data including formulas (with updated reference) and their resulting values, comments, cell formats, hidden cells, images and other drawing objects etc. from the source row to the destination row. This method takes three parameters i.e. source Cells object, source row index and destination row index. You may utilize this method to copy a row within a workbook sheets or to another workbook sheets.

Example:

The following example shows how to copy a row in a worhseet. The example utilizes a template excel file and copies the second row (The row has data, formattings, comment, images etc.) and paste it to 12th row in the same worksheet.

[C#]

//Create a new Workbook.

Workbook excelWorkbook1 = new Workbook();

//Open the existing excel file.

excelWorkbook1.Open(@"d:\test\MySampleBK.xls");

//Get the first worksheet in the workbook.

Worksheet wsTemplate = excelWorkbook1.Worksheets[0];

//Get the height of the second row in the worksheet.

double rowheight = wsTemplate.Cells.GetRowHeight(1);

//Copy the second row with data, formattings, images and drawing objects

//to the 12th row in the worksheet.

wsTemplate.Cells.CopyRow(wsTemplate.Cells,1,11);

//Set the destination row height to view the data.

wsTemplate.Cells.SetRowHeight(11,rowheight);

//Save the excel file.

excelWorkbook1.Save("d:\\test\\copyrow.xls");

[VB]

'Create a new Workbook.

Dim excelWorkbook1 As Workbook = New Workbook()

'Open the existing excel file.

excelWorkbook1.Open("d:\test\MySampleBK.xls")

'Get the first worksheet in the workbook.

Dim wsTemplate As Worksheet = excelWorkbook1.Worksheets(0)

'Get the height of the second row in the worksheet.

Dim rowheight As Double = wsTemplate.Cells.GetRowHeight(1)

'Copy the second row with data, formattings, images and drawing objects

'to the 12th row in the worksheet.

wsTemplate.Cells.CopyRow(wsTemplate.Cells,1,11)

'Set the destination row height to view the data.

wsTemplate.Cells.SetRowHeight(11,rowheight)

'Save the excel file.

excelWorkbook1.Save("d:\test\copyrow.xls")

[JAVA]

//Create a new Workbook.

Workbook excelWorkbook1 = new Workbook();

//Open the existing excel file.

excelWorkbook1.open("d:\\test\\MySampleBK.xls");

//Get the first worksheet in the workbook.

Worksheet wsTemplate = excelWorkbook1.getWorksheets().getSheet(0);

//Get the height of the second row in the worksheet.

double rowheight = wsTemplate.getCells().getRowHeight(1);

//Copy the second row with data, formattings, images and drawing objects

//to the 12th row in the worksheet.

wsTemplate.getCells().copyRow(wsTemplate.getCells(),1,11);

//Set the destination row height to view the data.

wsTemplate.getCells().setRowHeight(11,rowheight);

//Save the excel file.

excelWorkbook1.save("d:\\test\\copyrow.xls");

The following output would be generated after executing the above code:

Figure: A Row is copied with the highest degree of precision and accuracy.

Note While copying rows, it is important to note related images, charts or other drawing objects as this is same with MS Excel:

1. If the source row index is 5, the image / chart etc. will be copied if it is contained in the three rows (the starting row index is 4 and the ending row index is 6).

2, The existing images, charts etc. in the destination row will not be removed.

Copying Columns

Aspose.Cells provides CopyColumn method of the Aspose.Cells.Cells class, this method copies all types of data including formulas (with updated reference) and their resulting values, comments, cell formats, hidden cells, images and other drawing objects etc. from the source column to the destination column. This method takes three parameters i.e. source Cells object, source column index and destination column index. You may utilize this method to copy a column within a workbook sheets or to another workbook sheets.

Example:

The following example shows how to copy a column in a worhseet to another worksheet between workbooks. The example copies a column from a worksheet and paste it to another worksheet in another workbook.

[C#]

//Create a new Workbook.

Workbook excelWorkbook0 = new Workbook();

//Get the first worksheet in the book.

Worksheet ws0 = excelWorkbook0.Worksheets[0];

//Put some data into header rows (A1:A4)

for (int i = 0; i < 5; i++)

{

ws0.Cells[i, 0].PutValue(string.Format("Header Row {0}", i));

}

//Put some detail data (A5:A999)

for (int i = 5; i < 1000; i++)

{

ws0.Cells[i, 0].PutValue(string.Format("Detail Row {0}", i));

}

//Create another Workbook.

Workbook excelWorkbook1 = new Workbook();

//Get the first worksheet in the book.

Worksheet ws1 = excelWorkbook1.Worksheets[0];

//Copy the first column from the first worksheet of the first workbook into

//the first worksheet of the second workbook.

ws1.Cells.CopyColumn(ws0.Cells,ws0.Cells.Columns[0].Index,ws1.Cells.Columns[2].Index);

//Autofit the column.

ws1.AutoFitColumn(2);

//Save the excel file.

excelWorkbook1.Save(@"d:\test\copycolumn.xls", FileFormatType.Default);

[VB]

'Create a new Workbook.

Dim excelWorkbook0 As Workbook = New Workbook()

'Get the first worksheet in the book.

Dim ws0 As Worksheet = excelWorkbook0.Worksheets(0)

'Put some data into header rows (A1:A4)

Dim i As Integer

For i = 0 To 4 Step 1

ws0.Cells(i, 0).PutValue(String.Format("Header Row {0}", i))

For i = 5 To 999 Step 1

ws0.Cells(i, 0).PutValue(String.Format("Detail Row {0}", i))

Dim excelWorkbook1 As Workbook = New Workbook()

'Get the first worksheet in the book.

Dim ws1 As Worksheet = excelWorkbook1.Worksheets(0)

'Copy the first column from the first worksheet of the first workbook into

'the first worksheet of the second workbook.

ws1.Cells.CopyColumn(ws0.Cells,ws0.Cells.Columns(0).Index,ws1.Cells.Columns(2).Index)

'Autofit the column.

ws1.AutoFitColumn(2)

'Save the excel file.

excelWorkbook1.Save("d:\test\copycolumn.xls", FileFormatType.Default)

[JAVA]

//Create a new Workbook.

Workbook excelWorkbook0 = new Workbook();

//Get the first worksheet in the book.

Worksheet ws0 = excelWorkbook0.getWorksheets().getSheet(0);

//Put some data into header rows (A1:A4)

for (int i = 0; i < 5; i++)

{

ws0.getCells().getCell(i, 0).setValue("Header Row " + i);

}

//Put some detail data (A5:A999)

for (int i = 5; i < 1000; i++)

{

ws0.getCells().getCell(i, 0).setValue("Detail Row " + i);

}

//Create another Workbook.

Workbook excelWorkbook1 = new Workbook();

//Get the first worksheet in the book.

Worksheet ws1 = excelWorkbook1.getWorksheets().getSheet(0);

//Copy the first column from the first worksheet of the first workbook into

//the first worksheet of the second workbook.

ws1.getCells().copyColumn(ws0.getCells(),0,2);

//Autofit the column.

ws1.autoFitColumn(2);

//Save the excel file.

excelWorkbook1.save("d:\\test\\copycolumn.xls", FileFormatType.DEFAULT)

The following output would be generated after executing the above code:

Figure: A Column is copied in a worksheet from one workbook to another wokbook.