Python函数-操作Excel

1、写Excel

import xlwt

book = xlwt.Workbook()  # 生成一个Workbook
sheet = book.add_sheet('sheet1')  # 一个sheet页
sheet.write(0, 0, '学生姓名')  # 行、列
sheet.write(1, 0, '大白')
sheet.write(2, 0, '小白')
sheet.write(3, 0, '小黑')
book.save('student.xls')  # 用office只能用xls结尾的，如果用wps，可以用.xlsx

# 小练习
data = {
    "1": ["小花", 99, 100, 98.5],
    "2": ["小王", 90, 30.5, 95],
    "3": ["小明", 67.5, 49.6, 88]
}
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],
    ["2", "小王", 90, 30.5, 95],
    ["3", "小明", 67.5, 49.6, 88]
]
rows = 1
for row in data:  # 行循环
    col = 0
    sum_score = sum(row[2:])  # 算总分
    avg_score = round(sum_score / 3, 2)  # 算平均分
    row.append(sum_score)
    row.append(avg_score)
    for column in row:  # 列循环
        sheet.write(rows, col, column)
        col += 1
    rows += 1

# 优化之后（enumerate）
for rows, row in enumerate(data, 1):  # 行循环，用枚举将二维数组转成带下标的值
    sum_score = sum(row[2:])  # 算总分
    avg_score = round(sum_score / 3, 2)  # 算平均分，取两位小数点
    row.append(sum_score)
    row.append(avg_score)
    for col, column in enumerate(row):  # 列循环
        sheet.write(rows, col, column)

book.save('student.xls')

2、读Excel

import xlrd

book = xlrd.open_workbook('student.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)  # 多少列

3、修改Excel

from xlutils import copy
import xlrd
import os

book = xlrd.open_workbook('student.xls') #open这个文件
new_book = copy.copy(book)  #将book拷贝到xlutils模块的book
sheet = new_book.get_sheet(0)
sheet.write(0, 0, 'id')  #一个单元格改
sheet.write(0, 1, 'name')
os.rename('student.xls', 'student_bak.xls')   #备份一哈
new_book.save('student.xls')  #保存

4、小练习