R语言批量处理Excel产品规格表单：lapply应用方法求助

Hey there! Let's get that batch processing working for you—you're already off to a great start with your single-file workflow and list.files() setup. The key fix here is making your extraction function self-contained so it can handle each file path passed to it via lapply. Here's a step-by-step solution:

Step 1: Fix Your Extraction Function #

Your current form.extract relies on a global variable (product_tech) which won't work in a loop. Let's rewrite it to take a file path as input, handle reading/cleaning the file internally, and extract all your needed fields:

# Load required libraries first (install them if you haven't yet!)
library(readxl)
library(dplyr)
library(janitor)
library(writexl)

# Updated self-contained extraction function
form.extract <- function(file_path) {
  # Read the Excel file and clean column names
  product_tech <- read_excel(file_path, sheet = "Form") %>% 
    clean_names()
  
  # Extract fabric supplier name (adjust row/column to match your actual data)
  fabric_supplier_name <- product_tech %>% 
    select(x5) %>%  # Use the cleaned column name from clean_names()
    slice(3) %>%    # Select the row containing the data
    rename(fabric_supplier_name = x5)
  
  # Extract date (update select/slice values to match your Excel structure)
  date <- product_tech %>% 
    select(x1) %>%  # Replace with correct cleaned column name
    slice(1) %>%    # Replace with correct row number
    rename(date = x1)
  
  # Extract address (same note: adjust to your actual data)
  address <- product_tech %>% 
    select(x2) %>%  # Replace with correct cleaned column name
    slice(2) %>%    # Replace with correct row number
    rename(address = x2)
  
  # Combine all extracted fields into a single row
  combined <- bind_cols(date, fabric_supplier_name, address)
  
  # Add a column to track which file this data came from (super helpful for debugging!)
  combined <- combined %>% 
    mutate(source_file = basename(file_path))
  
  return(combined)
}

Key Fixes in the Function: #

• Self-contained: Reads the file directly from the input path, no more reliance on global variables.
• Clearer syntax: Uses dplyr verbs (select, slice, rename) instead of base R indexing for readability.
• Error-proofing: Added a source_file column to trace data back to its original Excel file.

Step 2: Use lapply to Process All Files #

Now that your function is ready, you can loop through all your files with lapply:

# Get list of all Excel files (your existing code is good here!)
files <- list.files(
  path = "C:/Users/me/productspecfolder",
  pattern = "*.xlsx",
  full.names = TRUE,
  recursive = FALSE
)

# Process every file in the list
all_processed_data <- lapply(files, form.extract)

# Combine all individual results into one master data frame
final_combined_data <- bind_rows(all_processed_data)

# Optional: Export the combined data to a new Excel/CSV file
write_xlsx(final_combined_data, "C:/Users/me/combined_product_specs.xlsx")
# Or for CSV: write_csv(final_combined_data, "C:/Users/me/combined_product_specs.csv")

Step 3: Handle Potential Errors (Optional but Recommended) #

If some of your Excel files have slightly different formats, the loop might break. Use purrr::safely to handle errors gracefully:

library(purrr)

# Wrap your function to catch errors without stopping the loop
safe_extract <- safely(form.extract)
results_with_errors <- lapply(files, safe_extract)

# Separate successful results from errors
successful_results <- map(results_with_errors, "result") %>% compact()
error_details <- map(results_with_errors, "error") %>% compact()

# Combine successful results
final_combined_data <- bind_rows(successful_results)

# Check which files failed (if any)
cat("Failed files:\n")
print(names(error_details))

Quick Notes for You: #

• Double-check the select and slice values for date and address—these need to match where those fields live in your actual Excel forms.
• If you get missing column errors, run clean_names() on a single file first to confirm the exact column names your function should target.

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