Does python support copy one excel to another excel without formation loss?

Python Help
1

Hi there,

I’m using Python as a tool to copy an Excel file to another Excel file. While the basic functionality using Pandas works fine, I noticed that the formatting of the Excel file is lost during the copy. This includes important elements such as cell URLs, background color, font size, and more.

My goal is to copy the contents of output_with_links_new.xlsx to one tab in a new Excel file and copy the contents of output_with_links_accept.xlsx to another tab. I want to preserve the formatting of both files (output_with_links_new.xlsx and output_with_links_accept.xlsx ). Could you please help me translate this into American English?

I wanted to check if Python supports preserving these formatting features during the copy process. Any help would be greatly appreciated!

Thank you in advance!

2

If you want to literally just copy the file without changing anything, you can use shutil.copy. If you do want to change something in the file, then it’s probably not possible; though maybe it is – I don’t know pandas that well. Maybe someone else does know.

3

It has been a loooong time since I needed to do this…

These days I think the solution you want to look at is openpyxl which is a library specifically for working with xlsx files [1]

I believe openpyxl will let you open a workbook, edit a cell, and save a copy of it with the formatting unchanged.

  1. if you need to work with xls files, you might need to use xlwt instead, and my condolences ↩︎

1 Like
4

shutil.copy is not intended for merging or combining the contents of two files. Its purpose is to create a duplicate of a single file. My objective is to merge two Excel files into one, shutil.copy is not suitable for this task.

5

I try multiple times via openyxl, seems no luck. The code is itemized below:

from openpyxl import load_workbook, Workbook

# Load the first Excel file
wb_new = load_workbook('output_with_links_new.xlsx')

# Load the second Excel file
wb_accept = load_workbook('output_with_links_accept.xlsx')

# Create a new Excel file
wb_merged = Workbook()

# Get the active worksheets from the input files
ws_new = wb_new.active
ws_accept = wb_accept.active

# Create new worksheets in the merged file
ws_merged_new = wb_merged.create_sheet('new')
ws_merged_accept = wb_merged.create_sheet('accept')

# Copy the contents from the first file to the 'new' worksheet in the merged file
for row in ws_new.iter_rows(values_only=True):
    ws_merged_new.append(row)

# Copy the contents from the second file to the 'accept' worksheet in the merged file
for row in ws_accept.iter_rows(values_only=True):
    ws_merged_accept.append(row)

# Remove the default sheet created in the merged file
default_sheet = wb_merged['Sheet']
wb_merged.remove(default_sheet)

# Save the merged file
wb_merged.save('merged_output.xlsx')

print("Files merged successfully.")
6

The above code successfully merges the files, but it loses the formatting of the previous files (URLs, colors, cell dimensions, widths).

7

I don’t know the details but the documentation has a section on applying and copying styles and rich text. It might get annoyingly complex, as the structure of an xlsx file is not designed to make this easy, but it is possible.

If there is a consistent formatting style across the sheets, you might be able to define it once and apply it on top of the values, rather than trying to copy it every time.

8

That got me thinking, if there is a consistent style, you can make a jinja-like xlsx template with xltpl (a package I’ve used in the past), and populate.

1 Like