excel操作--写excel

import xlwt

book = xlwt.Workbook()#1.新建一个excel
sheet = book.add_sheet('students')#2.新建一个sheet页

sheet.write(0,0,'id')#3.写内容:第0行,第0列,内容'id'
sheet.write(0,1,'name')
sheet.write(0,2,'age')

sheet.write(1,0,'1')
sheet.write(1,1,'xiaohei')
sheet.write(1,2,'38')

book.save('students.xls') #4.保存excel.以students保存excel,如果后缀写成xlsx，使用微软的office打不开

练习:

#以编号 姓名 地址 年龄 格式写excel,并在最后一行算出平均年龄,保留两位小数

#方法一：

stus = [
    [1,'ds','bejing',51],
    [2,'fd','shanghai',28],
    [3,'zc','shanghai',16],
    [4,'lhy','shanghai',21],
    [5,'ylm','shanghai',35],
    [6,'wxl','beijing',16],
]
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 if type(s[-1])!=str ]
avg_age = round(sum(ages) / len(ages),2)
content = '平均年龄:%s'%avg_age
sheet.write(row,0,content)

book.save('students.xls')

#方法二：
stus = [
    [1,'ds','bejing',51],
    [2,'fd','shanghai',28],
    [3,'zc','shanghai',16],
    [4,'lhy','shanghai',21],
    [5,'ylm','shanghai',35],
    [6,'wxl','beijing',16],
]

stus.insert(0,['编号','姓名','地址','年龄'])
book = xlwt.Workbook()
sheet = book.add_sheet('sheet1')

for row,stu in enumerate(stus):#控制行 0 1 2 3 4 5
    for col,s in enumerate(stu):#控制列
        sheet.write(row,col,s)

ages = [ s[-1] for s in stus if type(s[-1])!=str ]
avg_age = round(sum(ages) / len(ages),2)
content = '平均年龄:%s'%avg_age
sheet.write(row+1,0,content)

book.save('students.xls')

输出结果：