R语言：Excel日期提取与PDF文本替换后保存修改文件的方法

需要处理指定文件夹（top_folder）下的一批Excel文件，完成以下任务：

• 提取报价日期：从Excel文件中提取报价日期；
• 匹配PDF文件：查找与Excel文件命名模式对应的PDF文件；
• 修改PDF内容：找到匹配的PDF后，搜索其中与报价日期相关的特定模式，并用从Excel中获取的格式化报价日期替换；
• 保存修改后的PDF：将修改后的PDF内容保存为带_modified.pdf后缀的新文件。

目前已完成前3步，修改后的文本（modified_pdf_text）已能正常输出，但卡在修改后PDF的保存环节。

当前代码尝试：

library(RDCOMClient)
library(pdftools)

process_subfolders <- function(top_folder) {
  # Initialize Excel application
  xlApp <- COMCreate("Excel.Application")
  xlApp[["Visible"]] <- TRUE  # Set to TRUE for debugging purposes
  
  # List all files in the top folder
  all_files <- list.files(top_folder, recursive = TRUE, full.names = TRUE)
  
  # Filter for Excel files that start with 2 letters followed by 6 digits
  pattern <- "^.{1,2}\\d{6}.*\\.xlsm$"
  excel_files <- all_files[grep(pattern, basename(all_files), ignore.case = TRUE)]
  
  for (file_path in excel_files) {
    cat("Processing file:", file_path, "\n")
    
    xlBook <- NULL
    
    tryCatch({
      # Normalize path for Excel
      file_path <- normalizePath(file_path, winslash = "\\", mustWork = FALSE)
      
      # Open Excel workbook
      xlBook <- xlApp$Workbooks()$Open(file_path)
      
      if (is.null(xlBook)) {
        cat("Failed to open workbook:", file_path, "\n")
        next
      }
      
      # Get the date from cell C4 on the 'FURNITURE RENTAL' sheet
      xlSheet <- xlBook$Sheets("FURNITURE RENTAL")
      date_cell <- xlSheet$Range("C4")$Value()
      quote_date <- as.Date(date_cell, origin = "1899-12-30")
      
      # Format the date
      formatted_quote_date <- format(quote_date, "%A, %d %B %Y")
      
      cat("Quote date extracted:", formatted_quote_date, "\n")
      
      # Close the workbook
      xlBook$Close(FALSE)
      
      # Find the corresponding PDF file
      pdf_files <- all_files[grep("INVENTORY DOCKET.*\\.pdf$", all_files, ignore.case = TRUE)]
      
      if (length(pdf_files) == 0) {
        cat("No matching PDF files found for:", file_path, "\n")
        next
      }
      
      pdf_file <- pdf_files[1]  # Assuming the first match is the correct one
      
      cat("Processing PDF file:", pdf_file, "\n")
      
      # Extract PDF content
      pdf_text <- pdf_text(pdf_file)
      
      # Check if pdf_text is a character vector and concatenate if necessary
      if (is.character(pdf_text)) {
        pdf_text <- paste(pdf_text, collapse = "\n")
      }
      
      # Debugging output to inspect extracted PDF text
      cat("PDF text extracted:\n", pdf_text, "\n")
      
      # Find and replace the quote date in the PDF
      old_date_pattern <- "(?i)QUOTE\\s*DATE\\s*\\b(\\w+, \\d+ \\w+ \\d{4})\\b"
      
      # Attempt to extract the quote date using the defined pattern
      extracted_date <- str_match(pdf_text, old_date_pattern)[, 2]
      
      if (is.na(extracted_date)) {
        cat("No 'QUOTE DATE:' found in PDF\n")
      } else {
        cat("Extracted quote date:", extracted_date, "\n")
      }
      
      # Replace the old quote date with the new formatted date
      modified_pdf_text <- str_replace(pdf_text, old_date_pattern, paste("QUOTE DATE: ", formatted_quote_date))
      
      cat("modified_pdf_text:", modified_pdf_text)
       
      # this is where I am stuck       
      
    })
  }
  
  xlApp$Quit()
  cat("All files processed.\n")
}

# Example usage
top_folder <- top_folder_path
process_subfolders(top_folder)

pdftools包仅能提取PDF文本，无法直接编辑并保存原格式的PDF。以下提供两种可行的保存方案：

方案1：生成纯文本新PDF（格式简单场景） #

如果PDF内容以纯文本为主，可将修改后的文本拆分为原页面结构，再用pdftools::pdf_write生成新PDF。注意：此方法会丢失原PDF的排版样式（如字体、布局）。

在代码的# this is where I am stuck位置添加以下代码：

# 获取原PDF的页面数量
original_page_count <- length(pdf_text(pdf_file))
# 拆分修改后的文本为对应页面数量的段落（可根据原PDF的实际换行逻辑调整分割符）
modified_pages <- strsplit(modified_pdf_text, "\n\n")[[1]]
# 确保页面数量匹配，不足补空、过多截断
if(length(modified_pages) < original_page_count) {
  modified_pages <- c(modified_pages, rep("", original_page_count - length(modified_pages)))
} else if(length(modified_pages) > original_page_count) {
  modified_pages <- modified_pages[1:original_page_count]
}

# 生成输出文件名
output_pdf <- sub("\\.pdf$", "_modified.pdf", pdf_file)
# 保存修改后的PDF
pdf_write(modified_pages, output_pdf)
cat("Modified PDF saved to:", output_pdf, "\n")

方案2：使用pdftk保留原格式（推荐） #

