Python xlsxwriter. Header_format sub-property of columns parameter not working as expected

Question

I need to set a table header in Excel with the next date format: 'mmm-yy'. Formatting I've set:

title_date_format = workbook.add_format({
    'text_wrap': True,
    'font_size': 11,
    'num_format': 'mmm-yy'
})

Column settings:

column_settings = []
index = 0
for column in df.columns:    
    if index < 3:
        dct = {}
        dct['header'] = column
        column_settings.append(dct)       
    else:
        dct = {}                
        formula = '=[@[Value]]*[@Qty]'      
        dct['header'] = column
        dct['formula'] = formula
        dct['header_format'] = title_date_format
        column_settings.append(dct)
    index += 1

Table creation:

# Create a table
worksheet.add_table(0, 0, max_row + 2, max_col - 1, {
     'columns': column_settings
})

The problem is that only the 'text_wrap' and font_size' sub-properties work fine. The column header, which is a date, stays in '1/24/2022' format instead of 'Jan-22', so 'num_format': 'mmm-yy' doesn't apply.

Full example:

import datetime as dt
import pandas as pd
import numpy as np
import xlsxwriter

initial_data = {
    'Category': ['catA', 'catB', 'catC', 'catC'],
    'Item': ['item1', 'item2', 'item3', 'item4']
}
df = pd.DataFrame(initial_data)
# Add columns with month-year
for year in range(2,4):
    if year == 2:
        for month in range(11,13):
            date_str = str(month) + '/1/202' + str(year)
            df[date_str] = ''
    else:
        for month in range(1,4):
            date_str = str(month) + '/1/202' + str(year)
            df[date_str] = ''

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

title_date_format = workbook.add_format({
        'text_wrap': True,
        'font_name': 'Calibri',
        'font_size': 10,
        'num_format': 'mmm-yy'
    })
column_settings = []
for column in df.columns:    
    dct = {}
    dct['header'] = column
    dct['header_format'] = title_date_format
    column_settings.append(dct)

(max_row, max_col) = df.shape
worksheet.add_table(0, 0, max_row, max_col - 1, {
        'columns': column_settings, 
        'style': 'Table Style Light 9'
    })
writer.save()

Any ideas on how to make it work?

Thank you

Could you convert this into a full working example that demonstrates the issue. — jmcnamara, Jul 05 '22 at 13:56
@jmcnamara, full working example has been provided above. Thank you — EugLP, Jul 07 '22 at 10:10

score 0 · Answer 1 · answered Jul 08 '22 at 14:36

The issue is that the column headers are strings and the date number format only applies to numbers. So the solution would be to turn the column headers into datetime numbers so that the format can be applied. However, as far as I can see Table column headers in Excel need to be strings, so that isn't an option.

So as a workaround you could format the header strings that you are currently using into the format that you want:

# ...
from datetime import datetime

# ...

for year in range(2,4):
    if year == 2:
        for month in range(11,13):
            date_str = datetime(2022, month, 1).strftime("%b-%y")
            df[date_str] = ''
    else:
        for month in range(1,4):
            date_str = datetime(2024, month, 1).strftime("%b-%y")
            df[date_str] = ''

Output:

Python xlsxwriter. Header_format sub-property of columns parameter not working as expected

1 Answers1