使用Python定时备份多用户Excel文件时的路径保存问题

我来帮你完善这个多用户场景下的Excel定时备份方案，你的现有代码已经有了基础的读取和命名逻辑，但还需要解决非本地目录存储、定时任务触发、多用户访问时的文件冲突这几个关键问题，下面是具体的实现方案：

一、核心备份逻辑优化 #

首先要明确非本地目录的路径（比如网络共享盘、云存储挂载目录），直接在ExcelWriter中指定完整路径即可。另外，多用户访问的Excel容易被锁定，直接用pandas读取可能报错，建议搭配openpyxl引擎，并添加异常捕获处理文件占用的情况。

修改后的基础备份代码：

import pandas as pd
import datetime
from openpyxl import load_workbook

# 源Excel文件路径
source_file = r'Z:\new\Planner_New.xlsx'
# 非本地备份目录（替换成你的实际远程/共享路径）
backup_dir = r'\\backup-server\excel-backups\\'

# 生成带时间戳的备份文件名（替换空格为下划线，避免路径异常）
now = datetime.datetime.now()
time_suffix = now.strftime("%Y-%m-%d_%H.%M.%S")
backup_filename = f"Planner_{time_suffix}.xlsx"
backup_full_path = f"{backup_dir}{backup_filename}"

try:
    # 读取整个Excel文件（支持备份所有sheet）
    excel_file = pd.ExcelFile(source_file)
    # 使用openpyxl引擎保留原文件格式
    with pd.ExcelWriter(backup_full_path, engine='openpyxl') as writer:
        for sheet_name in excel_file.sheet_names:
            # 按需求读取指定列的内容
            df = excel_file.parse(
                sheet_name, 
                header=0, 
                index_col=0, 
                usecols="A:AY", 
                convert_float=True
            )
            df.to_excel(writer, sheet_name=sheet_name)
    print(f"备份成功：{backup_full_path}")
except PermissionError:
    print("错误：源文件正在被其他用户占用，无法读取")
except Exception as e:
    print(f"备份失败：{str(e)}")

二、添加定时任务功能 #

要实现自动定时备份，推荐用schedule库（简单易用），先通过pip install schedule安装。下面是整合了定时任务的代码：

import schedule
import time

def backup_excel():
    # 把上面的备份逻辑封装到这个函数里
    import pandas as pd
    import datetime
    from openpyxl import load_workbook

    source_file = r'Z:\new\Planner_New.xlsx'
    backup_dir = r'\\backup-server\excel-backups\\'
    now = datetime.datetime.now()
    time_suffix = now.strftime("%Y-%m-%d_%H.%M.%S")
    backup_filename = f"Planner_{time_suffix}.xlsx"
    backup_full_path = f"{backup_dir}{backup_filename}"

    try:
        excel_file = pd.ExcelFile(source_file)
        with pd.ExcelWriter(backup_full_path, engine='openpyxl') as writer:
            for sheet_name in excel_file.sheet_names:
                df = excel_file.parse(
                    sheet_name, 
                    header=0, 
                    index_col=0, 
                    usecols="A:AY", 
                    convert_float=True
                )
                df.to_excel(writer, sheet_name=sheet_name)
        print(f"[{now}] 备份成功：{backup_full_path}")
    except PermissionError:
        print(f"[{now}] 错误：源文件被占用，跳过本次备份")
    except Exception as e:
        print(f"[{now}] 备份失败：{str(e)}")

# 设置定时规则：比如每天上午9点和下午5点各备份一次
schedule.every().day.at("09:00").do(backup_excel)
schedule.every().day.at("17:00").do(backup_excel)

# 也可以设置每小时备份一次
# schedule.every(1).hours.do(backup_excel)

print("定时备份系统已启动，按Ctrl+C停止...")
while True:
    schedule.run_pending()
    time.sleep(60)  # 每分钟检查一次任务是否需要执行

三、多用户场景的额外优化建议 #

