python打开excel文件返回单元格值_python读取excel中单元格的内容返回的5种类型

#coding=utf-8

importxlrdimportsys#reload(sys)#sys.setdefaultencoding('utf-8')

importtracebackfrom datetime importdatetimefrom xlrd importxldate_as_tupleclassexcelHandle:#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

defread_excel(self, filename):#filename, sheetname = self.decode(filename, sheetname)

rbook =xlrd.open_workbook(filename)

sheets= rbook.sheet_names() #获取所有sheet名

allSheetsContent=[]for sh insheets:

sheet=rbook.sheet_by_name(sh)

rows=sheet.nrows

cols=sheet.ncols

sheetContent=[]for i in range(1,rows):

rowContent=[]for j inrange(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 elseFalse

rowContent.append(cell)

allSheetsContent.append(rowContent)#sheetContent.append(rowContent)

#print('[' + ','.join("'" + str(element) + "'" for element in rowContent) + ']')

returnallSheetsContentdefread_excel_by_sheetname(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 inrange(rows):

row_content=[]for j inrange(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 elseFalse

row_content.append(cell)

all_content.append(row_content)print

'[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'

returnall_contentif __name__ == '__main__':

eh=excelHandle()

filename= r'E:\Code\subject.xlsx'all_content=eh.read_excel(filename)for a inall_content:print(a)