读写excel

读excel
import xlrd
book = xlrd.open_workbook('处女座学员名单.xlsx')
# sheet1 = book.sheet_by_index(0)
sheet2 = book.sheet_by_name('作业')

# print(sheet2.row_values(0))#取某一行的数据
# print(sheet2.col_values(0))#取某一列的数据
# print(sheet2.cell(0,0).value)#取指定单元格的内容
print(sheet2.nrows) #多少行
print(sheet2.ncols) #多少列

写excel

import xlwt

book = xlwt.Workbook()
sheet = book.add_sheet('人员名单')

l = [
    [1,'jiajiju','beijing','186232424','女'], #1  2 3
    [2,'韩敏','beijing','186232424','女'],
    [4,'焦丽妃','beijing','186232424','女'],
]
# row = 1#行号
# for row_data in l:
#     col = 0 #列
#     for col_data in  row_data:
#         sheet.write(row,col,col_data)
#         col+=1 #每次写一列 列就加1
#     row+=1#每写一行 行号就加1

for row,row_data in enumerate(l,1):#
    for col,col_data in enumerate(row_data):
        sheet.write(row,col,col_data)


book.save('student.xls')
#保存的时候，如果你用的是微软的office，后缀就用.xls
#如果是wps .xls .xlsx

修改excel

import xlrd
from xlutils import copy
#1、打开一个excel
#2、复制一份
#3、修改

book = xlrd.open_workbook('student.xls')
new_book = copy.copy(book)#复制一份

sheet = new_book.get_sheet(0) #获取sheet页
title = ['编号','名字','地址','电话','性别']

for col,t in enumerate(title):
    sheet.write(0,col,t)

new_book.save('student.xls')

#1、先获取到表里面的数据
#2、再取出来表头
#3、把数据写入到excel
import pymysql
import xlwt


def get_data(table):
    conn=pymysql.connect(host='118.24.3.40',user='jxz',password='123456',
                    db='jxz',charset='utf8')
    cur = conn.cursor(pymysql.cursors.DictCursor)
    cur.execute('select * from %s'%table)
    result = cur.fetchall()
    # for d in cur:
    #     print('每次循环的结果',d)
    cur.close()
    conn.close()

    # return result
get_data('app_student')
def export_excel():
    table = input('请输入导出的表名:').strip()
    data = get_data(table)
    if data:#如果不为空
        book = xlwt.Workbook()
        sheet = book.add_sheet('sheet1')
        for col,title in enumerate(data[0].keys()): #处理表头
            sheet.write(0,col,title)
        for row,d in enumerate(data,1):
            for col,col_data in enumerate(d.values()):
                sheet.write(row,col,col_data)
        print('导出完成！')
        book.save('%s.xls'%table)

    else:
        print('%s表数据为空'%table)

# export_excel()