Formatting of Excel sheets in Python

Question

In my project I am opening an Excel file with multiple sheets. I want to manipulate "sheet2" in Python (which works fine) and after that overwrite the old "sheet2" with the new one but KEEP the formatting.. so something like this:

import pandas as pd

update_sheet2 = pd.read_excel(newest_isaac_file, sheet_name='sheet2')
#do stuff with the sheet

 with pd.ExcelWriter(filepath, engine='openpyxl', if_sheet_exists='replace', mode='a', 
                                                               KEEP_FORMATTING = True) as writer:
           df.to_excel(writer, sheet_name=sheetname, index=index)

In other words: Is there a way to get the formatting from an existing Excel sheet? I could not find anything about that. I know I can manually set the formatting in Python but the formatting of the existing sheet is really complicated and has to stay the same.

thanks for your help!

Redox · Answer

As per your comment, try this code. It will open a file (Sample.xlsx), go to a sheet (Sheet1), insert new row at 15, copy the text and formatting from row 22 and paste it in the empty row (row 15). Code and final screen shot attached.

import openpyxl
from copy import copy
wb=openpyxl.load_workbook('Sample.xlsx') #Load workbook
ws=wb['Sheet1'] #Open sheet
ws.insert_rows(15, 1) #Insert one row at 15 and move everything one row downwards

for row in ws.iter_rows(min_row=22, max_row=22, min_col=1, max_col=ws.max_column): # Read values from row 22
    for cell in row:
        ws.cell(row=15, column=cell.column).value = cell.value #Update value to row 22 to new row 15
        ws.cell(row=15, column=cell.column)._style = copy(cell._style) #Copy formatting
wb.save('Sample.xlsx')

How excel looks after running the code

Formatting of Excel sheets in Python

1 Answers