表格美化缺考打印练习

练习：

新建表，并在同一工作簿上对新表进行相应美化和打印出缺考考生：

import xlrd, xlwt

from xlutils.copy import copy

wbook = xlrd.open_workbook('files/stuinfo.xls')
wbook1 = copy(wbook)

sheet0 = wbook.sheet_by_index(0)
# 可操作表对象为：sheet1
sheet1 = wbook1.add_sheet('效果')
# 字体
f0 = xlwt.Font()
f0.name = '楷体'
f0.height = 18 * 18
# 紫色字体
f1 = xlwt.Font()
f1.name = '楷体'
f1.height = 18 * 18
f1.colour_index = 28

# 较大字体
f2 = xlwt.Font()
f2.name = '楷体'
f2.bold = True
f2.height = 22 * 22
# 红色字体
f3 = xlwt.Font()
f3.name = '楷体'
f3.height = 18 * 18
f3.colour_index = 10

# 居中对齐
a1 = xlwt.Alignment()
a1.horz = xlwt.Alignment.HORZ_CENTER
a1.vert = xlwt.Alignment.VERT_CENTER

# 添加填充样式
p1 = xlwt.Pattern()
p1.pattern = xlwt.Pattern.SOLID_PATTERN
p1.pattern_fore_colour = 51  # 金色填充色
p2 = xlwt.Pattern()
p2.pattern = xlwt.Pattern.SOLID_PATTERN
p2.pattern_fore_colour = 10  # 红色填充

# 添加边框样式
b1 = xlwt.Borders()
b1.bottom = b1.top = b1.right = b1.left = 1

style0 = xlwt.XFStyle()  # 普通属性
style1 = xlwt.XFStyle()  # 对齐，填充，黑色字体体，大字体
style2 = xlwt.XFStyle()  # 对齐，紫色字体
style3 = xlwt.XFStyle()  # 对齐，红色字体
style4 = xlwt.XFStyle()  # 对齐，红色填充

style0.alignment = a1
style1.alignment = a1
style2.alignment = a1
style3.alignment = a1
style4.alignment = a1

style1.pattern = p1
style4.pattern = p2

style0.font = f0
style1.font = f2
style2.font = f1
style3.font = f3
style4.font = f0

style0.borders = b1
style1.borders = b1
style2.borders = b1
style3.borders = b1
style4.borders = b1

nc = sheet0.ncols
nr = sheet0.nrows
# 运行指定的行可以设置高度
sheet1.row(0).height_mismatch = True
# 设置行的高度
sheet1.row(0).height = 15 * 60

for x in range(nr):
    for y in range(nc):
        print(x, y)
        # 遍历单元格，并以列为单位给sheet1的单元格赋值
        if x == 0:
            sheet1.write(x, y, sheet0.col_values(y)[x], style=style1)
        elif y == 0 and x != 0:
            sheet1.write(x, y, sheet0.col_values(y)[x], style=style0)
        elif int(sheet0.col_values(y)[x]) == 0:
            sheet1.write(x, y, '缺考', style=style3)
        elif int(sheet0.col_values(y)[x]) < 60:
            sheet1.write(x, y, sheet0.col_values(y)[x], style=style4)
        else:
            sheet1.write(x, y, sheet0.col_values(y)[x], style=style0)

for x in range(1, nr):
    sheet1.row(x).height_mismatch = True
    # 设置高度
    sheet1.row(x).height = 10 * 60
for y in range(nc):
    # 设置宽度
    sheet1.col(y).width = 40 * 256

wbook1.save('files/stuinfo.xls')
![之前](https://img-blog.csdnimg.cn/347e7cb3a1d4442b926ea5dbc9b3a293.png)
![之后](https://img-blog.csdnimg.cn/4e3900c15a2b40d1af14bbc8d722446e.png)