Copy Rows and Columns in Excel with Python
Copying rows and columns in Excel is an important technique that allows you to quickly duplicate and reuse data. Instead of manually entering the same information multiple times, you can simply select the desired rows or columns and use a simple copy command to create accurate duplicates.
Copying rows and columns is useful in a variety of scenarios. When you need to create data backups, copying rows and columns allows you to easily generate copies of the data, ensuring data integrity. Additionally, when you need to move data or perform data calculations within a worksheet, copying rows and columns enables you to manipulate and analyze data flexibly without altering the original data.
In this article, we will explore how to copy rows and columns in Excel using Python. We will discuss the following topics:
- Copy a Single Row and Column in Excel with Python
- Copy Multiple Rows and Columns in Excel with Python
- Copy Visible Rows and Columns Only in Excel with Python
Python Library to Copy Rows and Columns in Excel
To copy rows and columns in Excel with Python, we can use the Spire.XLS for Python library.
Spire.XLS for Python is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.
You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:
pip install Spire.XlsFor more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Copy a Single Row and Column in Excel with Python
You can copy a specific row or column easily by using the Worksheet.CopyRow(sourceRow, destSheet, destRowIndex, copyOptions) or Worksheet.CopyColumn(sourceColumn, destSheet, destColIndex, copyOptions) function. The copyOptions parameter allows you to specify additional copying options for the row or column being copied. These options include copying formula values only, copying styles, copying all attributes, and more.
Here is a simple example that shows how to copy a specific row and column in Excel using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Get the first worksheet
source_sheet = workbook.Worksheets[0]
# Get the second worksheet
dest_sheet = workbook.Worksheets[1]
# Get the first row of the first worksheet by its index (0-based) and copy it to the first row of the second worksheet
source_sheet.CopyRow(source_sheet.Rows[0], dest_sheet, 1, CopyRangeOptions.All)
# Get the first column of the first worksheet by its index (0-based) and copy it to the first column of the second worksheet
source_sheet.CopyColumn(source_sheet.Columns[0], dest_sheet, 1, CopyRangeOptions.All)
# Save the resulting file
workbook.SaveToFile("CopySingleRowAndColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Copy Multiple Rows and Columns in Excel with Python
Sometimes, you may want to copy multiple rows or columns in an Excel worksheet. In Spire.XLS for Python, multiple rows or columns can be copied based on the range specified.
Here is a simple example that shows how to copy multiple rows and columns in Excel using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Get the first worksheet
source_sheet = workbook.Worksheets[0]
# Get the second worksheet
dest_sheet = workbook.Worksheets[1]
# Copy the first 3 rows from the first worksheet to the second worksheet by specifying the corresponding cell range
source_sheet.CopyRow(source_sheet.Range["A1:C3"], dest_sheet, 1, CopyRangeOptions.All)
# Copy the first 2 columns from the first worksheet to the second worksheet by specifying the corresponding cell range
source_sheet.CopyColumn(source_sheet.Range["A1:B11"], dest_sheet, 1, CopyRangeOptions.All)
# Save the resulting file
workbook.SaveToFile("CopyMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Copy Visible Rows and Columns Only in Excel with Python
Copying visible rows and columns in Excel is an essential technique that enables you to duplicate data while excluding any hidden rows and columns.
By leveraging the Worksheet.GetRowIsHide(rowIndex) and Worksheet.GetColumnIsHide(colIndex) methods, you can effortlessly determine the visibility status of individual rows and columns within a worksheet.
Here is a simple example that shows how to copy visible rows or columns in Excel using Python and Spire.XLS for Python:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
# Get the first worksheet
source_sheet = workbook.Worksheets[0]
# Get the second worksheet
dest_sheet = workbook.Worksheets[1]
# Copy visible rows in the worksheet
dest_row_index = 1
# Iterate through the used rows in the worksheet
for i in range(0, source_sheet.LastRow):
# Find the visible rows
if not source_sheet.GetRowIsHide(i + 1):
# Copy the visible rows from the first worksheet to the second worksheet
source_sheet.CopyRow(source_sheet.Rows[i], dest_sheet, dest_row_index, CopyRangeOptions.All)
dest_row_index += 1
# # Or copy visible columns in the worksheet
# dest_col_index = 1
# # Iterate through the used columns in the worksheet
# for j in range(0, source_sheet.LastColumn):
# # Find the visible columns
# if not source_sheet.GetColumnIsHide(j + 1):
# # Copy the visible columns from the first worksheet to the second worksheet
# source_sheet.CopyColumn(source_sheet.Columns[j], dest_sheet, dest_col_index, CopyRangeOptions.All)
# dest_col_index += 1
# Save the resulting file
workbook.SaveToFile("CopyVisibleRows.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Conclusion
This article demonstrated how to copy a single row and column, copy multiple rows and columns, as well as copy visible rows and columns in Excel using Python. We hope you find it helpful.
