how to hide gridlines and autofit columns width in excel by python

Question

i'm trying to hide the gridlines and autofit columns width in the alterable excel files but i can't find the correct code

code:

FP_sheet=pd.read_excel(r"C:\Users\Mahmoud.Bader\Desktop\FP 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'})
FP_Attendance.style.hide_index()
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 1

import pandas as pd

FP_sheet = pd.read_excel(r"C:\Users\Mahmoud.Bader\Desktop\FP Attendance V1.6 Apr 22.xlsx", "Attendance").fillna("")
FP_sheet['Date'] = pd.to_datetime(FP_sheet['Date']).dt.date

dep = FP_sheet["Department"].unique()

for i in dep:
FP_Attendance = FP_sheet[FP_sheet["Department"].str.contains(i)]

# Create ExcelWriter object with openpyxl engine
writer = pd.ExcelWriter(f"C:\\Users\\Mahmoud.Bader\\Desktop\\Python\\Workforce Reports\\FP Test\\{i}.xlsx",
engine='openpyxl')

FP_Attendance.to_excel(writer, sheet_name=str(i), index=False, freeze_panes=(1, 0))

# Get the worksheet object
worksheet = writer.sheets[str(i)]

# Hide gridlines
worksheet.sheet_view.showGridLines = False

# Autofit column widths
for column_cells in worksheet.columns:
length = max(len(str(cell.value)) for cell in column_cells)
worksheet.column_dimensions[column_cells[0].column_letter].width = length + 2

writer.save()