python学习（二十二）读、写、导出数据库中数据到excel、修改excel

1、写EXCEL

import xlwt
book= xlwt.Workbook() #新建一个Excel
sheet=book.add_sheet('sheet1') #加sheet页
sheet.write(0,0,'姓名')  #行，列，写入的内容
sheet.write(0,1,'年龄')
sheet.write(0,2,'性别')
book.save('stu.xls') #结尾一定要用.xls

2、enumerate()方法

循环取列表的下标及对应的值

fileds=['d','name','sex','addr','gold','score']
for index,filed in enumerate(fileds):
    print(index,filed)   #循环的时候，直接获取到下标和值

3、导出数据库表所有数据到excel

import pymysql,xlwt
def export_excel(table_name):
    host, user, password, db = 'xxx', 'jxz', '123456', 'jxz'
    coon = pymysql.connect(user=user,
                           host=host,
                           password=password,
                           db=db,
                           charset='utf8',
                           port=3306)
    cur = coon.cursor()
    sql='select * from %s;'%table_name
    cur.execute(sql)
    fileds=[filed[0] for filed in cur.description]#获取数据库表头
    all_data=cur.fetchall()  #获取所有数据
    book=xlwt.Workbook()
    sheet=book.add_sheet('sheet1')
    for col, filed in enumerate(fileds):   #写表头
        sheet.write(0,col,filed)
    row=1
    for data in all_data:  #写多少行
        for col, filed in enumerate(data):  #控制列
            sheet.write(row, col, filed)
        row+=1 #每次写完一行，行数加一
    book.save('%s.xls'%table_name)
export_excel('app_student')

4、读excel

import xlrd
book=xlrd.open_workbook('app_student.xls')
sheet=book.sheet_by_index(0) #获取到第几个sheet页
sheet=book.sheet_by_name('sheet1') #根据sheet页名字获取
print(sheet.cell(0,0).value)  #指定sheet页里面行和列获取数据
print(sheet.row_values(1))  #获取到第几行的数据
print(sheet.nrows) #获取到Excel里面总共有多少行
for i in range(sheet.nrows):   #循环获取到每行的数据
    print(sheet.row_values(i))
print(sheet.ncols) #获取到Excel中总共有多少列
print(sheet.col_values(0)) #获取到第几列的数据
for i in range(sheet.ncols):   #循环获取到每列的数据
    print(sheet.col_values(i))

5、修改excel

import xlrd
from  xlutils import copy
book=xlrd.open_workbook('app_student.xls') #通过xlrd打开一个Excel
new_book=copy.copy(book)#通过xlutils这个模块里面copy方法，复制一份Excel
sheet=new_book.get_sheet(0) #获取sheet页
lis = ['编号','名字','性别','年龄','地址','班级','手机号','金币']
for col,filed in enumerate(lis):
  sheet.write(0,col,filed)
new_book.save('app_student.xls')