Pandas dataframe to Excel, retaining Excel sheet cells formatting

Excel, Python Leave a comment

Hi, when we transfer a dataframe to an pre-formatted Excel sheet, there will be a common issue where the cell formatting of the Excel sheet is is not being retain. In this post, I will use another method to transfer the dataframe to the desired Excel sheet with the cells formatting that I want.

Below is the video and the source code:

import shutil
from win32com.client import Dispatch
import pandas as pd

# Initializing variables
source = r"C:\Users\deadw\Documents\Algo\for_blog\Pandas df to Excel\SP500 Index.xlsx"
dest = r"C:\Users\deadw\Documents\Algo\for_blog\Pandas df to Excel\SP500 Template.xlsx"
dest2 = r"C:\Users\deadw\Documents\Algo\for_blog\Pandas df to Excel\SP500 Template 2.xlsx"


# create dataframe
sp500_df = pd.read_excel(source)


# copy dataframe to Excel Template with cells formatting

sp500_df.to_clipboard(excel = True, index = False)
excel = Dispatch("Excel.Application")
wb = excel.Workbooks.Open(dest)
ws = wb.Worksheets("SP500_Formatted")
wb.Worksheets("SP500_Formatted").Activate()
wb.Worksheets("SP500_Formatted").Range("A1").Select()
wb.ActiveSheet.PasteSpecial()
wb.Close(True)
excel.Quit()


# using dataframe to excel method - does not retain Excel columns formatting
sp500_df.to_excel(dest2, index = False)