如何使用``xlrd.xldate_as_tuple（）`` [英] How to use ``xlrd.xldate_as_tuple()``

问题描述

我不太清楚如何使用以下功能：

I am not quite sure how to use the following function:

xlrd.xldate_as_tuple

为以下数据

xldate:39274.0
xldate:39839.0

有人可以给我一个使用函数的例子对于数据？

Could someone please give me an example on usage of the function for the data?

推荐答案

Quoth 文档：

Excel电子表格中的日期

在现实中，没有这样的事情。
你有浮点
数字和虔诚的希望。有
Excel日期的几个问题：

Dates in Excel spreadsheets

In reality, there are no such things. What you have are floating point numbers and pious hope. There are several problems with Excel dates:

（1）日期不存储为单独的
数据类型;它们被存储为浮动
点数，您必须依赖
（a）在Excel中应用于
的数字格式和/或（b）知道哪些
细胞应该有
的日期。该模块有助于（a）通过
检查已将
应用于每个数字单元格的格式;如果
似乎是一个日期格式，单元格
被分类为一个日期而不是一个
数字。对于此功能的反馈，
特别是来自非英语的
语言环境，将不胜感激。

(1) Dates are not stored as a separate data type; they are stored as floating point numbers and you have to rely on (a) the "number format" applied to them in Excel and/or (b) knowing which cells are supposed to have dates in them. This module helps with (a) by inspecting the format that has been applied to each number cell; if it appears to be a date format, the cell is classified as a date rather than a number. Feedback on this feature, especially from non-English-speaking locales, would be appreciated.

（2）Excel for Windows存储日期由
默认为
1899-12-31T00：00：00之后的天数（或
分数）。 Excel for
Macintosh使用默认开始日期
1904-01-01T00：00：00。日期系统
可以在
每个工作簿的基础上更改（例如：工具
- >选项 - >计算，勾选1904日期系统框）。如果工作簿中已经有
日期，这是
课程是一个坏主意。即使
在工作簿中没有日期，也没有
有理由更改它。哪个
日期系统正在使用中记录在
的工作簿中。从
主机Excel运行
从Windows到Macintosh的工作簿（或副$ b $反之亦然）将正常运行。当使用此模块的
xldate_as_tuple函数从工作簿转换
数字时，您必须使用
Book
对象的datemode属性。如果您猜测，或者根据
相信工作簿的创建位置作出
的判断，您可以从
kilter中冒出1462天的风险。

(2) Excel for Windows stores dates by default as the number of days (or fraction thereof) since 1899-12-31T00:00:00. Excel for Macintosh uses a default start date of 1904-01-01T00:00:00. The date system can be changed in Excel on a per-workbook basis (for example: Tools -> Options -> Calculation, tick the "1904 date system" box). This is of course a bad idea if there are already dates in the workbook. There is no good reason to change it even if there are no dates in the workbook. Which date system is in use is recorded in the workbook. A workbook transported from Windows to Macintosh (or vice versa) will work correctly with the host Excel. When using this module's xldate_as_tuple function to convert numbers from a workbook, you must use the datemode attribute of the Book object. If you guess, or make a judgement depending on where you believe the workbook was created, you run the risk of being 1462 days out of kilter.

参考：
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q180162

（3）Excel实现的
Windows默认基于1900的日期系统
工作在错误的前提下，
1900是一个闰年。它解释
的第60个意思是1900-02-29，
这不是一个有效的日期。
因此，任何少于61
的数字是不明确的。例如：是直接输入1900-02-28的
结果，
还是1900-03-01减去2天？
OpenOffice.org Calc程序纠正
Microsoft问题;输入
1900-02-27导致编号59为
存储。另存为XLS文件，然后使用Excel打开
文件 - 您将看到
1900-02-28显示。

(3) The Excel implementation of the Windows-default 1900-based date system works on the incorrect premise that 1900 was a leap year. It interprets the number 60 as meaning 1900-02-29, which is not a valid date. Consequently any number less than 61 is ambiguous. Example: is 59 the result of 1900-02-28 entered directly, or is it 1900-03-01 minus 2 days? The OpenOffice.org Calc program "corrects" the Microsoft problem; entering 1900-02-27 causes the number 59 to be stored. Save as an XLS file, then open the file with Excel -- you'll see 1900-02-28 displayed.

参考：
http://support.microsoft.com/default .aspx？scid = kb; en-us; 214326

我在这里引用的是因为你的问题的答案是可能是错误的，除非你考虑到这一点。

which I quote here because the answer to your question is likely to be wrong unless you take that into account.

所以把它放入代码将是这样的：

So to put this into code would be something like:

import datetime
import xlrd

book = xlrd.open_workbook("myfile.xls")
sheet = book.sheet_by_index(0)
cell = sheet.cell(5, 19) # type, <class 'xlrd.sheet.Cell'>


if sheet.cell(5, 19).ctype == 3: # 3 means 'xldate' , 1 means 'text'
    ms_date_number = sheet.cell_value(5, 19) # Correct option 1
    ms_date_number = sheet.cell(5, 19).value # Correct option 2

    year, month, day, hour, minute, second = xlrd.xldate_as_tuple(ms_date_number, 
        book.datemode)
    py_date = datetime.datetime(year, month, day, hour, minute, nearest_second)

它为您提供了 py_date 中的Python日期时间，您可以在使用标准 datetime 模块。

which gives you a Python datetime in py_date that you can do useful operations upon using the standard datetime module.

我从来没有使用xlrd，而我的例子是完全弥补的，但是如果有一个 myfile.xls ，并且它在单元格F20中真的有一个日期数字，并且你不太关注精度以上，这段代码应该可以工作。

I've never used xlrd, and my example is completely made up, but if there is a myfile.xls and it really has a date number in cell F20, and you aren't too fussy about precision as noted above, this code should work.

这篇关于如何使用``xlrd.xldate_as_tuple（）``的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！