Copying Rows and Columns
Introduction
Sometimes, you need to copy rows and columns in a worksheet without copying the entire worksheet. With Aspose.Cells, it is possible to copy rows and columns within or between workbooks. When a row (or column) is copied, the data contained in it, including formulas - with updated references - and values, comments, formatting, hidden cells, images, and other drawing objects are copied too.
How to Copy Rows and Columns with Microsoft Excel
- Select the row or column that you want to copy.
- To copy rows or columns, click Copy on the Standard toolbar, or press CTRL+C.
- Select a row or column below or to the right of where you want to copy your selection.
- When you are copying rows or columns, click Copied Cells on the Insert menu.
How to Paste Rows and Columns using Paste Options with Microsoft Excel
- Select the cells that contain the data or other attributes that you want to copy.
- On the Home tab, click Copy.
- Click the first cell in the area where you want to paste what you copied.
- On the Home tab, click the arrow next to Paste, and then select Paste Special.
- Select the options you want.
How to Copy Rows and Columns Using Aspose.Cells for .NET
How to Copy Single Rows
Aspose.Cells provides the copy_row method of the Cells class. This method copies all types of data including formulas, values, comments, cell formats, hidden cells, images and other drawing objects from the source row to the destination row.
The copy_row method takes the following parameters:
- the source Cells object,
- source row index, and
- destination row index.
Use this method to copy a row within a sheet, or to another sheet. The copy_row method works in a similar way to Microsoft Excel. So, for example, you don’t need to set the height of the destination row explicitly, that value is copied too.
The following example shows how to copy a row in a worksheet. It uses a template Microsoft Excel file and copies the second row (complete with data, formatting, comments, images and so on) and paste it to the 12th row in the same worksheet.
You can skip the step that gets the source row height using the Cells.get_row_height method and then sets the destination row height using the Cells.set_row_height method as the copy_row method automatically takes care of the row height.
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Open the existing excel file. | |
| excelWorkbook1 = Workbook(dataDir + "book1.xls") | |
| # Get the first worksheet in the workbook. | |
| wsTemplate = excelWorkbook1.worksheets[0] | |
| # Copy the second row with data, formattings, images and drawing objects | |
| # To the 16th row in the worksheet. | |
| wsTemplate.cells.copy_row(wsTemplate.cells, 1, 15) | |
| # Save the excel file. | |
| excelWorkbook1.save(dataDir + "output.xls") |
When copying rows, it is important to note related images, charts or other drawing objects as this is the same with Microsoft Excel:
- If the source row index is 5, the image, chart, etc., is copied if it is contained in the three rows (the starting row index is 4 and the ending row index is 6).
- The existing images, charts, etc. in the destination row will not be removed.
How to Copy Multiple Rows
You can also copy multiple rows onto a new destination while using the Cells.copy_rows method which takes an additional parameter of type integer to specify the number of source rows to be copied.
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Create an instance of Workbook class by loading the existing spreadsheet | |
| workbook = Workbook(dataDir + "aspose-sample.xlsx") | |
| # Get the cells collection of worksheet by name Rows | |
| cells = workbook.worksheets.get("Rows").cells | |
| # Copy the first 3 rows to 7th row | |
| cells.copy_rows(cells, 0, 6, 3) | |
| # Save the result on disc | |
| workbook.save(dataDir + "output_out.xlsx") |
How to Copy Columns
Aspose.Cells provides the copy_column method of the Cells class, this method copies all types of data, including formulas - with updated references - and values, comments, cell formats, hidden cells, images and other drawing objects from the source column to the destination column.
The copy_column method takes the following parameters:
- the source Cells object,
- source column index, and
- the destination column index.
Use the copy_column method to copy a column within a sheet or to another sheet.
This example copies a column from a worksheet and pastes it into a worksheet in another workbook.
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Create another Workbook. | |
| excelWorkbook1 = Workbook(dataDir + "book1.xls") | |
| # Get the first worksheet in the book. | |
| 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.copy_column(ws1.cells, ws1.cells.columns[0].index, ws1.cells.columns[2].index) | |
| # Autofit the column. | |
| ws1.auto_fit_column(2) | |
| # Save the excel file. | |
| excelWorkbook1.save(dataDir + "output.xls") |
How to Copy Multiple Columns
Similar to Cells.copy_rows method, the Aspose.Cells APIs also provide the Cells.copy_columns method in order to copy multiple source columns to a new location.
| from aspose.cells import Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| dataDir = RunExamples.GetDataDir(".") | |
| # Create an instance of Workbook class by loading the existing spreadsheet | |
| workbook = Workbook(dataDir + "aspose-sample.xlsx") | |
| # Get the cells collection of worksheet by name Columns | |
| cells = workbook.worksheets.get("Columns").cells | |
| # Copy the first 3 columns 7th column | |
| cells.copy_columns(cells, 0, 6, 3) | |
| # Save the result on disc | |
| workbook.save(dataDir + "output_out.xlsx") |
How to Paste Rows and Columns with Paste Options
Aspose.Cells now provides PasteOptions while using functions copy_rows and copy_columns. It allows to set appropriate paste option similar to Excel.
| from aspose.cells import CopyOptions, PasteOptions, PasteType, SaveFormat, Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # The path to the documents directory. | |
| # Source directory | |
| sourceDir = RunExamples.Get_SourceDirectory() | |
| # Output directory | |
| outputDir = RunExamples.Get_OutputDirectory() | |
| # Load sample excel file | |
| wb = Workbook(sourceDir + "sampleChangeChartDataSource.xlsx") | |
| # Access the first sheet which contains chart | |
| source = wb.worksheets[0] | |
| # Add another sheet named DestSheet | |
| destination = wb.worksheets.add("DestSheet") | |
| # Set CopyOptions.ReferToDestinationSheet to true | |
| options = CopyOptions() | |
| options.refer_to_destination_sheet = True | |
| # Set PasteOptions | |
| pasteOptions = PasteOptions() | |
| pasteOptions.paste_type = PasteType.VALUES | |
| pasteOptions.only_visible_cells = True | |
| # Copy all the rows of source worksheet to destination worksheet which includes chart as well | |
| # The chart data source will now refer to DestSheet | |
| destination.cells.copy_rows(source.cells, 0, 0, source.cells.max_display_range.row_count, options, pasteOptions) | |
| # Save workbook in xlsx format | |
| wb.save(outputDir + "outputChangeChartDataSource.xlsx", SaveFormat.XLSX) |