Xlsxwriter Custom Column Formatting issue

Question

I am sure there is a more efficient way to write the code but the issue i am having is in my dataframe 2 of my columns (dtypes are objects that represent dates in format yyyy-mm-dd). When I use xlsxWriter everything else is coming out exactly as i need except the two columns that have dates are 'custom' column types in excel and aren't formatted (align top nor is the size 8). The rest of the columns in excel are 'general' and formatting is correct. Any suggestions would be greatly appreciated

writer=pd.ExcelWriter("file.xlsx", engine='xlsxwriter')
df_summary.to_excel(writer, (sheet_name)='Sheet1', startrow=15, header=True, index=False)

workbook=writer.book
worksheet=writer.sheets['Sheet1']
worksheet.set_column('A:O', 15)  
worksheet.set_column('P:R', 30) 
workbook.formats[0].set_font_size(8)  
workbook.formats[0].set_text_wrap() 
workbook.formats[0].set_align('top') 
bg_format=workbook.add_format({'bg_color': '#cccccc'})
worksheet.set_row(15, cell_format=(bg_format))
writer.save()

Dimitris Thomas · Accepted Answer · 2020-04-24T08:52:52.370

If your columns with the dates are objects you firstly need to convert them to datetime. You then need to specify the default date format when you create the writer object with xlsxwriter (for more info have a look here).

Here is a working example to demonstrate this:

import pandas as pd

df = pd.DataFrame({'Date': ['04/23/2020','04/24/2020',
                            '04/25/2020','04/26/2020']})

df['Date'] = pd.to_datetime(df['Date'])

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter',
                        datetime_format='m/d/yyyy')

df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

writer.save()

EDIT

import pandas as pd

df = pd.DataFrame({'Date': ['04/23/2020','04/24/2020',
                            '04/25/2020','04/26/2020']})

df['Date'] = pd.to_datetime(df['Date'])

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

df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

cellFormat = workbook.add_format({'font_size': 8,
                            'font_name': 'Calibri',
                            'valign': 'top',
                            'align': 'left',
                            'num_format': 'm/d/yyyy'})

for row in range(1,5):
    worksheet.write(row, 0, df.iloc[row-1,0], cellFormat)

writer.save()

Thank you very much Dimitris for your response. That helped in the excel to have the column type as a date but the format of just that column is still the default Calibri 11, bottom aligned vs Calibri 8, top aligned. Is there a trick about applying workbook.formats to 'date' columns? thanks again! — jas, Apr 23 '20 at 22:41
Truth is that applying format through the set_column method is not going to work with date columns. No worries, you can still apply format though using a for loop and the write method. Check my answer, i edited it — Dimitris Thomas, Apr 24 '20 at 08:51
Thank you again Dimitris, I was wondering if I was doing something wrong. I tried to implement the for statement but seem to be getting stuck on how it actually uses the values in the cell. Will continue to try this on the weekend but if I get stuck hopefully can reach back out for assistance.. Thanks again. — jas, Apr 24 '20 at 21:11

Xlsxwriter Custom Column Formatting issue

1 Answers1