Copy Excel Rows, Columns, and Cells Using Python

In daily office automation or data processing scenarios, copying cells, rows, and columns in Excel is a high-frequency requirement—such as batch reusing template data, migrating specific column data, copying formatted report content, etc. Today, I'd like to share a lightweight and powerful Python library: Free Spire.XLS for Python. It can realize reading, writing, and copying operations of Excel files without relying on Microsoft Excel, supporting .xls and .xlsx formats. The free version is sufficient for most personal and small-scale project needs. This article will share how to use this free library to implement "cell copying", "row copying", and "column copying".

---

I. Environment Setup: Install the Free Python Library

First, install the library via the pip command (supports Python 3.6 and above):

pip install spire.xls.free

⚠️ Note: The free version limits processing up to 5 worksheets at a time and 200 rows of data per worksheet, which is suitable for small files.

---

II. Core Practical Cases: 3 Scenarios for Copying Excel Elements

The following cases are based on the "source.xlsx" with the following structure (Sheet1 as source data, Sheet2 as target worksheet):

Scenario 1: Copy Excel Cells / Cell Ranges

For single cells or continuous cell ranges, use the CellRange.Copy() method to accurately copy specified areas.

Key Parameter Explanations:

• destRange: Target cell/range object (must match the size of the source range)
• copyOptions: Copy options (default: CopyRangeOptions.All)

Practical Code: Copy a Single Cell

Requirement: Copy A1 (Name) from Sheet1 to A4.

from spire.xls import *
from spire.xls.common import *

# 1. Load the Excel file
workbook = Workbook()
workbook.LoadFromFile("source.xlsx")

# 2. Get the source worksheet (Sheet1)
worksheet = workbook.Worksheets[0]  # Index starts from 0, corresponding to Sheet1

# 3. Copy the cell (source_cell.Copy(target_cell))
# Copy A1 to A4
source_cell = worksheet.Range["A1"]  # Get by cell address
target_cell = worksheet.Range["A4"]
source_cell.Copy(target_cell, CopyRangeOptions.All)

# 4. Save the result file
workbook.SaveToFile("result_cell_copy.xlsx")
workbook.Dispose()  # Release resources to avoid file occupation
print("Cell copying completed!")

To copy a cell range (e.g., A1:C3), use worksheet.Range["A1:C3"].

Scenario 2: Copy Specified Rows in Excel

Row copying is suitable for batch reusing entire rows of data (e.g., template rows, header rows). The Worksheet.CopyRow() method is provided for row copying, supporting synchronous copying of data, formats, and formulas.

Key Parameter Explanations:

• sourceRow: Source row object (obtained via Worksheet.Rows[index], index starts from 0)
• destSheet: Target worksheet object
• destRowIndex: Target row index (starts from 1, corresponding to actual Excel row numbers)
• copyOptions: Copy options (CopyRangeOptions.All means copying all properties: values, formats, formulas, etc.)

Practical Code: Copy Rows Across Worksheets

Copy the 2nd row from Sheet1 to the 1st row of Sheet2.

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("source.xlsx")

# Get source and target worksheets
source_sheet = workbook.Worksheets[0]  # Sheet1 (source)
target_sheet = workbook.Worksheets[1]  # Sheet2 (target)

# Copy the specified row
source_row = source_sheet.Rows[1]
source_sheet.CopyRow(source_row, target_sheet, 1, CopyRangeOptions.All)

workbook.SaveToFile("result_row_copy.xlsx")
workbook.Dispose()

Scenario 3: Copy Specified Columns in Excel

Column copying follows a similar logic to row copying and is suitable for migrating specific fields. Use the Worksheet.CopyColumn() method for column copying with the following parameters:

• sourceColumn: Source column object (obtained via Worksheet.Columns[index], index starts from 0)
• destColIndex: Target column index (starts from 1, corresponding to actual Excel column numbers)
• Other parameters are the same as CopyRow

Practical Code: Copy Columns Within the Same Worksheet

Copy Column C (City) from Sheet1 to Column D in the same worksheet.

from spire.xls import Workbook
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("source.xlsx")
worksheet = workbook.Worksheets[0]

# Copy Column C (3rd column) to Column D (4th column)
source_column = worksheet.Columns[2]
worksheet.CopyColumn(source_column, worksheet, 4, CopyRangeOptions.All)

workbook.SaveToFile("result_column_copy.xlsx")
workbook.Dispose()

Notes: Be sure to call workbook.Dispose() after the operation is completed, or use the with statement to automatically release resources to avoid Excel file occupation.

---

Free Spire.XLS for Python is a free and lightweight Excel processing tool. Compared with libraries like openpyxl and xlrd, it is more convenient for "format copying" and "formula processing" and does not require an Excel environment. Whether it's simple data copying or complex report template reuse, these methods can meet the needs of efficient office work.