Python学习笔记（21）Excel操作相关模块（xlwt/xlrd/xlutils）

import xlwt

book = xlwt.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("明星.xls")#如果是wps的话，可以使用xlsx

import xlwt
book = xlwt.Workbook()
sheet = book.add_sheet("学生成绩")
title = [‘学号‘,‘姓名‘,‘语文成绩‘,‘数学成绩‘,‘英语成绩‘,‘总分‘,‘平均分‘]

#优化前：
# data = {
# 	"1":["小花",99,100,98.5],
# 	"2":["小王",90,30.5,95],
# 	"3":["小明",67.5,49.6,88]
# }
# #处理表头
# for col,t in enumerate(title):
#     sheet.write(0,col,t)
# row = 1
# for k,v in data.items():
#     v.insert(0,k)#插入编号,将字典类型转换为list
#     sum_score = sum(v[2:])#计算总分
#     avg = round(sum_score/3,2)#计算平均分，并保留两位小数
#     v.append(sum_score)
#     v.append(avg)
#     col = 0
#     for value in v:
#         sheet.write(row,col,value)
#         col+=1
#     row +=1
# book.save(‘学生成绩表.xls‘)

#优化后：
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],#
	["3","小明",67.5,49.6,88]#
]

for row,v in enumerate(data,1): #行，row为从1开始的索引，默认枚举型从0开始
    sum_score = sum(v[2:])#算总分
    avg_score = round(sum_score / 3,2) #算平均分，round取两位小数
    v.append(sum_score)
    v.append(avg_score)
    for col,value in enumerate(v):
        sheet.write(row,col,value)

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

import xlrd
book = xlrd.open_workbook(‘students.xls‘)#打开表格
sheet = book.sheet_by_index(0)#打开sheet
# sheet = book.sheet_by_name("学生成绩")
print(sheet.cell(0,0).value)#指定单元格内容
print(sheet.row_values(1))#取索引值为1的整行数据
print(sheet.col_values(1))#取索引值为1的整列数据
print(sheet.nrows)#总行数
print(sheet.ncols)#总列数

id
[‘1‘, ‘小花‘, 99.0, 100.0, 98.5, 297.5, 99.17]
[‘name‘, ‘小花‘, ‘小王‘, ‘小明‘]
4
7

from xlutils import copy
import xlrd
import os

book = xlrd.open_workbook(‘students.xls‘)#打开表格
new_book = copy.copy(book)#拷贝表格
sheet = new_book.get_sheet(0)
sheet.write(0,0,"id")#将0行0列单元格修改为id
sheet.write(0,1,"name")
os.rename(‘students.xls‘,‘students_bak.xls‘)#为防止表格被覆盖无法复原，先备份表格
new_book.save(‘students.xls‘)