python Excel操作

"""
[['Id', 'Stu_id', 'C_name', 'Grade'], 
(1, 801, '计算机', 98), 
(2, 801, '中文', 49), 
(3, 801, '英语', 80), 
(4, 802, '计算机', 65), 
(21, 1006, '英语', 45)]
"""

#excell写操作
def wt_excell(temp):
    # 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
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('score', cell_overwrite_ok=True)
    for x in range(len(temp)):
        for i in range(len(temp[x])):
            print(temp[x][i])
            sheet.write(x, i, temp[x][i])
    workbook.save('stu.xls')

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)) #text:'编号'
print(sheet.cell(0, 0).value) #编号
print(sheet.cell_value(0, 0)) #编号
#获取一整行
print(sheet.row_values(0)) #['编号', '名字', 'sex', 'age', 'addr', 'grade', 'phone', 'gold']
print(sheet.row_values(1)) #[1.0, '小黑马', '男', 28.0, '河南省济源市北海大道32号', '天蝎座', '18002531114', 617741546.0]
print(sheet.nrows) #sheet多少行
print(sheet.ncols) #sheet多少列
print(sheet.col_values(0)) #获取一列的数据

Excel修改

import xlrd
from xlutils import copy

book = xlrd.open_workbook('app_student.xls')
new_book = copy.copy(book)

sheet = new_book.get_sheet('sheet1')#xlutils获取sheet页
# sheet = new_book.get_sheet(0)#xlutils获取sheet页
lis = ['编号', '年龄', '性别']
for i, j in enumerate(lis):
    sheet.write(0, i, j)
new_book.save('app_student.xls')