python - 如何在格式化用户定义标题 XLSXWriter 和 Pandas 时删除索引列

Question

我能够对标题行应用正确的格式，但即使我将索引设置为 False，数据开头也有一个空白索引列。如何在仍然应用标题格式的同时摆脱该索引列？

    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()

它仍然如下图所示，我希望 A 列作为起点而不是 B 列:

错误: Incorrect

正确: Correct

Answer 1

您需要将 worksheet.write() 中的列索引从 col_num +1 更改为 col_num。像这样:

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()

输出: