import xlrd
book = xlrd.open_workbook(r"C:\Users\XCP\PycharmProjects\pythonProject1\cheng_ji.xls") # 获取工作表格
sheet1 = book.sheets()[0] # 获取表格sheet
col0_value = sheet1.col_values(0, 1) # 获取学生姓名列表
col1_value = sheet1.col_values(1, 1) # 获取sheet第2列成绩列表
col_dict = zip(col0_value, col1_value) # 将姓名和成绩存为字典以便于遍历
avg_score = sum(col1_value) / len(col1_value) # 求取平均值
highest_score = max(col1_value) # 求取最高分数
lowest_score = min(col1_value) # 求取最低分数
print('成绩统计如下**')
print('平均成绩:', avg_score)
print('最高分:{}'.format(highest_score))
print('最低分:{}'.format(lowest_score))
for k, v in dict(col_dict).items():
if int(v) < 60:
print('不及格的学生:{0} 成绩:{1}'.format(k, v))