Day8——Excel操作作业

自动化办公Excel操作练习题

1.练习

import openpyxl

wb1 = openpyxl.Workbook()
wb1.save("files/example2.xlsx")
sheet1 = wb1.create_sheet("students")
m_r = sheet1.max_row
m_c = sheet1.max_column
sheet1.cell(1, 1).value = "姓名"
sheet1.cell(1, 2).value = "年龄"
sheet1.cell(1, 3).value = '性别'
sheet1.cell(1, 4).value = '电话'

while True:
    m_r = sheet1.max_row
    sheet1.cell(m_r+1, 1).value = input("请输入学生姓名")
    sheet1.cell(m_r+1, 2).value = input("请输入学生年龄")
    sheet1.cell(m_r+1, 3).value = input("请输入学生性别")
    sheet1.cell(m_r+1, 4).value = input("请输入学生电话")
    value = input("是否继续(Y/N)?")
    if value == 'N':
        break
wb1.save("files/example2.xlsx") #保存
print('输入完成！')

2.作业

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

wb = openpyxl.open("files/学生信息.xlsx")
if 'students1' in wb.sheetnames:
    sheet1 = wb["students1"]  # 获取工作簿中指定表名对应的工作表
else:
    sheet1 = wb.create_sheet("students1", 1)

# 设置字体
sheet1 = wb["students"]
f1 = Font(
    name="楷体",
    size=15,
)
f2 = Font(
    name="楷体",
    size=15,
    color="1E90FF"
)
f3 = Font(
    name="楷体",
    size=15,
    color="FF0000"
)


m_r = sheet1.max_row  # 获取sheet1
m_c1 = sheet1.max_column
sheet1.cell(1, m_c1+1).value = "留级建议"
m_c2 = sheet1.max_column


#设置单元格对齐方式
ag1 = Alignment(
    vertical='center',
    horizontal='center'
)
sheet1.column_dimensions['C'].width = 25
sheet1.column_dimensions['D'].width = 20
for column in range(1, m_c2 + 1):
    for row in range(1, m_r+1):
        sheet1.cell(row, column).font = f1
        sheet1.cell(1, column).font = f2
        sheet1.cell(row, column).alignment = ag1

# 设置填充
fill1 = PatternFill(
    fill_type='solid',
    start_color='f000FF00',
)
for first_row in range(1, 6):
    sheet1.cell(1, first_row).fill = fill1

# 设置边框样式
side1 = Side(
    border_style='medium',
    color='000fFFfF'
)

border1 = Border(bottom=side1, top=side1, left=side1, right=side1)
for column in range(1, m_c2 + 1):
    for row in range(1, m_r+1):
        sheet1.cell(row, column).border = border1
for score_row in range(2,m_r+1):
    value = sheet1.cell(score_row, 4).value
    if int(value) < 60:
        sheet1.cell(score_row, 4).font = f3
        sheet1.cell(score_row, 5).value = "留级"
print("操作完成！")
wb.save("files/学生信息.xlsx")  # 保存

效果展示