• 文件占用重试机制：如果频繁遇到文件被占用的情况，可以添加重试逻辑：

  except PermissionError:
      print("源文件被占用，等待30秒后重试...")
      time.sleep(30)
      backup_excel()  # 重新执行备份
  

• 旧备份清理：定期清理过期备份，避免非本地目录空间不足，可添加清理函数配合定时任务执行：

  def clean_old_backups(days_to_keep=30):
      import os
      cutoff_date = datetime.datetime.now() - datetime.timedelta(days=days_to_keep)
      for filename in os.listdir(backup_dir):
          file_path = os.path.join(backup_dir, filename)
          if os.path.isfile(file_path) and filename.startswith("Planner_"):
              try:
                  file_date = datetime.datetime.strptime(filename.split("_")[1], "%Y-%m-%d_%H.%M.%S")
                  if file_date < cutoff_date:
                      os.remove(file_path)
                      print(f"已清理旧备份：{file_path}")
              except Exception as e:
                  print(f"清理文件失败 {filename}：{str(e)}")
  

• 日志记录：用logging模块替代print，把备份日志写入文件，方便后续排查问题。

完整可运行代码 #

import pandas as pd
import datetime
import schedule
import time
from openpyxl import load_workbook
import logging

# 配置日志记录（日志文件也存到非本地目录）
logging.basicConfig(
    filename=r'\\backup-server\excel-backups\backup_logs.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def backup_excel():
    source_file = r'Z:\new\Planner_New.xlsx'
    backup_dir = r'\\backup-server\excel-backups\\'
    now = datetime.datetime.now()
    time_suffix = now.strftime("%Y-%m-%d_%H.%M.%S")
    backup_filename = f"Planner_{time_suffix}.xlsx"
    backup_full_path = f"{backup_dir}{backup_filename}"

    try:
        excel_file = pd.ExcelFile(source_file)
        with pd.ExcelWriter(backup_full_path, engine='openpyxl') as writer:
            for sheet_name in excel_file.sheet_names:
                df = excel_file.parse(
                    sheet_name, 
                    header=0, 
                    index_col=0, 
                    usecols="A:AY", 
                    convert_float=True
                )
                df.to_excel(writer, sheet_name=sheet_name)
        logging.info(f"备份成功：{backup_full_path}")
        print(f"[{now}] 备份成功：{backup_full_path}")
    except PermissionError:
        error_msg = "源文件正在被其他用户占用，跳过本次备份"
        logging.warning(error_msg)
        print(f"[{now}] {error_msg}")
    except Exception as e:
        error_msg = f"备份失败：{str(e)}"
        logging.error(error_msg)
        print(f"[{now}] {error_msg}")

def clean_old_backups(days_to_keep=30):
    import os
    cutoff_date = datetime.datetime.now() - datetime.timedelta(days=days_to_keep)
    for filename in os.listdir(backup_dir):
        file_path = os.path.join(backup_dir, filename)
        if os.path.isfile(file_path) and filename.startswith("Planner_"):
            try:
                file_date_str = filename.split("_")[1]
                file_date = datetime.datetime.strptime(file_date_str, "%Y-%m-%d_%H.%M.%S")
                if file_date < cutoff_date:
                    os.remove(file_path)
                    logging.info(f"已清理旧备份：{file_path}")
            except Exception as e:
                logging.warning(f"清理文件失败 {filename}：{str(e)}")

# 配置定时任务
schedule.every().day.at("09:00").do(backup_excel)
schedule.every().day.at("17:00").do(backup_excel)
schedule.every().sunday.at("02:00").do(clean_old_backups, days_to_keep=30)

print("定时备份系统已启动，按Ctrl+C停止...")
try:
    while True:
        schedule.run_pending()
        time.sleep(60)
except KeyboardInterrupt:
    print("系统已停止")
    logging.info("用户手动停止备份系统")

内容的提问来源于stack exchange，提问作者T.Scanlon