python表格浮点转日期_Python-用xlrd模块读取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 = 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']