python 在excle表格统计与制作方面的实践

zohoChou

年前老师让我整个excel统计全年级的成绩，差点没搞死我，于是痛定思痛，自己搞了个python出来，力求一个文件解决一切。。。。。。

【2022.06.24已更新】

吾爱首发完整解析（csdn上面的也是我写的，但是极其简略，别说什么抄袭改写了）
注，这次的没发csdn

我回来了！！！
因为学校把51假期挪到了暑假，没法鸽了（狗头
你木见过的船新版本等你来探……

1.0.1版……

事实证明意外还是有的……

更新内容：增加了判定缺考的功能。（其他零分的情况默认为因故缺考，不计入有效科目内）

只更新了精简注释版

目录架构更改：需要在目录下新建一个unattended.xlsx的文件

     __________________

例：|  AAA   |      1       |     一共两列，一列是姓名，一列记录那个人缺考的科目数量。

    --------------------

假的更新日志（溜……# 1.0.0(最后一版，如果没有意外……)

1.把几乎所有的变量全都进行了规范化命名，再也不是for i, for j, for k了 ……

注：函数外变量为完整命名，函数内为简化命名，如：each_class_stu -> class_stu 等。

2.重写40%以上逻辑特别是当时自己写的取总人数，绕了一大圈……

（逻辑上还可以优化，但是由于担心以后维护时看不懂，专门把逻辑写的很简单易懂【有小部分冗余】）。

3.回归初心，以pandas为主体，openpyxl专门格式化输出。### （因没看懂官方文档，均为pandas输出后使用openpyxl重读并重新输出，未使用openpyxl直接协同pandas）

4.重写注释，增加函数说明

小贴士：pandas与openpyxl有诸多不同，不要混了
传参一定不要反复逮着i用，极容易出现参数值混乱……


从未写过这么复杂的python……
注：实际上传时删掉了学校信息，可能少了十几个字


原始目录架构


原始文件：

结束运行后的目录架构

处理后的Sheet1

处理生成的Sheet2（排名已处理）

处理生成的汇总表


上代码！

# coding=utf-8
# 第一句很有必要！因为很多系统(特别是Windows，中文编码容易出问题)

# 为了执行cmd命令
import os
from time import sleep

# 核心模块
import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Border, Font, NamedStyle, Side

df_all_score = pd.DataFrame(columns=["班级",
                                     "学号",
                                     "姓名",
                                     "不及格门数",
                                     "平均分",
                                     "总分",
                                     "排名",
                                     "级部排名"])
# 这是提前定义的，储存所有学生信息的dataframe

file_dir = os.path.split(os.path.realpath(__file__))[0] + "\\"
# 储存表格的目录(当前目录)

