写excel--xlwt模块
import xlwt # book = xlwt.Workbook() # sheet = book.add_sheet('students1') # sheet.write(0,0,'id') # sheet.write(0,1,'name') # sheet.write(0,2,'age') # # sheet.write(1,0,'123') # sheet.write(1,1,'zjr') # sheet.write(1,2,'17') # # sheet.write(2,0,'123') # sheet.write(2,1,'zjr1') # sheet.write(2,2,'18') # # sheet.write(3,0,'123') # sheet.write(3,1,'zjr2') # sheet.write(3,2,'19') # # sheet.write(4,0,'平均年龄：') # average =sum(sheet) # # sheet.write(4,1,average) # book.save('students1.xls')#如果后缀是xlsx，office打不开 stus = [ [1,'zjr','BJ',66], [2,'zjr1','BJ1',60], [3,'zjr2','BJ1',60] ] stus.insert(0,['编号','姓名','地址','年龄']) book = xlwt.Workbook() sheet = book.add_sheet('sheet1') # row = 0#行 # for stu in stus:#控制行 # col = 0#列 # for s in stu:#控制列 # sheet.write(row,col,s) # col+=1 # row+=1 # ages = [s[-1] for s in stus] # avg_age = round(sum(ages[1:])/(len(ages)-1),2) # a = '平均年龄是：%s'%avg_age # sheet.write(row,0,a) for row,stu in enumerate(stus):# (0,[1,'zjr','BJ',66])... 枚举的第一个值代表下标 for col,s in enumerate(stu):#(0,1),(1,'zjr')... sheet.write(row,col,s) ages = [s[-1] for s in stus] avg_age = round(sum(ages[1:])/(len(ages)-1),2) a = '平均年龄是：%s'%avg_age sheet.write(row+1,0,a) book.save('students8.xls')#如果后缀是xlsx，office打不开
读excel--xlrd模块
import xlrd book = xlrd.open_workbook('students8.xls') # sheet = book.sheet_by_index(0) sheet = book.sheet_by_name('sheet1') sheet2 = book.sheet_by_name('Sheet2') print(book.sheets())#所有的sheet页，返回一个list，list里面就是每个sheet页对象 for s in book.sheets():#循环所有sheet页 print(s.cell(1,2)) print(sheet.cell(1,1)) print(sheet2.cell(1,1)) print(sheet.row_values(0))#获取整行 ['编号', '姓名', '地址', '年龄'] print(sheet.row_values(1))#获取第2行的数据 [1.0, 'zjr', 'BJ', 66.0] print(sheet.col_values(0))#获取整列数据，获取第一列数据 print(sheet.col_values(1))#整列 print(sheet.nrows)#多少行 print(sheet.ncols)#多少列
修改excel--xlutils和xlrd模块
from xlutils import copy import xlrd book = xlrd.open_workbook('students8.xls') sheet = book.sheet_by_index(0) new_book = copy.copy(book) print(dir(new_book))#查看方法 copy_sheet = new_book.get_sheet(0) for row in range(1,sheet.nrows-1):#1,2,3,4 addr = sheet.cell(row,2).value addr = addr.replace('BJ','北京').replace('sh','上海') copy_sheet.write(row,2,addr) new_book.save('students_8.xls')