python学习笔记13：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都可以使用

 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")

 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")

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)  #多少列

 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)
 7 sheet = new_book.get_sheet(0)
 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‘)