使用pandas读取多工作表Excel文件：如何从OrderedDict提取行列数据

Hey there! No worries—handling the OrderedDict returned by pandas when reading multi-sheet Excel files is actually super straightforward. Let me walk you through exactly how to access your data, extract rows/columns, and get individual values.

When you use pd.read_excel('your_file.xlsx', sheet_name=None), pandas returns an OrderedDict where:

• The keys are the names of your Excel worksheets (preserving the same order they appear in the file)
• The values are full pandas DataFrame objects for each sheet.

This means you don’t need any special handling for the OrderedDict itself—treat it like a regular dictionary, with the added benefit of keeping sheet order intact.

You can grab a specific sheet directly by name, or loop through all sheets to process them one by one:

Grab a single sheet directly #

import pandas as pd

# Read all sheets into an OrderedDict
sheet_dict = pd.read_excel('your_data.xlsx', sheet_name=None)

# Get the DataFrame for the "SalesData" sheet
sales_df = sheet_dict["SalesData"]

Loop through all sheets #

If you need to process every sheet in the file, use the items() method to iterate over sheet names and their corresponding DataFrames:

for sheet_name, df in sheet_dict.items():
    print(f"Now processing sheet: {sheet_name}")
    # Add your data processing logic here

Once you have a DataFrame (like sales_df above), use standard pandas methods to pull out the data you need:

Extract a column #

Use either the column name (most common) or positional index:

# By column name
customer_names = sales_df["CustomerName"]

# By position (e.g., 2nd column, 0-indexed)
second_column = sales_df.iloc[:, 1]

Extract a row #

Use the row index name (if your rows have labels) or positional index:

# By row index name
january_sales = sales_df.loc["Jan-2024"]

# By position (e.g., 5th row)
fifth_row = sales_df.iloc[4, :]

Extract a single value #

Target a specific cell using either labels or positions:

# By row and column labels
jan_customer_sales = sales_df.loc["Jan-2024", "TotalSales"]

# By positions (3rd row, 4th column)
specific_value = sales_df.iloc[2, 3]

If you ever forget what sheets are in your OrderedDict, just print the keys to get a list of all sheet names:

print(sheet_dict.keys())

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