python 处理 excel 实例 --- 成绩单合并与分析

场景与要求：

将文件夹内的所有成绩单合并到一张表格内，并计算平均值，将其中低于60的条目标记出来

设计：

1. 读取指定路径下的所有成绩单，以学号为新的索引进行合并，输出DataForm数据。
2. 保存DataFrom数据到指定的excel，再使用openpyxl进行单元格的样式设置并保存。

代码：

import os
import pandas as pd
import numpy as np
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, Border

def excel_df_data(dirs):
  if not os.path.exists(dirs) or not os.listdir(dirs): raise  Exception('文件夹不存在')
  # 文件列表
  file_list = [v for v in os.listdir(dirs)]
  # 文件数据集合
  pd_list = []
  # 成绩单名称的集合，用与生成二级表头
  pd_key_list = []
  for excle_file in file_list:
    v = pd.read_excel(os.path.join(dirs, excle_file))
    # 第一种方法  设置名称作为索引，并替换原先的索引
    v.set_index('名字',inplace=True)
    # 第二种方法  将名称的值作为索引
    # v.index = v['名字'].values
    # # 删除名称一列 axis 默认是0 表示为行
    # v= v.drop('学号', axis=1)
    # 二维数组
    # v.columns = [[excle_file],v.columns]
    # 新增加一列平均值
    v['svg'] = v.apply(lambda x: x.sum()/len(x), axis=1)
    # 保留一位小数
    v['svg'] = v['svg'].apply(lambda x: round(x, 1))
    # v['svg'] = v['svg'].apply(lambda x: format(x, '.2%'))
    pd_list.append(v)
    pd_key_list.append(excle_file[0:excle_file.index('.')])
  pd_list_data =pd.concat(pd_list, keys=pd_key_list,  axis=1,join='outer')
  # 输出最后合并的数据
  return pd_list_data

def add_style(file_path):
  #加载已存在的excel
  book = openpyxl.load_workbook(file_path)
  sheet = book[book.sheetnames[0]]
  # 删除第三行，因为是空行
  sheet.delete_rows(3)
  # 设置样式，让第一行表头有不一样的背景颜色，因为只能是单元格设置样式，所以需要循环
  for v in  sheet[1]:
    v.fill=PatternFill(fill_type='solid', fgColor="B4C6E7") 
  for v in  sheet[2]:
    v.fill=PatternFill(fill_type='solid', fgColor="4D83D6") 
  # 给低于60的条目加上红色
  for row_index in range(3, sheet.max_row+1):
    for col_index in range(1, sheet.max_column):
      __cel = sheet[row_index][col_index]
      if type(__cel.value) in [int, float] and __cel.value < 60:
        __cel.fill=PatternFill(fill_type='solid', fgColor="D6654D") 
 
  # 保存
  book.save(file_path)
   

if __name__ == '__main__':
  # 获取合并数据
  pd_data = excel_df_data('成绩单')
  # 保存
  pd_data.to_excel('b.xlsx')
  # 样式处理
  add_style('b.xlsx')