Python自动化-Excel:openpyxl练习，求和、条件判断

1、将工作簿中每张工作表中指定单元格的值汇总

import openpyxl
wb = openpyxl.load_workbook('练习1.xlsx')

# 将每张工作表中单元格D6的值汇总起来
v = []
for sh in wb.worksheets:
    v.append(sh["D6"].value)
print(sum(v))

# 将每张工作表中单元格D6的值汇总起来，代码简化版
v2 = sum([sh["D6"].value for sh in wb.worksheets])
print(v2)

2、按行、列求和

import openpyxl
wb = openpyxl.load_workbook("练习2.xlsx")
sh = wb["Sheet1"]

# 显示所有单元格的值
for r in sh.rows:
    print([c.value for c in r])

# 显示除第一行、第一列的单元格的值
for r in list(sh.rows)[1:]:
    print([c.value for c in r][1:])

# 显示除第一行、第一列的单元格的值,使用iter_rows()
for r in sh.iter_rows(min_row=2,min_col=2):
    print([c.value for c in r])

# 按行求和（无第一行）
for r in list(sh.rows)[1:]:
    vr = [c.value for c in r]
    print(vr) # 一行的值
    print(vr[0],sum(vr[1:]))

# 按列求和（无第一列）
for co in list(sh.columns)[1:]:
    vc = [c.value for c in co]
    print(vc) # 一列的值
    print(vc[0],sum(vc[1:]))

3、条件判断并进行替换：成绩为空的标记缺考

import openpyxl
wb = openpyxl.load_workbook("练习3.xlsx")
sh = wb["Sheet1"]

# 将空值赋值为：缺考
for r in sh.iter_rows(min_row=2,min_col=2):
    for c in r:
        if c.value == None:
            c.value = "缺考"
wb.save("1.xlsx")

4、条件判断并新增一列：总分大于270分评价为，优秀

import openpyxl
wb = openpyxl.load_workbook("练习2.xlsx")
sh = wb["Sheet1"]

# 总分大于270分是优秀
sh["E1"] = "评价"
quyu = sh.iter_rows(min_row=2,min_col=2)
for r in quyu:
    vr = [c.value for c in r][:-1]
    # 新增了E列，除E1外其他为空值，[:-1]:不包含最后一列E
    if sum(vr) >= 270:
        r[-1].value = "优秀"
        # 每行的最后一个单元格赋值为“优秀”
wb.save("2.xlsx")

参考资料：

B站：孙兴华《中文讲Python从入门到办公自动化》