convert xldate to python datetime

Question

I have a problem converting excel xldate to python datetime with the following code. Is this the right way to convert?

import xlrd
from datetime import datetime

book = xlrd.open_workbook("a.xls")
sh = book.sheet_by_index(0)
for rx in range(1,sh.nrows):
    a = sh.row(rx)
    print a  
    year, month, day, hour, minute = xlrd.xldate_as_tuple(a[0], book.datemode)
    py_date = datetime.datetime(year, month, day, hour, minute)

a is printed -->

   [xldate:40544.0, number:0.0, number:75.49847785316135, number:75.6401124106301]

The below error is shown

  year, month, day, hour, minute = xlrd.xldate_as_tuple(a[0], book.datemode)

  File "C:\Anaconda\Lib\site-packages\xlrd\xldate.py", line 67, in xldate_as_tuple
    xldays = int(xldate)
  TypeError: int() argument must be a string or a number, not 'Cell'

Don't out answers in the question; you can answer your own question if you figure it out in the meantime. — jonrsharpe, Sep 24 '14 at 07:18

score 23 · Accepted Answer · edited May 23 '17 at 12:34

a[0] is a xlrd.Cell, so you need to use a[0].value to access the cell content.

Also, you can't unpack to year, month, day, hour, minute because there are more than five values in the tuple produced (what about second?) However, you can avoid that by using tuple unpacking (see e.g. What does ** (double star) and * (star) do for parameters?) instead of the temporary variables:

py_date = datetime.datetime(*xlrd.xldate_as_tuple(a[0].value,
                                                  book.datemode))

score 22 · Answer 2 · answered Jul 29 '15 at 06:47

22

@jonrsharpe's accepted answer works well. But this is cleaner:

py_date = xlrd.xldate.xldate_as_datetime(a[0].value, book.datemode)

answered Jul 29 '15 at 06:47

dopstar

1,478
10
20

convert xldate to python datetime

2 Answers2

Linked