今天学习什么？
今天我们来学习如何读取Excel中的数据，让Python帮你分析表格！
读取单元格数据
单个单元格：
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
# 读取单个单元格
value = ws['A1'].value
print(f"A1的值：{value}")
# 也可以用行号和列号
value = ws.cell(row=1, column=1).value
print(f"第1行第1列的值：{value}")
wb.close()
读取一行数据：
# 读取第1行
row1 = ws[1]
for cell in row1:
print(cell.value, end=" ")
print()
# 读取第2行
row2 = ws[2]
for cell in row2:
print(cell.value, end=" ")
print()
读取一列数据：
# 读取A列
for cell in ws['A']:
print(cell.value)
遍历所有数据
方法1：使用iter_rows()
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
# 遍历所有行
for row in ws.iter_rows(values_only=True):
print(row)
wb.close()
方法2：使用iter_cols()
# 遍历所有列
for col in ws.iter_cols(min_col=2, values_only=True):
print(col)
方法3：使用循环
# 遍历所有单元格
for row in range(1, ws.max_row + 1):
for col in range(1, ws.max_column + 1):
cell = ws.cell(row=row, column=col)
print(cell.value, end="\t")
print()
实际案例
案例1：读取学生成绩
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
print("学生成绩表")
print("=" * 40)
# 读取表头
headers = []
for cell in ws[1]:
headers.append(cell.value)
print(" ".join(headers))
print("-" * 40)
# 读取数据
for row in ws.iter_rows(min_row=2, values_only=True):
name, math, chinese, english = row
avg = (math + chinese + english) / 3
print(f"{name:<8}{math:<6}{chinese:<6}{english:<6}{avg:.1f}")
wb.close()
案例2：查找最高分
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
# 查找数学最高分
max_math = 0
max_student = ""
for row in ws.iter_rows(min_row=2, values_only=True):
name, math, chinese, english = row
if math > max_math:
max_math = math
max_student = name
print(f"数学最高分：{max_student}，{max_math}分")
# 查找平均分最高的学生
max_avg = 0
max_avg_student = ""
for row in ws.iter_rows(min_row=2, values_only=True):
name, math, chinese, english = row
avg = (math + chinese + english) / 3
if avg > max_avg:
max_avg = avg
max_avg_student = name
print(f"平均分最高：{max_avg_student}，{max_avg:.1f}分")
wb.close()
案例3：统计各科平均分
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
# 统计各科总分
total_math = 0
total_chinese = 0
total_english = 0
student_count = 0
for row in ws.iter_rows(min_row=2, values_only=True):
name, math, chinese, english = row
total_math += math
total_chinese += chinese
total_english += english
student_count += 1
# 计算平均分
avg_math = total_math / student_count
avg_chinese = total_chinese / student_count
avg_english = total_english / student_count
print(f"学生人数：{student_count}")
print(f"数学平均分：{avg_math:.1f}")
print(f"语文平均分：{avg_chinese:.1f}")
print(f"英语平均分：{avg_english:.1f}")
wb.close()
动手实践
今天的任务：
练习1：读取课程表并显示
import openpyxl
wb = openpyxl.load_workbook('课程表.xlsx')
ws = wb.active
print("我的课程表")
print("=" * 50)
for row in ws.iter_rows(values_only=True):
for cell in row:
if cell isNone:
print(f"{'':<8}", end="")
else:
print(f"{str(cell):<8}", end="")
print()
wb.close()
练习2：分析成绩数据
import openpyxl
wb = openpyxl.load_workbook('学生成绩.xlsx')
ws = wb.active
print("成绩分析报告")
print("=" * 40)
# 收集所有成绩
all_scores = []
student_scores = []
for row in ws.iter_rows(min_row=2, values_only=True):
name, math, chinese, english = row
scores = [math, chinese, english]
all_scores.extend(scores)
student_scores.append((name, scores))
# 统计信息
print(f"学生人数：{len(student_scores)}")
print(f"科目数量：3")
print(f"总成绩数：{len(all_scores)}")
print(f"最高分：{max(all_scores)}")
print(f"最低分：{min(all_scores)}")
print(f"平均分：{sum(all_scores)/len(all_scores):.1f}")
# 查找单科最高分
subjects = ['数学', '语文', '英语']
for i, subject in enumerate(subjects):
max_score = 0
max_student = ""
for name, scores in student_scores:
if scores[i] > max_score:
max_score = scores[i]
max_student = name
print(f"{subject}最高分：{max_student}，{max_score}分")
wb.close()
本章小结
今天学到了什么？
明天预告：
明天我们将学习如何写入Excel数据！
练习题
学习心得
今天的学习心得：_______________
遇到的问题：_______________
明天要重点学习：_______________
