练习:
读取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')