认证后从URL下载Excel至Pandas的调度兼容问题排查

Hey there, let's work through your problem step by step! I've run into similar portal file download issues before, so here's what's going on and how to fix it:

1. Why does file_url return JavaScript redirect HTML? #

That human-readable URL with the month in it is a portal-friendly path—it's not the direct link to the Excel file. Portals like this often use JavaScript to route you to the actual file location after verifying your session (even if you're already logged in). When you hit it with requests.get(), you're getting the portal's intermediate page that triggers the JS redirect, not the file itself.

Also, note the & in your URL—this is an HTML-encoded & character. You should replace it with a plain & first, otherwise the portal might not parse the path correctly:

file_url = "https://xyz.xyz.com/portal/workspace/IN AWP ABRL/Reports & Analysis Library/CDI Reports/CDI_SM_Mar'20.xlsx"

2. What's the logic behind file_url2? #

That random alphanumeric string is a direct, session-bound download link generated by the portal's share feature. It maps directly to the specific file you shared, but it's tied to that file's unique ID—not the path or month. That's why you can't just modify the month to get a new file; each file gets its own unique share link.

3. Solutions to download via the friendly file_url (for scheduled tasks) #

We need to either extract the real download link from the JS redirect page, or simulate a browser to handle the redirect automatically.

Option 1: Extract the real download link with requests + regex #

If the JS redirect is simple (like a window.location.href call), we can parse the HTML to get the actual file URL:

import requests
import re
from io import BytesIO
import pandas as pd

# Your existing login code
url = 'http://xyz.xyz.com/portal/site'
username = 'your_username' 
password = 'your_password' 
s = requests.Session()
headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36'}
r = s.get(url, auth=(username, password), verify=False, headers=headers)

# Fix the URL by replacing & with &
file_url = "https://xyz.xyz.com/portal/workspace/IN AWP ABRL/Reports & Analysis Library/CDI Reports/CDI_SM_Mar'20.xlsx"
r2 = s.get(file_url, verify=False, allow_redirects=True)

# Extract the real download link from the JS redirect HTML
html_content = r2.text
# Look for window.location.href = "REAL_URL" pattern
match = re.search(r'window\.location\.href\s*=\s*["\'](.*?)["\']', html_content)

if match:
    real_download_url = match.group(1)
    # Handle relative URLs by adding the base domain
    if not real_download_url.startswith('http'):
        real_download_url = 'https://xyz.xyz.com' + real_download_url
    
    # Now download the actual file
    r3 = s.get(real_download_url, verify=False, headers=headers)
    df = pd.read_excel(BytesIO(r3.content))
    print("Success! DataFrame preview:")
    print(df.head())
else:
    print("Couldn't find the redirect URL—try the Selenium method below.")

Option 2: Simulate a browser with Selenium (for complex redirects) #

If the JS redirect involves more portal logic (like dynamic session checks or multiple hops), Selenium will mimic a real browser and handle all the redirects automatically:

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import pandas as pd
import os
import time

# Configure Chrome to auto-download files without prompts
download_dir = '/path/to/your/download/folder'
options = webdriver.ChromeOptions()
prefs = {
    'download.default_directory': download_dir,
    'download.prompt_for_download': False,
    'plugins.always_open_pdf_externally': True # Prevent Chrome from opening Excel in-browser
}
options.add_experimental_option('prefs', prefs)

# Initialize driver and log in
driver = webdriver.Chrome(options=options)
driver.get('http://xyz.xyz.com/portal/site')

# Replace with your portal's actual login element IDs
username_input = WebDriverWait(driver, 10).until(EC.presence_of_element_located((By.ID, 'username')))
password_input = driver.find_element(By.ID, 'password')
login_button = driver.find_element(By.ID, 'login-btn')

username_input.send_keys('your_username')
password_input.send_keys('your_password')
login_button.click()

# Generate the month-specific URL (easy to modify for scheduled tasks)
month_year = "Mar'20" # Change this to Apr'20, May'20, etc.
file_url = f"https://xyz.xyz.com/portal/workspace/IN AWP ABRL/Reports & Analysis Library/CDI Reports/CDI_SM_{month_year}.xlsx"

# Navigate to the URL and wait for download to finish
driver.get(file_url)

# Function to wait for the file to download
def wait_for_download(filename):
    file_path = os.path.join(download_dir, filename)
    while not os.path.exists(file_path):
        time.sleep(1)
    # Wait a bit more to ensure the file is fully written
    time.sleep(2)
    return file_path

target_filename = f"CDI_SM_{month_year}.xlsx"
downloaded_file = wait_for_download(target_filename)

# Import into Pandas
df = pd.read_excel(downloaded_file)
print("Success! DataFrame preview:")
print(df.head())

# Clean up
driver.quit()

Bonus: Automate month generation for scheduled tasks #

Use Python's datetime module to generate month-year strings dynamically, so you can schedule this to run monthly without manual changes:

from datetime import datetime, timedelta

# Get last month's abbreviation and year (e.g., Mar'20 for April 2020)
last_month = datetime.now() - timedelta(days=30)
month_year = last_month.strftime("%b'%y") # Gives something like "Mar'24"

# Generate the URL
file_url = f"https://xyz.xyz.com/portal/workspace/IN AWP ABRL/Reports & Analysis Library/CDI Reports/CDI_SM_{month_year}.xlsx"

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