day7 Excel文件操作 作业

import openpyxl
import os

if os.path.exists(('files/lixi.xlsx')):
    wb2 = openpyxl.load_workbook('files/lixi.xlsx')
else:
    wb2 = openpyxl.Workbook()
    wb2.save('files/lixi.xlsx')
sheet1 = wb2.create_sheet('student')
count = 0
student = {}
line = ['姓名', '年龄', '性别', '电话']
while True:
    for x in range(4):
        if count == 0:
            sheet1.cell(1, x + 1).value = line[x]
    if count>0:
        sheet1.cell(count, 1).value = str(input('请输入姓名：'))
        sheet1.cell(count, 2).value = int(input('请输入年龄：'))
        sheet1.cell(count, 3).value = str(input('请输入性别：'))
        sheet1.cell(count, 4).value = str(input('请输入电话：'))
        e = input('是否继续？（Y/N）')
        if e =='N':
            break
    count += 1
wb2.save('files/lixi.xlsx')

import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import Side, Border
from openpyxl.styles import Alignment

wb = openpyxl.open('files/学生信息.xlsx')
sheet1 = wb.active
sheet1.cell(1, 5).value = '留级建议'

f1 = Font(
    name=None,
    color='DC143C'
)
f2 = Font(
    name=None,
    color='800080'
)
f3 = Font(
    name=None,
    size=15,
    color='0000FF',
    bold=True
)
f4 = Font(
    bold=True
)
# 1)创建填充对象
fill1 = PatternFill(
    fill_type='solid',
    start_color='98FB98'
)
# 1)创建边
s1 = Side(
    border_style='medium',
    color='00ff00'
)
# 2）创建边框
b1 = Border(
    top=s1,
    bottom=s1,
    left=s1,
    right=s1
)
al = Alignment(
    horizontal='center',  # 水平方向:center, left, right
    vertical='center'  # 垂直方向: center, top, bottom
)
for x in range(2, sheet1.max_row):
    sh1 = sheet1.cell(x, 4).value
    if int(sh1) <= 60:
        sheet1.cell(x, 5).value = '留级'
        sheet1.cell(x, 4).font = f1
        sheet1.cell(x, 5).font = f2

sheet1.row_dimensions[1].height = 25
sheet1.column_dimensions['A'].width = 18
sheet1.column_dimensions['B'].width = 15
sheet1.column_dimensions['C'].width = 18
sheet1.column_dimensions['D'].width = 10
sheet1.column_dimensions['E'].width = 18

for i in range(1, 6):
    sheet1.cell(1, i).font = f3
    sheet1.cell(1, i).fill = fill1
    for y in range(1, sheet1.max_column + 1):
        sheet1.cell(y + 1, 1).font = f4
        sheet1.cell(y, i).border = b1
        sheet1.cell(y, i).alignment = al
wb.save('files/学生信息.xlsx')