使用Python win32com批量转Excel为PDF遇保存错误及解决

问题背景 #

要实现读取存储Excel文件路径的TXT文件，将每个Excel文件转存为PDF到指定目录。脚本能正常读取路径，但保存PDF时持续报错。

初始报错代码及错误信息 #

最初的save_pdfs函数：

def save_pdfs(files_list,export_folder):
    for line_item, filename in enumerate(files_list, start=1):
        excel = client.Dispatch("Excel.Application")
        excel.Application.DisplayAlerts = False
        print(line_item, f'{filename}')
        try:
            wb = excel.Workbooks.Open(filename, ReadOnly=True)
            work_sheets = wb.Worksheets[0]
            if len(str(line_item)) == 1:
                work_sheets.ExportAsFixedFormat(0, f'{export_folder}\0{line_item}')
            else:
                work_sheets.ExportAsFixedFormat(0, f'{export_folder}\{line_item}')
        except Exception as e:
            print(f"An error occurred: {e}")
        finally:
            wb.Close(False)
            excel.Application.DisplayAlerts = True
            excel.Quit()

运行后每个文件都触发错误：

An error occurred: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel', 'Document not saved. The document may be open, or an error may have been encountered when saving.', 'xlmain11.chm', 0, -2146827284), None)

已确认运行脚本时没有打开Excel，也无其他用户占用文件。

第一次修改（仍报错） #

将Excel实例的创建和销毁移到循环外，但问题依旧，完整脚本如下：

from win32com import client 
from tkinter import filedialog, messagebox
from pypdf import PdfWriter
import glob

def pdf_merge(directory):
    merger = PdfWriter()
    pdf_file_list = glob.glob(f'{directory}\*.pdf')
    for pdf in pdf_file_list:
        merger.append(pdf)
    merger.write(f'{directory}\Merged.pdf')
    merger.close()


def read_filenames(filepath):
    filenames = []
    try:
        with open(filepath, 'r') as file:
            for line in file:
                filename = line.strip().replace('"','')
                if filename:
                    filenames.append(filename)
    except FileNotFoundError:
        print(f"Error: File not found at path: {filepath}")
    except Exception as e:
        print(f"An error occurred: {e}")
    return filenames

def save_pdfs(files_list,export_folder):
    excel = client.Dispatch("Excel.Application")
    excel.Application.DisplayAlerts = False
    for line_item, filename in enumerate(files_list, start=1):
        print(line_item, f'{filename}')
        try:
            wb = excel.Workbooks.Open(filename, ReadOnly=True)
            work_sheets = wb.Worksheets[0]
            if len(str(line_item)) == 1:
                work_sheets.ExportAsFixedFormat(0, f'{export_folder}\0{line_item}.pdf')
            else:
                work_sheets.ExportAsFixedFormat(0, f'{export_folder}\{line_item}.pdf')
        except Exception as e:
            print(f"An error occurred: {e}")
        finally:
            wb.Close(False)
    excel.Application.DisplayAlerts = True
    excel.Quit()

messagebox.showinfo(title='Seleect File', message= 'Please select text file with listed file paths.')
file_path = filedialog.askopenfilename()
files_list = read_filenames(file_path)
messagebox.showinfo(title='Seleect Folder', message= 'Please select folder to save output files.')
export_folder = filedialog.askdirectory()
save_pdfs(files_list, export_folder)
if messagebox.askyesno(title="Merge?",message="Do you want to merge the PDFs?"):
    pdf_merge(export_folder)

测试用TXT内容：

"C:\Python Scripts\Test Files\Test File 1.xlsx"
"C:\Python Scripts\Test Files\Test File 2.xlsx"
"C:\Python Scripts\Test Files\Test File 3.xlsx"
"C:\Python Scripts\Test Files\Test File 4.xlsx"

测试确认Excel能正常打开，仅PDF保存步骤失败。

最终解决：使用pathlib优化路径处理 #

通过pathlib统一路径处理逻辑，替换ExportAsFixedFormat为SaveAs并指定格式代码57（对应PDF），脚本恢复正常运行，完整可用脚本如下：

from win32com import client 
from tkinter import filedialog, messagebox
from pypdf import PdfWriter
from pathlib import Path
import glob

def pdf_merge(directory):
    merger = PdfWriter()
    pdf_file_list = glob.glob(f'{directory}\*.pdf')
    for pdf in pdf_file_list:
        merger.append(pdf)
    merger.write(f'{directory}\Merged.pdf')
    merger.close()


def read_filenames(filepath):
    filenames = []
    try:
        with open(filepath, 'r') as file:
            for line in file:
                filename = line.strip().replace('"','')
                if filename:
                    filenames.append(filename)
    except FileNotFoundError:
        print(f"Error: File not found at path: {filepath}")
    except Exception as e:
        print(f"An error occurred: {e}")
    return filenames

def save_pdfs(files_list,export_folder):
    excel = client.Dispatch("Excel.Application")
    excel.Visible = False
    excel.Application.DisplayAlerts = False

    for line_item, source_file_path in enumerate(files_list, start=1):
        print(line_item, f'{source_file_path}')
        final_path = export_folder / (str(line_item).zfill(2) + '.pdf')
        print(final_path)
        try:
            wb = excel.Workbooks.Open(source_file_path, ReadOnly=True)
            work_sheets = wb.Worksheets[0]
            work_sheets.SaveAs(final_path, 57)
        except Exception as e:
            print(f"An error occurred: {e}")
        finally:
            wb.Close(False)
    excel.Application.DisplayAlerts = True
    excel.Visible = True
    excel.Quit()


messagebox.showinfo(title='Seleect File', message= 'Please select text file with listed file paths.')
file_path = Path(filedialog.askopenfilename())
#print(file_path)
files_list = read_filenames(file_path)
#print(files_list)
messagebox.showinfo(title='Seleect Folder', message= 'Please select folder to save output files.')
export_folder = Path(filedialog.askdirectory())
#print(export_folder)
save_pdfs(files_list, export_folder)
if messagebox.askyesno(title="Merge?",message="Do you want to merge the PDFs?"):
    pdf_merge(export_folder)

关键修复点 #

1. 路径处理：用pathlib.Path统一管理路径，避免字符串拼接时的转义字符问题（比如原代码中\0会被解析为空字符，导致路径无效）
2. PDF保存方式：改用SaveAs方法并指定格式代码57（Excel中PDF对应的文件格式代码），替代ExportAsFixedFormat，解决保存权限或格式解析问题
3. 格式统一：用zfill(2)生成两位数字的文件名，简化原有的长度判断逻辑

内容的提问来源于stack exchange，提问作者Keronin