python读取excel，数字都是浮点型，⽇期格式是数字的解决办
法
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 = ws
cols = ls
all_content = []
for i in range(rows):
row_content = []
for j in range(cols):
cell = ll_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'
输出：
['整形','175.0']
['字符串','最后的骑⼠']
['浮点型','6.23']
['⽇期','42909.6461574']
['空值','']
['布尔型','1']
可以看到，数字⼀律按浮点型输出，⽇期却输出成⼀串⼩数？！布尔型输出0或1
代码稍做改动：来看⼀看表格的数据类型
for i in range(rows):
row_content = []
for j in range(cols):
ctype = ll(i, j).ctype #表格的数据类型
print ctype,
cell = ll_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 = ws
cols = ls
all_content = []
for i in range(rows):
row_content = []
for j in range(cols):
ctype = ll(i, j).ctype  # 表格的数据类型
cell = ll_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'
python怎么读取xls文件
输出：
['整形','175']
['字符串','最后的骑⼠']
['浮点型','6.23']
['⽇期','2017/23/06 15:30:28']
['空值','']
['布尔型','True']
更多操作excel可参考：www.2cto/kf/201501/373655.html

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。