用Python处理Excel表——按列内容分割、修改格式

以下代码可以实现：

1. 按“姓名”列的内容将一个总表拆分为多个表；
2. 添加列和行
3. 修改拆分后表格的格式，包括列宽、有无框线等；
4. 单独修改某一行的格式：apply_style_by_indexes

import pandas as pd
from styleframe import StyleFrame, Styler, utils

data = pd.read_excel(r"考勤.xls")
rows = data.shape[0]
department_list = []
ListNum = 0
for i in range(rows):
    temp = data[r"姓名"][i]
    if temp not in department_list:
        department_list.append(temp)
for department in department_list:
    ListNum = 0
    new_df = pd.DataFrame()
    print(new_df)
    for i in range(0, rows):
        if data["姓名"][i] == department:
            new_df = pd.concat([new_df, data.iloc[[i], :]], axis=0, ignore_index=True)
            ListNum += 1
            # print(new_df)
    # 将数据框的列名全部提取出来存放在列表里
    col_name = new_df.columns.tolist()
    # 添加列
    col_name.append('考勤异常备注')
    new_df = new_df.reindex(columns=col_name)
    # 添加页尾
    new_df = new_df.append([{'日期时间': ''}], ignore_index=True)
    new_df = new_df.append([{'日期时间': '部门负责人签字：'}], ignore_index=True)
    print(new_df)
    new_df.to_csv(department + '.xls', encoding='utf_8_sig', index=False)
    print(ListNum)
    sf = StyleFrame(new_df)

    # sf.apply_column_style(cols_to_style=["姓名"],
    #                       styler_obj=Styler(font_color='green'),
    #                       style_header=True)
    # 修改列宽
    sf.set_column_width(1, 14)
    sf.set_column_width(2, 10)
    sf.set_column_width(3, 12)
    sf.set_column_width(4, 28)
    sf.set_column_width(5, 18)
    # yellow = Styler(bg_color='yellow')
    # sf.apply_style_by_indexes(sf.index[0], yellow)
    # 修改最后两行没有边框
    sf.apply_style_by_indexes(sf.index[ListNum], Styler(border_type=None))
    sf.apply_style_by_indexes(sf.index[ListNum+1], Styler(border_type=None))

    ew = StyleFrame.ExcelWriter(department + '.xls')
    sf.to_excel(ew)
    ew.save()