How do I read Excel file in Python?

[John Machin]

Giles Brown wrote:
> 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.
>
> John,
> Just for me own curiosity, is this Excel concept of date numbers same
> as the OLE
> concept (see http://msdn2.microsoft.com/en-us/library/82ab7w69.aspx or
> search "MFC DATE" on MSDN).
>
> I put in some test cases for conversion code here:
>    http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/496683
>
> But would be interested to add any additional info on variations on
> this theme.
>

Hi Giles,

Those OLE date numbers coincide with Excel 1900-system date numbers
from 1900-03-01 onwards. Excel treats day 60 as the non-existent
1900-02-29.

Cheers,
John