如何在Python中以Excel格式读取日期？

Question

如何在Python中将Excel日期（以数字格式）转换为正确的日期？

Answer 1

日期始终存储为数字，但是，您可以xldate_as_tuple将其转换为python日期。

Answer 2

import datetime

def minimalist_xldate_as_datetime(xldate, datemode):

    # datemode: 0 for 1900-based, 1 for 1904-based

    return (

        datetime.datetime(1899, 12, 30)

        + datetime.timedelta(days=xldate + 1462 * datemode)

        )

Answer 3

经过测试并等待几天的反馈后，我将在xlrd的xldate模块中svn-commit-commit以下新功能……请注意，仍在运行Python 2.1或2.2的顽固派将无法使用该功能。

##

# Convert an Excel number (presumed to represent a date, a datetime or a time) into

# a Python datetime.datetime

# @param xldate The Excel number

# @param datemode 0: 1900-based, 1: 1904-based.

# <br>WARNING: when using this function to

# interpret the contents of a workbook, you should pass in the Book.datemode

# attribute of that workbook. Whether

# the workbook has ever been anywhere near a Macintosh is irrelevant.

# @return a datetime.datetime object, to the nearest_second.

# <br>Special case: if 0.0 <= xldate < 1.0, it is assumed to represent a time;

# a datetime.time object will be returned.

# <br>Note: 1904-01-01 is not regarded as a valid date in the datemode 1 system; its "serial number"

# is zero.

# @throws XLDateNegative xldate < 0.00

# @throws XLDateAmbiguous The 1900 leap-year problem (datemode == 0 and 1.0 <= xldate < 61.0)

# @throws XLDateTooLarge Gregorian year 10000 or later

# @throws XLDateBadDatemode datemode arg is neither 0 nor 1

# @throws XLDateError Covers the 4 specific errors

def xldate_as_datetime(xldate, datemode):

    if datemode not in (0, 1):

        raise XLDateBadDatemode(datemode)

    if xldate == 0.00:

        return datetime.time(0, 0, 0)

    if xldate < 0.00:

        raise XLDateNegative(xldate)

    xldays = int(xldate)

    frac = xldate - xldays

    seconds = int(round(frac * 86400.0))

    assert 0 <= seconds <= 86400

    if seconds == 86400:

        seconds = 0

        xldays += 1

    if xldays >= _XLDAYS_TOO_LARGE[datemode]:

        raise XLDateTooLarge(xldate)

    if xldays == 0:

        # second = seconds % 60; minutes = seconds // 60

        minutes, second = divmod(seconds, 60)

        # minute = minutes % 60; hour    = minutes // 60

        hour, minute = divmod(minutes, 60)

        return datetime.time(hour, minute, second)

    if xldays < 61 and datemode == 0:

        raise XLDateAmbiguous(xldate)

    return (

        datetime.datetime.fromordinal(xldays + 693594 + 1462 * datemode)

        + datetime.timedelta(seconds=seconds)

        )