求助：PDF提取后Excel列G日期格式修改无效（Pandas实现）

从PDF提取数据保存至Excel后，列G中的日期无法通过修改格式直接生效，必须双击单元格才会显示目标格式。尝试用strftime("%d/%m/%Y")格式化也未能解决问题，原代码如下：

import pdfplumber
import pandas as pd

def extract_lines(pdf_file_path, excel_output_path):
    table_data = []
    with pdfplumber.open(pdf_file_path) as pdf:
        for page_number in range(len(pdf.pages)):
            page = pdf.pages[page_number]
            page_text = page.extract_text()
            rows = page_text.strip().split('\n')
            for row in rows:
                if row.strip()[-1].isdigit():
                    segments = row.strip().split()
                    table_data.append(segments)
    if table_data:
        df = pd.DataFrame(table_data)
        df = df.iloc[:, ::-1]
        excel_writer = pd.ExcelWriter(excel_output_path, engine='xlsxwriter')
        df.to_excel(excel_writer, index=False, sheet_name='Sheet1')
        workbook = excel_writer.book
        worksheet = excel_writer.sheets['Sheet1']
        worksheet.right_to_left()
        excel_writer._save()
        print(f'PDF Data Converted And Saved To {excel_output_path}')
    else:
        print('No Lines Ending With Digits Found In The PDF')

if __name__ == '__main__':
    extract_lines('Sample.pdf', 'Output.xlsx')

解决方案 #

问题核心是：从PDF提取的日期以纯文本形式存入Excel，Excel无法识别为日期类型，所以修改格式无响应，双击时才触发Excel的自动类型转换。要彻底解决，需在代码中完成类型转换和格式设置：

关键步骤 #

1. 将文本转换为日期类型：用pd.to_datetime把DataFrame中对应列的文本转为Python日期对象
2. 设置Excel单元格格式：用xlsxwriter定义日期格式，应用到目标列

修改后的代码 #

import pdfplumber
import pandas as pd

def extract_lines(pdf_file_path, excel_output_path):
    table_data = []
    with pdfplumber.open(pdf_file_path) as pdf:
        for page_number in range(len(pdf.pages)):
            page = pdf.pages[page_number]
            page_text = page.extract_text()
            rows = page_text.strip().split('\n')
            for row in rows:
                if row.strip()[-1].isdigit():
                    segments = row.strip().split()
                    table_data.append(segments)
    if table_data:
        df = pd.DataFrame(table_data)
        df = df.iloc[:, ::-1]
        
        # 转换日期列（假设反转后列索引6对应Excel的G列，需根据实际调整）
        # 若PDF日期格式固定，可替换infer_datetime_format为具体格式，比如format="%d/%m/%Y"
        df[6] = pd.to_datetime(df[6], infer_datetime_format=False, errors='coerce')
        
        excel_writer = pd.ExcelWriter(excel_output_path, engine='xlsxwriter')
        df.to_excel(excel_writer, index=False, sheet_name='Sheet1')
        workbook = excel_writer.book
        worksheet = excel_writer.sheets['Sheet1']
        
        # 创建并应用日期格式
        date_format = workbook.add_format({'num_format': 'dd/mm/yyyy'})
        # 设置G列（xlsxwriter列索引从0开始，G列对应索引6）的格式和宽度
        worksheet.set_column(6, 6, 15, date_format)
        
        worksheet.right_to_left()
        excel_writer._save()
        print(f'PDF Data Converted And Saved To {excel_output_path}')
    else:
        print('No Lines Ending With Digits Found In The PDF')

if __name__ == '__main__':
    extract_lines('Sample.pdf', 'Output.xlsx')

注意事项 #

• 确认df[6]是实际对应Excel列G的DataFrame列索引，需根据PDF中日期列的位置调整
• 若PDF中日期格式固定，使用具体格式字符串替换infer_datetime_format=False，能提升转换准确性
• errors='coerce'会把无法转换的内容设为NaT，可根据需求调整为'raise'（抛出错误）或'ignore'（保留原文本）

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