xlrd 读取 xls (excel)的日期、时间单元格的问题
source link: https://blogread.cn/it/article/5127?f=hot1
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
xlrd 读取 xls (excel)的日期、时间单元格的问题
xlrd 是python 的xls处理扩展包,常用来读取xls文件:
遇到的问题:
1. 解析日期2005-7-8这样的单元格后,print 出来的是整数:38541
2. 解析日期12:35:00这样的单元格,print出来时浮点数:0.524305555556
处理日期和时间就卡住了,转换起来也很麻烦,因为在xls内部就是按照长整型和浮点数来存储日期和时间的。搜索后发现很多人都碰到这个问题:
limodou 自己去写了计算方法:
思路确实是把整形转化成datetime对象即可。
解决方式:
其实xlrd本身就带有这个功能了,在xlrd的example里面就有。
xlrd.xldate_as_tuple(cell_value, 0)
@param datemode 0: 1900-based, 1: 1904-based.
其中cell_value是单元格的值,如何读取就省略了先。
这样12:35:00格式的时间久可以转化成:(0, 0, 0, 12, 35, 0)
datetime读取这个tuple就好。
再附上转化时间的源码:
def xldate_as_tuple(xldate, datemode): if datemode not in (0, 1): raise XLDateBadDatemode(datemode) if xldate == 0.00: return (0, 0, 0, 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: hour = minute = second = 0 xldays += 1 else: # second = seconds % 60; minutes = seconds // 60 minutes, second = divmod(seconds, 60) # minute = minutes % 60; hour = minutes // 60 hour, minute = divmod(minutes, 60) if xldays >= _XLDAYS_TOO_LARGE[datemode]: raise XLDateTooLarge(xldate) if xldays == 0: return (0, 0, 0, hour, minute, second) if xldays < 61 and datemode == 0: raise XLDateAmbiguous(xldate) jdn = xldays + _JDN_delta[datemode] yreg = (ifd(ifd(jdn * 4 + 274277, 146097) * 3, 4) + jdn + 1363) * 4 + 3 mp = ifd(yreg % 1461, 4) * 535 + 333 d = ifd(mp % 16384, 535) + 1 # mp /= 16384 mp >>= 14 if mp >= 10: return (ifd(yreg, 1461) - 4715, mp - 9, d, hour, minute, second) else: return (ifd(yreg, 1461) - 4716, mp + 3, d, hour, minute, second)
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK