How align content in every cell to center?

Question

I'm trying to center the table content using df.style.set_properties(**{'text-align': 'center'}). But I couldn't do it. Is there any other way?

Here is the full code:

import pandas as pd
import time


subject = 'demo'
checked_names = ['First','Second']
checked_ID = [64,65]
index =[i+1 for i in range(len(checked_names))]
df = pd.DataFrame(list(zip(index, checked_names, checked_ID)), columns=['Index','Student Name','Student_ID'])

path = (r"C:\Users\heman\Desktop\Attendance_list")
file_name = (path  + "/" + subject +"("+time.strftime("%d.%m.%Y_%H.%M")+")"+ "_" + "attendance.xlsx")
sheet_name = "sheet1"

writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
df.to_excel(writer, sheet_name=sheet_name, startrow = 4, index = False)

for column in df:
    column_width = max(df[column].astype(str).map(len).max(), len(column))
    col_idx = df.columns.get_loc(column)
    writer.sheets[sheet_name].set_column(col_idx, col_idx, column_width)

workbook = writer.book
worksheet = writer.sheets[sheet_name]
worksheet.write(0, 0, 'Attendance report of '+subject+' class', workbook.add_format({'bold': True, 'color': '#E26B0A', 'size': 14, 'align': 'left'}))
worksheet.write(2, 0, 'Date: '+time.strftime("%d/%m/%Y"))
worksheet.write(2, 3, 'Time: '+time.strftime("%r"))

df.style.set_properties(**{'text-align': 'center'})

writer.save()

Here is the excel file after executing

Answer 1

I usually use openpyxl for alignments or Excel formatting operations.

You could run something similar to this at the end of your code ( adapting the filename path, sheet name and desired cells you wish to align)

from openpyxl import load_workbook

from openpyxl.styles import Alignment

wb = load_workbook(filename = r'Attendance_list.xlsx')

mysheet = wb['Sheet1']


for row in range(1,mysheet.max_row+1):
    for col in range(1,mysheet.max_column+1):
        cell=mysheet.cell(row, col)
        cell.alignment = Alignment(horizontal='center', vertical='center')
        
wb.save(r'Aligned.xlsx')