Python - save new Excel sheet with existing format

1
votes

I want to save excel sheet with existing sheet formats and background color.

I could save successfully to a new file with out old file format.

How can I keep the existing excel sheet format when save in to a new file.

writer = pd.ExcelWriter('New.xlsx', engine='xlsxwriter')
dfDiff.to_excel(writer, sheet_name='DIFF', index=False)
writer.save()
1
pythonexcelpandasformat

1 Answers

0
votes

I had no good experience with pandas and format styles... what works if you use win32com like this example:

from win32com.client import DispatchEx
excel = DispatchEx('Excel.Application')
wbP=excel.Workbooks.Open(r'C:\Temp\Junk\Temp.xlsx')
wbG=excel.Workbooks.Open(r'C:\Temp\Junk\Temp2.xlsx')
wbG.Worksheets('TAA2').Copy(Before=wbP.Worksheets("TAA"))
wbP.SaveAs(r'C:\Temp\Junk\Temp.xlsx')
excel.Quit()
del excel # ensure Excel process ends

this copies everything... (styles, formats, formulas etc.)

Another option is copy the whole workbook and delete the not needed sheets, if you create a new file and not editing an existing one... Copy worksheet from one workbook to another one using Openpyxl