BUG - to_excel() fails using xlsxwriter if the index uses local timestamps

[kay1793]

In [93]: ts= pd.Timestamp(datetime.datetime(2014, 1,1)).tz_localize(
    ...:     'America/New_York')
    ...: df=pd.DataFrame([1],index=[ts])
    ...: df
Out[93]: 
                           0
2014-01-01 00:00:00-05:00  1

In [94]: df.to_excel('/home/yank/2.xls')
TypeError: can't subtract offset-naive and offset-aware datetimes

I'm using 0.15RC1, dateutil: 2.2, pytz: 2014.7 and xlsxwriter 0.5.5 all fresh pip.

[jmcnamara]

Excel and as a result XlsxWriter doesn't support timezones in dates.

You could add a feature request for it to XlsxWriter but I'm wary of adding this since there is no support for it in Excel. There is already an open PR for this in XlsxWriter.

[kay1793]

That sounds right, but then the error message is not informative and it should still be possible to output the dates as a text string with tz offset, the same way they appear when they output to the terminal. MIgrating pandas types into excel types when outputting is nice but it's not a requirement.

It's not only a xlsxwriter problem actually, xlwt and xlsxwriter fail with this error and openpyxl creates the file but throws away the timezone information, so it actually silently corrupts the data. This is a little broken all around.

[jreback]

this issue has already been discussed in #7056, closing this as a dupe

[kay1793]

up2u @jreback, only please read my last comment.

[jreback]

@kay1793
just make a comment on the other issue. We don't need 2 issues for the exact same issue.