【python脚本】自动化办公处理excel表格

# 模块导入
import os
import sys
import openpyxl
import shutil
import xlrd
from openpyxl import load_workbook

# 参数
count = 0 # 计数变量

# 函数

# 删除某个目录底下的所有空文件夹
def remove_empty_subdirectories(directory):
    for root, dirs, files in os.walk(directory, topdown=False):
        for name in dirs:
            subdirectory = os.path.join(root, name)
            if not os.listdir(subdirectory):  # Check if the directory is empty
                os.rmdir(subdirectory)

# 将指定的文件夹内的文件归纳到一个指定文件夹里面
def collect_file(source_folder='./collect',destination_folder='./new'):
    if not os.path.exists(source_folder):
        print("指定目录不存在")
        return False

    if not os.path.exists(destination_folder):
        os.makedirs(destination_folder)

    # 遍历原始文件夹下的所有子文件夹
    for root, dirs, files in os.walk(source_folder):
        for file_name in files:
            # 构建文件的完整路径
            file_path = os.path.join(root, file_name)
            target_file_path = os.path.join(destination_folder, file_name)
            if not os.path.exists(target_file_path):
                shutil.move(file_path, destination_folder)
            else:
                base, ext = os.path.splitext(file_name)
                index = 1
                while True:
                    new_file_name = f"{base}_{index}{ext}"
                    new_target_file_path = os.path.join(destination_folder, new_file_name)
                    if not os.path.exists(new_target_file_path):
                        shutil.move(file_path, new_target_file_path)
                        break
                    else:
                        index += 1

    remove_empty_subdirectories(source_folder)

# 根据匹配字段移动文件到新的文件夹（文件归类到不同文件夹）
def move_file_sorted(folder_path, search_string, new_path=""):
    if not os.path.exists(new_path):
        os.makedirs(new_path)

    for file_name in os.listdir(folder_path):
        if search_string in file_name:
            shutil.move(folder_path +file_name, new_path)

# 根据匹配字段重命名并移动文件
def move_renamed_file(folder_path, new_folder_path, search_string, is_test=True):
    # 检查移动的目标文件夹是否存在，不存在则创建
    if not os.path.exists(new_folder_path):
        os.makedirs(new_folder_path)
    # 遍历查找文件夹，根据字段匹配，匹配成果则重命名移动到
    for file_name in os.listdir(folder_path):
        if search_string == file_name:
            old_path = os.path.join(folder_path, file_name)
            new_path = os.path.join(new_folder_path, new_name)  # 更新 new_path 为新文件夹路径
            if is_test:
                print(f"文件 {file_name} 已重命名为 {new_name} 并存入新文件夹")
            else:
                os.rename(old_path, new_path)

# 创建一个新的xlsx表格
def create_xlsx(file_name="default.xlsx", is_head=False, head_list=["学号", "姓名"]):
    if os.path.exists(file_name):
        # 如果文件存在，先删除
        os.remove(file_name)

    # 创建一个新的 Excel 工作簿
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    if is_head:
        # 写入表头
        sheet.append(head_list)
    else:
        print("no head")
    # 保存 Excel 文件
    workbook.save(file_name)

# 在excel表格添加新的数据
def append_data_to_excel(excel, new_data):
    # 打开已存在的 Excel 文件
    workbook = openpyxl.load_workbook(excel)
    # 选择要添加数据的工作表，这里假设工作表的名字是 "Sheet"
    sheet = workbook["Sheet"]
    # 准备要添加的新数据，例如一个列表
    # new_data = [num, CourseName, week, day, lesson, classroom, teacher]
    # 在工作表中添加新数据
    sheet.append(new_data)
    # 保存修改后的 Excel 文件
    workbook.save(excel)

