Day7-Excel文件样式作业

一、Excel文件样式作业

"""
Author:Ren Yulian
Create Time: 2023/2/14 17:15
Happy every day!
"""
import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

wb = openpyxl.open('files/学生信息.xlsx')
# sheet1 = wb.active
sheet1 = wb['students']

m_c1 = sheet1.max_column
# 修改单元格内容
sheet1.cell(1, m_c1+1).value = '留级建议'
wb.save('files/学生信息.xlsx')

m_r = sheet1.max_row
m_c = sheet1.max_column
# 1.设置行高和列宽度
# 1）工作表.row_dimensions[行号].height = 高度
# 2)工作表.column_dimensions[列号(字母)].width =
for r in range(1,m_r+1):
    sheet1.row_dimensions[r].height = 30
sheet1.column_dimensions['C'].width = 30
sheet1.column_dimensions['E'].width = 20

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
# 2.设置单元格字体样式
# 设置第一行字体
f1 = Font(
    name='宋体',
    color='00CED1',
    size=20,
    bold=True,
)
for col in range(1,m_c+1):
    sheet1.cell(1, col).font = f1

# 设置分数列的字体
f2 = Font(
    name='宋体',
    color='DC143C',
    size=15,
    bold=True,
)


# 1）创建字体对象
# 导入字体类
f3 = Font(
    name='宋体',
    size=15,
    bold=True,
)

# 2）设置单元格字体
for row in range(1,m_r+1):
    for col in range(1,m_c+1):
        sheet1.cell(row, col).font = f3

# 3.设置填充样式
# 1)创建填充对象
fill1 = PatternFill(
    fill_type='solid',
    # 起始颜色：fill_type为solid的时候只有起始颜色有效
    start_color='90EE90'
)

# 2)设置单元格填充样式
for col in range(1,m_c+1):
    sheet1.cell(1, col).fill = fill1

# 4.设置单元格对齐方式
ag1 = Alignment(
    # 垂直对齐方式：center、top、bottom
    vertical='center',
    # 水平对齐方式：center、letf、right
    horizontal='center'
)
# 2)设置单元格对齐方式
for row in range(1,m_r+1):
    for col in range(1,m_c+1):
        sheet1.cell(row, col).alignment = ag1

# 5.设置边框样式
# 1）创建边对象
side1 = Side(
    # 'dashDot', 'dotted', 'thin', 'double', 'medium', 'thick', 'dashed', 'mediumDashDot', 'slantDashDot', 'mediumDashed', 'mediumDashDotDot', 'hair', 'dashDotDot'
    border_style='medium',
    color='3CB371'
)

# 2)创建边框
border1 = Border(bottom=side1, right=side1)

# 3)设置单元格边框样式
for row in range(1,m_r+1):
    for col in range(1,m_c+1):
        sheet1.cell(row, col).border = border1

# 获取第四列的分数
col4 = []
for row in range(2,m_r+1):
    col4.append(sheet1.cell(row,4).value)
for index in range(0,len(col4)):
    if col4[index] < 60:
        sheet1.cell(index+2, 4).font = f2
        sheet1.cell(index+2, 5).value = '留级'


wb.save('files/学生信息.xlsx')

二、Excel文件写操作练习

if os.path.exists('files/example4.xlsx'):
    wb = openpyxl.load_workbook('files/example4.xlsx')
else:
    wb = openpyxl.Workbook()
    wb.save('files/example4.xlsx')

if 'student' in wb.sheetnames:
    sheet = wb['student']
else:
    sheet = wb.create_sheet('student', 0)

sheet.cell(1,1).value = '姓名'
sheet.cell(1,2).value = '年龄'
sheet.cell(1,3).value = '性别'
sheet.cell(1,4).value = '电话'

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

m_c = sheet.max_column
r = 1
while True:
    r += 1
    name = input('请输入学生的姓名：')
    age = input('请输入学生的年龄：')
    gender = input('请输入学生的性别：')
    tel = input('请输入学生的电话：')
    yn = input('是否继续(Y/N)：')

    sheet.cell(r, 1).value = name
    sheet.cell(r, 2).value = age
    sheet.cell(r, 3).value = gender
    sheet.cell(r, 4).value = tel

    if yn == 'N':
        break

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