若需要保留原PDF的排版样式，可使用pdftk命令行工具，通过生成FDF表单文件替换文本。步骤如下：

1. 先安装pdftk工具（Windows可下载安装包，Linux/macOS可通过包管理器安装）；
2. 在代码的# this is where I am stuck位置添加以下逻辑：

# 生成临时FDF替换文件
fdf_file <- tempfile(fileext = ".fdf")
# 构造FDF内容，替换指定文本
fdf_content <- sprintf('<<
/V(QUOTE DATE: %s)
/T(QUOTE DATE)
>>', formatted_quote_date)
writeLines(fdf_content, fdf_file)

# 生成输出文件名
output_pdf <- sub("\\.pdf$", "_modified.pdf", pdf_file)
# 调用pdftk替换文本并保存
system(sprintf('pdftk "%s" fill_form "%s" output "%s" flatten', pdf_file, fdf_file, output_pdf))
# 删除临时FDF文件
file.remove(fdf_file)
cat("Modified PDF saved to:", output_pdf, "\n")

注意：此方法要求PDF中的"QUOTE DATE"是可编辑的表单字段，若原PDF为纯文本无表单，需先将PDF转为可编辑表单，或结合OCR工具处理。

library(RDCOMClient)
library(pdftools)
library(stringr)

process_subfolders <- function(top_folder) {
  # Initialize Excel application
  xlApp <- COMCreate("Excel.Application")
  xlApp[["Visible"]] <- TRUE  # Set to TRUE for debugging purposes
  
  # List all files in the top folder
  all_files <- list.files(top_folder, recursive = TRUE, full.names = TRUE)
  
  # Filter for Excel files that start with 2 letters followed by 6 digits
  pattern <- "^.{1,2}\\d{6}.*\\.xlsm$"
  excel_files <- all_files[grep(pattern, basename(all_files), ignore.case = TRUE)]
  
  for (file_path in excel_files) {
    cat("Processing file:", file_path, "\n")
    
    xlBook <- NULL
    
    tryCatch({
      # Normalize path for Excel
      file_path <- normalizePath(file_path, winslash = "\\", mustWork = FALSE)
      
      # Open Excel workbook
      xlBook <- xlApp$Workbooks()$Open(file_path)
      
      if (is.null(xlBook)) {
        cat("Failed to open workbook:", file_path, "\n")
        next
      }
      
      # Get the date from cell C4 on the 'FURNITURE RENTAL' sheet
      xlSheet <- xlBook$Sheets("FURNITURE RENTAL")
      date_cell <- xlSheet$Range("C4")$Value()
      quote_date <- as.Date(date_cell, origin = "1899-12-30")
      
      # Format the date
      formatted_quote_date <- format(quote_date, "%A, %d %B %Y")
      
      cat("Quote date extracted:", formatted_quote_date, "\n")
      
      # Close the workbook
      xlBook$Close(FALSE)
      
      # Find the corresponding PDF file
      pdf_files <- all_files[grep("INVENTORY DOCKET.*\\.pdf$", all_files, ignore.case = TRUE)]
      
      if (length(pdf_files) == 0) {
        cat("No matching PDF files found for:", file_path, "\n")
        next
      }
      
      pdf_file <- pdf_files[1]  # Assuming the first match is the correct one
      
      cat("Processing PDF file:", pdf_file, "\n")
      
      # Extract PDF content（保留原页面结构）
      original_pdf_pages <- pdf_text(pdf_file)
      pdf_text_combined <- paste(original_pdf_pages, collapse = "\n")
      
      # Debugging output to inspect extracted PDF text
      cat("PDF text extracted:\n", pdf_text_combined, "\n")
      
      # Find and replace the quote date in the PDF
      old_date_pattern <- "(?i)QUOTE\\s*DATE\\s*\\b(\\w+, \\d+ \\w+ \\d{4})\\b"
      
      # Attempt to extract the quote date using the defined pattern
      extracted_date <- str_match(pdf_text_combined, old_date_pattern)[, 2]
      
      if (is.na(extracted_date)) {
        cat("No 'QUOTE DATE:' found in PDF\n")
        next
      } else {
        cat("Extracted quote date:", extracted_date, "\n")
      }
      
      # Replace the old quote date with the new formatted date
      modified_text_combined <- str_replace(pdf_text_combined, old_date_pattern, paste("QUOTE DATE: ", formatted_quote_date))
      
      # 拆分回原页面结构
      modified_pages <- strsplit(modified_text_combined, "\n\n")[[1]]
      original_page_count <- length(original_pdf_pages)
      # 调整页面数量匹配
      if(length(modified_pages) < original_page_count) {
        modified_pages <- c(modified_pages, rep("", original_page_count - length(modified_pages)))
      } else if(length(modified_pages) > original_page_count) {
        modified_pages <- modified_pages[1:original_page_count]
      }
      
      # 生成输出文件名
      output_pdf <- sub("\\.pdf$", "_modified.pdf", pdf_file)
      # 保存修改后的PDF
      pdf_write(modified_pages, output_pdf)
      cat("Modified PDF saved to:", output_pdf, "\n")
      
    }, error = function(e) {
      cat("Error processing file:", file_path, "\nError message:", e$message, "\n")
      if(!is.null(xlBook)) {
        xlBook$Close(FALSE)
      }
    })
  }
  
  xlApp$Quit()
  cat("All files processed.\n")
}

# Example usage
# top_folder <- "your/folder/path"
# process_subfolders(top_folder)

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