如何用Python验证表格内公司营收是否存在于对应PDF并标记结果

Got it, let's walk through how to build this Python solution. We'll use a mix of libraries to handle spreadsheet data and PDF text extraction—super straightforward once you break it down.

Required Libraries #

First, install the packages we need:

pip install pandas pdfplumber openpyxl

• pandas: Handles reading/writing spreadsheets (Excel/CSV)
• pdfplumber: Reliably extracts text from editable PDFs (better than some alternatives for preserving formatting context)
• openpyxl: Required for reading/writing .xlsx files

Step-by-Step Implementation #

1. Define a PDF Text Extraction Function #

This function will take a PDF file path and return all the text content from the document. We'll add basic error handling for cases where the file doesn't exist or can't be read.

import pdfplumber

def extract_pdf_text(pdf_path):
    try:
        with pdfplumber.open(pdf_path) as pdf:
            full_text = ""
            for page in pdf.pages:
                # Extract text, default to empty string if page has no text
                full_text += page.extract_text() or ""
            return full_text
    except Exception as e:
        print(f"Error accessing {pdf_path}: {str(e)}")
        return ""

2. Process the Spreadsheet #

We'll read the spreadsheet, loop through each row, check if the revenue value exists in the corresponding PDF, and mark the result in column D.

Note: Customize the column names below to match your actual spreadsheet. For example, if column A is "Company Name", column B is "Revenue", column C is "PDF File Path", column D will hold our verification result.

import pandas as pd

# Load your spreadsheet (adjust file path and engine if using CSV)
df = pd.read_excel("company_revenue_data.xlsx", engine="openpyxl")

# Map your sheet's columns to variables (update these to match your data)
REVENUE_COL = "Revenue"
PDF_PATH_COL = "PDF Path"
RESULT_COL = "Verification Result"  # This will populate column D

# Loop through each row to check revenue presence
for index, row in df.iterrows():
    # Convert revenue to string for text matching
    raw_revenue = str(row[REVENUE_COL])
    pdf_path = row[PDF_PATH_COL]
    
    # Pull text from the target PDF
    pdf_content = extract_pdf_text(pdf_path)
    
    # Clean values to handle formatting differences (e.g., commas, currency symbols)
    cleaned_revenue = raw_revenue.replace(",", "").replace("$", "").strip()
    cleaned_pdf_text = pdf_content.replace(",", "").replace("$", "").strip()
    
    # Check and mark result
    if cleaned_revenue in cleaned_pdf_text:
        df.at[index, RESULT_COL] = "存在"
    else:
        df.at[index, RESULT_COL] = "不存在"

# Save the updated spreadsheet
df.to_excel("verified_revenue_data.xlsx", index=False, engine="openpyxl")
print("Verification complete! Updated spreadsheet saved.")

Key Notes & Adjustments #

• Formatting Edge Cases: Revenue might appear differently in PDFs (e.g., 1.2M vs 1200000, or ¥50,000 vs 50000). Extend the cleaning logic to handle these—for example, replace "M" with "000000" or convert currency symbols to empty strings.
• Large PDFs: If you know revenue data is only on specific pages (e.g., page 3 of each PDF), modify the extraction function to only read those pages to save time.
• Error Handling: You can expand the error logic to mark rows with invalid PDF paths as "PDF Access Error" in the result column instead of just printing a message.

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