python操作excel

1、写excel

import xlwt

# 写excel
book = xlwt.Workbook()
sheet = book.add_sheet(‘sheet1‘)

# 第一行第一列
sheet.write(0,0,‘学生姓名‘)
# 第二行第一列
sheet.write(1,0,‘lzh‘)
sheet.write(2,0,‘zsb‘)
sheet.write(3,0,‘zrh‘)

# 如果用wps,后缀名可写其他excel的格式
book.save("student.xls")

2、写excel小练习

import xlwt

book = xlwt.Workbook()
sheet = book.add_sheet(‘sheet1‘)


title = [‘编号‘,‘姓名‘,‘语文成绩‘,‘数学成绩‘,‘英语成绩‘,‘总分‘,‘平均分‘]
# 处理表头
row = 0
for t in title:
    sheet.write(0,row,t)
    row += 1

data = [
    ["1","小花",99,100,98.5],  # 1
    ["2","小王",90,30.5,95],  # 2
    ["3","小明",67.5,49.6,88]   # 3
]

# 1表示下标从1开始计数
for row,v in enumerate(data,1): #行
    sum_score = sum(v[2:])#算总分
    avg_score = round(sum_score / 3,2) #算平均分
    v.append(sum_score)
    v.append(avg_score)
    print(v)
    for col,value in enumerate(v):
        # 从第二行，第一列开始写入数据
        sheet.write(row,col,value)

book.save("students.xls")  # 如果你用的是wps的话

结果：

3、读excel

import xlrd

book = xlrd.open_workbook(‘students.xls‘)

# 根据下标取
sheet = book.sheet_by_index(0)
# sheet = book.sheet_by_name(‘sheet1‘)

# 指定单元格的内容
print(sheet.cell(0,0).value)
# 取整行的数据
print(sheet.row_values(1))
# 取整列的数据
print(sheet.col_values(0))

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

4、修改excel

from xlutils import copy
import xlrd
import os


book = xlrd.open_workbook(‘students.xls‘)

# 通过xlutils的copy方法复制一个
new_book = copy.copy(book)
sheet = new_book.get_sheet(0)

sheet.write(0,0,‘id‘)
sheet.write(0,1,‘name‘)

# 备份原有文件
os.rename(‘students.xls‘,‘students_bak.xls‘)

# 写入新文件
new_book.save(‘students.xls‘)

结果：