day7 openpyxl作业

day7 openpyxl作业

"""
Author:TianGuoHui
Create Time:2023/2/14 18:28
"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
import os

if os.path.exists("files/学生信息.xlsx"):
    workbook_1 = openpyxl.load_workbook("files/学生信息.xlsx")
else:
    workbook_1 = openpyxl.Workbook()
    workbook_1.save("files/学生信息.xlsx")

sheet_names = workbook_1.sheetnames

sheet_student = workbook_1.active

sheet_student.cell(1, 5).value = "留级建议"

max_row = sheet_student.max_row
max_column = sheet_student.max_column

font_1 = Font(
    name="黑体",
    color="026CA8",
    size=15
)
fill_1 = PatternFill(
    fill_type="solid",
    start_color="C3D79A"
)
for i in range(1,max_column+1):
    sheet_student.cell(1,i).font = font_1
    sheet_student.cell(1,i).fill = fill_1

for i in range(2, max_row + 1):
    if sheet_student.cell(i, 4).value < 60:
        sheet_student.cell(i, 4).font = Font(color="FF1526")
        sheet_student.cell(i, 5).value = "留级"
        sheet_student.cell(i, 5).font = Font(size=12,color="FF1526")

ag_1 = Alignment(
    vertical="center",
    horizontal="center"
)

side_1 = Side(
    border_style="medium",
    color="91bc41"
)
border_1 = Border(bottom=side_1,top=side_1,left=side_1,right=side_1)
for i in range(1, max_row+1):
    for j in range(1, max_column+1):
        sheet_student.cell(i, j).alignment = ag_1
        sheet_student.cell(i,j).border = border_1

workbook_1.save("files/学生信息.xlsx")