how to apply a format on multiple excel files at once (hide gridlines & autofit columns)

I’m trying to apply a specific format on an iterate excel files, i need to hide the gridlines and autofit columns width, i tried many codes and styles but no one of them worked with me.

the exported excel files shown as below PSenter image description here

FP_sheet=pd.read_excel(r"C:UsersMahmoud.BaderDesktopFP Attendance V1.6 Apr 22.xlsx","Attendance").fillna("")
FP_sheet['Date']=pd.to_datetime(FP_sheet['Date']).dt.date

    
dep = FP_sheet["Department"].unique()
#x=('Customer Care','Retail Customer Care','Social Media')
for i in dep:
    FP_Attendance = FP_sheet[FP_sheet["Department"].str.contains(i)]
    FP_Attendance=FP_Attendance.style.set_properties(**{'color':'black','text-align': 'center','font-family': 'Arial Narrow','border-color':'Black','border-width':'thin','border-style':'solid','vertical-align': 'middle'})
    with pd.ExcelWriter(f"C:\Users\Mahmoud.Bader\Desktop\Python\Workforce Reports\FP Test\{i}.xlsx",engine='xlsxwriter') as writer:
        FP_Attendance.to_excel(writer,sheet_name=str(i), index=False,freeze_panes=(1,0))

Answer

Haven’t dealt with excel formatting yet, but maybe openpyxl will offer a more friendly interface than pandas.

Advertisement

Answer