Copy Date Data from Pandas Series to Excel Result in Format Change

[DavidG4869]

Hi All,

I encounter a problem. So, I try to copy DATE data (<type 'str'>) over from pandas dataframe (<class 'pandas.core.series.Series'>) to Excel. However, this result in the date format changes to wrong date format (Ex: 11/03/2021 >> 03/11/2021) when copied over to Excel. Below is my code:

wb.sheets[0].range('A2').options(index=None, header=None).value = df

Any idea how to keep the source formatting (pandas)?

[fzumstein]

Please add a fully functional sample including pandas DataFrame.

[DavidG4869]

Hi @fzumstein,

Thanks for getting back to me. Below is the sample code:

import pandas as pd
import numpy as np
import xlwings as xw

data = np.array(['11/03/2021','12/03/2021','10/03/2021','27/01/2021','08/03/2021'])
testData = pd.Series(data)
print(testData)

wb = xw.Book(os.path.abspath('Test.xlsx'))
wb.sheets[0].range('A2').options(index=None, header=None).value = testData

Tested and only '27/01/2021' stay on the right format. The others datapoints format switched (see [attached)]

[fzumstein]

You will need to convert your column's dtype into datetime, see: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.to_datetime.html

[DavidG4869]

Hi @fzumstein

I try to convert the data into datetime but the output become epoch date format [See attached code and output below]

data = np.array(['11/03/2021','12/03/2021','10/03/2021','27/01/2021','08/03/2021'])
testData = pd.Series(data)

# Change String to datetime format
#finalData = pd.to_datetime(testData, format='%d%m%Y', errors='ignore')
finalData = pd.to_datetime(testData, infer_datetime_format=True)
#finalData = pd.to_datetime(testData, infer_datetime_format=False)

print(finalData)
wb = xw.Book(os.path.abspath('Test.xlsx'))
wb.sheets[0].range('A2').options(index=None, header=None).value = finalData

Output on Excel

I try couple methods I can find. Any solution so that only the dtype change to datetime but the format of the date stay the same when copied over to Excel?

[fzumstein]

I see, this is an edge case that isn't handled properly -- for now, you can set index=True or you could make a transformation on your end to come with a list of datetime objects. Something like this in your last line:

wb.sheets[0].range('A1').options(transpose=True).value = finalData.dt.date.tolist()

[DavidG4869]

Hi @fzumstein

Thanks for your help. The following is my solution:

data = np.array(['11/03/2021','12/03/2021','10/03/2021','27/01/2021','08/03/2021'])
testData = pd.Series(data)

# Change String to datetime format
finalData = pd.to_datetime(testData, format='%d%m%Y')

wb = xw.Book(os.path.abspath('Test.xlsx'))
wb.sheets[0].range('A2').options(index=True, header=None).value = finalData

# Delete the index afterwards
from xlwings.constants import DeleteShiftDirection
wb.sheets[0].range('A2: A100000').api.Delete(DeleteShiftDirection.xlShiftToLeft)

Just one last question, anyway to delete A2 row till the end of the A row? (This is for my real size data frame)

For now, I just put A2: A100000. Feel free to close the issue after this

Many Thanks, David

[fzumstein]

you could do wb.sheets[0].range('A2').expand('down').api...