DataFrame写入Excel的日期格式修改及垂直对齐问题咨询

Q1: Why didn't add_format work for modifying the date format? #

The core issue here is Excel's format precedence rule: cell-level formats always override column-level formats. When you run df.to_excel() with the xlsxwriter engine, pandas automatically applies a default cell-level datetime format to every cell in your Date column. Even though you defined a column-level format with worksheet.set_column(), Excel ignored it because each cell already had an explicit format assigned by pandas.

Your fix using datetime_format='yyyy/mm/dd' in ExcelWriter worked because this parameter tells pandas to use your custom format instead of the default one when writing cell-level formats. This way, every date cell gets your desired format directly, no column-level override needed.

If you still wanted to use add_format instead, you’d need to prevent pandas from applying its default datetime formatting (e.g., converting the datetime column to Excel serial numbers first), but using the datetime_format parameter is far cleaner.

Q2: How to set vertical alignment when set_align('vcenter') doesn’t work? #

You ran into a simple method mix-up:

• set_align() in xlsxwriter’s Format class controls horizontal alignment (left, center, right, etc.).
• For vertical alignment, you need to use set_vert_align('vcenter') or specify the 'valign' key when creating the format.

Here are two reliable ways to add vertical center alignment alongside your desired date format:

Option 1: Combine datetime_format with column-level vertical alignment

Since pandas doesn’t set vertical alignment by default, you can use datetime_format for the date format and apply vertical alignment via a column format:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter', datetime_format='yyyy/mm/dd')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Create a format for vertical alignment
vert_format = workbook.add_format({'valign': 'vcenter'})
# Apply to the Date column (adjust column letter if needed)
worksheet.set_column('A:A', 15, vert_format)

writer.close()

Option 2: Single custom format for both date and alignment

If you want to handle everything in one format, you can overwrite pandas’ default cell formatting by applying your combined format directly to each cell:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Create a format with both date format and vertical alignment
custom_format = workbook.add_format({
    'num_format': 'yyyy/mm/dd',
    'valign': 'vcenter'
})

# Apply the format to every cell in the Date column (skip header row)
for row_num in range(1, len(df) + 1):
    worksheet.write(row_num, 0, df['Date'].iloc[row_num - 1], custom_format)

writer.close()

内容的提问来源于stack exchange，提问作者Sora Liu