Set_column breaks on date formats in xlsxwriter for python

I am a new python user and I am trying to understand why set_column and conditional_format work differently when I feel they should work the same. Here is an example of my code for set column.

**Coloring does not work for dates**
 elif name == 'MTM valuation comparison':
        col1 = list(item.columns).index('Contract')
        col2 = list(item.columns).index('asOfDate.y')
        col3 = list(item.columns).index('Mean.of.Credit.Adj.Discounted.Settlement.Valuation.x')
        col4 = list(item.columns).index('Mean.of.Credit.Adj.Discounted.Settlement.Valuation.y')
        col5 = list(item.columns).index('Change')
        col6 = list(item.columns).index('startDate')
        col7 = list(item.columns).index('endDate')
        col8 = list(item.columns).index('Days.x')
        col9 = list(item.columns).index('Days.y')
        col10 = list(item.columns).index('Location')
        col11 = list(item.columns).index('asOfDate.x')
        col12 = list(item.columns).index('Notional.Quantity')
        
        worksheet.set_column(col1, col2, 14.71, date_color, None)
        worksheet.set_column(col3, col3, 49.86, X_colour_MTM, {'level':1,'hidden':True})
        worksheet.set_column(col4, col4, 49.86, Y_colour_MTM, {'level':1,'hidden':True})
        worksheet.set_column(col5, col5, 14.29, varianceColor_MTM, None)
        worksheet.set_column(col6, col7, 14.71, date_color, {'level':1,'hidden':True})
        worksheet.set_column(col8, col8, 6, X_colour_MTM, {'level':1,'hidden':True})
        worksheet.set_column(col9, col9, 6, Y_colour_MTM, {'level':1,'hidden':True})
        worksheet.set_column(col12, col12, 16.45, bold, {'level':1,'hidden':True})

What I am doing here is trying to apply a date color for col6 and col 7 ‘startDate’ and ‘endDate’. The issue is I am only able to provide a default color when we use worksheet.conditional_format.

Here is a snip of my code that works

    # header information
    for col_num, value in enumerate(item.columns.values):
        worksheet.write(0, col_num, value, header_format)
        worksheet.freeze_panes(1, 0)


        # adding shading
        elif value in ['asOfDate.x', 'asOfDate.y', 'maturityDate','startDate','endDate']:
            worksheet.conditional_format(f'{col_letter}{Startrow}:{col_letter}{EndRow}', {'type': 'top', 'format': date_color, 'value': str(EndRow)})

Here are my formatting inputs

varianceColor = workbook.add_format({'bg_color': '#ccccff','bold':True,'num_format':'#,##0.00'})
X_colour = workbook.add_format({'bg_color':'#ED7C31','bold':True,'num_format':'#,##0.00'})
Y_colour = workbook.add_format({'bg_color':'#FFFF99','bold':True,'num_format':'#,##0.00'})
varianceColor_MTM = workbook.add_format({'bg_color': '#ccccff','bold':True,'num_format':'#,##0'})
X_colour_MTM = workbook.add_format({'bg_color':'#ED7C31','bold':True,'num_format':'#,##0'})
Y_colour_MTM = workbook.add_format({'bg_color':'#FFFF99','bold':True,'num_format':'#,##0'})
bold = workbook.add_format({'bold':True,'bg_color':'#F0F0F0','num_format':'#,##0'})
date_color = workbook.add_format({'bg_color':'#F0F0F0','num_format':'mm/dd/yyyy'}) 
num_format = workbook.add_format({'num_format':'#,##0'})

The really strange thing that I am trying to understand is why specifically it doesn’t work for dates when I use the set column but for other things set column doesn’t provide the correct formatting. This is the only place I am initialing these formats in my code, please advise on what mistake I am making. Thank you

Answer

Just circling back on this. So after doing a good amount of research I believe I have found the answer. The issue here is when you are converting dates to DateTime via pandas it will override the column/row formatting that you overlay. The reason this happens is that in Excel a cell format overrides a column format and Pandas is using a format for DateTime objects. Hence the cell date format is overriding your column format.

As far as I know, it isn’t possible to tell Pandas not to use that DateTime format.

The answer that I discovered after much searching converts the DateTime objects in the data frame column to an Excel serial number using an XlsxWriter function. It then formats it using a number format (which is effective in what date is in Excel).

Assuming you are working with more than one date you can implement the below when setting up your data frame.

[datetime_to_excel_datetime(x, False, False) for x in final_df['asOfDate']]

I am seeing that by using this function you will now let excel know the respect for the column formatting and solves my initial issue above. I hope this helps future inquirers!

Advertisement

Answer