Formatting plays a crucial role in making your Excel spreadsheets clean, organized, and visually appealing. Often, you may want to apply the same formatting to multiple cells or ranges in your workbook. Instead of manually formatting each cell individually, Excel provides a convenient feature called "Copy Cell Formatting" that allows you to quickly replicate the formatting from one cell to others.
Here in this article, you will learn how to programmatically copy cell formatting in Excel using Spire.XLS for Python.
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your system through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Copy Formatting from One Cell to Another in Python
You can access a specific cell by using the Worksheet.Range[row:int, column:int] property. The formatting of that cell can be retrieved through the CellRange.Style property, and this formatting can then be applied to a different cell.
The steps to copy formatting from one to cell to anther are as follows.
- Create a Workbook object.
- Load an Excel document from a give path.
- Get a specific worksheet within the workbook.
- Get a specific cell through Worksheet.Range[row:int, column:int] property.
- Get the cell formatting through CellRange.Style property, and apply it to another cell through the same property.
- Save the workbook to a different Excel file.
This code example loads an existing Excel document, copies the formatting (style) from the cells in the second column to the cells in the fourth column for rows 2 through 14, and then saves the modified workbook to a new Excel file.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Loop through the selected rows
for i in range(2, 15):
# Get style (formatting) of a specific cell
style = worksheet.Range[i, 2].Style
# Apply the style to a different cell
worksheet.Range[i, 4].Style = style
# Save the workbook to file
workbook.SaveToFile("output/CopyFormatting.xlsx",ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()
Copy Formatting from One Cell to a Cell Range in Python
Once you get the style (formatting) of a certain cell, you can apply it to a cell rang which is retrieved through the Worksheet.Range[row:int, column:int, endRow:int, endColumn:int] property.
Here are the steps to copy formatting from once cell to a cell range.
- Create a Workbook object.
- Load an Excel document from a give path.
- Get a specific worksheet within the workbook.
- Get a specific cell through Worksheet.Range[row:int, column:int] property.
- Get the formatting of the cell through CellRange.Style property.
- Get a cell range through Worksheet.Range[row:int, column:int, endRow:int, endColumn:int] property.
- Apply the formatting to the cell range through CellRange.Style property.
- Save the workbook to a different Excel file.
This code example loads an existing Excel document, retrieves the style of a cell located in the third row and first column, and then applies that style to a range of cells from the third row, fourth column to the fourth row, sixth column.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx")
# Get a specific worksheet
worksheet = workbook.Worksheets[0]
# Get style (formatting) of a specific cell
style = worksheet.Range[3, 1].Style
# Apply the style to a cell range
worksheet.Range[3, 4, 4, 6].Style = style
# Save the workbook to file
workbook.SaveToFile("output/ApplyFormatToCellRange.xlsx",ExcelVersion.Version2016)
# Dispose resources
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