# 读取xlsx文件，打印信息
def read_xlsx(xlsx_name, sheet_name, start_row=0, column=0, is_print=True):
    """
    :param xlsx_name: 指定excel表格的名字
    :param sheet_name: 指定sheet工作表的名字
    :param start_row: 指定从工作表第几行开始读取，默认是0
    :param column: 指定读取工作表的第几列
    :param is_print: 是否打印
    :return: 返回操作的工作表
    """
    # 打开工作簿并选择指定的工作表
    workbook = load_workbook(filename=xlsx_name, read_only=True)
    # 选择要读取的工作表
    sheet = workbook[sheet_name]

    if is_print:
        # 读取数据，打印
        for row in sheet.iter_rows(min_row=start_row, values_only=True):
            print(row)
            # print(row[column])
    else:
        # 不打印工作表，返回工作表
        return sheet

# 根据匹配的信息移动文件夹
def move_dir(source_directory, target_directory, info_to_match, is_print=True):
    """
    :param source_directory: 指定源文件夹路径
    :param target_directory: 指定目标文件夹路径
    :param info_to_match: 指定要匹配的信息
    :return: 无
    """
    count = 0
    for root, dirs, files in os.walk(source_directory):
        for directory in dirs:
            # 检查文件夹名称是否包含要匹配的信息
            if info_to_match in directory:
                # 构建源文件夹的路径
                source_folder_path = os.path.join(root, directory)
                # 构建目标文件夹的路径
                target_folder_path = os.path.join(target_directory, directory)

                # 移动文件夹到目标位置
                shutil.move(source_folder_path, target_folder_path)
                if is_print:
                    print(f"Moved folder: {source_folder_path} to {target_folder_path}")

# 指定目录的文件夹根据excel信息匹配移动到新的文件夹
def move_according_excel(xlsx_name, sheet_name, start_row=0, folder_path="", count=0):
    workbook = openpyxl.load_workbook(xlsx_name)
    work_sheet = workbook[sheet_name]

    c = 0
    # 读取excel信息
    for row in work_sheet.iter_rows(min_row=start_row, values_only=True):
        # 读取的信息根据需求进行修改
        yjs = row[0]
        num = row[1]
        name = row[2]

        if num is None:
            continue

        # print(c,yjs,num,name)
        # 设置匹配项
        search_string = str(num)
        # 构造新的目录
        new_folder_path = "./照片（归类）/" + str(yjs)
        if not os.path.exists(new_folder_path):
            os.makedirs(new_folder_path)
        flag = 0
        for file_name in os.listdir(folder_path):

            if search_string in file_name:
                count = count + 1
                flag = 1
                # print(count,folder_path +file_name)
                old_path = os.path.join(folder_path, file_name)
                # print(old_path)

                newfileName = str(num) + str(name) + '.jpg'
                # print(newfileName)

                new_path = os.path.join(new_folder_path, newfileName)  # 更新 new_path 为新文件夹路径
                # print(new_folder_path)

                # os.rename(old_path, new_path)

                try:
                    # os.rename(old_path, new_path)
                    print(count, f"成功将文件重命名为: {new_path}")
                except FileNotFoundError:
                    print(count, f"文件{old_path}不存在，无法进行重命名")
                    print(newfileName)


        # move_dir(source_directory=folder_path, target_directory=new_folder_path, info_to_match=search_string)

    print("finish")
    return count

# 提取一个excel表格的信息到一个新的excel表格
def to_new_excel(soure_excel="test.xlsx", soure_sheet="Sheet1",targer_excel="targer.xlsx"):

    # 创建目标文件excel根据需求修改
    create_xlsx(file_name=targer_excel, is_head=True, head_list=["学号", "姓名", "答辩分数", "平均分"])

    workbook = openpyxl.load_workbook(soure_excel)
    work_sheet = workbook[soure_sheet]

    for row in work_sheet.iter_rows(min_row=2, values_only=True):

        # 提取的内容可根据需求修改
        num = row[0]
        name = row[1]
        score = row[2]
        scores = score.split(",")  # 使用逗号作为分隔符
        i = 0
        sum = 0
        avg = 0
        for s in scores:
            i = i + 1
            # print(s,end=' ')
            sum = sum + int(s)
        avg = sum / i

        new_data = [num,name,score,avg]
        # 将数据存入excel表格
        append_data_to_excel(targer_excel, new_data)