Python 如何使用``xlrd.xldate_as_tuple()``
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3727916/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use ``xlrd.xldate_as_tuple()``
提问by
I am not quite sure how to use the following function:
我不太确定如何使用以下功能：
xlrd.xldate_as_tuple
for the following data
对于以下数据
xldate:39274.0
xldate:39839.0
Could someone please give me an example on usage of the function for the data?
有人可以给我一个关于数据函数使用的例子吗？
采纳答案by msw
Quoth the documentation:
引用文档：
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) 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 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.
Reference: http://support.microsoft.com/default.aspx?scid=KB;EN-US;q180162
(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.
Reference: http://support.microsoft.com/default.aspx?scid=kb;en-us;214326
Excel 电子表格中的日期
实际上，没有这样的事情。你拥有的是浮点数和虔诚的希望。Excel日期有几个问题：
(1) 日期不作为单独的数据类型存储；它们存储为浮点数，您必须依赖于 (a) 在 Excel 中应用于它们的“数字格式”和/或 (b) 知道哪些单元格中应该包含日期。该模块通过检查已应用于每个数字单元格的格式来帮助 (a)；如果它看起来是日期格式，则该单元格被归类为日期而不是数字。对此功能的反馈，尤其是来自非英语语言环境的反馈，将不胜感激。
(2) Excel for Windows 默认将日期存储为自 1899-12-31T00:00:00 以来的天数（或其分数）。Excel for Macintosh 使用默认开始日期 1904-01-01T00:00:00。可以在 Excel 中针对每个工作簿更改日期系统（例如：工具 -> 选项 -> 计算，勾选“1904 日期系统”框）。如果工作簿中已经有日期，这当然是一个坏主意。即使工作簿中没有日期，也没有充分的理由更改它。工作簿中记录了正在使用的日期系统。从 Windows 传输到 Macintosh（反之亦然）的工作簿将与宿主 Excel 一起正常工作。使用此模块的 xldate_as_tuple 函数从工作簿转换数字时，必须使用 Book 对象的 datemode 属性。
参考：http: //support.microsoft.com/default.aspx?scid=KB; EN-US; q180162
(3) Windows 默认的基于 1900 年的日期系统的 Excel 实现工作在错误的前提下，即 1900 年是闰年。它将数字 60 解释为 1900-02-29，这不是有效日期。因此，任何小于 61 的数字都是不明确的。例子：59是直接输入1900-02-28的结果，还是1900-03-01减去2天？OpenOffice.org Calc 程序“纠正”了微软的问题；输入 1900-02-27 会导致存储数字 59。另存为 XLS 文件，然后使用 Excel 打开该文件——您将看到显示 1900-02-28。
参考：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)
which gives you a Python datetime in py_datethat you can do useful operations upon using the standard datetimemodule.
它为您提供了一个 Python 日期时间，py_date因为您可以在使用标准日期时间模块时执行有用的操作。
I've never used xlrd, and my example is completely made up, but if there is a myfile.xlsand 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，我的例子是完全编造的，但是如果有一个myfile.xls并且它在单元格 F20 中确实有一个日期数字，并且如上所述你对精度不太挑剔，那么这段代码应该可以工作。
回答by Deniz Dogan
回答by John Machin
The documentation of the function (minus the list of possible exceptions):
该函数的文档（减去可能的异常列表）：
xldate_as_tuple(xldate, datemode) [#]
Convert an Excel number (presumed to represent a date, a datetime or a time) into a tuple suitable for feeding to datetime or mx.DateTime constructors. xldate The Excel number datemode 0: 1900-based, 1: 1904-based. 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. Returns: Gregorian (year, month, day, hour, minute, nearest_second).
xldate_as_tuple(xldate, datemode) [#]
Convert an Excel number (presumed to represent a date, a datetime or a time) into a tuple suitable for feeding to datetime or mx.DateTime constructors. xldate The Excel number datemode 0: 1900-based, 1: 1904-based. 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. Returns: Gregorian (year, month, day, hour, minute, nearest_second).
As the author of xlrd, I'm interested in knowing how the documentation can be made better. Could you please answer these:
作为 xlrd 的作者，我很想知道如何改进文档。你能不能回答这些：
Did you read the general section on dates (quoted by @msw)?
Did you read the above specific documentation of the function?
Can you suggest any improvement in the documentation?
Did you actually try running the function, like this:
您是否阅读了有关日期的一般部分（@msw 引用）？
您是否阅读了上述功能的特定文档？
你能建议文档中的任何改进吗？
您是否真的尝试过运行该函数，如下所示：
>>> import xlrd
>>> xlrd.xldate_as_tuple(39274.0, 0)
(2007, 7, 11, 0, 0, 0)
>>> xlrd.xldate_as_tuple(39274.0 - 1.0/60/60/24, 0)
(2007, 7, 10, 23, 59, 59)
>>>
回答by KC007
import datetime as dt
import xlrd
log_dir = 'C:\Users\'
infile = 'myfile.xls'
book = xlrd.open_workbook(log_dir+infile)
sheet1 = book.sheet_by_index(0)
date_column_idx = 1
## iterate through the sheet to locate the date columns
for rownum in range(sheet1.nrows):
rows = sheet1.row_values(rownum)
## check if the cell is a date; continue otherwise
if sheet1.cell(rownum, date_column_idx).ctype != 3 :
continue
install_dt_tuple = xlrd.xldate_as_tuple((rows[date_column_idx ]), book.datemode)
## the "*date_tuple" will automatically unpack the tuple. Thanks mfitzp :-)
date = dt.datetime(*date_tuple)
回答by Collin Anderson
Here's what I use to automatically convert dates:
这是我用来自动转换日期的内容：
cell = sheet.cell(row, col)
value = cell.value
if cell.ctype == 3: # xldate
value = datetime.datetime(*xlrd.xldate_as_tuple(value, workbook.datemode))
