Note: Want to align all columns in the export? See our followup post How to center-align all columns in pandas XLSX export
When exporting a DataFrame in pandas, often you want to center a column in order to make the resulting table visually more appealing.
In this post we’ll show how to do this using the openpyxl engine.
If you want to center column B, add this code within the pd.ExcelWriter with: block:
for cell in sheet["B"]:
cell.alignment = Alignment(horizontal="center")Full example:
import pandas as pd
from openpyxl.styles.alignment import Alignment
df = pd.DataFrame([
{"a": 1.0},
{"a": 2.0},
{"a": 3.0},
{"a": 4.0},
{"a": 5.0},
])
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
sheet_name = "Bool"
# Export DataFrame content
df.to_excel(writer, sheet_name=sheet_name)
# Align to center
sheet = writer.sheets[sheet_name]
# Align every cell in column "B" horizontally
for cell in sheet["B"]:
cell.alignment = Alignment(horizontal="center")The table will look like this with the centering enabled:
whereas the table is right-aligned with only the title centered by Pandas with the centering code disabled:
