筛选学生留级练习

练习：

读取files/stu.xlsx文件中的学生表数据，新建表格，扩展学生的升学建议，并对学生数据进行筛选，在升学建议栏打印出”留级“或者”升学“建议，并对为表格进行一定的美好。

import openpyxl
import os

# 检查创建并读取工作簿student
if os.path.exists('files/stu.xlsx'):
    wb = openpyxl.load_workbook('files/stu.xlsx')
else:
    wb = openpyxl.Workbook()
# 检查创建并读取工作表student
if 'students' in wb.sheetnames:
    sheet = wb['students']
else:
    sheet = wb.create_sheet('students', 0)
# 获取最大行号和列号
rStu = sheet.max_row  # 行
cStu = sheet.max_column  # 列
print(rStu, cStu)
# 导入字体类
from openpyxl.styles import Font, PatternFill, Side, Border, Alignment

f1 = Font(
    name='楷体',
    size=15,
)
f2 = Font(
    name='楷体',
    size=15,
    color='00FF00'
)
f3 = Font(
    name='楷体',
    size=15,
    color='FF0000'
)
# 3.设置填充样式
fill1 = PatternFill(
    fill_type='solid',
    fgColor='7FFFAA'
)
# 4.设置边框样式

# 1)创建边
s1 = Side(
    border_style='thick',
    color='3CB371'
)
# 2）创建边框
b1 = Border(
    bottom=s1,
    right=s1
)
aa = Alignment(
    horizontal='center',
    vertical='center'
)
sheet.cell(1, cStu + 1).value = '升学建议'
sheet.cell(1, cStu + 1).font = f1

rStu = sheet.max_row  # 行
cStu = sheet.max_column  # 列

for i in range(cStu):
    sheet.cell(1, i + 1).fill = fill1

print(rStu, cStu)
for x in range(rStu):
    if x > 0:
        if int(sheet.cell(x + 1, 4).value) < 60:
            sheet.cell(x + 1, 5).value = '留级'
            sheet.cell(x + 1, 5).font = f3
        else:
            sheet.cell(x + 1, 5).value = '升学'
            sheet.cell(x + 1, 5).font = f2
    for y in range(cStu):
        sheet.cell(x + 1, y + 1).alignment = aa
        sheet.row_dimensions[x].height = 20
        sheet.column_dimensions[chr(65 + y)].width = 15
        sheet.cell(x + 1, y + 1).border = b1
        if y == 4:
            sheet.cell(x + 1, y + 1).border = b1
            pass
        else:
            sheet.cell(x + 1, y + 1).font = f1

wb.save('files/stu.xlsx')