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、小练习