excel自动格式化python脚本

import os import sys import glob import getpass import xlwings as xw from datetime import datetime def find_latest_xlsx_file(directory): # 使用glob找到目录下所有.xlsx文件 xlsx_files = glob.glob(os.path.join(directory, '*.xlsx')) # 如果没有找到任何.xlsx文件，返回None if not xlsx_files: return None # 初始化最新文件路径为列表中的第一个文件 latest_file = xlsx_files[0] # 遍历所有文件，找到最后修改时间最晚的文件 for file in xlsx_files: if os.path.getmtime(file) > os.path.getmtime(latest_file): latest_file = file return latest_file def num_to_alphabet(num): if num == 0: return "A" result = "" while num > 0: # 使用 % 运算符获取余数，这相当于26进制下的当前位 # 使用 // 运算符进行整数除法，以去除当前位 num, remainder = divmod(num - 1, 26) # 减去1是因为我们想要'A'对应1 result = chr(remainder + ord('A')) + result # 将余数转换为字母并添加到结果字符串的开头 return result # 打开excel,参数visible表示处理过程是否可视,add_book表示是否打开新的Excel程序 with xw.App(visible=False,add_book=False) as app: # 获取脚本所在目录 script_dir = os.path.dirname(os.path.abspath(__file__)) # 构建到文件的相对路径 file_path = os.path.join(script_dir, './template/template.xlsx') # 假设template.xlsx在data目录下，而data是scripts的上一级目录的同级目录 book_tem = app.books.open(file_path) # book_tem = app.books.open('./template.xlsx') sheet_tem = book_tem.sheets[0] user = getpass.getuser() directory_path = f'C:/Users/{user}/Downloads' # 修改为你的目录路径 latest_xlsx_file = find_latest_xlsx_file(directory_path) if latest_xlsx_file: print(f"使用的.xlsx文件是: {latest_xlsx_file}") book_target = app.books.open(latest_xlsx_file) sheet_target = book_target.sheets[0] range_target = sheet_target.used_range last_row = range_target.last_cell.row last_column = range_target.last_cell.column for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "姓名": name = f'{num_to_alphabet(i)}2:{num_to_alphabet(i)}{last_row}' data = sheet_target.range(name).value client = set() for row in data: if row is not None: client.add(row) sheet_tem.range('F3').value = len(client) for j in range(1, last_row): sheet_tem.range(j+6,1).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "镇": town = f'{num_to_alphabet(i)}2:{num_to_alphabet(i)}{last_row}' data = sheet_target.range(town).value # 使用集合来存储唯一值 region = set() # 遍历数据，将唯一值添加到集合中 for row in data: if row is not None: region.add(row) region_formatted = "、".join(region) sheet_tem.range('F2').value = region_formatted for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "自然村": for j in range(1, last_row): sheet_tem.range(j+6,2).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "详细地址": for j in range(1, last_row): sheet_tem.range(j+6,3).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "母鸡": for j in range(1, last_row): sheet_tem.range(j+6,4).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "阉鸡(三黄)": for j in range(1, last_row): sheet_tem.range(j+6,5).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "阉鸡(胡须)": for j in range(1, last_row): sheet_tem.range(j+6,6).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "生鸡": for j in range(1, last_row): sheet_tem.range(j+6,7).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "饲料": for j in range(1, last_row): sheet_tem.range(j+6,9).value = sheet_target.range((j+1, i)).value for i in range(1, last_column+1): if sheet_target.range((1, i)).value == "备注": for j in range(1, last_row): sheet_tem.range(j+6,10).value = sheet_target.range((j+1, i)).value for i in range(7, last_row+6): sheet_tem.range(f'H{i}').value = f'=SUM(D{i}:G{i})' now = datetime.now() formatted_now = now.strftime("%Y%m%d%M%S")[2:] sheet_tem.range('A4').value = now.strftime("%Y年%m月%d日") sheet_tem.range('F1').value = f'NO{formatted_now}' save_path = os.path.join(script_dir, f'./excel/配送表_{formatted_now}.xlsx') # 保存 book_tem.save(save_path) book_tem.close() book_target.close() # app.quit() print(f"成功生成以下文件: {save_path}") else: print("在该目录下没有找到.xlsx文件。") input("按Enter键退出...")