【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 emptyos.rmdir(subdirectory)# 将指定的文件夹内的文件归纳到一个指定文件夹里面
def collect_file(source_folder='./collect',destination_folder='./new'):if not os.path.exists(source_folder):print("指定目录不存在")return Falseif 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 = 1while 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)breakelse:index += 1remove_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.activeif 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 = 0for 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 = 0for file_name in os.listdir(folder_path):if search_string in file_name:count = count + 1flag = 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 = 0sum = 0avg = 0for s in scores:i = i + 1# print(s,end=' ')sum = sum + int(s)avg = sum / inew_data = [num,name,score,avg]# 将数据存入excel表格append_data_to_excel(targer_excel, new_data)