Changing header format python pandas to excel

There are a few things you will need to do to get this working.

The first is to avoid the Pandas default header since that will set a cell header which can't be overwritten with set_row(). The best thing to do is to skip the default header and write your own (see Formatting of the Dataframe headers section of the XlsxWriter docs).

Secondly, dates in Excel are formatted numbers so you will need to convert the string header into a number, or better to a datetime object (see the Working with Dates and Time section of the docs).

Finally '1899-12-30' isn't a valid date in Excel.

Here is a working example with some of these fixes:

import pandas as pd
from datetime import datetime

data = pd.DataFrame({'2020-09-18 12:30:00': [1.5, 2.5, 3.5, 4.5, 5.4]})

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Turn off the default header and skip one row to allow us to insert a user
# defined header.
data.to_excel(writer,
              sheet_name='Sheet1', index=True,
              startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Add a header format.
date_fmt = workbook.add_format({'num_format': 'dd.mm.yyyy  hh:mm:ss'})

# Convert the column headers to datetime objects and write them with the
# defined format.
for col_num, value in enumerate(data.columns.values):

    # Convert the date string to a datetime object.
    date_time = datetime.strptime(value, '%Y-%m-%d %H:%M:%S')

    # Make the column wider for clarity.
    worksheet.set_column(col_num + 1, col_num + 1, 20)

    # Write the date.
    worksheet.write(0, col_num + 1, date_time, date_fmt)

writer.save()

Output: