Copy or Move Cells in Excel with Python
Copying and moving cells in Excel are crucial for organizing and manipulating data efficiently. By copying cells, you can easily duplicate data, formulas, formatting, and more within the same worksheet or across different worksheets without repetitive manual data input and formula creation. Moving cells enables you to cut and paste them to new locations, making it easier to reorganize data and create logical, structured layouts. In this article, we will explore how to copy or move cells in Excel using Python.
We’ll discuss the following topics:
- Copy Cells in the Same Excel Worksheet with Python
- Copy Cells across Different Excel Worksheets with Python
- Move Cells in the Same Excel Worksheet with Python
- Move Cells across Different Excel Worksheets with Python
Python Library to Copy or Move Cells in Excel
To copy or move cells in Excel with Python, we can use the Spire.XLS for Python library. It 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.Xls
For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Copy Cells in the Same Excel Worksheet with Python
You can easily copy a cell or range of cells from one place to another in an Excel worksheet with Spire.XLS for Python. Additionally, you can specify copy options, such as whether to retain the original formatting of the source cells like background color and font color.
Here is a simple example that shows how to copy cells in the same Excel worksheet:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel workbook
workbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Get the source cell range and destination cell range in the worksheet
sourceRange = sheet.Range["A2:E4"]
destRange = sheet.Range["G12:K14"]
# Copy the source cell range to the destination cell range, and keep its original formatting
sheet.Copy(sourceRange, destRange, True)
# Copy the row heights from the source range to the destination range
for i in range(sourceRange.Rows.Length):
destRange.Rows[i].RowHeight = sourceRange.Rows[i].RowHeight
# Copy the column widths from the source range to the destination range
for j in range(sourceRange.Columns.Length):
destRange.Columns[j].ColumnWidth = sourceRange.Columns[j].ColumnWidth
# Save the resulting workbook
workbook.SaveToFile("CopyCellsInSameSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Note: when performing the copy operation, column widths and row heights will not be maintained. In the above example, we also copied the row heights and column widths from the source cell range to the destination cell range.
Copy Cells across Different Excel Worksheets with Python
When working with multiple workbooks or worksheets, you may come across the need to copy cells from one worksheet to another worksheet within the same workbook or to a different workbook.
Here is a simple example that shows how to copy cells from one worksheet to another worksheet in the same Excel workbook:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel workbook
workbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet
sheet1 = workbook.Worksheets[0]
# Get the second worksheet
sheet2 = workbook.Worksheets[1]
# Specify the source cell range in the first worksheet
sourceRange = sheet1.Range["A2:E4"]
# Specify the destination cell range in the second worksheet
destRange = sheet2.Range["A2:E4"]
# Copy the source cell range to the destination cell range, and keep its original styles
sheet1.Copy(sourceRange, destRange, True)
# Copy the row heights from the source range to the destination range
for i in range(sourceRange.Rows.Length):
destRange.Rows[i].RowHeight = sourceRange.Rows[i].RowHeight
# Copy the column widths from the source range to the destination range
for j in range(sourceRange.Columns.Length):
destRange.Columns[j].ColumnWidth = sourceRange.Columns[j].ColumnWidth
# Save the resulting workbook
workbook.SaveToFile("CopyCellsToDifferentSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
If you want to copy cells to a worksheet within another Excel workbook, refer to the following code example:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
sourceWorkbook = Workbook()
# Load the first Excel workbook
sourceWorkbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet of the first workbook
sourceSheet = sourceWorkbook.Worksheets[0]
# Instantiate a Workbook object
destWorkbook = Workbook()
# Load the second Excel workbook
destWorkbook.LoadFromFile("Sample2.xlsx")
# Get the first worksheet of the second workbook
destSheet = destWorkbook.Worksheets[0]
# Specify the source cell range
sourceRange = sourceSheet.Range["A2:E4"]
# Specify the destination cell range
destRange = destSheet.Range["A8:E10"]
# Copy the source cell range to the destination cell range, and keep its original styles
sourceSheet.Copy(sourceRange, destRange, True)
# Copy the row heights from the source range to the destination range
for i in range(sourceRange.Rows.Length):
destRange.Rows[i].RowHeight = sourceRange.Rows[i].RowHeight
# Copy the column widths from the source range to the destination range
for j in range(sourceRange.Columns.Length):
destRange.Columns[j].ColumnWidth = sourceRange.Columns[j].ColumnWidth
# Save the second workbook
destWorkbook.SaveToFile("CopyCellsToDifferentWorkbook.xlsx", ExcelVersion.Version2013)
sourceWorkbook.Dispose()
destWorkbook.Dispose()
Move Cells in the Same Excel Worksheet with Python
There is no direct method to move cells from one place to another. But you can achieve it by copying the cells to the desired location and then clearing all content and formatting of the cells from their source location.
Here is a simple example that shows how to move cells in the same Excel worksheet:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Specify the source cell range and destination cell range in the worksheet
sourceRange = sheet.Range["A2:E4"]
destRange = sheet.Range["G12:K14"]
# Copy the source cell range to the destination cell range, and keep its original styles
sheet.Copy(sourceRange, destRange, True)
# Copy the row heights from the source range to the destination range
for i in range(sourceRange.Rows.Length):
destRange.Rows[i].RowHeight = sourceRange.Rows[i].RowHeight
# Copy the column widths from the source range to the destination range
for j in range(sourceRange.Columns.Length):
destRange.Columns[j].ColumnWidth = sourceRange.Columns[j].ColumnWidth
# Remove all cell content and formatting from the source location
for cell in sourceRange:
cell.ClearAll()
# Save the resulting file
workbook.SaveToFile("MoveCellsInSameSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Move Cells across Different Excel Worksheets with Python
You are also able to move cells to different Excel worksheets.
Here is a simple example that shows how to move cells from one worksheet to another worksheet within an Excel workbook:
from spire.xls import *
from spire.xls.common import *
# Instantiate a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Sample1.xlsx")
# Get the first worksheet
sheet1 = workbook.Worksheets[0]
# Get the second worksheet
sheet2 = workbook.Worksheets[1]
# Specify the source cell range in the first worksheet
sourceRange = sheet1.Range["A2:E4"]
# Specify the destination cell range in the second worksheet
destRange = sheet2.Range["A2:E4"]
# Copy the source cell range to the destination cell range, and keep its original styles
sheet1.Copy(sourceRange, destRange, True)
# Copy the row heights from the source range to destination range
for i in range(sourceRange.Rows.Length):
destRange.Rows[i].RowHeight = sourceRange.Rows[i].RowHeight
# Copy the column widths from the source range to destination range
for j in range(sourceRange.Columns.Length):
destRange.Columns[j].ColumnWidth = sourceRange.Columns[j].ColumnWidth
# Remove all cell content and formatting from the source location
for cell in sourceRange:
cell.ClearAll()
# Save the resulting file
workbook.SaveToFile("MoveCellsToDifferentSheet.xlsx", ExcelVersion.Version2013)
workbook.Dispose()
Conclusion
This article demonstrated how to copy and move cells in Excel using Python. We hope you can find it helpful.
Related Topics
- Count the Number of Sheets, Rows, Columns and Cells in Excel in Python
- Quickly Delete or Remove Blank Rows, Columns or Cells from Excel with Python
- Merge or Unmerge Cells in Excel with Python (Simple Example)
- Convert Excel to Images (PNG, JPEG, BMP) with Python
- Python — How to Convert Excel XLS or XLSX to PDF