请求实现信实石油全22-23州加油站数据每日自动化爬取至Excel

Got it, let's break down how to solve this problem effectively. I'll cover two practical approaches—VBA (for Excel-native automation) and Python (for more flexible web scraping)—both of which can extract all state-wise fuel station data, aggregate it into one Excel sheet, and run automatically every day.

一、VBA实现方案 #

This approach uses Excel's built-in VBA with Internet Explorer (IE) to simulate user interactions (selecting states from the dropdown) and extract data.

1. Pre-requisites #

• Enable Excel's Developer tab (File > Options > Customize Ribbon > Check "Developer")
• Go to Developer > Visual Basic > Tools > References, then check:
  • Microsoft Internet Controls
  • Microsoft HTML Object Library

2. VBA Code #

Paste this into a new module in the VBA editor:

Sub ScrapeRelianceFuelStations()
    Dim ie As InternetExplorer
    Dim doc As HTMLDocument
    Dim stateDropdown As HTMLSelectElement
    Dim stateOption As HTMLOptionElement
    Dim stationRows As IHTMLElementCollection
    Dim row As IHTMLElement
    Dim cell As IHTMLElement
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Integer, j As Integer
    
    ' Initialize worksheet
    Set ws = ThisWorkbook.Sheets("FuelStations")
    ws.Cells.Clear
    ws.Range("A1:F1") = Array("State", "Station Name", "Address", "City", "Pincode", "Contact")
    lastRow = 2
    
    ' Launch IE
    Set ie = New InternetExplorer
    ie.Visible = False ' Set to True if you want to see the browser
    ie.Navigate "https://www.reliancepetroleum.com/locateafuelstation"
    
    ' Wait for page to load
    Do While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop
    Set doc = ie.Document
    
    ' Get state dropdown
    Set stateDropdown = doc.getElementById("state") ' Adjust ID if page changes
    
    ' Loop through all state options
    For Each stateOption In stateDropdown.Options
        ' Skip default/empty option
        If stateOption.Value <> "" Then
            stateOption.Selected = True
            ' Trigger search (simulate clicking the search button)
            doc.getElementById("searchBtn").Click ' Adjust button ID if needed
            
            ' Wait for results to load
            Application.Wait Now + TimeValue("00:00:03") ' Adjust wait time if needed
            
            ' Get station rows
            Set stationRows = doc.getElementsByClassName("station-row") ' Adjust class name if page changes
            
            ' Extract data from each row
            For Each row In stationRows
                ws.Cells(lastRow, 1) = stateOption.Text
                j = 2
                For Each cell In row.getElementsByTagName("td")
                    ws.Cells(lastRow, j) = cell.innerText
                    j = j + 1
                Next cell
                lastRow = lastRow + 1
            Next row
        End If
    Next stateOption
    
    ' Cleanup
    ie.Quit
    Set ie = Nothing
    Set doc = Nothing
    
    ' Auto-fit columns
    ws.Columns.AutoFit
    MsgBox "Scraping completed successfully!"
End Sub

3. Set Up Daily Automatic Execution #

1. Save your Excel file as a .xlsm (macro-enabled workbook)
2. Use Windows Task Scheduler to:
   • Create a new task that runs daily at your desired time
   • Set the action to "Start a program", select EXCEL.EXE
   • Add arguments: /e "C:\Path\To\Your\File.xlsm"
   • To auto-run the macro on open, add this to the ThisWorkbook module:

     Private Sub Workbook_Open()
         ScrapeRelianceFuelStations
     End Sub
     

二、Python实现方案 #

Python is more reliable for dynamic web scraping and automation. We'll use Selenium to handle the dropdown and extract data, pandas to aggregate it into Excel, and schedule for daily runs.

1. Install Dependencies #

Run these commands in your terminal:

pip install selenium pandas openpyxl schedule

• Download the ChromeDriver matching your Chrome version and place it in your Python path.

2. Python Code #

import time
import pandas as pd
from selenium import webdriver
from selenium.webdriver.support.ui import Select
import schedule

def scrape_fuel_stations():
    # Initialize driver
    driver = webdriver.Chrome()
    driver.get("https://www.reliancepetroleum.com/locateafuelstation")
    time.sleep(3)
    
    # Get state dropdown
    state_dropdown = Select(driver.find_element("id", "state"))
    all_states = [option.text for option in state_dropdown.options if option.text != ""]
    
    # Initialize empty DataFrame
    df = pd.DataFrame(columns=["State", "Station Name", "Address", "City", "Pincode", "Contact"])
    
    # Loop through each state
    for state in all_states:
        state_dropdown.select_by_visible_text(state)
        # Click search button
        driver.find_element("id", "searchBtn").click()
        time.sleep(3)
        
        # Extract station data
        station_rows = driver.find_elements("class name", "station-row")
        for row in station_rows:
            cells = row.find_elements("tag name", "td")
            station_data = {
                "State": state,
                "Station Name": cells[0].text,
                "Address": cells[1].text,
                "City": cells[2].text,
                "Pincode": cells[3].text,
                "Contact": cells[4].text
            }
            df = pd.concat([df, pd.DataFrame([station_data])], ignore_index=True)
    
    # Save to Excel
    df.to_excel("RelianceFuelStations.xlsx", index=False)
    print(f"Scraped {len(df)} stations successfully!")
    
    # Cleanup
    driver.quit()

# Set up daily execution (e.g., at 9 AM every day)
schedule.every().day.at("09:00").do(scrape_fuel_stations)

# Keep the script running
while True:
    schedule.run_pending()
    time.sleep(60)

3. Set Up Daily Automatic Execution #

1. Save the script as scrape_stations.py
2. Use Windows Task Scheduler to create a daily task that runs:
   • Program: python.exe
   • Arguments: "C:\Path\To\scrape_stations.py"

Important Notes #

• Page Structure Changes: If the target site updates its HTML (e.g., IDs, class names), you'll need to adjust the selectors in the code.
• Wait Times: Adjust time.sleep() or use explicit waits (in Selenium) if the page loads slower.
• Anti-Scraping: Avoid running the script too frequently to prevent being blocked. The daily schedule is reasonable.

内容的提问来源于stack exchange，提问作者Deepika.Gohil