python使用笔记15--操作Excel

python操作Excel需要引入第三方模块

执行以下命令：

pip install xlwt

pip install xlrd

pip install xlutils

1.写Excel

 1 import xlwt
 2 
 3 book = xlwt.Workbook()#生成一个workbook
 4 sheet = book.add_sheet('sheet1')#添加一个sheet页
 5 
 6 title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分']#标题
 7 data = [
 8     ["1","小花",99,100,98.5],#1
 9     ["2","小王",90,30.5,95],#
10     ["3","小明",67.5,49.6,88]#
11 ]#数据
12 
13 #处理表头
14 row = 0
15 for t in title:
16     sheet.write(0,row,t)
17     row += 1
18 
19 
20 for row,v in enumerate(data,1):#用枚举将二维数组转成带下标的值
21     sum_score = sum(v[2:])
22     avg_score = round(sum_score/3,2)#求平均值，取两位小数点
23     v.append(sum_score)
24     v.append(avg_score)
25     for col,value in enumerate(v):
26         sheet.write(row,col,value)
27 
28 book.save('student.xls')#用office只能用xls结尾的，用wps可以用xls，xlxs

2.读Excel

 1 import xlrd
 2 
 3 book = xlrd.open_workbook('student.xls')
 4 #sheet = book.sheet_by_index(0)#根据索引来获取sheet页
 5 sheet = book.sheet_by_name('sheet1')
 6 
 7 print(sheet.cell(0,0).value)#指定单元格内容
 8 print(sheet.row_values(1))#获取整行内容
 9 print(sheet.col_values(0))#获取整列内容
10 print(sheet.nrows)#多少行
11 print(sheet.ncols)#多少列

3.修改Excel

 1 from xlutils import copy
 2 import xlrd
 3 import os
 4 book = xlrd.open_workbook('student.xls')#先打卡excel文件
 5 new_book = copy.copy(book)#将book拷贝到xlutils模块的book
 6 sheet = new_book.get_sheet(0)
 7 sheet.write(0,0,'id')
 8 sheet.write(0,1,'name')
 9 
10 os.rename('student.xls','student_bak,xls')#拷贝一下
11 new_book.save('student.xls')#保存

4.小练习

 1 #写一个函数，传入表名，然后把表里面的数据导出到excel里面
 2 import pymysql,xlwt
 3 mysql_info = {'host':'127.0.0.1',
 4               'port':3306,
 5               'user':'root',
 6               'password':'123456',
 7               'autocommit':True,
 8               'db':'db001',
 9               'charset':'utf8'}
10 
11 def select(sql):
12     try:
13         connect = pymysql.connect(**mysql_info)#**表示将字典转成host='xxx',port=3306
14     except Exception as e:
15         print('数据库连接失败',e)
16     else:
17         cur = connect.cursor(pymysql.cursors.DictCursor)#获取游标,获取表头
18         try:
19             cur.execute(sql)
20         except Exception as e:
21             print('sql执行失败',e)
22         else:
23             result = cur.fetchall()#获取查询结果
24             return result
25         finally:
26             cur.close()
27             connect.close()
28 
29 def export_by_tbname(tbname):
30     select_sql = 'select * from %s ;'%tbname
31     result = select(select_sql)
32     if result:
33         book = xlwt.Workbook()  # 生成一个workbook
34         sheet = book.add_sheet('sheet1')  # 添加一个sheet页
35         print(list(result[0].keys()))
36         for col,key in enumerate(result[0].keys()):
37             sheet.write(0,col,key)#处理表头
38         #处理数据
39         for row,v in enumerate(result,1):
40             for col,value in enumerate(v.values()):
41                 sheet.write(row,col,value)
42         book.save('%s.xls'%tbname)
43     else:
44         print('输入的表名不存在/输入的表没有数据')
45 
46 
47 export_by_tbname('user1')