def clean(mood: int):
    """清除之前的生成文件\n
    mood=0 -> 清除所有\n
    mood=1 -> 仅清除Pandas的生成\n
    因程序测试时每次都会输出*_*.xlsx，为方便起见，每次生成前先删除旧的生成文件。"""
    if(mood):
        # 直接利用0/1判断False/True
        os.system("del /f /q /s " + file_dir + "*pandas*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)
    else:
        os.system("del /f /q /s " + file_dir + "*_*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)

def read_xlsx(class_i: str):
    """读表格并返回表格路径、表格内容"""

    # 根据表格名称，<10与>=10两种情况(已在最底下的for循环里面处理为str_i)
    full_path = file_dir + "临床2021" + class_i
    sheet = pd.read_excel((full_path + ".xlsx"), header=1)
    # header = 1 意为把表格的第二行作为标题。(忽略了第一行的读取，最后还得重新生成第一行……)

    #  [5,2]为姓名起始序号
    # 请注意，python的计数比较别扭，都是从0开始。上面的注释便于以后查看、改写。
    return full_path, sheet

def format_each(df: pd.core.frame.DataFrame, r: int, c: int):
    """处理单元格的NaN问题"""
    each_grade = df.iloc[r + 5, c + 4]
    if(pd.isna(each_grade)):
        # 注：只能使用pd.isna进行判断，除非使用try，int()等一律失效
        df.iloc[r + 5, c + 4] = 0
        each_grade = 0
    each_grade = int(each_grade)
    # 方便下面的再次取值(如果有的话)，防止部分成绩的储存格式为文本型
    df.iloc[r + 5, c + 4] = each_grade
    return each_grade

def get_sum_average_grade(class_stu: int, sheet: pd.core.frame.DataFrame):
    """计算有效课程数量与总分，并求有效平均分"""
    # 完全可以使用loc[]进行取值，但是最原始的表格里面(pandas读取之前)有合并单元格的一个大标题
    # 还牵扯到了中文编码的识别问题……建议不要用。
    for row in range(class_stu):
        sum_grade = 0
        # 重新计算个人总分
        eff_count = 0  # effective_count
        # 我们学校要求的是用平均成绩计算，且不把0分科目(一般都是缓考造成的)计算在内
        # 所以说需要计算有意义的科目【这里应该能简写，希望大佬指点】
        for column in range(11):
            each_subject_garde = format_each(sheet, row, column)

            if (each_subject_garde != 0):
                # 如果成绩不为 0 则计数
                sum_grade += each_subject_garde  # 计算总成绩
                eff_count += 1
                # eff_count 有分数课程的总数

        sheet.iloc[row + 5, 17] = sum_grade

        if(eff_count == 0):
            sheet.iloc[row + 5, 16] = 0  # Avoid ZeroErr
        else:
            sheet.iloc[row + 5, 16] = (sum_grade / eff_count)  # 输出平均分

def rank_grades(class_stu: int, sheet1: pd.core.frame.DataFrame):
    """成绩排序"""
    original_list = []
    for i in range(class_stu):
        original_list.append(sheet1.iloc[i + 5, 16])  # 储存的是原始成绩
    ranked_list = sorted(original_list, reverse=True)  # 排序后的原始成绩
    rank_list = [1]  # 名次(用来对应原始姓名)
    # 演示：
    # original_list： [59，88，90，88，83，60]
    # ranked_list：   [90，88，88，83，60，59]
    # rank_list：     [1， 2， 2， 4， 5， 6 ]
    # final_list：    [6， 2， 1， 2， 4， 5 ]
    # (最后通过对应ranked_list与rank_list的对应关系让final_list变成原来顺序的纯名次)

    # 一开始尝试过用字典或列表嵌套的方式，都失败了……

    """生成rank_list"""
    # 实际排名
    rank = 1
    # 用ranked_list排序，第一个一定是第一名啊
    count = 0
    # 计算同分数段有多少人
    for i in range(class_stu - 1):
        if(ranked_list[i] > ranked_list[i + 1]):
            # 正常情况
            rank = rank + count + 1
            count = 0
            # 别忘了count清零
        else:
            # 名次相等的情况
            rank = rank
            count += 1
        rank_list.append(rank)

    """这里将生成final_list，通过对应使其变为顺序
    不变的名次排序，然后直接输出到表格即可"""
    final_list = original_list  # 初始化final_list
    # 两个循环匹配相同项
    for i in range(len(original_list)):
        j = 0
        while (original_list[i] != ranked_list[j]):
            j += 1
        final_list[i] = rank_list[j]

    for i in range(class_stu):
        # 更改到表格的内容
        sheet1.iloc[i + 5, 19] = final_list[i]

def sheet2_produce(sum: int, sheet1: pd.core.frame.DataFrame, class_stu: str):
    """根据Sheet1的内容生成Sheet2数据"""
    class_stu = class_stu + "班"
    sheet2 = sheet1.filter(
        items=["学号", "姓名", "不及格门数", "平均分", "总分", "排名"])
    # 删除原来的不相关列即可
    sheet2.drop([0, 1, 2, 3, 4, sum + 5, sum + 6],
                inplace=True)
    # 插入班级列，方便对应，不再使用学号进行对应。
    sheet2.insert(0, "班级", class_stu)
    sheet2.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    # 注：inplace可免于自赋值
    # kind="mergesort"使其有序排序，避免学号混乱。请自行搜索mergesort。
    # 排序依据平均分，升序。
    return sheet2

def pandas_xlsx_write(path: str, sheet1: pd.core.frame.DataFrame, sheet2: pd.core.frame.DataFrame):
    """pandas数据处理结束后写入表格"""
    with pd.ExcelWriter(path + "_out_pandas.xlsx") as writer:
        # 使用with语句可以避免Sheet2输出时覆盖Sheet1导致最后表格里只有一个sheet2的情况
        sheet1.to_excel(writer,
                        sheet_name="Sheet1",
                        index=False,
                        engine="openpyxl")
        sheet2.to_excel(writer,
                        sheet_name="Sheet2",
                        index=False,
                        engine="openpyxl")
    print("Pandas汇总各班成绩完成")

def format_out_sheet(sheet_path: str, class_stu: int, str_num: str, dir: str):
    """用Openpyxl格式化文档并输出\n
    同时为生成汇总表准备\n
    """
    wb = openpyxl.load_workbook(sheet_path + "_out_pandas.xlsx")
    ws1 = wb["Sheet1"]
    thin = Side(border_style="thin", color="000000")
    # 定义边框样式
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    # 定义一种样式方便后面直接使用
    wb.add_named_style(normal_style)

    ws1.insert_rows(0, 1)
    ws1["A1"] = "班级成绩表 2021-2022-1 2021级临床" + str_num + "班"
    # 插入一行
    ws1.merge_cells("A1:T1")
    ws1["A1"].font = Font(name="宋体", size=11)
    ws1["A1"].alignment = Alignment(horizontal="center",
                                    vertical="center",
                                    wrapText=False)
    ws1.row_dimensions[1].height = 20
    # 合并单元格、设置字体、居中、行高

    for i in list("APQRST"):
        ws1.merge_cells(i + "2:" + i + "7")
    for i in range(3, 8):
        i = str(i)
        ws1.merge_cells("B" + i + ":D" + i)
    for i in list("ABCDEFGHIJKLMNOPQRST"):
        for j in range(2, class_stu + 10):
            ws1[i + str(j)].style = normal_style
        ws1[i + str(2)].alignment = Alignment(horizontal="center",
                                              vertical="center",
                                              wrapText=True)
    # 各种合并单元格。最后的wrapText代表是否允许换行。

    for i in list("BCEFGHIJKLMNO"):
        ws1.column_dimensions[i].width = (10 - 0.36)
    # 注：实际上width属性写5，输出为4.36(height没问题)
    # 所有的好像都是设置为(x + 1)，实际为(x + 0.36)
    ws1.column_dimensions["A"].width = (5 - 0.36)
    for i in list("DPQRST"):
        ws1.column_dimensions[i].width = (9 - 0.36)
    for i in range(class_stu):
        for j in list("EFGHIJKLMNO"):
            if(int(ws1[j + str(i + 8)].value) < 60):
                # 注：如果把if前置到for j那里则会出现格式失效的情况
                # 原因不明，暂时用此法避免
                ws1[j + str(i + 8)].font = Font(color="00FF0000")
                # 颜色只能设置RGB值

    ws2 = wb["Sheet2"]
    for i in list("ABCDEFG"):
        for j in range(1, class_stu + 2):
            # 我真是受够了这个地方，openpyxl和pandas不一样
            # openpyxl计数从1开始，pandas从0开始。
            # 还有题头是否包含等各种问题……
            ws2[i + str(j)].style = normal_style
    ws2.column_dimensions["A"].width = (5 - 0.36)
    ws2.column_dimensions["B"].width = (10 - 0.36)
    ws2.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFG"):
        ws2.column_dimensions[i].width = (9 - 0.36)
    for i in range(2, class_stu + 1):
        if (int(ws2["D" + str(i)].value) > 0):
            # 突出不及格科目
            ws2["D" + str(i)].font = Font(color="00FF0000", size=11)
    wb.save(dir + "_out_openpyxl.xlsx")
    # openpyxl保存时就不用with……
    print("Openpyxl格式化完毕")
    print(str_num + "班已结束\n")

def concat_all(each_sheet: pd.core.frame.DataFrame, df: pd.core.frame.DataFrame):
    """输出总汇总表"""
    df = pd.concat([df, each_sheet], axis=0, ignore_index=True)
    # 连接每个表的Sheet2
    return df

def sort_all_out(df_all: pd.core.frame.DataFrame, sum_stu: int, file_dir: str):
    """用pandas排序汇总表"""
    df_all.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    # 前面有写，略。
    # 后面的逻辑和前面我自己写的排序模块基本一致
    # 思路相同，略。
    df_all.iloc[0, 7] = 1
    rank = 1
    count = 0
    for i in range(sum_stu - 1):
        if(df_all.iloc[i, 4] > df_all.iloc[i + 1, 4]):
            rank = rank + count + 1
            count = 0
        else:
            count += 1
        df_all.iloc[i + 1, 7] = rank

    df_all.to_excel(file_dir + "汇总_out_pandas.xlsx",
                    sheet_name="个人成绩汇总表",
                    index=False)
    # 这个index=False一旦漏了就跟白排版了没啥区别了。
    print("Pandas汇总全年级排名完毕")

def format_sumed_sheet(df_dir: str):
    """格式化汇总表并输出"""
    wb = openpyxl.load_workbook(df_dir + "汇总_out_pandas.xlsx")
    ws = wb["个人成绩汇总表"]
    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)
    for i in list("ABCDEFGH"):
        for j in range(1, ws.max_row + 1):
            ws[i + str(j)].style = normal_style
            if ((i == "D") and (j > 1) and (int(ws[i + str(j)].value) > 0)):
                # 和前面思路一致，融合到一句话里了而已
                ws[i + str(j)].font = Font(color="00FF0000", size=11)

    ws.column_dimensions["A"].width = (5 - 0.36)
    ws.column_dimensions["B"].width = (10 - 0.36)
    ws.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFGH"):
        ws.column_dimensions[i].width = (9 - 0.36)
    # 设置列宽
    wb.save(df_dir + "汇总_out_openpyxl.xlsx")
    print("Openpyxl格式化排名完毕")

for class_int in range(1, 17):
    """说句大实话，可以直接把这些东西都放在一起的
    完全可以避免这么搞。事实证明这么搞
    除了增加了维护难度之外(传参的排查bug要人小命)，
    并没什么优点(模块化除外)

    并且，我为了实现整体的逻辑统一与美观
    被迫写了不少冗余代码，就为了让整体代码模式化
    """
    # 注：全部变量名基本都改了一遍
    # 现在的都是原始变量名为复杂的全称，函数内部的一般为简称
    if(class_int == 1):
        clean(0)
    if(class_int < 10):
        class_str = "0" + str(class_int)
    else:
        class_str = str(class_int)

    full_path_no_ext, original_sheet = read_xlsx(class_str)
    each_class_stu = len(original_sheet) - 7
    get_sum_average_grade(each_class_stu, original_sheet)
    rank_grades(each_class_stu, original_sheet)
    original_sheet2 = sheet2_produce(
        each_class_stu, original_sheet, class_str)
    df_all_score = concat_all(original_sheet2, df_all_score)
    pandas_xlsx_write(full_path_no_ext, original_sheet, original_sheet2)
    format_out_sheet(full_path_no_ext, each_class_stu,
                     class_str, full_path_no_ext)

    if(class_int == 16):
        # 说白了就是班级的全部汇总完了就行了
        # 完全可以放到大循环for的外面，我只是想利用一下现有的参数
        # 又担心出了循环，参数可能失效的问题。
        sort_all_out(df_all_score, len(df_all_score), file_dir)
        format_sumed_sheet(file_dir)
        clean(1)

print("\n")

精简版代码（无冗余注释，逻辑较清晰）

# coding=utf-8
import os
from time import sleep
import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Border, Font, NamedStyle, Side
df_all_score = pd.DataFrame(columns=["班级",
                                     "学号",
                                     "姓名",
                                     "不及格门数",
                                     "平均分",
                                     "总分",
                                     "排名",
                                     "级部排名"])
# 储存所有学生信息的dataframe

file_dir = os.path.split(os.path.realpath(__file__))[0] + "\\"
# 当前目录

unattended = pd.read_excel(file_dir + "unattended.xlsx", header=None)
unattended_stu = {}
for each_stu in range(len(unattended)):
    unattended_stu[unattended.iloc[each_stu, 0]] = unattended.iloc[each_stu, 1

def clean(mood: int):
    """清除之前的生成文件\n
    mood=0 -> 清除所有\n
    mood=1 -> 仅清除Pandas的生成"""
    if(mood):
        os.system("del /f /q /s " + file_dir + "*pandas*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)
    else:
        os.system("del /f /q /s " + file_dir + "*_*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)

def read_xlsx(class_i: str):
    """读表格并返回表格路径、表格内容"""
    full_path = file_dir + "临床2021" + class_i
    sheet = pd.read_excel((full_path + ".xlsx"), header=1)
    #  [5,2]为姓名起始序号
    return full_path, sheet

def format_each(df: pd.core.frame.DataFrame, r: int, c: int):
    """处理单元格的NaN问题"""
    each_grade = df.iloc[r + 5, c + 4
    if(pd.isna(each_grade)):
        df.iloc[r + 5, c + 4] = 0
        each_grade = 0
    each_grade = int(each_grade)
    df.iloc[r + 5, c + 4] = each_grade
    return each_grade

def get_sum_average_grade(class_stu: int, sheet: pd.core.frame.DataFrame):
    """计算有效课程数量与总分，并求有效平均分"""
    for row in range(class_stu):
        sum_grade = 0
        eff_count = 0
        unattended_count = 0
        for column in range(11):
            each_subject_garde = format_each(sheet, row, column)
            if (each_subject_garde != 0):
                sum_grade += each_subject_garde
                eff_count += 1
            else:
                if(sheet.iloc[row + 5, column + 2] in unattended_stu):
                    while(unattended_count <= int(unattended_stu[sheet.iloc[row + 5, column + 2]])):
                        eff_count += 1
                        unattended_count += 1
        sheet.iloc[row + 5, 17] = sum_grade
        if(eff_count == 0 or sum_grade == 0):
            sheet.iloc[row + 5, 16] = 0  # ZeroErr
        else:
            sheet.iloc[row + 5, 16] = round((sum_grade / eff_count),2)

def rank_grades(class_stu: int, sheet1: pd.core.frame.DataFrame):
    """成绩排序"""
    original_list = []
    for i in range(class_stu):
        original_list.append(sheet1.iloc[i + 5, 16])
    ranked_list = sorted(original_list, reverse=True)
    rank_list = [1
    # 演示：
    # original_list： [59，88，90，88，83，60]
    # ranked_list：   [90，88，88，83，60，59]
    # rank_list：     [1， 2， 2， 4， 5， 6 ]
    # final_list：    [6， 2， 1， 2， 4， 5 ]
    """生成rank_list"""
    rank = 1
    count = 0
    for i in range(class_stu - 1):
        if(ranked_list[i] > ranked_list[i + 1]):
            rank = rank + count + 1
            count = 0
        else:
            rank = rank
            count += 1
        rank_list.append(rank)
    final_list = original_list
    for i in range(len(original_list)):
        j = 0
        while (original_list[i] != ranked_list[j]):
            j += 1
        final_list[i] = rank_list[j
    for i in range(class_stu):
        sheet1.iloc[i + 5, 19] = final_list[i

def sheet2_produce(sum: int, sheet1: pd.core.frame.DataFrame, class_stu: str):
    """根据Sheet1的内容生成Sheet2数据"""
    class_stu = class_stu + "班"
    sheet2 = sheet1.filter(
        items=["学号", "姓名", "不及格门数", "平均分", "总分", "排名"])
    sheet2.drop([0, 1, 2, 3, 4, sum + 5, sum + 6],
                inplace=True)
    sheet2.insert(0, "班级", class_stu)
    sheet2.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    return sheet2

def pandas_xlsx_write(path: str, sheet1: pd.core.frame.DataFrame, sheet2: pd.core.frame.DataFrame):
    """pandas数据处理结束后写入表格"""
    with pd.ExcelWriter(path + "_out_pandas.xlsx") as writer:
        sheet1.to_excel(writer,
                        sheet_name="Sheet1",
                        index=False,
                        engine="openpyxl")
        sheet2.to_excel(writer,
                        sheet_name="Sheet2",
                        index=False,
                        engine="openpyxl")
    print("Pandas汇总各班成绩完成")

def format_out_sheet(sheet_path: str, class_stu: int, str_num: str, dir: str):
    """用Openpyxl格式化文档并输出\n
    同时为生成汇总表准备\n
    """
    wb = openpyxl.load_workbook(sheet_path + "_out_pandas.xlsx")
    ws1 = wb["Sheet1"
    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)
    ws1.insert_rows(0, 1)
    ws1["A1"] = "潍坊医学院 班级成绩表 2021-2022-1 2021级临床" + str_num + "班"
    ws1.merge_cells("A1:T1")
    ws1["A1"].font = Font(name="宋体", size=11)
    ws1["A1"].alignment = Alignment(horizontal="center",
                                    vertical="center",
                                    wrapText=False)
    ws1.row_dimensions[1].height = 20
    for i in list("APQRST"):
        ws1.merge_cells(i + "2:" + i + "7")
    for i in range(3, 8):
        i = str(i)
        ws1.merge_cells("B" + i + ":D" + i)
    for i in list("ABCDEFGHIJKLMNOPQRST"):
        for j in range(2, class_stu + 10):
            ws1[i + str(j)].style = normal_style
        ws1[i + str(2)].alignment = Alignment(horizontal="center",
                                              vertical="center",
                                              wrapText=True)
    for i in list("BCEFGHIJKLMNO"):
        ws1.column_dimensions[i].width = (10 - 0.36)
    # 注：实际上width属性设置为(x + 1)，实际为(x + 0.36)
    ws1.column_dimensions["A"].width = (5 - 0.36)
    for i in list("DPQRST"):
        ws1.column_dimensions[i].width = (9 - 0.36)
    for i in range(class_stu):
        for j in list("EFGHIJKLMNO"):
            if(int(ws1[j + str(i + 8)].value) < 60):
                ws1[j + str(i + 8)].font = Font(color="00FF0000")
    ws2 = wb["Sheet2"
    for i in list("ABCDEFGH"):
        for j in range(1, class_stu + 2):
            # openpyxl计数从1开始，pandas从0开始。
            ws2[i + str(j)].style = normal_style
    ws2.column_dimensions["A"].width = (5 - 0.36)
    ws2.column_dimensions["B"].width = (10 - 0.36)
    ws2.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFGH"):
        ws2.column_dimensions[i].width = (9 - 0.36)
    for i in range(2, class_stu + 1):
        if (int(ws2["D" + str(i)].value) > 0):
            # 突出不及格科目
            ws2["D" + str(i)].font = Font(color="00FF0000", size=11)
    wb.save(dir + "_out_openpyxl.xlsx")
    print("Openpyxl格式化完毕")
    print(str_num + "班已结束\n")

def concat_all(each_sheet: pd.core.frame.DataFrame, df: pd.core.frame.DataFrame):
    """输出总汇总表"""
    df = pd.concat([df, each_sheet], axis=0, ignore_index=True)
    return df

def sort_all_out(df_all: pd.core.frame.DataFrame, sum_stu: int, file_dir: str):
    """用pandas排序汇总表"""
    df_all.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    df_all.iloc[0, 7] = 1
    rank = 1
    count = 0
    for i in range(sum_stu - 1):
        if(df_all.iloc[i, 4] > df_all.iloc[i + 1, 4]):
            rank = rank + count + 1
            count = 0
        else:
            count += 1
        df_all.iloc[i + 1, 7] = rank
    df_all.to_excel(file_dir + "汇总_out_pandas.xlsx",
                    sheet_name="个人成绩汇总表",
                    index=False)
    df_all.close()
    print("Pandas汇总全年级排名完毕")

def format_sumed_sheet(df_dir: str):
    """格式化汇总表并输出"""
    wb = openpyxl.load_workbook(df_dir + "汇总_out_pandas.xlsx")
    ws = wb["个人成绩汇总表"
    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)
    for i in list("ABCDEFGH"):
        for j in range(1, ws.max_row + 1):
            ws[i + str(j)].style = normal_style
            if ((i == "D") and (j > 1) and (int(ws[i + str(j)].value) > 0)):
                ws[i + str(j)].font = Font(color="00FF0000", size=11)
    ws.column_dimensions["A"].width = (5 - 0.36)
    ws.column_dimensions["B"].width = (10 - 0.36)
    ws.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFGH"):
        ws.column_dimensions[i].width = (9 - 0.36)
    wb.save(df_dir + "汇总_out_openpyxl.xlsx")
    print("Openpyxl格式化排名完毕")

for class_int in range(1, 17):
    """为了实现整体的逻辑统一与美观
    被迫写了不少冗余代码
    """
    if(class_int == 1):
        clean(0)
    if(class_int < 10):
        class_str = "0" + str(class_int)
    else:
        class_str = str(class_int)
    full_path_no_ext, original_sheet = read_xlsx(class_str)
    each_class_stu = len(original_sheet) - 7
    get_sum_average_grade(each_class_stu, original_sheet)
    rank_grades(each_class_stu, original_sheet)
    original_sheet2 = sheet2_produce(each_class_stu,
                                     original_sheet, class_str)
    df_all_score = concat_all(original_sheet2, df_all_score)
    pandas_xlsx_write(full_path_no_ext, original_sheet, original_sheet2)
    format_out_sheet(full_path_no_ext, each_class_stu,
                     class_str, full_path_no_ext)
    if(class_int == 16):
        sort_all_out(df_all_score, len(df_all_score), file_dir)
        format_sumed_sheet(file_dir)
        clean(1)

print("\n")

```

zohoChou

还有，重要提示，我使用的是windows10系统，自己手动把编码改成了utf-8，一般电脑默认是gbk，可能出现编码问题！！！


下面的更新我全都改成了openpyxl写的，反而没有python的pandas与numpy（发现这俩玩意对可视化太不友好了……）

zohoChou

咕咕咕，来更新了（本来打算把那个整合全年级成绩的模块（函数，叫习惯了）也一起发出来…………
但是，咕咕使我没有动力…………不想动弹…………
完成后的成果如下图：







注：懒得写注释了……抽空补上
生成出来的表里面都是公式和引用！
(对于宽松的要求来说够用了，但是如果要严谨的话请自行复制+粘贴值（我也没找到好办法……

还有，汇总出来的表没法用，引用炸了……正在积极寻找办法中（咕咕咕，有啥没看明白的可以问我，共同讨论共同提升

import os
from time import sleep
import openpyxl

os.system("taskkill /f /t /im EXCEL.EXE")
file_dir = "D:\\Desktop\\成绩\\"
if(os.path.exists(file_dir + "临床202101_out3.xlsx")):
    os.system("cls")
    os.system("del /f /q /s D:\\Desktop\\成绩\\*_out*")
    os.system("del /f /q /s D:\\Desktop\\成绩\\*汇总*")
    print("已清除之前的生成", end="\n\n")
    sleep(3)

def get_sum(num: str, lrange):
    """注：因为openpyxl无法承载函数嵌套，故出此下策"""
    A_Z_range = list("ABCDEFGHIJKLMNOPQRSTUVWXYZ")
    sum_grades = 0
    for k in range(4, 16):
        # 注：有的值是NaN，要人小命，调试了N多遍
        try:
            int(lrange[A_Z_range[k] + num].value)
        except:
            lrange[A_Z_range[k] + num] = 0
        sum_grades += int(lrange[A_Z_range[k] + num].value)
    return sum_grades

def Starting(path: str):
    original_wb = openpyxl.load_workbook(path + ".xlsx", data_only=True)
    original_ws = original_wb["sheet1"]
    original_ws.title = "Sh"
    original_ws.title = "Sheet1"
    # 注：原表名为sheet1，直接改为Sheet1会变成Sheet11（可能是bug，故以此绕过bug判定）

    sum_people = original_ws.max_row - 9

    for l in range(sum_people):
        temp = str(l + 8)
        original_ws["R" + temp] = get_sum(temp, original_ws)
        original_ws["Q" + temp] = "=R" + temp + \
            "/COUNTIF(E" + temp + ":O" + temp + ",\"<>0\")"
        original_ws["T" + temp] = "=RANK(Q" + temp + ",$Q$8:$Q$" + \
            str(sum_people + 7) + ",0)"

    changed_ws2 = original_wb.copy_worksheet(original_ws)
    changed_ws2.title = "Sheet2"
    changed_ws2.unmerge_cells("A1:T1")
    temp = ["A", "P", "Q", "R", "S", "T"]
    # 大坑记：openpyxl的删除行只是清除内容，不是真正的删除！
    # 所以说需要手动排版！！！
    for m in temp:
        changed_ws2.unmerge_cells(m + "2:" + m + "7")
    for n in range(3, 8):
        n = str(n)
        changed_ws2.unmerge_cells("B" + n + ":D" + n)
    changed_ws2.delete_cols(19, 1)
    changed_ws2.delete_cols(5, 11)
    changed_ws2.delete_cols(4, 1)
    changed_ws2.delete_cols(1, 1)
    changed_ws2.delete_rows(changed_ws2.max_row-1, 2)
    changed_ws2.delete_rows(3, 5)
    changed_ws2.delete_rows(1, 1)
    changed_ws2.column_dimensions["A"].width = 12
    changed_ws2.row_dimensions[2].height = 14.3
    for o in range(sum_people):
        changed_ws2["D" + str(o + 2)] = "=Sheet1!Q" + str(o + 8)
        changed_ws2["F" + str(o + 2)] = "=Sheet1!T" + str(o + 8)

    original_wb.save(file_path + "_out3.xlsx")
    print("success3")
    return sum_people, original_ws, changed_ws2

def class_judge(num: int):
    if(20211150001 <= num <= 20211150050):
        return "1班"
    elif(20211150051 <= num <= 20211150100):
        return "2班"
    elif(20211150101 <= num <= 20211150150):
        return "3班"
    elif(20211150151 <= num <= 20211150200):
        return "4班"
    elif(20211150201 <= num <= 20211150250):
        return "5班"
    elif(20211150251 <= num <= 20211150300):
        return "6班"
    elif(20211150301 <= num <= 20211150349):
        return "7班"
    elif(20211150350 <= num <= 20211150399):
        return "8班"
    elif(20211150400 <= num <= 20211150449):
        return "9班"
    elif(20211150450 <= num <= 20211150499):
        return "10班"
    elif(20211150500 <= num <= 20211150549):
        return "11班"
    elif(20211150550 <= num <= 20211150599):
        return "12班"
    elif(20211150600 <= num <= 20211150648):
        return "13班"
    elif(20211150649 <= num <= 20211150700):
        return "14班"
    elif(20211150701 <= num <= 20211150753):
        return "15班"
    elif(20211150754 <= num <= 20211150806):
        return "16班"
    else:
        print(num)

sum_wb = openpyxl.Workbook()
sum_wb["Sheet"].title = "个人成绩汇总"
sum_ws = sum_wb["个人成绩汇总"]
sum_ws.append(["学号", "班级", "姓名", "不及格门数", "平均分", "总分", "级部排名", "班级内排名"])

for i in range(1, 17):
    if (i < 10):
        file_path = file_dir + "临床20210" + str(i)
        str_i = "0" + str(i)
    else:
        file_path = file_dir + "临床2021" + str(i)
        str_i = str(i)

    people, ws1, ws2 = Starting(file_path)
    sum_last_all = 0
    for j in range(people):
        # 神奇的事情，“ValueError: Cells cannot be copied from other worksheets” ……没办法
        sum_wb["个人成绩汇总"].append([
            ws2["A" + str(2 + j)].value,
            class_judge(int(ws2["A" + str(2 + j)].value)),
            ws2["B" + str(2 + j)].value,
            ws2["C" + str(2 + j)].value,
            "=\"" + file_path + "\"" + str_i +
            "_out3.xlsx]Sheet1!$Q$8" + str(i),
            ws2["E" + str(2 + j)].value,
            "=RANK(E" + str(sum_last_all + j + 2) + ",E1:E805)",
            "=[临床2021" + str_i + "_out3.xlsx]Sheet1!$T$8" + str(i)])
    sum_last_all += people
    print(str(i) + "班完成")
sum_wb.save(file_dir + "_汇总.xlsx")
print("汇总")

zohoChou

我回来了！！！
因为学校把51假期挪到了暑假，没法鸽了（狗头
你木见过的船新版本等你来探……

假的更新日志（溜……

1.0.0(最后一版，如果没有意外……)

1.把几乎所有的变量全都进行了规范化命名，再也不是for i, for j, for k了 ……

注：函数外变量为完整命名，函数内为简化命名，如：each_class_stu -> class_stu 等。

2.重写40%以上逻辑特别是当时自己写的取总人数，绕了一大圈……

（逻辑上还可以优化，但是由于担心以后维护时看不懂，专门把逻辑写的很简单易懂【有小部分冗余】）。## 3.回归初心，以pandas为主体，openpyxl专门格式化输出。

（因没看懂官方文档，均为pandas输出后使用openpyxl重读并重新输出，未使用openpyxl直接协同pandas）

4.重写注释，增加函数说明

小贴士：
pandas与openpyxl有诸多不同，不要混了
传参一定不要反复逮着i用，极容易出现参数值混乱……


从未写过这么复杂的python……
注：实际上传时删掉了学校信息，可能少了十几个字




原始目录架构


原始文件：

结束运行后的目录架构

处理后的Sheet1

处理生成的Sheet2（排名已处理）

处理生成的汇总表


上代码！

# coding=utf-8
# 第一句很有必要！因为很多系统(特别是Windows，中文编码容易出问题)

# 为了执行cmd命令
import os
from time import sleep

# 核心模块
import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Border, Font, NamedStyle, Side

df_all_score = pd.DataFrame(columns=["班级",
                                     "学号",
                                     "姓名",
                                     "不及格门数",
                                     "平均分",
                                     "总分",
                                     "排名",
                                     "级部排名"])
# 这是提前定义的，储存所有学生信息的dataframe

file_dir = os.path.split(os.path.realpath(__file__))[0] + "\\"
# 储存表格的目录(当前目录)

def clean(mood: int):
    """清除之前的生成文件\n
    mood=0 -> 清除所有\n
    mood=1 -> 仅清除Pandas的生成\n
    因程序测试时每次都会输出*_*.xlsx，为方便起见，每次生成前先删除旧的生成文件。"""
    if(mood):
        # 直接利用0/1判断False/True
        os.system("del /f /q /s " + file_dir + "*pandas*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)
    else:
        os.system("del /f /q /s " + file_dir + "*_*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)

def read_xlsx(class_i: str):
    """读表格并返回表格路径、表格内容"""

    # 根据表格名称，<10与>=10两种情况(已在最底下的for循环里面处理为str_i)
    full_path = file_dir + "临床2021" + class_i
    sheet = pd.read_excel((full_path + ".xlsx"), header=1)
    # header = 1 意为把表格的第二行作为标题。(忽略了第一行的读取，最后还得重新生成第一行……)

    #  [5,2]为姓名起始序号
    # 请注意，python的计数比较别扭，都是从0开始。上面的注释便于以后查看、改写。
    return full_path, sheet

def format_each(df: pd.core.frame.DataFrame, r: int, c: int):
    """处理单元格的NaN问题"""
    each_grade = df.iloc[r + 5, c + 4]
    if(pd.isna(each_grade)):
        # 注：只能使用pd.isna进行判断，除非使用try，int()等一律失效
        df.iloc[r + 5, c + 4] = 0
        each_grade = 0
    each_grade = int(each_grade)
    # 方便下面的再次取值(如果有的话)，防止部分成绩的储存格式为文本型
    df.iloc[r + 5, c + 4] = each_grade
    return each_grade

def get_sum_average_grade(class_stu: int, sheet: pd.core.frame.DataFrame):
    """计算有效课程数量与总分，并求有效平均分"""
    # 完全可以使用loc[]进行取值，但是最原始的表格里面(pandas读取之前)有合并单元格的一个大标题
    # 还牵扯到了中文编码的识别问题……建议不要用。
    for row in range(class_stu):
        sum_grade = 0
        # 重新计算个人总分
        eff_count = 0  # effective_count
        # 我们学校要求的是用平均成绩计算，且不把0分科目(一般都是缓考造成的)计算在内
        # 所以说需要计算有意义的科目【这里应该能简写，希望大佬指点】
        for column in range(11):
            each_subject_garde = format_each(sheet, row, column)

            if (each_subject_garde != 0):
                # 如果成绩不为 0 则计数
                sum_grade += each_subject_garde  # 计算总成绩
                eff_count += 1
                # eff_count 有分数课程的总数

        sheet.iloc[row + 5, 17] = sum_grade

        if(eff_count == 0):
            sheet.iloc[row + 5, 16] = 0  # Avoid ZeroErr
        else:
            sheet.iloc[row + 5, 16] = (sum_grade / eff_count)  # 输出平均分

def rank_grades(class_stu: int, sheet1: pd.core.frame.DataFrame):
    """成绩排序"""
    original_list = []
    for i in range(class_stu):
        original_list.append(sheet1.iloc[i + 5, 16])  # 储存的是原始成绩
    ranked_list = sorted(original_list, reverse=True)  # 排序后的原始成绩
    rank_list = [1]  # 名次(用来对应原始姓名)
    # 演示：
    # original_list： [59，88，90，88，83，60]
    # ranked_list：   [90，88，88，83，60，59]
    # rank_list：     [1， 2， 2， 4， 5， 6 ]
    # final_list：    [6， 2， 1， 2， 4， 5 ]
    # (最后通过对应ranked_list与rank_list的对应关系让final_list变成原来顺序的纯名次)

    # 一开始尝试过用字典或列表嵌套的方式，都失败了……

    """生成rank_list"""
    # 实际排名
    rank = 1
    # 用ranked_list排序，第一个一定是第一名啊
    count = 0
    # 计算同分数段有多少人
    for i in range(class_stu - 1):
        if(ranked_list[i] > ranked_list[i + 1]):
            # 正常情况
            rank = rank + count + 1
            count = 0
            # 别忘了count清零
        else:
            # 名次相等的情况
            rank = rank
            count += 1
        rank_list.append(rank)

    """这里将生成final_list，通过对应使其变为顺序
    不变的名次排序，然后直接输出到表格即可"""
    final_list = original_list  # 初始化final_list
    # 两个循环匹配相同项
    for i in range(len(original_list)):
        j = 0
        while (original_list[i] != ranked_list[j]):
            j += 1
        final_list[i] = rank_list[j]

    for i in range(class_stu):
        # 更改到表格的内容
        sheet1.iloc[i + 5, 19] = final_list[i]

def sheet2_produce(sum: int, sheet1: pd.core.frame.DataFrame, class_stu: str):
    """根据Sheet1的内容生成Sheet2数据"""
    class_stu = class_stu + "班"
    sheet2 = sheet1.filter(
        items=["学号", "姓名", "不及格门数", "平均分", "总分", "排名"])
    # 删除原来的不相关列即可
    sheet2.drop([0, 1, 2, 3, 4, sum + 5, sum + 6],
                inplace=True)
    # 插入班级列，方便对应，不再使用学号进行对应。
    sheet2.insert(0, "班级", class_stu)
    sheet2.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    # 注：inplace可免于自赋值
    # kind="mergesort"使其有序排序，避免学号混乱。请自行搜索mergesort。
    # 排序依据平均分，升序。
    return sheet2

def pandas_xlsx_write(path: str, sheet1: pd.core.frame.DataFrame, sheet2: pd.core.frame.DataFrame):
    """pandas数据处理结束后写入表格"""
    with pd.ExcelWriter(path + "_out_pandas.xlsx") as writer:
        # 使用with语句可以避免Sheet2输出时覆盖Sheet1导致最后表格里只有一个sheet2的情况
        sheet1.to_excel(writer,
                        sheet_name="Sheet1",
                        index=False,
                        engine="openpyxl")
        sheet2.to_excel(writer,
                        sheet_name="Sheet2",
                        index=False,
                        engine="openpyxl")
    print("Pandas汇总各班成绩完成")

def format_out_sheet(sheet_path: str, class_stu: int, str_num: str, dir: str):
    """用Openpyxl格式化文档并输出\n
    同时为生成汇总表准备\n
    """
    wb = openpyxl.load_workbook(sheet_path + "_out_pandas.xlsx")
    ws1 = wb["Sheet1"]
    thin = Side(border_style="thin", color="000000")
    # 定义边框样式
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    # 定义一种样式方便后面直接使用
    wb.add_named_style(normal_style)

    ws1.insert_rows(0, 1)
    ws1["A1"] = "班级成绩表 2021-2022-1 2021级临床" + str_num + "班"
    # 插入一行
    ws1.merge_cells("A1:T1")
    ws1["A1"].font = Font(name="宋体", size=11)
    ws1["A1"].alignment = Alignment(horizontal="center",
                                    vertical="center",
                                    wrapText=False)
    ws1.row_dimensions[1].height = 20
    # 合并单元格、设置字体、居中、行高

    for i in list("APQRST"):
        ws1.merge_cells(i + "2:" + i + "7")
    for i in range(3, 8):
        i = str(i)
        ws1.merge_cells("B" + i + ":D" + i)
    for i in list("ABCDEFGHIJKLMNOPQRST"):
        for j in range(2, class_stu + 10):
            ws1[i + str(j)].style = normal_style
        ws1[i + str(2)].alignment = Alignment(horizontal="center",
                                              vertical="center",
                                              wrapText=True)
    # 各种合并单元格。最后的wrapText代表是否允许换行。

    for i in list("BCEFGHIJKLMNO"):
        ws1.column_dimensions[i].width = (10 - 0.36)
    # 注：实际上width属性写5，输出为4.36(height没问题)
    # 所有的好像都是设置为(x + 1)，实际为(x + 0.36)
    ws1.column_dimensions["A"].width = (5 - 0.36)
    for i in list("DPQRST"):
        ws1.column_dimensions[i].width = (9 - 0.36)
    for i in range(class_stu):
        for j in list("EFGHIJKLMNO"):
            if(int(ws1[j + str(i + 8)].value) < 60):
                # 注：如果把if前置到for j那里则会出现格式失效的情况
                # 原因不明，暂时用此法避免
                ws1[j + str(i + 8)].font = Font(color="00FF0000")
                # 颜色只能设置RGB值

    ws2 = wb["Sheet2"]
    for i in list("ABCDEFG"):
        for j in range(1, class_stu + 2):
            # 我真是受够了这个地方，openpyxl和pandas不一样
            # openpyxl计数从1开始，pandas从0开始。
            # 还有题头是否包含等各种问题……
            ws2[i + str(j)].style = normal_style
    ws2.column_dimensions["A"].width = (5 - 0.36)
    ws2.column_dimensions["B"].width = (10 - 0.36)
    ws2.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFG"):
        ws2.column_dimensions[i].width = (9 - 0.36)
    for i in range(2, class_stu + 1):
        if (int(ws2["D" + str(i)].value) > 0):
            # 突出不及格科目
            ws2["D" + str(i)].font = Font(color="00FF0000", size=11)
    wb.save(dir + "_out_openpyxl.xlsx")
    # openpyxl保存时就不用with……
    print("Openpyxl格式化完毕")
    print(str_num + "班已结束\n")

def concat_all(each_sheet: pd.core.frame.DataFrame, df: pd.core.frame.DataFrame):
    """输出总汇总表"""
    df = pd.concat([df, each_sheet], axis=0, ignore_index=True)
    # 连接每个表的Sheet2
    return df

def sort_all_out(df_all: pd.core.frame.DataFrame, sum_stu: int, file_dir: str):
    """用pandas排序汇总表"""
    df_all.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    # 前面有写，略。
    # 后面的逻辑和前面我自己写的排序模块基本一致
    # 思路相同，略。
    df_all.iloc[0, 7] = 1
    rank = 1
    count = 0
    for i in range(sum_stu - 1):
        if(df_all.iloc[i, 4] > df_all.iloc[i + 1, 4]):
            rank = rank + count + 1
            count = 0
        else:
            count += 1
        df_all.iloc[i + 1, 7] = rank

    df_all.to_excel(file_dir + "汇总_out_pandas.xlsx",
                    sheet_name="个人成绩汇总表",
                    index=False)
    # 这个index=False一旦漏了就跟白排版了没啥区别了。
    print("Pandas汇总全年级排名完毕")

def format_sumed_sheet(df_dir: str):
    """格式化汇总表并输出"""
    wb = openpyxl.load_workbook(df_dir + "汇总_out_pandas.xlsx")
    ws = wb["个人成绩汇总表"]
    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)
    for i in list("ABCDEFGH"):
        for j in range(1, ws.max_row + 1):
            ws[i + str(j)].style = normal_style
            if ((i == "D") and (j > 1) and (int(ws[i + str(j)].value) > 0)):
                # 和前面思路一致，融合到一句话里了而已
                ws[i + str(j)].font = Font(color="00FF0000", size=11)

    ws.column_dimensions["A"].width = (5 - 0.36)
    ws.column_dimensions["B"].width = (10 - 0.36)
    ws.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFGH"):
        ws.column_dimensions[i].width = (9 - 0.36)
    # 设置列宽
    wb.save(df_dir + "汇总_out_openpyxl.xlsx")
    print("Openpyxl格式化排名完毕")

for class_int in range(1, 17):
    """说句大实话，可以直接把这些东西都放在一起的
    完全可以避免这么搞。事实证明这么搞
    除了增加了维护难度之外(传参的排查bug要人小命)，
    并没什么优点(模块化除外)

    并且，我为了实现整体的逻辑统一与美观
    被迫写了不少冗余代码，就为了让整体代码模式化
    """
    # 注：全部变量名基本都改了一遍
    # 现在的都是原始变量名为复杂的全称，函数内部的一般为简称
    if(class_int == 1):
        clean(0)
    if(class_int < 10):
        class_str = "0" + str(class_int)
    else:
        class_str = str(class_int)

    full_path_no_ext, original_sheet = read_xlsx(class_str)
    each_class_stu = len(original_sheet) - 7
    get_sum_average_grade(each_class_stu, original_sheet)
    rank_grades(each_class_stu, original_sheet)
    original_sheet2 = sheet2_produce(
        each_class_stu, original_sheet, class_str)
    df_all_score = concat_all(original_sheet2, df_all_score)
    pandas_xlsx_write(full_path_no_ext, original_sheet, original_sheet2)
    format_out_sheet(full_path_no_ext, each_class_stu,
                     class_str, full_path_no_ext)

    if(class_int == 16):
        # 说白了就是班级的全部汇总完了就行了
        # 完全可以放到大循环for的外面，我只是想利用一下现有的参数
        # 又担心出了循环，参数可能失效的问题。
        sort_all_out(df_all_score, len(df_all_score), file_dir)
        format_sumed_sheet(file_dir)
        clean(1)

print("\n")

精简版代码（无冗余注释，逻辑较清晰）

# coding=utf-8
import os
from time import sleep

import openpyxl
import pandas as pd
from openpyxl.styles import Alignment, Border, Font, NamedStyle, Side

df_all_score = pd.DataFrame(columns=["班级",
                                     "学号",
                                     "姓名",
                                     "不及格门数",
                                     "平均分",
                                     "总分",
                                     "排名",
                                     "级部排名"])
# 储存所有学生信息的dataframe

file_dir = os.path.split(os.path.realpath(__file__))[0] + "\\"
# 当前目录

def clean(mood: int):
    """清除之前的生成文件\n
    mood=0 -> 清除所有\n
    mood=1 -> 仅清除Pandas的生成"""

    if(mood):
        os.system("del /f /q /s " + file_dir + "*pandas*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)
    else:
        os.system("del /f /q /s " + file_dir + "*_*.xlsx")
        print("已清除之前的生成", end="\n\n")
        sleep(3)

def read_xlsx(class_i: str):
    """读表格并返回表格路径、表格内容"""
    full_path = file_dir + "临床2021" + class_i
    sheet = pd.read_excel((full_path + ".xlsx"), header=1)
    #  [5,2]为姓名起始序号
    return full_path, sheet

def format_each(df: pd.core.frame.DataFrame, r: int, c: int):
    """处理单元格的NaN问题"""
    each_grade = df.iloc[r + 5, c + 4]

    if(pd.isna(each_grade)):
        df.iloc[r + 5, c + 4] = 0
        each_grade = 0

    each_grade = int(each_grade)
    df.iloc[r + 5, c + 4] = each_grade
    return each_grade

def get_sum_average_grade(class_stu: int, sheet: pd.core.frame.DataFrame):
    """计算有效课程数量与总分，并求有效平均分"""
    for row in range(class_stu):
        sum_grade = 0
        eff_count = 0
        for column in range(11):

            each_subject_garde = format_each(sheet, row, column)
            if (each_subject_garde != 0):
                sum_grade += each_subject_garde
                eff_count += 1
        sheet.iloc[row + 5, 17] = sum_grade

        if(eff_count == 0):
            sheet.iloc[row + 5, 16] = 0  # ZeroErr
        else:
            sheet.iloc[row + 5, 16] = (sum_grade / eff_count)

def rank_grades(class_stu: int, sheet1: pd.core.frame.DataFrame):
    """成绩排序"""
    original_list = []
    for i in range(class_stu):
        original_list.append(sheet1.iloc[i + 5, 16])
    ranked_list = sorted(original_list, reverse=True)
    rank_list = [1]
    # 演示：
    # original_list： [59，88，90，88，83，60]
    # ranked_list：   [90，88，88，83，60，59]
    # rank_list：     [1， 2， 2， 4， 5， 6 ]
    # final_list：    [6， 2， 1， 2， 4， 5 ]

    """生成rank_list"""
    rank = 1
    count = 0

    for i in range(class_stu - 1):
        if(ranked_list[i] > ranked_list[i + 1]):
            rank = rank + count + 1
            count = 0
        else:
            rank = rank
            count += 1
        rank_list.append(rank)

    final_list = original_list
    for i in range(len(original_list)):
        j = 0
        while (original_list[i] != ranked_list[j]):
            j += 1
        final_list[i] = rank_list[j]

    for i in range(class_stu):
        sheet1.iloc[i + 5, 19] = final_list[i]

def sheet2_produce(sum: int, sheet1: pd.core.frame.DataFrame, class_stu: str):
    """根据Sheet1的内容生成Sheet2数据"""
    class_stu = class_stu + "班"
    sheet2 = sheet1.filter(
        items=["学号", "姓名", "不及格门数", "平均分", "总分", "排名"])
    sheet2.drop([0, 1, 2, 3, 4, sum + 5, sum + 6],
                inplace=True)

    sheet2.insert(0, "班级", class_stu)
    sheet2.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)
    return sheet2

def pandas_xlsx_write(path: str, sheet1: pd.core.frame.DataFrame, sheet2: pd.core.frame.DataFrame):
    """pandas数据处理结束后写入表格"""
    with pd.ExcelWriter(path + "_out_pandas.xlsx") as writer:
        sheet1.to_excel(writer,
                        sheet_name="Sheet1",
                        index=False,
                        engine="openpyxl")
        sheet2.to_excel(writer,
                        sheet_name="Sheet2",
                        index=False,
                        engine="openpyxl")

    print("Pandas汇总各班成绩完成")

def format_out_sheet(sheet_path: str, class_stu: int, str_num: str, dir: str):
    """用Openpyxl格式化文档并输出\n
    同时为生成汇总表准备\n
    """
    wb = openpyxl.load_workbook(sheet_path + "_out_pandas.xlsx")
    ws1 = wb["Sheet1"]

    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)

    ws1.insert_rows(0, 1)
    ws1["A1"] = "班级成绩表 2021-2022-1 2021级临床" + str_num + "班"
    ws1.merge_cells("A1:T1")
    ws1["A1"].font = Font(name="宋体", size=11)
    ws1["A1"].alignment = Alignment(horizontal="center",
                                    vertical="center",
                                    wrapText=False)
    ws1.row_dimensions[1].height = 20

    for i in list("APQRST"):
        ws1.merge_cells(i + "2:" + i + "7")
    for i in range(3, 8):
        i = str(i)
        ws1.merge_cells("B" + i + ":D" + i)
    for i in list("ABCDEFGHIJKLMNOPQRST"):
        for j in range(2, class_stu + 10):
            ws1[i + str(j)].style = normal_style
        ws1[i + str(2)].alignment = Alignment(horizontal="center",
                                              vertical="center",
                                              wrapText=True)
    for i in list("BCEFGHIJKLMNO"):
        ws1.column_dimensions[i].width = (10 - 0.36)
    # 注：实际上width属性设置为(x + 1)，实际为(x + 0.36)
    ws1.column_dimensions["A"].width = (5 - 0.36)
    for i in list("DPQRST"):
        ws1.column_dimensions[i].width = (9 - 0.36)
    for i in range(class_stu):
        for j in list("EFGHIJKLMNO"):
            if(int(ws1[j + str(i + 8)].value) < 60):
                ws1[j + str(i + 8)].font = Font(color="00FF0000")

    ws2 = wb["Sheet2"]
    for i in list("ABCDEFG"):
        for j in range(1, class_stu + 2):
            # openpyxl计数从1开始，pandas从0开始。
            ws2[i + str(j)].style = normal_style
    ws2.column_dimensions["A"].width = (5 - 0.36)
    ws2.column_dimensions["B"].width = (10 - 0.36)
    ws2.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFG"):
        ws2.column_dimensions[i].width = (9 - 0.36)
    for i in range(2, class_stu + 1):
        if (int(ws2["D" + str(i)].value) > 0):
            # 突出不及格科目
            ws2["D" + str(i)].font = Font(color="00FF0000", size=11)

    wb.save(dir + "_out_openpyxl.xlsx")
    print("Openpyxl格式化完毕")
    print(str_num + "班已结束\n")

def concat_all(each_sheet: pd.core.frame.DataFrame, df: pd.core.frame.DataFrame):
    """输出总汇总表"""
    df = pd.concat([df, each_sheet], axis=0, ignore_index=True)
    return df

def sort_all_out(df_all: pd.core.frame.DataFrame, sum_stu: int, file_dir: str):
    """用pandas排序汇总表"""
    df_all.sort_values(by="平均分",
                       inplace=True,
                       kind="mergesort",
                       ascending=False)

    df_all.iloc[0, 7] = 1
    rank = 1
    count = 0
    for i in range(sum_stu - 1):
        if(df_all.iloc[i, 4] > df_all.iloc[i + 1, 4]):
            rank = rank + count + 1
            count = 0
        else:
            count += 1
        df_all.iloc[i + 1, 7] = rank

    df_all.to_excel(file_dir + "汇总_out_pandas.xlsx",
                    sheet_name="个人成绩汇总表",
                    index=False)
    print("Pandas汇总全年级排名完毕")

def format_sumed_sheet(df_dir: str):
    """格式化汇总表并输出"""
    wb = openpyxl.load_workbook(df_dir + "汇总_out_pandas.xlsx")
    ws = wb["个人成绩汇总表"]

    thin = Side(border_style="thin", color="000000")
    normal_style = NamedStyle(
        name="normal_style",
        font=Font(name="宋体", size=9),
        alignment=Alignment(horizontal="center",
                            vertical="center",
                            wrapText=False),
        border=Border(top=thin,
                      left=thin,
                      right=thin,
                      bottom=thin))
    wb.add_named_style(normal_style)

    for i in list("ABCDEFGH"):
        for j in range(1, ws.max_row + 1):
            ws[i + str(j)].style = normal_style
            if ((i == "D") and (j > 1) and (int(ws[i + str(j)].value) > 0)):
                ws[i + str(j)].font = Font(color="00FF0000", size=11)
    ws.column_dimensions["A"].width = (5 - 0.36)
    ws.column_dimensions["B"].width = (10 - 0.36)
    ws.column_dimensions["C"].width = (10 - 0.36)
    for i in list("EFGH"):
        ws.column_dimensions[i].width = (9 - 0.36)

    wb.save(df_dir + "汇总_out_openpyxl.xlsx")
    print("Openpyxl格式化排名完毕")

for class_int in range(1, 17):
    """为了实现整体的逻辑统一与美观
    被迫写了不少冗余代码
    """

    if(class_int == 1):
        clean(0)
    if(class_int < 10):
        class_str = "0" + str(class_int)
    else:
        class_str = str(class_int)

    full_path_no_ext, original_sheet = read_xlsx(class_str)
    each_class_stu = len(original_sheet) - 7

    get_sum_average_grade(each_class_stu, original_sheet)
    rank_grades(each_class_stu, original_sheet)
    original_sheet2 = sheet2_produce(each_class_stu,
                                     original_sheet, class_str)
    df_all_score = concat_all(original_sheet2, df_all_score)

    pandas_xlsx_write(full_path_no_ext, original_sheet, original_sheet2)

    format_out_sheet(full_path_no_ext, each_class_stu,
                     class_str, full_path_no_ext)

    if(class_int == 16):
        sort_all_out(df_all_score, len(df_all_score), file_dir)

        format_sumed_sheet(file_dir)

        clean(1)

print("\n")

ymhld

https://blog.csdn.net/zwliang98/ ... tm_relevant_index=1


headerCellStyle = NamedStyle(name = 'headerCellStyle')
        headerCellStyle.alignment = Alignment(horizontal = 'center', vertical = 'center') # 水平垂直居中
        border = Side(border_style = 'thin', color = '000000') # 线框样式
        headerCellStyle.border = Border(left = border, top = border, right = border, bottom = border) # 设置单元格边框样式

onlyreg

openpyxl 可以修改格式

jcorner

厉害，我最近也是在学习python，看到你的解说，我觉得这个软件确实不错

xuanyuanhs

新人学习下，感觉蛮不错的，详细注释的代码很不错！^O^

zohoChou

jcorner 发表于 2022-3-16 10:53
厉害，我最近也是在学习python，看到你的解说，我觉得这个软件确实不错

这属于pandas基本的使用（其实把pandas这个极其强大的模块用成这样是不对的，他的强大之处在于直接在python环境下进行各种统计、生成图表等操作，而不是重新变成excel）
python的大数据分析很大一部分是运用了pandas模块，建议提高英文水平，找找官网看。

还有，菜鸟教程你值得拥有。以及，《python从入门到精通》是个垃圾。。。在我刚刚接触python这门编程语言时就发现了诸多错误。避坑！

最后，加油！我也是一步步踩坑踩过来的（笑哭

hacker922

同求方法 等大神来指导 pandas也不太会用

zohoChou

xuanyuanhs 发表于 2022-3-16 10:56
新人学习下，感觉蛮不错的，详细注释的代码很不错！^O^

其实一般不会写这么详细的，除了我自己开发的这个成绩排序的模块，因为实在写的太乱，思路太绕，必须得写注释，否则下面大佬写的那段就是一般的文件的样子

zohoChou

hacker922 发表于 2022-3-16 11:04
同求方法 等大神来指导 pandas也不太会用

我尝试过xlrt什么的，然而全失败了……最后只能自己手动格式刷
再实在不行我手撸一个函数……（只会写函数，不会写模块。。。溜……

无语凝噎……

hacker922

zohoChou 发表于 2022-3-16 11:02
这属于pandas基本的使用（其实把pandas这个极其强大的模块用成这样是不对的，他的强大之处在于直接在pyth ...

求菜鸟教程 正在学python从入门到精通，看的头疼 赶紧有错误不说，还不好理解

hacker922

zohoChou 发表于 2022-3-16 11:06
我尝试过xlrt什么的，然而全失败了……最后只能自己手动格式刷
再实在不行我手撸一个函数……（只会写函 ...

哈哈哈 我也是 弄不来就处理出来 自己再手动 蹲坑多年还是要多来学习大神