1、读excel
import xlrd
book = xlrd.open_workbook('金牛座.xls')
sheet = book.sheet_by_index(0)
#sheet = book.sheet_by_name('sheet1')
print(sheet.nrows) #excel里面有多少行
print(sheet.ncols) #excel中有多少列
print(sheet.cell(0,0).value)#获取指定单元格的内容
print(sheet.cell(0,1).value)
#获取整行整列的内容,将获取到的内容存到list里
print(sheet.row_values(1))
print(sheet.col_values(1))
for i in range(sheet.nrows):#循环获取每行的内容
print(sheet.row_values(i))
2、写excel
import xlwt #只能写excel
import xlrd #只能读excel
import xlutils #修改excel!重要!
#写excel
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')
sheet.write(0,0,'id') #指定行和列写内容
sheet.write(0,1,'username')
sheet.write(0,2,'password')
sheet.write(1,0,'1')
sheet.write(1,1,'linhuizhen')
sheet.write(1,2,'123456')
####################################
stus = [
[1,'njf','1234'],
[2,'xiaojun','1234'],
[3,'hailong','1234'],
[4,'xiaohei','1234'],
[5,'xiaohei','1234'],
[6,'xiaohei','1234'],
[7,'xiaohei','1234'],
[8,'xiaohei','1234'],
[9,'xiaohei','1234'],
]
line = 0#控制的是行
for stu in stus: #外面的循环控制 行
#stu = [1,'njf','1234']
col = 0 # 控制列
for s in stu: #内部循环控制 列
#0行 0列 1
#0行 1列 njf
#0行 2列 1234
sheet.write(line,col,s)
col += 1
line += 1
book.save('stu.xls')#只能用.xls结尾
'''
#双重循环,循环了5*10=50次
for i in range(5):
for j in range(10):
print('haha')
'''
3、修改excel
#修改excel很重要!与xlrd配合用
import xlutils
import xlrd
from xlutils import copy #从xlutils中导入copy这个功能
book = xlrd.open_workbook('stu.xls')
#先用xlrd打开一个excel
new_book = copy.copy(book)
#然后用xlutils里面的copy功能,复制一个excel
sheet = new_book.get_sheet(0)#获取sheet页
sheet.write(0,1,'test')
sheet.write(1,1,'test2')
new_book.save('stu.xls')