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键退出...")
