python Excel操作

 1 """
 2 [['Id', 'Stu_id', 'C_name', 'Grade'], 
 3 (1, 801, '计算机', 98), 
 4 (2, 801, '中文', 49), 
 5 (3, 801, '英语', 80), 
 6 (4, 802, '计算机', 65), 
 7 (21, 1006, '英语', 45)]
 8 """
 9 
10 #excell写操作
11 def wt_excell(temp):
12     # book = xlwt.Workbook()  # 新建一个excel
13     # sheet = book.add_sheet('sheet1')  # 加sheet页
14     # sheet.write(0, 0, '姓名')  # 行、列、写入的内容
15     # sheet.write(0, 1, '年龄')
16     # sheet.write(0, 2, '性别')
17     # book.save('stu.xls')  # 结尾一定要用.xls
18     workbook = xlwt.Workbook()
19     sheet = workbook.add_sheet('score', cell_overwrite_ok=True)
20     for x in range(len(temp)):
21         for i in range(len(temp[x])):
22             print(temp[x][i])
23             sheet.write(x, i, temp[x][i])
24     workbook.save('stu.xls')

 1 Excel读操作
 2 import xlrd
 3 
 4 book = xlrd.open_workbook('app_student.xls')
 5 sheet = book.sheet_by_index(0) #第一个sheet，编号
 6 sheet = book.sheet_by_name('sheet1')#按sheet的名称
 7 print(sheet.cell(0, 0)) #text:'编号'
 8 print(sheet.cell(0, 0).value) #编号
 9 print(sheet.cell_value(0, 0)) #编号
10 #获取一整行
11 print(sheet.row_values(0)) #['编号', '名字', 'sex', 'age', 'addr', 'grade', 'phone', 'gold']
12 print(sheet.row_values(1)) #[1.0, '小黑马', '男', 28.0, '河南省济源市北海大道32号', '天蝎座', '18002531114', 617741546.0]
13 print(sheet.nrows) #sheet多少行
14 print(sheet.ncols) #sheet多少列
15 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')