Day8-xlrd和xlwt操作Excel文件作业

Day8-xlrd和xlwt操作Excel文件作业

import xlwt, xlrd
from xlutils.copy import copy

# 1. 使用xlrd打开已经存在的工作薄对象(得到一个只读的工作薄)
wb1 = xlrd.open_workbook('files/副本作业数据.xls')

# 2. 将只读的工作薄转换成可写的工作薄
wb2 = copy(wb1)

sheet = wb2.add_sheet('sheet', cell_overwrite_ok=True)

# 边框
b1 = xlwt.Borders()
b1.bottom = b1.top = b1.left = b1.right = 1
b1.bottom_colour = b1.top_colour = b1.left_colour = b1.right_colour = 8

# 对齐样式
al1 = xlwt.Alignment()
al1.vert = xlwt.Alignment.VERT_CENTER
al1.horz = xlwt.Alignment.HORZ_CENTER

# 关键字样式
style1 = xlwt.XFStyle()
font1 = xlwt.Font()
font1.name = '黑体'
font1.bold = True
font1.height = 15*15
font1.colour_index = 8
style1.font = font1
style1.borders = b1
style1.alignment = al1
p2 = xlwt.Pattern()
p2.pattern = xlwt.Pattern.SOLID_PATTERN
p2.pattern_fore_colour = 52
style1.pattern = p2

# 名字的样式
style2 = xlwt.XFStyle()
font2 = xlwt.Font()
font2.colour_index = 28
style2.font = font2
style2.borders = b1
style2.alignment = al1

# 补考的样式
style3 = xlwt.XFStyle()
font3 = xlwt.Font()
font3.colour_index = 10
style3.font = font3
style3.borders = b1
style3.alignment = al1

# 不及格的样式
style4 = xlwt.XFStyle()
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 47
style4.pattern = p1
style4.borders = b1
style4.alignment = al1

# 及格成绩
style5 = xlwt.XFStyle()
style5.borders = b1
style5.alignment = al1

# 设置关键词内容
sheet_wb1 = wb1.sheet_by_name('原始数据')
list_key = sheet_wb1.row_values(0)
for col in range(len(list_key)):
    sheet.write(0, col, list_key[col], style=style1)
sheet.row(0).height_mismatch = True
sheet.row(0).height = 10*60

# 设置每一列的列宽
for col in range(len(list_key)):
    sheet.col(col).width = 25 * 256
    
# 设置姓名的内容
list_name = sheet_wb1.col_values(0, 1, 19)
for row in range(1, len(list_name)+1):
    sheet.write(row, 0, list_name[row-1], style=style2)

for col in range(1, len(list_key)):
    scores = sheet_wb1.col_values(col, 1, 19)
    for row in range(1, len(scores) + 1):
        if sheet_wb1.cell(row, col).value < 60:
            sheet.write(row, col, scores[row - 1], style=style4)
        if sheet_wb1.cell(row, col).value == 0:
            sheet.write(row, col, '补考', style=style3)
        else:
            sheet.write(row, col, scores[row - 1], style=style5)

wb2.save('files/副本作业数据.xls')