[Comprehensive Guide] How to Copy Format in Excel with Python

Cell formatting is essential to presenting clear and organized data. For example, when dealing with financial statements, sales data, and other forms with multiple data, maintaining a consistent format helps a lot in readability and understanding. Sometimes you need to manage formatting in different worksheets or even copy the formatting to another Excel document.

This comprehensive guide will introduce how to copy format in Excel with Python, allowing you to standardize your data's appearance efficiently.

Prepare for Tasks

To copy formats in Excel with Python, we need Spire.XLS for Python. It is a strong Python Excel library. By using it in Python, you can perform nearly all the functions available in Microsoft Office, such as format conversion, various operations on documents and cells, and more. This article will specifically focus on how to copy cell format in Excel.

You can install Spire.XLS for Python from PyPI using the pip command below:

pip install Spire.XLS

How to Copy Format in Excel with Python: Same Worksheet

Now that you have prepared for the task, it’s time to check out specific formatting operations. In this section, we will explore how to copy the format within the same worksheet using Python. This technique is particularly useful when you want to maintain a consistent look across multiple cells or ranges, ensuring that your spreadsheet remains organized and visually appealing.

Steps to copy format in Excel with Python:

• Create a Workbook instance and use the Workbook.LoadFromFile() method to open an Excel file to be operated.

• Get a certain worksheet with the Workbook.Worksheets[] method.

• Retrieve the format of a cell and apply it to other cells using the Worksheet.Range[].Style property.

• Save the resulting document with the Workbook.SaveToFile() method, and then release the resources.

Here is a code example. This example shows how to copy the formatting from the 1st to the 13th row of the 1st column to the 1st to the 13th row of the 3rd column.

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


# Create an object of the Workbook class
workbook = Workbook()

# Load the Excel document to be modified
workbook.LoadFromFile("text.xlsx")

# Get the first worksheet from the workbook
worksheet = workbook.Worksheets[0]


# Retrieve the format of the cell
#style = worksheet.Range[3, 1].Style

# Apply the format to the range
#worksheet.Range[3, 4, 4, 6].Style = style


# Loop through the rows of the worksheet
for i in range(1, 14):

    # Get the format of the cell
    style = worksheet.Range[i, 1].Style

    # Apply formats to the cell
    worksheet.Range[i, 3].Style = style

# Save the resulting document
workbook.SaveToFile("CopyFormatting.xlsx",ExcelVersion.Version2016)

# Release the resources
workbook.Dispose()

Copy All Formatting to Another Sheet in Excel: Same File

After learning how to copy format within the same worksheet, you might find it necessary to apply formats to another worksheet in the same Excel file. This is helpful especially when you are dealing with a multi-sheet project and you want all of them to stay in a consistent appearance. This part will demonstrate how to copy all formatting to another sheet in Python, providing detailed steps and a code example for your reference.

Steps to copy format to another sheet in Python:

• Create an object of the Workbook class, and use the Workbook.LoadFromFile() method to load an Excel document.

• Get the source worksheet and the destination worksheet with the Workbook.Worksheets[] method.

• Get the format of cells in the source worksheet and apply it to the destination sheet using the Worksheet.Range[].Style property.

• Store the modified file and release the workbook.

Below is an example of copying the format of the first worksheet and pasting it to the third sheet:

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


# Create a Workbook object
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("text.xlsx")

# Get the first worksheet
worksheet = workbook.Worksheets[0]

# Get the third worksheet of the workbook
worksheet2 = workbook.Worksheets[2]

# Loop through the selected rows
for i in range(1, 14):

    # Get the format of the cell
    style = worksheet.Range[i, 1].Style

    # Apply the format to the 1st worksheet of the 2nd workbook
    worksheet2.Range[i,1].Style =style

# Save the workbook to file
workbook.SaveToFile("toanothersheet.xlsx",ExcelVersion.Version2016)

# Release the resources
workbook.Dispose()

Copy All Formatting to Another Sheet in Excel: Different Excel File

In the two sections above, we talked about how to copy a cell format in Excel within the same file. There may be times when you need to replicate cell formats to another Excel file. This often happens when handling multiple Excel documents, and they require uniform formatting.

In this section, we will explore how to copy all formatting to another sheet in a different file, including instructions and a code example.

Steps to copy cell format to a different Excel file:

• Instantiate an object of the Workbook class and read the file from the disk using the Workbook.LoadFromFile() method.

• Retrieve the source worksheet from the workbook with the Workbook.Worksheets[] method.

• Create another Workbook object and get the destination worksheet from it using the Workbook.Worksheets[] method.

• Loop through cells in the first worksheet from the last steps, and get the cell formats.

• Copy all the formatting to the second worksheet from the above steps by calling the Worksheet.Range[].Style property.

• Save the new document to the disk with the Workbook.SaveToFile() method, and release the resources.

Here is the code example of copying the format of cells and pasting it to another Excel file:

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


# Create a Workbook object
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("text.xlsx")

# Get the specified worksheet
worksheet = workbook.Worksheets[0]

# Create a new Workbook object
workbook2 = Workbook()
workbook2.LoadFromFile("sample.xlsx")

# Get the second worksheet of the 2nd workbook
worksheet2 = workbook2.Worksheets[1]

# Loop through the selected rows
for i in range(1, 14):

    # Get the format of the cell
    style = worksheet.Range[i, 1].Style

    # Apply the format to the 1st worksheet of the 2nd workbook
    worksheet2.Range[i,1].Style =style

# Save the workbook to file
workbook2.SaveToFile("toanotherfile.xlsx",ExcelVersion.Version2016)

# Release the resources
workbook.Dispose()
workbook2.Dispose()

The Conclusion

This article introduces how to copy format in Excel with Python, including copying cell format within the same worksheet, to another sheet, and to another Excel file. Each part contains step-by-step instructions and a code example for your reference. We hope you find it useful!