0. xlrd是Python读取Excel文件内容的一个类库。正常使用pip就可以安装了。不管2.7.*还是3.*都可以正常使用。它是一个神奇的读取模块!
import xlrd #导入模块
1. 打开Excel文件读取数据
data = xlrd.open_workbook('ExcelFile.xlsx')
data相当于文件句柄。后面只要操作,只要找data就可以了。8过,xlrd貌似木有关闭方法。所以一开始说它很神奇。
2. 读取内容三种方式:
2.1 通过索引顺序获取<1>
worktable = data.sheets()[0]
2.2 通过索引顺序获取<2>
worktable = data.sheet_by_index(0)
2.3 通过名称获取
worktable = data.sheet_by_name(u'Sheet1')
3. Excel Sheet的行、列:
3.1 获取行数和列数
nrows1 = worktable.nrows
ncols1 = worktable.ncols
3.2 获取整行和整列的值
worktable.row_values(i)
worktable.col_values(i)
3.3 循环行列表数据
for i in range(nrows1):
print worktable.row_values(i)
4. 单元格:
4.1 直接取单元格值<1>
worktable.cell(0,0).value.encode('utf-8')
4.2 直接取单元格值<2>
worktable.cell_value(0,0).encode('utf-8')
4.3 按行、列定位取值
worktable.row(0)[0].value.encode('utf-8')
worktable.col(0)[0].value.encode('utf-8')
5. 日期处理:
Excel里的日期格式如果不加处理,会返回一个浮点数。所以,需要简单处理一下。
if table.cell(rownum, colnum).ctype == 3:
date_value = xlrd.xldate_as_tuple(table.cell_value(rownum, colnum), data.datemode)
date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
6. 举个栗子:
#coding=utf-8
import xlrd
from datetime import date
def open_excel(file= 'file.xlsx'):
try:
data = xlrd.open_workbook(file)
return data
except Exception, e:
print str(e)
def excel_table_byindex(file1= 'file.xlsx',colnameindex=0,by_index=0):
data = open_excel(file1)
# table = data.sheets()[by_index] # 也是可以的
table = data.sheet_by_index(by_index)
nrows = table.nrows #行数
ncols = table.ncols #列数
colnames = table.row_values(colnameindex) #某一行数据
list = []
for rownum in range(1, nrows):
row = table.row_values(rownum)
if row:
app = {}
for i in range(len(colnames)):
if table.cell(rownum, i).ctype == 3:
date_value = xlrd.xldate_as_tuple(table.cell_value(rownum, i), data.datemode)
date_tmp = date(*date_value[:3]).strftime('%Y/%m/%d')
app[colnames[i]] = date_tmp
else:
app[colnames[i]] = row[i]
list.append(app)
return list
def excel_table_byname(file1 = 'file.xlsx', colnameindex = 0, by_name = u'Sheet1'):
data = open_excel(file1)
table = data.sheet_by_name(by_name)
nrows = table.nrows #行数
colnames = table.row_values(colnameindex) #某一行数据
list =[]
for rownum in range(1, nrows):
row = table.row_values(rownum)
if row:
app = {}
for i in range(len(colnames)):
app[colnames[i]] = row[i]
list.append(app)
return list
def main():
tables = excel_table_byindex()
for row in tables:
for key, value in row.items():
print key + " =>",
print value
print
if __name__ == "__main__":
main()