Pandas DataFrame to Excel cell alignment

Question

I noticed that for string in Dataframe will keep left align in Excel and for numerical value will keep right align in Excel.

How do we set the desired alignment we wanted when exporting DataFrame to Excel? Example: Center Alignment

df = pd.DataFrame({"colname1": ["a","b","c","d"], "colname2": [1,2,3,4]})

with pd.ExcelWriter("test.xlsx") as writer:
    df.to_excel(
        writer,
        index=False,
        header=False,
    )

Answer 1

You can set the styles of the dataframe using the Styler object, which uses the same conventions as CSS. The documentation has a great primer on the different ways of styling your dataframes.

For a simple solution to your example, you can set the desired alignment by first creating a function:

def align_center(x):
    return ['text-align: center' for x in x]

Then write it to Excel while applying the function you just defined:

with pd.ExcelWriter("test.xlsx") as writer:
    df.style.apply(align_center, axis=0).to_excel(
        writer,
        index=False,
        header=False
    )

This will center-align the cells in the Excel file. For an exhaustive list of available text alignment options I would suggest the MDN docs.

Answer 2

Or even better with just set_properties:

with pd.ExcelWriter("test.xlsx") as writer:
    df.style.set_properties(**{'text-align': 'center'}).to_excel(
        writer,
        index=False,
        header=False,
    )

This aligns all text to the center.

This will work because it sets the property text-align to center. Then with the context manager instance (with statement), it can write to an excel file.