原直接Excel URL失效，如何用R从落地页读取目标Excel？

Great question! Now that you can’t access the Excel file directly via a direct URL, you’ll need to first scrape the download link from the landing page. The rvest package is perfect for this—it’s built specifically for web scraping in R and makes parsing HTML content straightforward.

Here’s how to adapt your existing code to work with the new workflow:

Step 1: Install and Load Required Packages #

First, make sure you have rvest installed (alongside your existing packages):

# Install rvest if you haven't already
install.packages("rvest")

# Load all needed packages
library(ggplot2)
library(lubridate)
library(openxlsx)
library(reshape2)
library(dplyr)
library(rvest)

Step 2: Scrape the Download Link from the Landing Page #

We’ll fetch the landing page HTML, locate the link matching your target file name, and convert it to a full absolute URL:

# Define the landing page URL
landing_page_url <- "http://www.snamretegas.it/it/business-servizi/dati-operativi-business/8_dati_operativi_bilanciamento_sistema/"

# Fetch and parse the page content
page_html <- read_html(landing_page_url)

# Identify the link for your target Excel file
target_file_name <- "Dati operativi relativi al bilanciamento del sistema post Del. 312/2016/R/gas - Database 2018"
excel_link <- page_html %>%
  html_nodes("a") %>%  # Select all anchor tags on the page
  html_text() %>%      # Extract the text displayed for each link
  equals(target_file_name) %>%  # Find which link matches your target file name
  which() %>%          # Get the index of the matching link
  {html_nodes(page_html, "a")[.]} %>%  # Retrieve the corresponding anchor tag
  html_attr("href") %>%  # Extract the underlying URL from the href attribute
  url_absolute(landing_page_url)  # Convert relative paths to a full, valid URL

Step 3: Read the Excel File and Continue Your Workflow #

Now you can use the scraped link with read.xlsx just like your original code:

# Read the Excel file using the scraped link
Bilres <- read.xlsx(xlsxFile = excel_link, sheet = "Storico_G", startRow = 1, colNames = TRUE)

# Your existing data processing code (unchanged)
Bilres_df <- data.frame(Bilres$pubblicazione, Bilres$BILANCIAMENTO.RESIDUALE )
Bilres_df$pubblicazione <- ymd_h(Bilres_df$Bilres.pubblicazione)
Bilreslast <- tail(Bilres_df,1)
Bilreslast <- data.frame(Bilreslast)
Bilreslast$Bilres.BILANCIAMENTO.RESIDUALE <- as.numeric(as.character(Bilreslast$Bilres.BILANCIAMENTO.RESIDUALE))

Quick Notes: #

• If the landing page’s HTML structure ever changes (e.g., link classes or positions), you may need to tweak the html_nodes selector—but filtering by the exact file name keeps this approach fairly robust.
• url_absolute ensures you get a valid full URL even if the page uses relative paths for its links.

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