8

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,
    )
Raymond Toh
  • 779
  • 1
  • 8
  • 27
  • Strings at left and numerical at right are the default behaviour in Excel. To change format i think [this answer](https://stackoverflow.com/questions/41364380/pandas-dataframe-to-excel-vertical-alignment-of-index) may be helpful and because it's outdated, also check [this one](https://stackoverflow.com/questions/42234622/pandas-raising-attributeerror-module-pandas-core-has-no-attribute-format) – Foxfire And Burns And Burns Sep 21 '21 at 08:17

2 Answers2

6

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.

vtasca
  • 1,660
  • 11
  • 17
  • in this case `applymap` would be simpler: `df.style.applymap(lambda _: 'text-align: center').to_excel(...)` – tdy Sep 23 '21 at 16:43
3

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.

U13-Forward
  • 69,221
  • 14
  • 89
  • 114