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


原文地址:https://www.cnblogs.com/Dorami/p/11097580.html