前言：个人测试正在入门，今天试着测试去哪儿网站，自己写了一个xls的文件用于存储出发地、目的地、出发时间、返回时间。在读取到返回时间时，在Pycharm运行输出是日期格式成了一个浮点数，我开始以为是读取成了格林尼治时间，预估是时间间隔。为了验证我试着计算1970年1月1日到出发时间之间的时间间隔，并不符合时间差；太扎心了老铁，这怎么换算咯？几番查阅资料，发现了解决方法，特做记录。
----解决方式，可以直接看第9点----
1、先给您看下我xls文件中的数据：
def read_excel(filename, index, ishead=False):
xls = xlrd.open_workbook(filename)
sheet = xls.sheet_by_index(index)
print("date.today", date.today())
data = []
for i in range(sheet.nrows):
print("----", sheet.row_values(i))
if i == 0:
if ishead:
continue
data.append(sheet.row_values(i))
return data
data = read_excel("qunaer.xls", 0, True)2、看运行输出结果：
3、现在我们来计算一下这两个日期相差的时间间隔，以第一条为例：
4、现在来判断表格中数据的数据类型
def read_excel(filename, index, ishead=False):
xls = xlrd.open_workbook(filename)
sheet = xls.sheet_by_index(index)
print("date.today", date.today())
# 判断数据类型
for row in range(sheet.nrows):
for col in range(sheet.ncols):
print('--', sheet.cell(row, col), '--type:', sheet.cell(row, col).ctype)
data = []
for i in range(sheet.nrows):
print("----", sheet.row_values(i))
if i == 0:
if ishead:
continue
data.append(sheet.row_values(i))
return data
data = read_excel("qunaer.xls", 0, True)?5、看运行输出结果（只截取了部分，可对比xls表格中前四行数据）
?
?6、那么输出的type对应的数据类型是什么呢？我们到 第44行代码中调用的sheet.cell()中寻找答案-->查看源码
# xlrd.sheet.Cell
class Cell(BaseObject):
"""
Contains the data for one cell.
.. warning::
You don't call this class yourself. You access :class:`Cell` objects
via methods of the :class:`Sheet` object(s) that you found in the
:class:`~xlrd.book.Book` object that was returned when you called
:func:`~xlrd.open_workbook`
Cell objects have three attributes: ``ctype`` is an int, ``value``
(which depends on ``ctype``) and ``xf_index``.
If ``formatting_info`` is not enabled when the workbook is opened,
``xf_index`` will be ``None``.
The following table describes the types of cells and how their values
are represented in Python.
.. raw:: html
<table border="1" cellpadding="7">
<tr>
<th>Type symbol</th>
<th>Type number</th>
<th>Python value</th>
</tr>
<tr>
<td>XL_CELL_EMPTY</td>
<td align="center">0</td>
<td>empty string ''</td>
</tr>
<tr>
<td>XL_CELL_TEXT</td>
<td align="center">1</td>
<td>a Unicode string</td>
</tr>
<tr>
<td>XL_CELL_NUMBER</td>
<td align="center">2</td>
<td>float</td>
</tr>
<tr>
<td>XL_CELL_DATE</td>
<td align="center">3</td>
<td>float</td>
</tr>
<tr>
<td>XL_CELL_BOOLEAN</td>
<td align="center">4</td>
<td>int; 1 means TRUE, 0 means FALSE</td>
</tr>
<tr>
<td>XL_CELL_ERROR</td>
<td align="center">5</td>
<td>int representing internal Excel codes; for a text representation,
refer to the supplied dictionary error_text_from_code</td>
</tr>
<tr>
<td>XL_CELL_BLANK</td>
<td align="center">6</td>
<td>empty string ''. Note: this type will appear only when
open_workbook(..., formatting_info=True) is used.</td>
</tr>
</table>
"""
__slots__ = ['ctype', 'value', 'xf_index']
def __init__(self, ctype, value, xf_index=None):
self.ctype = ctype
self.value = value
self.xf_index = xf_index
def __repr__(self):
if self.xf_index is None:
return "%s:%r" % (ctype_text[self.ctype], self.value)
else:
return "%s:%r (XF:%r)" % (ctype_text[self.ctype], self.value, self.xf_index)7、分析部分源码，查看提示
?8、总结一下我们xls表中出现的数据类型
| 类型码 | 数据类型 | Python值 |
| 1 | Text | String |
| 2 | Number | float |
| 3 | Date | float |
也就是说在xls表格中的数据在Python中用xldr读取后，数据类型不完全一致
9、解决方式
import datetime
from xlrd import xldate_as_tuple
def read_excel(filename, index, ishead=False):
xls = xlrd.open_workbook(filename)
sheet = xls.sheet_by_index(index)
print("date.today", date.today())
# 判断数据类型
# for row in range(sheet.nrows):
# for col in range(sheet.ncols):
# print('--', sheet.cell(row, col), '--type:', sheet.cell(row, col).ctype)
data = []
for i in range(sheet.nrows):
data_list_i = []
if i == 0:
if ishead:
continue
if i > 0: # 从第2行 第3列开始的date日期类型数据，需要修改的日期只在第3列(2=3-1)
start_Time_float = sheet.cell(i, 2)
# 输出获取到的从第二行开始的每个第三列数据的 数据类型 及 数据 及 类型码
# print("==========", type(start_Time_float), start_Time_float, start_Time_float.ctype)
# 把获取从第二行开始的每个第三列数据转成我们习惯的日期格式，需要 import datetime 以及 from xlrd import xldate_as_tuple
start_Time = datetime.datetime(*(xldate_as_tuple(start_Time_float.value, 0)))
# print("__~~__", start_Time_float.value, type(start_Time_float.value), type(start_Time), start_Time)
data_list_i = sheet.row_values(i)
data_list_i[2] = start_Time
print("---", i, data_list_i[2], data_list_i)
data.append(data_list_i)
return data
data = read_excel("qunaer.xls", 0, True)
10、看运行结果
?
11、如果要判断每个单元格的数据类型，修改为日期类型--代码示例：
nrows = sheet.nrows
ncols = sheet.ncols
for row in range(1, nrows):
for col in range(ncols):
date_float = sheet.cell(row, col).value
if sheet.cell(row, col).ctype == 3:
date_time = datetime.datetime(*xldate_as_tuple(date_float, 0))
print(date_time)12、仅做记录，感谢您的参考，如果您有建议，不胜感激