python读取excel，数字都是浮点型，日期格式是数字的解决办法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了python读取excel，数字都是浮点型，日期格式是数字的解决办法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3021字，纯文字阅读大概需要5分钟。
内容图文
excel文件内容：
读取excel：
# coding=utf-8
import xlrd
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import traceback
class excelHandle:
def decode(self, filename, sheetname):
try:
filename = filename.decode('utf-8')
sheetname = sheetname.decode('utf-8')
except Exception:
print traceback.print_exc()
return filename, sheetname
def read_excel(self, filename, sheetname):
filename, sheetname = self.decode(filename, sheetname)
rbook = xlrd.open_workbook(filename)
sheet = rbook.sheet_by_name(sheetname)
rows = sheet.nrows
cols = sheet.ncols
all_content = []
for i in range(rows):
row_content = []
for j in range(cols):
cell = sheet.cell_value(i, j)
row_content.append(cell)
all_content.append(row_content)
print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'
return all_content
if __name__ == '__main__':
eh = excelHandle()
filename = r'G:\test\ctype.xls'
sheetname = 'Sheet1'
eh.read_excel(filename, sheetname)
输出：
['整形','175.0'] ['字符串','最后的骑士'] ['浮点型','6.23'] ['日期','42909.6461574'] ['空值',''] ['布尔型','1']
可以看到，数字一律按浮点型输出，日期却输出成一串小数？！布尔型输出0或1
代码稍做改动：来看一看表格的数据类型
for i in range(rows):
row_content = []
for j in range(cols):
ctype = sheet.cell(i, j).ctype #表格的数据类型
print ctype,
cell = sheet.cell_value(i, j)
row_content.append(cell)
all_content.append(row_content)
print
print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'
输出：
1 2 ['整形','175.0'] 1 1 ['字符串','最后的骑士'] 1 2 ['浮点型','6.23'] 1 3 ['日期','42909.6461574'] 1 0 ['空值',''] 1 4 ['布尔型','1']
python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype:
ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
所以，判断一下ctype，然后再做相应处理就可以了。
最终代码：
# coding=utf-8
import xlrd
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
import traceback
from datetime import datetime
from xlrd import xldate_as_tuple
class excelHandle:
def decode(self, filename, sheetname):
try:
filename = filename.decode('utf-8')
sheetname = sheetname.decode('utf-8')
except Exception:
print traceback.print_exc()
return filename, sheetname
def read_excel(self, filename, sheetname):
filename, sheetname = self.decode(filename, sheetname)
rbook = xlrd.open_workbook(filename)
sheet = rbook.sheet_by_name(sheetname)
rows = sheet.nrows
cols = sheet.ncols
all_content = []
for i in range(rows):
row_content = []
for j in range(cols):
ctype = sheet.cell(i, j).ctype # 表格的数据类型
cell = sheet.cell_value(i, j)
if ctype == 2 and cell % 1 == 0: # 如果是整形
cell = int(cell)
elif ctype == 3:
# 转成datetime对象
date = datetime(*xldate_as_tuple(cell, 0))
cell = date.strftime('%Y/%d/%m %H:%M:%S')
elif ctype == 4:
cell = True if cell == 1 else False
row_content.append(cell)
all_content.append(row_content)
print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'
return all_content
if __name__ == '__main__':
eh = excelHandle()
filename = r'G:\test\ctype.xls'
sheetname = 'Sheet1'
eh.read_excel(filename, sheetname)
输出：
['整形','175'] ['字符串','最后的骑士'] ['浮点型','6.23'] ['日期','2017/23/06 15:30:28'] ['空值',''] ['布尔型','True']
转自：https://www.cnblogs.com/xxiong1031/p/7069006.html
内容总结
以上是互联网集市为您收集整理的python读取excel，数字都是浮点型，日期格式是数字的解决办法全部内容，希望文章能够帮你解决python读取excel，数字都是浮点型，日期格式是数字的解决办法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。