Date Column overrides border formatting when using Pandas Excel Writer

user_14463396 0
Viewed 304

This has been bugging me a bit because I don't know quite why it's happening. I haven't been able to find anyone else with this problem, so hoping this is not a duplicate. Basically when exporting a pandas data frame to Excel with formatting, my date column seems to override my border formatting. See some example code below:

import numpy as np
import pandas as pd

#Example dataframe
df = pd.DataFrame({'Col 1' : [1, 1, 1, 1, 1, 1],
                   'Date' : [np.nan, '2021-09-23', '2021-09-23', np.nan, '2021-09-23', np.nan],
                   'Col 2' : [2, 2, 2, 2, 2, 2]})
df['Date'] = pd.to_datetime(df['Date'])

#Writing to excel
writer = pd.ExcelWriter('Example.xlsx', datetime_format = 'dd/mm/yyyy')
df.to_excel(writer, 'Sheet 1', index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet 1']

center = workbook.add_format({'align' : 'center'})
center_rborder = workbook.add_format({'align' : 'center', 'right' : True})

worksheet.set_column('A:A', 10, center_rborder)
worksheet.set_column('B:B', 10, center_rborder)
worksheet.set_column('C:C', 10, center_rborder)

writer.save()

The below is what the resulting spreadsheet looks like. As you can see, for the date column the right border has gaps whenever there is a date there:

enter image description here

If anyone knows why this happens and/or any way to prevent this, that would be great.

xlsxwriter pandas excel python
1 Answers

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 the that datetime format.

In this particular case you can work around the issue by setting a left border as well as the right border:

center_rborder = workbook.add_format({'align' : 'center', 'right' : True, 'left': True})

Output:

enter image description here

Update:

Here is another approach that converts the datetime objects in the dataframe column to an Excel serial number using an XlsxWriter function. It then formats it using a number format (which is effectively what a date is in Excel).

import pandas as pd
from xlsxwriter.utility import datetime_to_excel_datetime

# Example dataframe
df = pd.DataFrame({'Col 1' : [1, 1, 1, 1, 1, 1],
                   'Date' : ['2021-09-23'] * 6,
                   'Col 2' : [2, 2, 2, 2, 2, 2]})
df['Date'] = pd.to_datetime(df['Date'])
df['Date'] = [datetime_to_excel_datetime(x, False, False) for x in df['Date']]

# Writing to excel
writer = pd.ExcelWriter('Example.xlsx', datetime_format = 'dd/mm/yyyy')
df.to_excel(writer, 'Sheet 1', index=False)

workbook = writer.book
worksheet = writer.sheets['Sheet 1']

center = workbook.add_format({'align' : 'center'})
center_rborder = workbook.add_format({'align' : 'center', 'right' : True})
center_rborder_date = workbook.add_format({'align' : 'center',
                                           'right' : True,
                                           'num_format':'dd/mm/yyyy'})

worksheet.set_column('A:A', 10, center_rborder)
worksheet.set_column('B:B', 10, center_rborder_date)
worksheet.set_column('C:C', 10, center_rborder)

writer.save()

The output is the same as the image above.

Note, that I avoided handling the np.nan elements in this example for the sake of simplicity. Also, I fixed a minor syntax error (that was ignored) in the set_column() ranges.

user_102380
Related