python读取excel中单元格的内容返回的5种类型
(1) 读取单个sheetname的内容。
此部分转自:https://www.cnblogs.com/xxiong1031/p/7069006.html
python读取excel中单元格的内容返回的有5种类型,即上面例子中的ctype:
ctype:
0 empty
1 string
2 number
3 date
4 boolean
5 Error
1 # coding=utf-8 2 import xlrd 3 import sys 4 5 reload(sys) 6 sys.setdefaultencoding('utf-8') 7 import traceback 8 from datetime import datetime 9 from xlrd import xldate_as_tuple 10 11 12 class excelHandle: 13 def decode(self, filename, sheetname): 14 try: 15 filename = filename.decode('utf-8') 16 sheetname = sheetname.decode('utf-8') 17 except Exception: 18 print traceback.print_exc() 19 return filename, sheetname 20 21 def read_excel(self, filename, sheetname): 22 filename, sheetname = self.decode(filename, sheetname) 23 rbook = xlrd.open_workbook(filename) 24 sheet = rbook.sheet_by_name(sheetname) 25 rows = sheet.nrows 26 cols = sheet.ncols 27 all_content = [] 28 for i in range(rows): 29 row_content = [] 30 for j in range(cols): 31 ctype = sheet.cell(i, j).ctype # 表格的数据类型 32 cell = sheet.cell_value(i, j) 33 if ctype == 2 and cell % 1 == 0: # 如果是整形 34 cell = int(cell) 35 elif ctype == 3: 36 # 转成datetime对象 37 date = datetime(*xldate_as_tuple(cell, 0)) 38 cell = date.strftime('%Y/%d/%m %H:%M:%S') 39 elif ctype == 4: 40 cell = True if cell == 1 else False 41 row_content.append(cell) 42 all_content.append(row_content) 43 print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']' 44 return all_content 45 46 47 if __name__ == '__main__': 48 eh = excelHandle() 49 filename = r'G:\test\ctype.xls' 50 sheetname = 'Sheet1' 51 eh.read_excel(filename, sheetname)
(2) 稍微修改了一下,读取excel所有sheets的内容。
# 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): # filename, sheetname = self.decode(filename, sheetname) rbook = xlrd.open_workbook(filename) sheets = rbook.sheet_names() # 获取所有sheet名 allSheetsContent=[] for sh in sheets: sheet = rbook.sheet_by_name(sh) rows = sheet.nrows cols = sheet.ncols sheetContent = [] for i in range(1,rows): rowContent = [] 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 rowContent.append(cell) allSheetsContent.append(rowContent) # sheetContent.append(rowContent) # print('[' + ','.join("'" + str(element) + "'" for element in rowContent) + ']') return allSheetsContent def read_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 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'E:\Code\subject.xlsx' all_content = eh.read_excel(filename) for a in all_content: print(a)