import xlwt #写excel
import xlrd #读excel
import xlutils #修改excel
book = xlwt.Workbook()#创建book
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,'niuhanyang')
sheet.write(1,2,'123456')
book.save('stu.xls')# .xlsx 只能写.xls后缀,.xlsx后缀只能以wps打开,不能用office打开
--------------------------------------------------------------------------------
写excel:
import xlwt
import xlrd
import xlutils
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')
stus = [
[1,'njf','1234'],
[2,'xiaojun','1234'],
[3,'hailong','1234'],
[4,'xiaohei','1234'],
[4,'xiaohei','1234'],
[4,'xiaohei','1234'],
[4,'xiaohei','1234'],
[4,'xiaohei','1234'],
[4,'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
# 1 0 2
# 1 1 xiaojun
# 1 2 1234
sheet.write(line,col,s)
col+=1
line+=1
book.save('stu.xls')# .xlsx
--------------------------------------------------------------------------------
读excel:
import xlrd
book = xlrd.open_workbook('stu.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) #获取到指定单元格的内容
print(sheet.row_values(0))#获取到整行的内容 打印出list
# print(sheet.col_values(0))#获取到整行的内容
for i in range(sheet.nrows):#循环获取每行的内容
print(sheet.row_values(i))
--------------------------------------------------------------------------------
修改excel:
import xlrd
from xlutils import 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,'倪菊芳')#替换第1行、第2列内容为倪菊芳
sheet.write(1,1,'白小军')
new_book.save('stu.xls')