How to remove index column when formatting user defined headers XLSXWriter and Pandas

Question

I was able to apply the correct formatting for the header row, but I have a blank index column at the beginning of my data even though I have index set to False. How to I get rid of that index column while still applying the header formatting?

    writer = pd.ExcelWriter(r'L:\Test.xlsx', 
                    engine='xlsxwriter',
                    datetime_format='mm/dd/yyyy'
                    )

    data3.to_excel(writer, index=False, sheet_name='TEST', startrow=1, 
    header = False)

    pandas.io.formats.excel.header_style = None

    #Get the xlsxwriter objects from the dataframe writer object.
    workbook  = writer.book
    worksheet = writer.sheets['TEST']

    ##Autofilter
    worksheet.autofilter('A1:W5000')

    #Add a header format.
    header_format = workbook.add_format({
        'font_color': 'white',
        'text_wrap': True,
        'font_name': 'Calibri',
        'font_size': 11,
        'fg_color': '#44546a'})

    # Write the column headers with the defined format.
    for col_num, value in enumerate(data3.columns.values):
        worksheet.write(0, col_num + 1, value, header_format)

    # Close the Pandas Excel writer and output the Excel file.
    writer.save()

It still looks like the below picture and I would like for column A to be the starting point instead of column B:

Incorrect: Incorrect

Correct: Correct

Answer 1

You need to change the column index in worksheet.write() from col_num +1 to col_num. Like this:

import pandas
from datetime import datetime

data3 = pandas.DataFrame({'Dates': [datetime(2019, 1, 1, 11, 30, 55),
                                    datetime(2019, 1, 2, 1,  20, 33),
                                    datetime(2019, 1, 3, 11, 10    ),
                                    datetime(2019, 1, 4, 16, 45, 35),
                                    datetime(2019, 1, 5, 12, 10, 15)]})

writer = pandas.ExcelWriter('test.xlsx', 
                            engine='xlsxwriter',
                            datetime_format='mm/dd/yyyy')

data3.to_excel(writer, index=False, sheet_name='TEST', 
               startrow=1, header = False)

pandas.io.formats.excel.header_style = None

#Get the xlsxwriter objects from the dataframe writer object.
workbook  = writer.book
worksheet = writer.sheets['TEST']

worksheet.set_column('A:A', 20)

#Add a header format.
header_format = workbook.add_format({
    'font_color': 'white',
    'text_wrap': True,
    'font_name': 'Calibri',
    'font_size': 11,
    'fg_color': '#44546a'})

# Write the column headers with the defined format.
for col_num, value in enumerate(data3.columns.values):
    worksheet.write(0, col_num, value, header_format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: