如何从指定网页批量下载.xlsx文件并直接导入R DataFrame？（适配非固定链接模式场景）

Got it, let's break this down into two clear parts: first bulk downloading those tricky unpatterned .xlsx files, then importing them straight into R DataFrames without saving anything locally. Let's dive in!

First, we'll use R's web scraping tools to extract all .xlsx links from the page, even if they don't follow a consistent pattern.

For Static Webpages (Links in HTML Source) #

Most pages store links directly in the HTML, so we can use the rvest package to parse them:

# Install required packages if you haven't already
install.packages(c("rvest", "dplyr", "purrr"))
library(rvest)
library(dplyr)
library(purrr)

# Replace this with your target webpage URL
target_url <- "https://your-target-page.com"

# Fetch the page content
page_content <- read_html(target_url)

# Extract all <a> tag URLs, then filter for .xlsx files (case-insensitive)
xlsx_links <- page_content %>%
  html_elements("a") %>%  # Grab all hyperlink tags
  html_attr("href") %>%   # Extract the URL from each tag
  grep("\\.xlsx$", ., ignore.case = TRUE, value = TRUE)  # Keep only links ending with .xlsx

# Convert relative links to absolute URLs (critical if links don't start with http://)
xlsx_links <- url_absolute(xlsx_links, target_url)

Now download all files in bulk:

# Create a folder to store downloads (keeps things organized)
dir.create("excel_downloads", showWarnings = FALSE)

# Loop through each link and download
walk(xlsx_links, function(link) {
  # Pull the filename from the end of the URL
  file_name <- basename(link)
  # Download the file (mode = "wb" ensures binary files are saved correctly)
  download.file(link, destfile = file.path("excel_downloads", file_name), mode = "wb")
  cat("Downloaded:", file_name, "\n")
})

For Dynamic Webpages (Links Loaded via JavaScript) #

If the links only appear after the page runs JavaScript (e.g., lazy-loaded content), rvest won't see them. Use playwright (a modern browser automation tool) to simulate a real browser session:

install.packages("playwright")
library(playwright)

# Launch a headless Chrome browser
pw <- playwright$launch()
browser_page <- pw$chromium$new_page()

# Load the target page
browser_page$goto(target_url)
# Wait a few seconds for dynamic content to load (adjust timeout as needed)
browser_page$wait_for_timeout(3000)

# Extract all links just like before
xlsx_links <- browser_page$eval_handle(
  "Array.from(document.querySelectorAll('a')).map(a => a.href)"
)$json_value() %>%
  grep("\\.xlsx$", ., ignore.case = TRUE, value = TRUE)

# Close the browser
pw$close()

You don't need to save files to your computer first—R can read them straight from the web using readxl (and httr for edge cases).

Basic Method: Directly Read from URLs #

The readxl package supports URLs out of the box:

install.packages("readxl")
library(readxl)

# Import a single Excel file into a DataFrame
single_df <- read_excel(xlsx_links[1])

# Import all files into a list of DataFrames (one per file)
all_excel_dfs <- map(xlsx_links, read_excel)

# Optional: Name the list elements with the original filenames
names(all_excel_dfs) <- basename(xlsx_links)

Advanced Method: Handle Authentication/Headers #

If the website blocks direct URL access (e.g., requires a user-agent header or login), use httr to fetch the file first:

install.packages("httr")
library(httr)

# Custom function to import from protected URLs
import_protected_xlsx <- function(link) {
  # Send a request with a realistic user-agent to avoid being blocked
  response <- GET(link, add_headers(
    "User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
  ))
  
  # Stop if the request fails (e.g., 404 error)
  stop_for_status(response)
  
  # Read the binary content into a DataFrame
  read_excel(rawConnection(content(response, "raw")))
}

# Import all protected links
all_excel_dfs <- map(xlsx_links, import_protected_xlsx)

Quick Notes #

• If you need to combine all DataFrames into one, use dplyr::bind_rows(all_excel_dfs) (make sure columns match across files).
• Password-protected Excel files can't be read directly with readxl—you'll need to download them first and use a package like openxlsx to handle passwords.

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