python-xlwt模块& xlrd模块& xlutils模块&操作excel

1、写excel

 1 import xlwt
 2 
 3 # 写excel
 4 book = xlwt.Workbook()
 5 sheet = book.add_sheet('sheet1')
 6 
 7 # 第一行第一列
 8 sheet.write(0,0,'学生姓名')
 9 # 第二行第一列
10 sheet.write(1,0,'lzh')
11 sheet.write(2,0,'zsb')
12 sheet.write(3,0,'zrh')
13 
14 # 如果用wps,后缀名可写其他excel的格式
15 book.save("student.xls")

 

2、写excel小练习

 1 import xlwt
 2 
 3 book = xlwt.Workbook()
 4 sheet = book.add_sheet('sheet1')
 5 
 6 
 7 title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分']
 8 # 处理表头
 9 row = 0
10 for t in title:
11     sheet.write(0,row,t)
12     row += 1
13 
14 data = [
15     ["1","小花",99,100,98.5],  # 1
16     ["2","小王",90,30.5,95],  # 2
17     ["3","小明",67.5,49.6,88]   # 3
18 ]
19 
20 # 1表示下标从1开始计数
21 for row,v in enumerate(data,1): #行
22     sum_score = sum(v[2:])#算总分
23     avg_score = round(sum_score / 3,2) #算平均分
24     v.append(sum_score)
25     v.append(avg_score)
26     print(v)
27     for col,value in enumerate(v):
28         # 从第二行，第一列开始写入数据
29         sheet.write(row,col,value)
30 
31 book.save("students.xls")  # 如果你用的是wps的话

结果：

 

3、读excel

 1 import xlrd
 2 
 3 book = xlrd.open_workbook('students.xls')
 4 
 5 # 根据下标取
 6 sheet = book.sheet_by_index(0)
 7 # sheet = book.sheet_by_name('sheet1')
 8 
 9 # 指定单元格的内容
10 print(sheet.cell(0,0).value)
11 # 取整行的数据
12 print(sheet.row_values(1))
13 # 取整列的数据
14 print(sheet.col_values(0))
15 
16 # 多少行
17 print(sheet.nrows)
18 # 多少列
19 print(sheet.ncols)

 

4、修改excel

 1 from xlutils import copy
 2 import xlrd
 3 import os
 4 
 5 
 6 book = xlrd.open_workbook('students.xls')
 7 
 8 # 通过xlutils的copy方法复制一个
 9 new_book = copy.copy(book)
10 sheet = new_book.get_sheet(0)
11 
12 sheet.write(0,0,'id')
13 sheet.write(0,1,'name')
14 
15 # 备份原有文件
16 os.rename('students.xls','students_bak.xls')
17 
18 # 写入新文件
19 new_book.save('students.xls')

结果：

 

posted @ 2020-05-25 23:33  tour8  阅读(189)  评论(0)    收藏  举报