读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()
