How do I read Excel file in Python?

kath nitte.sudhir at gmail.com
Fri Oct 6 12:34:27 CEST 2006


John Machin wrote:
> houdinihound at yahoo.com wrote:
> > > > >>> excel_date = 38938.0
> > > > >>> python_date = datetime.date(1900, 1, 1) + datetime.timedelta(days=excel_date)
> > > > >>> python_date
> > > > datetime.date(2006, 8, 11)
> > >
> > > Err, that's the wrong answer, isn't it? Perhaps it shoud be
> > > datetime.date(1900, 1, 29)?
> >
> > Actually was about to post same solution and got same results. (BTW
> > Simon, the OP date is Aug 9th, 2006).  Scratched head and googled for
> > excel date calculations... found this bug where it treats 1900 as leap
> > year incorrectly:
> > http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
> >
> > Plus it treats 1 jan 1900 as day 1, not 0 so just subtract 2 in the
> > calc:
> > >>>python_date = datetime.date(1900, 1, 1) + datetime.timedelta(days=excel_date - 2)
> > >>> python_date
> > datetime.date(2006, 8, 9)
> >
>
> ... and 2006-08-09 is only the correct answer if the spreadsheet was,
> when last saved, using the 1900 ("Windows") date system, not the 1904
> ("Macintosh") date system.
>
> All the OP needs to do is to read the documentation that comes with the
> xlrd package. It describes the problems with Excel dates, and offers
> functions for conversion between the Excel date numbers and (year,
> month, day, hour, minute, second) tuples which of course are
> interoperable with Python's datetime module and with mx.DateTime.
>
> | >>> import xlrd
> | >>> xlrd.xldate_as_tuple(38938.0, 0)
> | (2006, 8, 9, 0, 0, 0)
> | >>> xlrd.xldate_as_tuple(38938.0, 1)
> | (2010, 8, 10, 0, 0, 0)
> | >>>
>
> Cheers,
> John



Hi,

>>> import xlrd
>>> book = xlrd.open_workbook("testbook1.xls")
>>> sh = book.sheet_by_index(0)
>>> sh.cell_value(rowx=1,colx=0)
38938.0
>>> type(sh.cell_value(rowx=1,colx=0))
<type 'unicode'>
>>> xlrd.xldate_as_tuple( sh.cell_value( rowx = 1,colx= 0 ), 0 )

Traceback (most recent call last):
  File "D:\Python23\Testing area\Python and Excel\xlrdRead.py", line
30, in ?
    temp=xlrd.xldate_as_tuple(sh.cell_value(rowx=r,colx=c),0)
  File "D:\PYTHON23\Lib\site-packages\xlrd\xldate.py", line 61, in
xldate_as_tuple
    xldays = int(xldate)
ValueError: invalid literal for int(): Date

because xlrd.xldate_as_tuple() function expects first argument to be an
integer. How do I convert an unicode character to integer, so that I
could get the date using xlrd.xldate_as_tuple() function.

Thank you,
sudhir.




More information about the Python-list mailing list