ISSUE with set_row format and columns with dates

[jmhutchi]

Hi,

I am using XlsxWriter to do write a pandas dataframe and change the background color of rows but it appears to NOT change background color of the column with datetime data. All other columns in the rows that I have set a color format are properly colored.

I am using Python version 2.7.6 and XlsxWriter 0.8.4 and Excel version 2013, pandas 0.17.1

test.xlsx

Here is some code that demonstrates the problem:

import os
import pandas as pd
path = r'.'
xlsfname = 'test.xlsx'

df = pd.DataFrame({'date' :pd.date_range('01/05/2016','01/10/2016'),
                    'data': pd.Series(1,index=list(range(6)),dtype='float32')                   
                   })
writer = pd.ExcelWriter(os.path.join(path,xlsfname), engine='xlsxwriter')
df.to_excel(writer,'wip',index=False)
workbook = writer.book
wipWKS = writer.sheets['wip']
#set up format for highlighted rows
hform1 = workbook.add_format()
hform1.set_pattern(1)
hform1.set_bg_color('#DDD9C4') # dark tan
wipWKS.set_column('B:B',18)
wipWKS.set_row(3,None,hform1)
workbook.close()

[jmhutchi]

I think this is covered in the xlsxwriter documentation for pandas. I guess one cannot change datetime column formatting, unfortunately.

https://xlsxwriter.readthedocs.org/working_with_pandas.html

[jmcnamara]

I think this is covered in the xlsxwriter documentation for pandas. I guess one cannot change datetime column formatting, unfortunately.

Correct, unfortunately. The cell formatting used in Pandas for the datetimes overrides the row formatting.