python使用笔记15--操作Excel

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

执行以下命令：

pip install xlwt

pip install xlrd

pip install xlutils

1.写Excel

import xlwt

book = xlwt.Workbook()#生成一个workbook
sheet = book.add_sheet('sheet1')#添加一个sheet页

title = ['编号','姓名','语文成绩','数学成绩','英语成绩','总分','平均分']#标题
data = [
    ["1","小花",99,100,98.5],#1
    ["2","小王",90,30.5,95],#
    ["3","小明",67.5,49.6,88]#
]#数据

#处理表头
row = 0
for t in title:
    sheet.write(0,row,t)
    row += 1


for row,v in enumerate(data,1):#用枚举将二维数组转成带下标的值
    sum_score = sum(v[2:])
    avg_score = round(sum_score/3,2)#求平均值，取两位小数点
    v.append(sum_score)
    v.append(avg_score)
    for col,value in enumerate(v):
        sheet.write(row,col,value)

book.save('student.xls')#用office只能用xls结尾的，用wps可以用xls，xlxs

2.读Excel

import xlrd

book = xlrd.open_workbook('student.xls')
#sheet = book.sheet_by_index(0)#根据索引来获取sheet页
sheet = book.sheet_by_name('sheet1')

print(sheet.cell(0,0).value)#指定单元格内容
print(sheet.row_values(1))#获取整行内容
print(sheet.col_values(0))#获取整列内容
print(sheet.nrows)#多少行
print(sheet.ncols)#多少列

3.修改Excel

from xlutils import copy
import xlrd
import os
book = xlrd.open_workbook('student.xls')#先打卡excel文件
new_book = copy.copy(book)#将book拷贝到xlutils模块的book
sheet = new_book.get_sheet(0)
sheet.write(0,0,'id')
sheet.write(0,1,'name')

os.rename('student.xls','student_bak,xls')#拷贝一下
new_book.save('student.xls')#保存

4.小练习

#写一个函数，传入表名，然后把表里面的数据导出到excel里面
import pymysql,xlwt
mysql_info = {'host':'127.0.0.1',
              'port':3306,
              'user':'root',
              'password':'123456',
              'autocommit':True,
              'db':'db001',
              'charset':'utf8'}

def select(sql):
    try:
        connect = pymysql.connect(**mysql_info)#**表示将字典转成host='xxx',port=3306
    except Exception as e:
        print('数据库连接失败',e)
    else:
        cur = connect.cursor(pymysql.cursors.DictCursor)#获取游标,获取表头
        try:
            cur.execute(sql)
        except Exception as e:
            print('sql执行失败',e)
        else:
            result = cur.fetchall()#获取查询结果
            return result
        finally:
            cur.close()
            connect.close()

def export_by_tbname(tbname):
    select_sql = 'select * from %s ;'%tbname
    result = select(select_sql)
    if result:
        book = xlwt.Workbook()  # 生成一个workbook
        sheet = book.add_sheet('sheet1')  # 添加一个sheet页
        print(list(result[0].keys()))
        for col,key in enumerate(result[0].keys()):
            sheet.write(0,col,key)#处理表头
        #处理数据
        for row,v in enumerate(result,1):
            for col,value in enumerate(v.values()):
                sheet.write(row,col,value)
        book.save('%s.xls'%tbname)
    else:
        print('输入的表名不存在/输入的表没有数据')


export_by_tbname('user1')