python基础篇 21-读写excel 修改 excel

读excel

import xlrd

book = xlrd.open_workbook('students.xls')       # 用完之后自动关闭

# sheet = book.sheet_by_index(0)
sheet = book.sheet_by_name('sheet1')
print(book.sheets())#所有的sheet页，返回的是一个list，list里面就是每个sheet对象

for s in book.sheets():#  获取每sheet页的第二行值
    print(s.row_values(1))


print(sheet.cell(0,0).value)        # 获取某行某列的内容

print(sheet.row_values(0))          # 获取某行的内容
print(sheet.row_values(1))

print(sheet.col_values(0))          # 获取某列的内容
print(sheet.col_values(1))

print(sheet.nrows)#多少行           # 获取多少行
print(sheet.ncols)#多少列           # 获取多少列

写excel

import xlwt

book = xlwt.Workbook()                  # 创建book对象
sheet = book.add_sheet('students')      # 创建sheet对象
#
sheet.write(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')               #如果后缀写成xlsx，使用微软的office打不开


#编号 姓名 地址 年龄

#
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

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')

修改excel

#思路：
    # copy一个新book，然后使用就book的sheet页读取数据，sheet.cell(1,2).value 获取数据，
    # 然后使用new_sheet对象写入new_sheet.write(row,col,string)
    # 然后new_book保存为同名文件。

from xlutils import copy
import xlrd

book = xlrd.open_workbook('stu_info.xls')
sheet = book.sheet_by_index(0)


new_book = copy.copy(book)      # copy一个新的book
# print(dir(new_book))
copy_sheet = new_book.get_sheet(0)
# print(dir(copy_sheet))
                 #1,7     #1 2 3 4 5 6
for row in range(1,sheet.nrows-1):#1 2 3 4 5 6 7
    addr = sheet.cell(row,2).value
    addr = addr.replace('beijing',"北京").replace('shanghai','上海')
    copy_sheet.write(row,2,addr)


new_book.save('stu_info.xls')