如何用Python通过URL获取XLSX文件的首行单元格数据？

Hey there! Let's break down why you're seeing those weird PK symbols and how to get the actual cell data you want.

Why You're Seeing "PK [Symbols]" #

First off, here's the quick reason: XLSX files aren't plain text files like your typical .txt or .csv. They're actually ZIP-compressed archives that store table data in XML files under the hood. When you use iter_lines() to read the raw bytes of an XLSX, you're looking at the start of the ZIP archive (the PK is the signature for ZIP files) instead of structured table content—hence those unprintable symbols.

Solution: Use Excel-Specific Libraries #

To pull actual cell rows from an XLSX file loaded via URL, you need a library that can parse the XLSX format properly. Two popular, reliable options are pandas (great for easy data manipulation) and openpyxl (ideal for granular control over Excel files).

Option 1: Using Pandas (Simplest for Data Access)

First, install the required packages if you haven't already:

pip install pandas openpyxl

Then adjust your code to load and parse the XLSX correctly:

import requests
import pandas as pd
from io import BytesIO

def get_excel_data(url):
    # Fetch the XLSX file as binary content
    resp = requests.get(url)
    # Convert binary content into a file-like object pandas can read
    excel_file = BytesIO(resp.content)
    # Read the Excel file into a structured DataFrame
    df = pd.read_excel(excel_file)
    return df

# Example usage
xlsx_url = "your_xlsx_file_url_here"
df = get_excel_data(xlsx_url)

# Get the header row (column names)
print("Header row:", df.columns.tolist())
# Get the first row of data (use iloc[n] for the nth data row)
print("First data row:", df.iloc[0].tolist())

If you want to yield rows one at a time (matching your original generator function style), tweak it like this:

def yield_excel_rows(url):
    resp = requests.get(url)
    excel_file = BytesIO(resp.content)
    df = pd.read_excel(excel_file)
    # Yield each row as a dictionary (swap to row.tolist() for a list instead)
    for _, row in df.iterrows():
        yield row.to_dict()

# Example usage
rows = yield_excel_rows(xlsx_url)
print(next(rows))  # Grab the first data row

Option 2: Using OpenPyXL (More Control Over Excel Structure)

If you need finer control (like targeting specific sheets or accessing cell formatting), use openpyxl:

Install it first:

pip install openpyxl

Then the code:

import requests
import openpyxl
from io import BytesIO

def yield_excel_rows(url):
    resp = requests.get(url)
    excel_file = BytesIO(resp.content)
    # Load the workbook from the file-like object
    wb = openpyxl.load_workbook(excel_file)
    # Use the active sheet (or specify by name: wb["Sheet1"])
    sheet = wb.active
    # Yield each row (values_only=True returns cell values instead of cell objects)
    for row in sheet.iter_rows(values_only=True):
        yield row

# Example usage
rows = yield_excel_rows(xlsx_url)
print(next(rows))  # Header row
print(next(rows))  # First data row

Key Takeaway #

You can't read XLSX files like plain text—they're compressed binary formats. Libraries like pandas or openpyxl handle the decompression and XML parsing for you, so you can access clean, structured cell data directly.

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