(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
# 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)
(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:
# 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)
'[' + ','.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)