python学习笔记13：excel操作
一、安装第三方模块xlwt、xlrd、xlutils
安装命令为：
写：pip install xlwt
读：pip install xlrd
修改：pip install xlutils
二、使用实例
1.写excel
1 import xlwt 2 book = xlwt.Workbook() #建excel 3 sheet = book.add_sheet('sheet1') #增加sheet页 4 sheet.write(0,0,"学生姓名") #行、列 5 sheet.write(1,0,"小黑") 6 sheet.write(2,0,"小白") 7 sheet.write(3,0,"小兔") 8 book.save("student.xls") #xmls代码不会报错，但会打不开文件，若用wps，xmls都可以使用
2.读excel
1 import xlrd 2 book = xlrd.open_workbook('students.xls') #打开excel 3 sheet = book.sheet_by_index(0) #根据下标获取sheet页 4 # sheet = book_sheet_by_name('sheet1') #根据sheet页名字取 5 print(sheet.cell(0,0).value) #取指定单元格的内容 6 print(sheet.row_values(1)) #取整行的数据 7 print(sheet.col_values(0)) #取整列的数据 8 print(sheet.nrows) #多少行 9 print(sheet.ncols) #多少列
3.修改excel
1 # 修改excel需要xlutils和xlrd两个模块 2 from xlutils import copy 3 import xlrd 4 import os 5 book = xlrd.open_workbook('students.xls') 6 new_book = copy.copy(book) #复制一个新的excel 7 sheet = new_book.get_sheet(0) #获取第几个sheet页 8 sheet.write(0,0,'id') 9 sheet.write(0,1,'name') 10 os.rename('students.xls','students_bak.xls') #原文件名修改，备份 11 new_book.save('students.xls')
三.小练习
# 把下面的数据写入excel里
# {
# "1":["小花",99,100,98.5],
# "2":["小王",90,30.5,95],
# "3":["小明",67.5,49.6,88]
# }
1 import xlwt 2 book = xlwt.Workbook() 3 sheet = book.add_sheet('sheet1') 4 data = { 5 "1":["小花",99,100,98.5], 6 "2":["小王",90,30.5,95], 7 "3":["小明",67.5,49.6,88] 8 } 9 10 title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分'] #写表头 11 # 处理表头 12 row = 0 13 for t in title: 14 sheet.write(0,row,t) 15 row+=1 16 17 row = 1 18 for k,v in data.items(): #循环行 19 v.insert(0,k) #插入编号 20 col = 0 21 for value in v: #循环列 22 sheet.write(row,col,value) 23 col+=1 24 row+=1 25 book.save("student.xls")
# 把下面的数据写入excel里
# [
# ["1","小花",99,100,98.5],
# ["2","小王",90,30.5,95],
# ["3","小明",67.5,49.6,88]
# ]
1 # 方法一： 2 import xlwt 3 book = xlwt.Workbook() 4 sheet = book.add_sheet('sheet1') 5 6 title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分'] 7 row = 0 8 for t in title: 9 sheet.write(0,row,t) 10 row+=1 11 12 data = [ 13 ["1","小花",99,100,98.5], 14 ["2","小王",90,30.5,95], 15 ["3","小明",67.5,49.6,88] 16 ] 17 row = 1 18 for v in data: #行 19 col = 0 20 sum_score = sum(v[2:]) #算总分 21 avg_score = round(sum_score / 3,2) #算平均分 22 v.append(sum_score) 23 v.append(avg_score) 24 for value in v: 25 sheet.write(row,col,value) 26 col+=1 27 row+=1 28 book.save("students.xls") 29 30 31 # 方法二： 32 import xlwt 33 book = xlwt.Workbook() 34 sheet = book.add_sheet('sheet1') 35 title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分'] 36 row = 0 37 for t in title: 38 sheet.write(0,row,t) 39 row+=1 40 data = [ 41 ["1","小花",99,100,98.5], 42 ["2","小王",90,30.5,95], 43 ["3","小明",67.5,49.6,88] 44 ] 45 for row,v in enumerate(data,1): #enumerate每次循环自动+1 46 sum_score = sum(v[2:]) 47 avg_score = round(sum_score / 3,2) 48 v.append(sum_score) 49 v.append(avg_score) 50 for col,value in enumerate(v): 51 sheet.write(row,col,value) 52 book.save("students.xls")
